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
Requires Free Membership to View
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.
ABOUT THE AUTHOR
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.
This was first published in January 2012
Join the conversationComment
Share
Comments
Results
Contribute to the conversation