SQLTeam.com | Weblogs | Forums

VM's and SQL Server


So, I'm trying to figure something out about SQL Server running on a VM. So the use a VMotion makes sense to me for a planned move of a VM ... memory structures are copied, and once both VM's are identical the switch is made.

But is there also a hot backup concept when talking about VM's (and maybe it's categorized under VMotion) too? It seems like I've heard people talk about VM's as a solution for failover. So if a VM "MY_VM" is running on SERVER1, and SERVER1 fails, then MY_VM can be brought online on SERVER2. But this sounds like it opens ups the chance for corruption in the case of SQL Server, since it would seem that the memory structures would be inaccessible.

So, is this "hot backup" even a possibility within the VM environment, and if so, is there more protection being performed for the memory structures than I am concerned about?



Hi starsky,
It would depends on the setup, if you are using VM cluster, when an ESX fail and it failover the VM to the other node, it will considered a restart (failover), it will work the same as a normal cluster failover, where SQL will perform its crash recovery.

I do not know what you mean by "hot backup", but there is a VM rpelica, where it can replicate the VM to another ESX, normally it might set to async mode, which will have data lost (depends on the setup). If you refer to SRM, then its a different story, it just automated the failover to another site (DR), and depends on how you setup the sync between the 2 sites, result will be different.

But in general rule, VM does not protect your SQL data, SQL does! Whenever you perform a failover, SQL will perform its own crash recovery, just like you were on physical hardware.

Hope this helps.


Thanks for the reply Dennisc.
By "hot backup" (and in going down this path I was purely speculating,) I was wondering if a VM farm could actually perform the same kind of action during a purposeful move via VMotion, in the case of a system crash (in my head I'm thinking that there could be a continuous memory sync on another server in the case of server failure). It seemed unlikely, but I'm at the stage of where I don't know what I don't know, about VM's

So, if you're using VM cluster and failover occurs, as long as the services for SQL Server are set to startup automatically, the instance performs it's own recovery. Thanks, that helps out considerably.



Hi starsky,
I was once having the same confusion as you, where thinking Vmotion can help to protect my SQL. However, it cannot! Put it in simple terms, Vmotion is only for planned move, for any unplanned move (crash/not response), it will still go through crash recovery.

In terms of planning your HA/DR for SQL, it will be the same as you plan on physical, the only different here is that you might not need the physical hardware anymore, as its all virtual. However, for VM cluster, the OS will stored on the shared storage, since it only got 1 copy of it, if that got corrupted, you might not be able to fail-over (only protect the hardware failure)! Compare to the old school cluster (MS cluster for example), you will have 2 OS copies on 2 server, so it can protect the software part as well.

Having said that, you can still have 2 VM running on different ESXs and build your traditional cluster around that. That way, you do not have to have physical hardware, and still can protect your OS in case of corruption. Do note that there are setting that can ensure the 2 VMs will not be sitting on the same ESXs as that is the whole point of HA.

As for the setting of SQL on VM, there will be a whole different story, and there are many things to look out for. As for the choice of hypervisor, you might want to take a look at what you need and what is best for your company (they all got its pros and cons).

In short, Yes, if your system crash, no matter its physical or VM, SQL will still require to go through crash recovery.

Hope this helps.