SQL Server Consolidation Options

  • Article

Consolidation of server is a very hot topic as server rooms fill up and cooling becomes ever more expensive. Virtualization is the current darling in this space and indeed it is fantastic for many application workloads.  However it is not the only solution to the problem and this is particularly true for SQL Server. There are several tools available form Microsoft as well as para virtualization solutions like HP’s Polyserve.  Looking at the Microsoft solutions in a little bit more detail:

  • Consolidate databases using SQL Server instances.  This has been a popular approach because you can assign CPU and memory to an instance to guarantee the resource each database will get.  The down side is that if you split your machine into instances like this and one instance is not working that hard the other instances can’t get at the unused resources even if they are under pressure.   You also have to patch both instances here and so the maintenance can be a bit tricky.
  • Consolidate databases into one instance of SQL Server.  This is not so popular as up until now you have no control over resource contention when the server is under pressure.  However with SQL Server 2008 there is resource governor in the enterprise edition which allows for fine grain control of memory and CPU, (but not IO yet). 
  • And then of course there is virtualization, which is being widely used, but here again you are carving up a physical machine in terms of cores, RAM etc. so a virtual machine can only ever use what is allocated to it no matter how busy the other parts of it are.

One thing to note on all of these approaches this is network performance. If you had 2 servers before and you only have 1 now then unless you have multiple network card in the new box the two instances will have contend for the network card. 

There is a white paper on SQL Consolidation here

Finally the licensing of all of these options can affect your decision. Enterprise edition makes this quite simple as you license the physical machine per CPU, so you can have as many SQL Server instances, SQL Server virtual machines (any vendor not just Hyper-V) as you can cram on there.

Technorati Tags: SQL Server 2008,consolidation,virtualization,instances,resource governor