Demonstrate a SQL Server Architecture with Lego

By: Graham Okely   |   Comments   |   Related: > 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

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.)

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			

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'			

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.

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

Then you will see the RAM displayed.

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)

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.

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.

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?

Here is a link to a Lego Kanban board.

Next Steps

• Attached is a simple stud calculator for RAM.
• How can you track your dev, test and prod migrations?
• Here is an example of how I explained SQL Server High Availability.
• For details on how to install SQL Server 2016 on a Windows failover cluster see these series of tips by Edwin Sarmiento.

About the author

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

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips