SQL HA Mirroring with vCenter

We’re probably all aware of the benefits of clustering things like SQL Server in order to provide highly available data.  But shared storage clustering has some drawbacks on VMware ESXi clusters such as not being able to vMotion.

• Database Mirroring – SQL Server database mirrors utilize a non-shared storage availability solution,
using built-in SQL Server replication technology to create and maintain one or more copies of each
database on other SQL Servers in the environment. SQL Server database mirrors provide
application-aware availability, and the lack of a quorum disk makes this a VMware-friendly solution,
allowing the full use of vMotion, DRS, and HA.

• Log Shipping – Geared towards disaster recovery scenarios, log shipping delays the commitment of
log files into the database copy based on an administrator defined lag time. Replay lag provides
protection against logical database corruption by providing the ability to recover up to the last copied
and inspected log file, or to a specific point-in-time (PIT) within the lag window by manipulating the log files.

• Microsoft Failover Clustering (MSCS) – Microsoft failover clusters utilize a shared storage
availability solution using the Microsoft Clustering Service to handle failover of application services in
the event of active node failure. Microsoft failover clusters provide application-aware availability, but
offer a single point of failure in the storage subsystem and because of the need for a quorum disk,
Microsoft failover clusters cannot utilize VMware features like vMotion, DRS, and HA.
http://www.vmware.com/files/pdf/sql_server_use_cases.pdf

VMware vCenter may be a great candidate for Microsoft HA Mirroring.   I’m assuming that Microsoft Clustering Services  is out because we definitely want to be able to vMotion our vCenter server to other hosts.  Especially, when we use VMware Update Manager to deploy patches.  If we’re using Clustering services then we would need to power off the VM and patch the host.  Then once we’re done, power on the VM and power off the second VM in the cluster and patch that host.  What a pain!

Log Shipping is certainly a possibility and gives you the opportunity to replay log files to a point in time.  This would be beneficial if you had a corrupt database or experienced user error.  The downfall is that it doesn’t support automatic failover.

Database Mirroring will provide us automatic failover and will still let us use the HA, DRS, and vMotion capabilities if vCenter is on a virtual machine.  If your vCenter is on a physical server(s) MS HA Mirroring will allow you to have automatic failover without having to setup shared storage.

There is a great article on how to setup SQL HA Mirroring at http://www.packtpub.com/article/microsoft-sql-server-2008-high-availability-installing-database-mirroring.

Once you’ve setup your database mirror, all you need to do is modify your ODBC Connection to specify both the Primary and Secondary Servers.  Then if there is a failure, the mirrored database will take over while VMware HA restarts the original.

SQL 2012 is now promoting the “Always On” functionality which will likely be supported on vSphere eventually, since it is similar to the Exchange DAGs that are officially supported.