SQL Server Hyper-V Virtual Machines, Where do I put the Cluster?

  • Article

I remember the old days, when I wasn’t a fan of Installing SQL Server on Virtual Machines, but in todays world thanks to Hyper-V 2012 (and I guess VMWARE too), a lot of the resource bottlenecks for virtual machines has disappear. However still it’s not for every workload.

Recently I was on one of my clients that has a kind of big Virtual Shop on Hyper-V 2012R2, and he ask me. Well I’m planning the Installation of Multiple SQL Server for some management applications on a few VMs, how should I provide the High Availability for this?

That was a good question, we had many options, but we rapidly decided to have some kind of a Cluster, now we had two options, Clustering the Hyper-V Virtual Machines or doing a SQL Guest Failover Cluster between Virtual Machines.  

The first one is a good option to mitigate downtime when the hardware failed, if one of the Hyper-V Virtual Machines Node failed, the Virtual Machines will just failover to another node, but what would happen just when the SQL Services failed? It wouldn’t trigger a failover, at least not by default, I guess we could create some kind of script to monitor the SQL Services and then trigger a VM Failover, but It doesn’t sound right. Now in the other hand we have Live Migration on this configuration, that allows us to move virtual machines with 0 downtime.

The second one is a great option, and have all the usual benefits of a SQL Server Failover Cluster, just use the 2 Virtual Machines, and create a SQL Cluster between this 2, this is called a SQL Guest Failover Cluster.

                   

One of the question was could I combine the 2 options, and yes you could, but take into consideration the following:

  • Your could Use a lot more resources than necessary
  • In a physical failure you could lose your High Availability

Why? Lets suppose this scenario, We have 2 physical nodes were we create a Windows Cluster, and we configure 2 VMs as resources on this Windows Cluster (The First Option), you configure one VM to run in node1 and the other VM to run in node2, now you create a SQL Guest Failover Cluster between the to VMs (The Second Option). What will happen when Node1 crash (ex: Mother Board Failure), the 2 VMs will be running on Node2 consuming a lot of resources, and you will also not have real High Availability as your workload is running only in one physical server.

In conclusion, should I combine the two options, depend on you resource availability, and also how critical is the workload you are running against SQL. However what you should ALWAYS do is follow the best practices for configuring SQL on a Hyper-V environment.

Running SQL Server 2008 in Hyper-V Environment 

Running SQL Server with Hyper-V Dynamic Memory - Best Practices and Considerations Consolidating Databases Using Virtualization Planning Guide

Hope you like the post, you can follow me on twitter @SQLDixitox and on facebook https://www.facebook.com/SQLbyEdinsonMedina