A database is only as good as the operating system it runs on
Simon and the rest of my division regularly rag me about how I go on about databases all the time so when Sarah asked me for a post on virtualisation I decided to ignore her and talk about SQL Server instead!
Databases are one of the last things to be virtualised especially the larger ones and this is often down to performance and the ability to guarantee that performance. So what are the barriers to that in virtualisation?
The obvious limit in hyper-V is that you can only assign 4 logical processors to a virtual machine, the upside being that you will get more or less 4 times the processing in a virtual machine than if you just give it one. I mention this because this isn’t always the case with other hypervisor, indeed it can be slower to add more logical CPUs in some cases. Note that Microsoft best practice is not to over commit CPU by more than 2:1 on a machine running SQL Server (more on this here).
All this changes in Windows Server 2012; you can assign up to 64 logical processes to a virtual machine and virtual machines are also NUMA aware so that SQL Server in a VM will also be NUMA aware.
Storage performance is also an issue and stories abound of SAN engineers and DBAs arguing when performance issues arise so it’s no wonder that the additional complexity of virtualisation is avoided. Today this additional complexity actually isn’t an issue, you simply use pass through disks to directly reference a LUN on a SAN and put the database files on as many of those disks as you decide so that the database files are actually outside of the virtual machine. This makes management easier and also gives the best performance – typically SQL Server will run at about 90% of the physical host it is running on i.e. you loose 10% of the power of your server.
However at TechEd this year there was a demo of 1 million iops inside a virtual machine and I am sure this will be improved upon.
This also changes in Windows Server 2012 in that you can store databases and even virtual machines on file shares using the new SMB 3 protocol. This turns your file servers into a SAN with near SAN performance but more importantly with per counters that make sense to DBA trying to tune database performance. One other change is that Virtual Hard Disks can now be up to 64TBs in size with the new VHDX format.
Of course having a high performance SQL Server VM is all very well but what your users see is dependant on how good the networking is. Modern servers typically have multiple network interfaces (NICs) and currently if you want to team those into one fat pipe to shower your users with data then you are going to rely on the teaming software that comes with those NICs.
Yet another change in Windows Server 2012 is the ability to team disparate NICs in the operating system and then through group policy regulate Quality of Service over that team, ensuring that SQL Server networking gets a high priority.
So that gets rid of many concerns I have heard from DBAs about virtualising SQL Server, but why bother anyway?
The main reason for virtualising SQL Server in my opinion is to improve availability and agility by decoupling SQL Server form the physical host. For example a SQL Server VM can be moved around as desired without interrupting the service using live migration in Hyper-V. One reason to do this is to patch the underlying server or swap it out. With the upcoming changes to System Center 2012 in sp1 you can even move a SQL Server virtual machine right out of your data centre to Azure or to a hosting provider in a couple of clicks.
Windows Server 2012 has other options for moving VMs around as well; for example live migrations without the need for shared storage, and replica where you can have a copy of a virtual machine you can failover to, on another server. These options compliment but do not replace the need for high availability in SQL Server itself in that the virtual machine and underlying fabric might be OK but there’s something wrong in a SQL Server instance itself or because SQL Server itself needs to be patched or upgraded. So you might well have a cluster of SQL Server virtual machines running on a cluster of physical servers (known as guest clustering) or use mirroring Always On etc. as well to provide continuous availability of your database applications regardless of what goes wrong with your infrastructure.
Which is why I think the best place for SQL Server 2012 is running as a Windows Server 2012 virtual machine, and while you may disagree I would hope that you will have at least tried and can tell me what the issue is before you comment below. Actually you should probably try Windows Server 2012 anyway whether as a the host or guest operating system for your databases or at least come along to the launch event or one of our IT camps