I have been at a couple of trade shows over the last couple of weeks and a consistent theme has emerged, the reluctance of DBA’s to virtualise SQL Server, whether or not that is Hyper-V.  If you fall into this camp please read-on…

Hopefully we can agree that many organisations are worried about SQL server databases popping up on lots of servers and that this borne out of the need to:

• optimise the licenses you need to buy
• manage all of this
• control access to this data  and possibly audit it

The traditional method of consolidation for SQL Server has been to a two tiered approach:

1. Try and co-locate databases on one installation of SQL Server.
2. If this isn’t possible then run multiple instances of SQL Server on one machine.This allows you to:

• Assign CPU and memory to each instance.
• Further manage memory and CPU with windows resource manager (introduced in Windows Server 2003)
• Isolate sysadmin privileges.
• Run different versions of SQL Server alongside each other albeit on the same operating system.

Virtualisation is simply an extension of this approach. It isolates the whole environment at the operating system level not only from other environments on the physical machine but also from the physical hardware itself.   This not only allows many lightly loaded servers to be combined onto one lump of tin, but also allows the movement of these to any server running virtualisation without changing them as required to balance load of for maintenance purposes.

Typically this is initially done in the dev and test servers allowing complete production environments to be quickly created. However in most of the community events I go to 20-30% of the audience now have SQL Severs running in virtual machines in production.

The primary reason many DBA’s object to virtualisation is the loss in performance they will suffer.  However many servers are only under 10-20% load so combing 3-6 of these onto one server is often possible.  Of course the virtualisation process (known as the hypervisor) must use some resource but this is typically only about 10%.

Research and advice on how to get the most out of SQL server on Microsoft’s Hyper-V virtualisation platform is here which is basically:

• Not to over commit CPU or memory. Below are some simple rules around processors in Hyper-V in Windows Server 2008 R2

• Use fixed or pass through hard disks i.e. NOT dynamically expanding disks. Pass through disks are simply a logical pointer to a LUN on a SAN or other storage so the database itself is still on a physical disk.
• Remember to provision enough network bandwidth when you are consolidating physical machines

BTW there is similar advice and guidance for this from Vmware here.

So basically the flexibility and power of virtualisation comes at small (10%-ish ) cost which for pretty everyone else in the IT world is a price worth paying.  Of course you still  have to manage support and tune in this new world and I’ll cover that in my next post.