This content is part of the Essential Guide: Guide to SQL Server virtualization best practices
Problem solve Get help with specific problems with your technologies, process and projects.

SQL Server consolidation good -- not checking metrics bad

SQL Server consolidation could save your company big money, writes expert Denny Cherry, but first you’ll need to examine metrics like concurrent connections, buffer pool size and drive space.

In today’s world of low-cost, high-powered servers, SQL Server consolidation is a topic that should be on everyone’s mind. Especially when you take the sluggish economy into account, doing more with less hardware looks attractive. Consolidating several databases that are on different physical servers into a single SQL Server instance on one server requires examining a few key metrics to make sure everything fits together nicely.

Concurrent connections
The number of concurrent connections that the consolidated SQL Server will need to handle is something not everyone remembers to check. SQL Server can only handle so many concurrent connections at a time. If you have a bunch of SQL Server databases with several thousand concurrent connections at a time and you put all those applications on the same SQL Server, that could cause a problem: SQL Server won’t have enough concurrent connections available. The easiest way to check the number of concurrent connections is to monitor the “User Connections” performance monitor counter under the “SQLServer:General Statistics” counter group. Monitor this statistic at the highest load time for the servers you wish to consolidate. When determining the number of concurrent connections, don’t forget that each database connection requires memory to maintain. You can track the amount of memory used to maintain connections by checking the “Connection Memory (KB)” performance monitor counter under the counter group “SQLServer:Memory Manager”.

Buffer pool size
When consolidating databases onto a single server, remember that the buffer pool that used to be on separate servers will now be shared. Let’s say you have five servers you want to consolidate into one, and each of the old servers has 16 GB of random access memory (RAM). The new server has 32 GB of RAM, and you may end up with a buffer-pool-size problem. That will then increase the storage I/O needs because the data will be flushed from the buffer pool more often. You can track how much memory is being used by the buffer pools by looking at the “Database Cache Memory (KB)” performance monitor counter under “SQLServer:Memory Manager”.


Procedure cache size
Along with the buffer pool possibly getting smaller is the procedure cache size. In the same five-servers-onto-one example, there is less procedure cache available on the new, consolidated server than on the five smaller servers combined. This could lead to an increase in the number of compiles and recompiles per second which will lead to an increase in CPU processing power being used. Track how much memory is being used by the procedure cache by looking at “SQL Cache Memory (KB)” under “SQLServer:Memory Manager”.

Drive space
The amount of disk space needed is the easiest thing to figure out during SQL Server consolidation. You can simply look at the amount of space you have in use today and the amount that you had in use a few months ago, and then make an estimate about how much room you’ll need in the future. As long as the new server has enough space to hold all the data from the old servers (plus room for growth) you are good to go.

Storage I/O
Storage I/O is another metric that needs to be accounted for -- especially if the buffer cache will be reduced, as this will increase storage I/O requirements. You’ll want to start by looking at the current I/O for each disk on each of your SQL Servers by looking at the “Disk Reads/sec” and “Disk Writes/sec” counters under “PhysicalDisk”. When planning your consolidated SQL Server, make sure that the new server can handle the combined I/O requirements of all the servers you’ll be moving onto it. If the buffer cache is being reduced, don’t forget to account for the increased I/O you’ll be seeing on the new server.

You can test the available performance of the new server by using Microsoft’s SQLIO tool to push I/O to the disks of the new server; that way, you can see what sort of performance you’ll get from the new system. If the I/O numbers you are getting from the new SQL Server aren’t as high as the combined numbers from the old server, don’t move those servers onto the new one.

SQL login names
When you are consolidating database applications onto a single SQL Server, there are bound to be conflicts with database login names. If the login names conflict, there are a few things you can do. The first and probably worst option is sharing a single login across multiple applications. The second is changing a login for one of the applications so that there isn’t a conflict. The third is waiting for the SQL Server 2012 release and using the partially contained databases feature to have the same username for multiple applications.

CPU load
Another important metric to track is the CPU load required to run each SQL Server. When all the SQL Server databases are combined onto the new hardware, there has to be enough processing power available to handle the work. This number is a little harder to figure out. First you need to figure out how much processing power each of the old servers has. Applications like CPU-z can tell you how many gigahertz (or megahertz if you are on a really old server) the server has. Then take the CPU percentage used and multiply that by the total CPU power of your SQL Server. This will give you an idea of how much CPU power the server needs. The same can be done for the new server to see how much CPU power is available. Then use basic math to see if the old servers will fit onto the new one.

Last word on SQL Server consolidation
Consolidating database applications can be a big money saver when it comes to removing old servers from the data center. This can save you SQL Server licenses, hardware costs and administrative overhead, provided that the consolidation is done correctly. But remember: Assess all the right metrics or your SQL Server consolidation project could fail.

Denny Cherry is an independent consultant with more than a decade of experience working with SQL Server, Hyper-V, vSphere and enterprise storage systems. A Microsoft Certified Master and MVP, Cherry has written books and articles on SQL Server management and integration. Check out his blog, SQL Server with Mr. Denny.

Dig Deeper on Microsoft SQL Server Consolidation and Virtualization

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.