How to Choose an Availability Strategy for SQL Server on vSphere

When designing the architecture for your SQL Server virtualized on VMware vSphere, your requirements will determine which SQL availability or vSphere availability features you should use. There are several availability features packaged with SQL server before you even get to vSphere features like Distributed Resource Scheduler, High Availability, Fault Tolerance, or vMotion, each of which have their own considerations when interacting with SQL.

To get started, you’ll want to ask yourself a few questions about your SQL deployment.

• What am I trying to protect?
• Will my IT team abilities, licensing, Windows Server version, SQL server edition, or vSphere version have any effect on my availability strategy?
• What applications is the SQL server talking to?
• What are my Recovery Point Objective (RPO) and Recovery Time Objective (RTO)?

Because you have several options both at the SQL server level and the vSphere level, there are many ways to ensure availability. The main difference between the SQL and vSphere features is that vSphere focuses on protecting the entire VM, while SQL protects the data itself, usually with a point-in-time backup.

If you have a larger IT team with specialized administrators, some of your decision making may boil down to who you want managing the availability. Do you already have VMware administrators working on HA strategies and daily upkeep? Or do you want your SQL admins managing availability? In most cases, database administrators handle these duties, but they’ll have to work closely with your virtualization team to choose the ideal combination of SQL and vSphere availability tools.

One critical side note is that you can not deploy Availability Groups or failover clusters in SQL server without Windows Server configured as a Windows Server Failover Cluster (WSFC). Each VM participating in this cluster is known as a node. The amount of nodes possible depends on your version of Windows Server.

With critical applications, you may want to use an Availability Group (AG), which is an SQL server feature that configures synchronous copies of the database. Then vSphere features like vMotion or High Availability can be used for geographically separate failover points or for additional reliability of server configuration on top of the data protection from SQL AG.

Another popular feature are FCIs, or Always On Failover Cluster Instances, which have remained in use even with VMware Availability tools largely because they protect the Windows Server. VMware options like HA can protect the VM configuration, but you could still encounter downtime due to Windows server issues like failed patching. When updating Windows Server and SQL, FCI can be used to failover to another node.

Availability Groups are made up of one primary and multiple (depending on SQL version) replicas of nodes. Unlike FCIs, they can be configured after SQL server is set up and configured. AGs have some nifty features like using replicas as read-only for activities like backup.

Applications that do not require 24/7 database accessibility may be able to get away with a more simple vSphere Fault Tolerance configuration. Many of these lower priority applications may not have had any kind of failover, backup, or fault tolerance prior to virtualizing, so this is already a marked improvement with limited administration effort.

For more detailed information on configuring the physical and virtual architecture when designing availability for virtualized SQL Server, including storage considerations, licensing and version compatibility, and detailed descriptions of vSphere Availability features, reference this VMware white paper or contact one of our certified Microsoft and VMware administrators.