While virtualizing an instance of SQL Server the following questions often appear: “what availability features should I use? Should I implement SQL Server or vSphere high availability options?” The blog article helps to select the most effective combination of high availability options from both products.
SQL Server High Availability features
Contents
Table 1 summarizes SQL Server high availability features (subject to the version and editions limitations).
Table 1. SQL Server availability features overview (p.10 of the above mentioned document)
| PROTECTION LEVEL | SQL SERVER AVAILABILITY FEATURE |
| SQL Server Instance | Always On Failover Cluster Instance |
| SQL Server Database | Always On Availability Group (SQL Server 2012 and later, SQL Server 2014 SP1 and later recommended)
Database Mirroring (deprecated in SQL Server 2012) Log Shipping |
| SQL Server Data | Replication (SQL Server’s built-in feature, not hardware or non-SQL Server software-based) |
vSphere High Availability features
Table 2 provides VMware vSphere high availability features overview (subject to the version and edition limitation)
Table 2. VMware vSphere high availability features
| PROTECTION LEVEL | VSPHERE AVAILABILITY FEATURES |
| VM with restart | vSphere High Availability Cluster (HA): In case of a hardware failure of an ESXi host, a VM will be restarted on one of the surviving ESXi host in the cluster. Guest OS and SQL Server will experience a non-graceful shutdown and restart similar to a start of the system after power loss. SQL Server databases should be checked before client connections are allowed. |
| VM with no restart | vSphere Fault Tolerance (FT): An Identical copy of the primary VM will be created and all CPU instructions will be replicated from primary to secondary. In case of a failure of the primary VM, the secondary will take over with the exact the same state as the primary had before the failure. Limitations apply (max 8 vCPU per host on vSphere 6.7). |
| Planned (proactive) VM migration | vSphere vMotion: vSphere vMotion can be used to migrate a VM from one ESXi host to other. Guest OS and SQL Server will continue to run as usual during and after vMotion. |
| Ensuring high availability deployments by preventing VMs, nodes of the same cluster, to run on a single ESXi host | vSphere Distributed Resource Schedule (DRS) can be used to ensure that VMs hosting SQL Server in a high availability configuration will not have a single point of failure (SPOF) on the compute resource level. DRS Affinity Rules combined with vSphere vMotion can be used to ensure that VMs are run on different ESXi hosts in a vSphere cluster |
Combination of SQL Server and vSphere High Availability options
Both SQL Server and vSphere High Availability options can be combined to achieve even higher level of availability while still preserving manageability and performance. Table 3 below outlines supported combinations.
Table 3. SQL Server and vSphere High Availability options intermix
| vSphere / SQL Server High Availability feature | SQL Server single instance | Always On Failover Cluster Instance | Always On Availability Group | Replication / Log shipping |
| vSphere High Availability Cluster (HA) | YES | YES | YES | YES |
| vSphere Fault Tolerance (FT) | YES | NO | NO | YES |
| vSphere vMotion | YES | YES1 | YES | YES |
| vSphere DRS | YES | YES1 | YES | YES |
1. Require vSphere 6.0 and later. Limitations apply. Check the following blog for more details.
Depending on business and technical requirements defining the availability of SQL Server databases, different combinations of features may be used. While planning and designing such deployments consider the following:
- vSphere vMotion greatly enhance manageability of the solution build and should always be considered. For a production deployment testing under load is highly recommended.
- Only OS/SQL Server native high availability options (like FCI or Availability Groups) will allow a non-disruptive OS and SQL Server maintenance.
- vSphere FT will not protect against any in-guest issues (OS crash, SQL Server database/service corruption, etc.), FT can speed up a recovery from a hardware failure of an ESXi host compared to native vSphere HA.
- SQL Server and vSphere availability features do not supplant the need to have good, known, and tested backups.
- Combination of vSphere HA with SQL Server FCI or AG will increase the total availability of the solution build: once a VM is restarted, the FCI or AG solution will be “whole” again.
Summary
Combination of SQL Server and vSphere High Availability options allows enterprises to get best of both worlds increasing manageability, recoverability and availability of SQL Server. Want to learn more – consider to check the document Planning Highly Available, Mission Critical SQL Server Deployments with VMware vSphere for more technical details.
Special “thank you” goes to Deji Akomolafe – Staff Solution Architect, CPBU, VMware. “Thank you”, Deji!
