Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

Special Announcement: SQL Server Performance Tuning Tips and Tricks Webinar
 

Demonstrate a SQL Server Architecture with Lego


By:   |   Read Comments   |   Related Tips: More > Upgrades and Migrations

Problem

We built a brand-new SQL Server 2016 cluster to move databases from an older SQL Server version and older hardware, but I had a hard time convincing the database owners to move their databases to the newer and faster hardware.  So, I built a model to show them the differences.

Solution

I built a Lego model of the old server and the new system and kept it on my desk to improve the uptake of the shiny new clustered SQL Server 2016 and this helped give people a visual of what they had and what they were getting.

Make the new system more impressive

When emailing the message ‘Please move your database off the old system.’, it had little affect, so I decided to make it more visual and in a creative moment, building it with Lego came to mind.

We had a legacy SQL Server 2008R2 server with 4 CPU and 12 GB of RAM. It ran the CPUs very high. We had three instances of SQL Server on the server and too many systems had their databases hosted there. It was clearly overloaded.

A new SQL Server 2016 cluster was built on physical servers with 32 CPUs and 256 GB of RAM.  It had a better design having nodes in data centers a few miles apart and they were on separate power supplies. Clearly, on paper and email, it was a superior solution.

So, how could I encourage the application owners to work with me and migrate their databases?

Communications options

Method Pros Cons
Email Quick and easy to send. Did not really show how much better the new system was. They were not taking it up.
Verbal Quick and easy. If the application owners are busy then the message is slow to get through.
Lego Model Visible, different, colorful. None.

Build a model

When it came to a design of the model I went for the simplest I could think of. I wanted to contrast the old and new, not build an engineering master piece.

Choosing a base for the model

I wanted to be clear that we had a cluster, so I used two bases to reflect the two data centers.  (I already had Lego part 53588 Baseplate Mountain 16 x 16 Raised with Shaped 10 x 10 Hole and 4 Peg holes.)

lego bases

Adding CPU to the model

For the CPUs, I found 1-stud could represent 1 CPU easily. That meant a 4-stud block for the old system and 32 studs for each of the nodes for the cluster.

You can use this T-SQL to find out how many CPUs are in your system:

Select cpu_count
From sys.dm_os_sys_info
GO			
cpu count

However sometimes CPUs can be allocated to individual instances by using an affinity mask. We can use this T-SQL to show which CPUs, of the currently active cluster node, are allocated to the instance we are connected to.

select SERVERPROPERTY('ComputerNamePhysicalNetBIOS') as 'Active Cluster Node'
,cpu_id, status, is_online 
from sys.dm_os_schedulers 
where status = 'VISIBLE ONLINE'			
cpu count

For more information, see tip 4801 by Kun Lee on why SQL Server might not use all assigned CPUs on a VM. Also, see tip 3040 by Simon Liew on how to hot-add a vCPU to a virtual SQL Server.

Adding RAM to the model

Each node in the new system had 256 GB of RAM. If I made 1 stud represent 12 GB of RAM then about 21 studs would represent 256 GB. If I used 8 stud blocks then that would be about 3 blocks.  That will be good enough. I am trying to keep it simple!

There are many ways to determine how much RAM your servers have. You can ask whoever supplied the server to tell you how much RAM is has. Some servers have the amount RAM displayed on the desktop when you log in. You can also start Task Manager and click on the performance tab to see the amount of RAM.

cpu

Another method is to right click on the Server / PC icon and choose properties of the server as shown below.

server properties

Then you will see the RAM displayed.

ram

The amount of memory the SQL Server instance ends up using can be tracked by monitoring memory counters via a SQL Server job using tip 2188 by Tim Ford.

To find out how much RAM is currently allocated to your instance use this T-SQL:

EXEC sp_configure 'show advanced options', 1;
RECONFIGURE WITH OVERRIDE;
EXEC sp_configure 'max server memory' 
GO
EXEC sp_configure 'show advanced options', 0
GO
RECONFIGURE WITH OVERRIDE;
GO			

When "Maximum server memory" is set to 2147483647 MB, this is the default value and SQL will use whatever is available.

See tip 4182 by Sergey Gigoyan for advice on setting a fixed amount of memory for SQL Server.

Data Centers Cluster Networking

I selected a long white connector to represent the network connectivity between the two nodes. (I had Lego part 47122 brick white Modified 2 x 24 with Peg at each end)

lego block

Databases

I choose to go with the SSMS color of gold for the databases. A basic shape that could be placed on either node.

Assembling the model

  • Add the CPUs to each of the baseplates.
  • Add the RAM next to the CPUs on the baseplates.
  • Connect the baseplate with the network connector.
  • Place the databases over either of the nodes.
  • The labeling was the most difficult part. I used a labeling machine at first then I cut up sticky labels for each part.

Here is my model of the old system with 4 CPUs in blue studs and 12 GB of RAM as one white stud and the databases in yellow.

lego model

Models for comparison

For the new system, there are two nodes, 32 CPU (blue), 256GB RAM (white), networking (white), data center (grey) versus the smaller model with the green base for the old system.  The new system is clearly much more impressive.

lego model

Impact

It had the right affect. Application owners were dropping past to correct my model and tell me what was what. They were taking responsibility for their systems.

Each time a database was moved to the cluster I moved the database or stack of yellow blocks as well.

Imagine

Where will your imagination take you?

lego model

Here is a link to a Lego Kanban board.

Next Steps


Last Update:


next webcast button


next tip button



About the author
MSSQLTips author Graham Okely Graham Okely is a contract SQL Server DBA and has been working with database systems since 1984 and has been specializing in SQL Server since 2007.

View all my tips
Related Resources





Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Email me updates 


Signup for our newsletter
 I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



    



Learn more about SQL Server tools