Why should I virtualize SQL?

For the future.

I have run across countless admins who have said “SQL should not be virtualized, hypervisors just cannot handle the load”. Well, that is one of the most common reasons I hear not to virtualize SQL. Is it true? It depends. A poorly planned virtualized SQL environment can suffer from performance issues, just like a physcial environment. There is no cookie cutter plan or guide that will tell you how your environment should be laid out, only guide lines to help you gain the most potential from your virtualized SQL environment. If you find instructions on how to virtualize SQL, take it as a guide and not a plan set in stone. Some companies may want to use only iSCSI storage vs fiber channel or some companies may opt for SQL clusters vs single instance servers. There are countless other options like using the paravirtualized controller, physical or virtual RDM, run a SQL environment in a cluster (up to 5 nodes in vSphere 5.1), single instance SQL servers, proper zoning of the shared disks in a cluster, vNIC types. And most important, do all of the DBAs / network / VM admins have a clear understanding of how the environment should be laid out for maximum performance.

Consolidation has been the biggest driver in virtualizing. Big tier 1 applications should not be immune from this. As hardware becomes EOL, we always have to plan for migrations. It takes less overhead if you have to move a virtual server from one hardware platform to another rather than spending man hours working with the application server itself. For the future, I try to always put applications on a virtualized platform because of the benefits.

I have heard and seen many of the negatives in forums and from other admins. Here are some of the horror stories:

1. One admin was running a virtualized SQL environment in a MSCS cluster. The shared quorum and data disks were zoned and properly setup in the cluster with physical compatibility mode. Things had been running smoothly for months. A VM admin came along and noticed some disks in vCenter and decided to report them as storage that can be reclaimed by the storage team. As soon as the storage team removed the disks, the virtualized SQL environment failed. My question is this: How do you prevent this in a physical environment? This scenario can easily happen in a physical cluster outside of VMware or any other hypervisor. It sounds like a undocumented process that the VM admin was unaware of. The VM admin should have also investigated it further. The storage admins should have documentation on what was provisioned for each server. Was the hypervisor responsible for this outage? You decide. If there is not good communications between the DBAs, VM admins and storage admins, it may not be a good idea to virtualize SQL (or any server).

2. SQL just doesn’t performance well. I have heard this from countless admins at local VMUGs, but none can point to a direct cause of the performance issue. If you are over provisioning the hypervisor, starving SQL for resources, not setting resource reservations, not properly configuring storage, not taking advantage of NUMA, have improperly configured physical NICs or you are just running SLQ on VM workstation (silly), I promise you will suffer performance issues. There is no good answer you can give about a poorly performing virtualized SQL environment, any one of the items listed could be the culprit. Having a good monitoring system in place like vFogLight can give you insight to these issues. In a perfect world a virtualized SQL environment would be on an auto tiering storage array, have all 10GB physical NICs, be on fiber channel SSD and all housed in a dedicated hypervisor cluster with all like application servers. Since we don’t all have this, it is up to you to determine how to best run a virtualized SQL environment in your infrastructure if you can at all.

3. Virtualizing SQL creates to much complexity. DBAs have a hard time managing performance because they don’t understand VMware. Are there more steps in setting up a MSCS SQL environment in VMware? Yes. For a VM admin this should be a routine process. The end user or DBA should be oblivious as to whether the server is virtual or physical. At least in a virtual server you can hot add resources. Troubleshooting does involve one more person in the role, the VM admin.

It all comes down to this: Can your teams can support it?

Can a NASCAR vehicle keep going all day? Not without a good pit crew! The same goes for any virtual or physcial infrastructure.

To have a successful project to virtualize SQL, all teams must have an open mind and understanding of how the hypervisor should properly function in your environment. Expectations should be set as well. Creating a virtualized SQL environment does not always mean better performance. It depends on what you are coming from and going to. There is a big difference in moving from an old Dell 2850 with 32BG of RAM in a 2 node MSCS SQL cluster and direct attached SCSI storage to a Dell M820 Blade with 768GB of RAM in a 5 node virtualized MSCS SQL cluster and a Pure Storage array (SSD) with fusion IO cards. Don’t let the storage admins or the DBAs scare the CIO with horror stories of failed implementations. The Freddy Kruger hypervisor is coming to get you! There are plenty of marketing white papers out there with success stories. I am not published in any VMware white papers for doing SQL clusters, but I can tell you that I have been doing it successfully for years!

In my next post I will be walking through the process on how to P2V an existing SQL 2 node cluster. I will be virtualizing two SQL blades that are using an EMC fiber channel array for shared storage.

Please review the Microsoft SQL server on VMware Best Practice Guide.