VMware vSphere is a popular platform for many organizations that run various applications and workloads. Microsoft SQL Server is a popular workload that is run on vSphere for purposes ranging from development and testing to mission-critical data workloads. Dell PowerStore offers deep integration with vSphere and enables several storage capabilities to enable vSphere features. Some of these features can be used with SQL Server capabilities to provide a more robust high availability solution.

This paper describes how to combine these features to provide increased availability for SQL Server.

Dell Technologies and the authors of this document welcome your feedback on this document. Contact the Dell Technologies team by email.

Author: Doug Bernhardt

Note: For links to other documentation for this topic, see PowerStore Info Hub.

vSphere HA is an easy-to-use and cost-effective option to provide automatic restart of a virtual machine (VM) if there is a physical server failure. When used in a vSphere cluster with PowerStore, virtual machines will automatically restart on another available node that has storage visibility. vSphere HA works with multiple applications and is commonly used in most production environments. This means that it can be an option for increased availability for stand-alone SQL Server instances without the need for configuring other solutions such as SQL Server Always On features.

vSphere migration is one of the fundamental features that enables VMs to move between compute or storage locations. Migration allows workloads to be reallocated to different hardware for load balancing, hardware, or hypervisor upgrades. vSphere migration can be performed in two basic ways:

• cold migration - When the VM is powered off, relocated, and then powered back on, it is referred to as a cold migration. Although vSphere cold migration is the most basic, it is a powerful migration tool to quickly relocate virtual host resources.
• hot migration – When a hot migration, otherwise known as a “live” migration or vMotion is performed, the VM continues to run and availability is uninterrupted. vMotion allows applications such as SQL Server to continue to run uninterrupted during hardware and vSphere upgrades.

While vSphere vMotion technology allows VMs to run uninterrupted, there are several considerations to enable vMotion functionality. Design and configuration of the vSphere environment, including compute, storage, and network, all need to be done properly with vMotion in mind. Although VMs run uninterrupted during vMotion migrations, the migration time and workload performance impact will vary depending on the environment. The environment needs to be designed to support the required migration times and performance requirements during vMotion activities.

During a vMotion, either compute, storage or both can be migrated. Typically, storage migrations are the most challenging due to the storage size. Because applications such as SQL Server can have a large storage footprint and demanding performance requirements, it is beneficial to avoid a storage migration. In environments with larger SQL Server instances, vMotion was often not a viable option due to storage size or performance requirements.

Dell PowerStore has deep integration with vSphere and supports many vSphere storage features that can help to avoid storage migrations. One of those key features is PowerStore support for SCSI-3 Persistent Reservations (PR) that allow multiple nodes to access the same PowerStore storage device. SCSI-3 PR allows PowerStore appliances in a cluster to present block volume or virtual volume (vVol) resources to multiple VMware ESXi hosts. Therefore, when a vMotion is required between hosts, the storage migration is not necessary. The VMware vVol or Virtual Machine File System (VMFS) datastore is already visible to the other host, allowing for a more efficient compute only vMotion. Compute only vMotion provides a faster vMotion experience with minimal performance impact.

Eliminating a storage vMotion for a SQL Server VM can enable vMotion as a HA option where it was not possible otherwise. vMotion provides a powerful and simple option for increasing SQL Server availability without the complexity of adding additional HA features.

Adding a second PowerStore cluster can increase redundancy at a local site or provide multisite availability. The PowerStore Metro volume feature allows VMFS datastores to be synchronously replicated to a second PowerStore cluster for load balancing and increased availability. By adding a PowerStore Metro witness to the Metro volume configuration, automatic failover can be often achieved. In this configuration, SQL Server VMs can run uninterrupted through a storage appliance or network failure and can vMotion between data centers or physical sites with a compute only migration. Metro volume is a powerful feature to enhance SQL Server availability and enable additional vMotion SQL Server use cases.

vSphere Distributed Resource Scheduler (DRS) can increase availability by balancing workloads within a vSphere cluster. Leveraging vMotion capability, workloads such as SQL Server can be migrated to another host. DRS migrations help if another VM suddenly consumes excessive resources. Examples include vSphere HA failover, or unplanned events that could create a resource constraint on a vSphere host and affect a SQL Server workload.

PowerStore improves the capabilities of vMotion. Features such as vSphere DRS become more effective or feasible where they may have been impractical in situations where a storage migration was required.

VMware Site Recovery Manager (SRM) simplifies disaster recovery for multiple virtual machines and vSphere resources. Centralized recovery plans can be used to protect many virtual machines, including SQL Server workloads. Orchestration and automaton of recovery at scale minimizes downtime if there is a disaster. PowerStore supports VMware Site Recovery Manager with the PowerStore Storage Replication Adapter (SRA). PowerStore array-based replication provides efficient replication of vSphere storage resources, enabling SRM storage features. For more information, see the white paper PowerStore: VMware Site Recovery Manager Best Practices.

SQL Server provides many features that you can use for high availability. Two of the primary availability features are branded as Always On features: Always On Failover Clustering Instances (FCI) and Always On availability groups (AG). These features use multiple copies of compute, storage, or both to increase availability for SQL Server applications.

A SQL Server Always On Failover Cluster Instance (FCI) relies on Windows Server Failover Clustering (WSFC) or on Pacemaker when running FCI on Linux. FCI is the basis of many enterprise SQL Server deployments. FCI operates at the SQL Server instance level to protect all databases and resources within a SQL Server instance. A basic SQL Server FCI runs on two or more servers and requires shared storage between them. If an issue is detected on the SQL Server instance, that SQL instance will be restarted on another server in the cluster, and the restarted instance will bring related databases online. WSFC provides the ability to configure cluster service dependencies and preferences, so that required services and resources appear online in the correct order and on the correct cluster nodes. SQL Server FCI features can be used to complement or provide options for other HA solutions. For complete information, see the Microsoft Always On Failover Cluster Instances page.

Always On availability groups (AGs) provide a replicated environment for one or more SQL Server databases. Databases that need to failover together are placed in the same AG. When using Availability Groups, the main database or databases are known as the ‘primary’ and data is replicated to one or more copies known as ‘secondaries’. An important distinction between FCI and AG is that FCI protects an entire SQL Server instance and all the databases it contains. By contrast, AGs are configured at the database level and protect a single database or a group of databases. Another important difference is that shared storage used with FCI, and storage for AGs are agnostic. Because AG uses separate compute and storage, it is sometimes referred to as a shared nothing architecture. Availability groups can be deployed on all supported platforms such as Windows, Linux, containers, and Kubernetes. SQL Server FCIs can be used as the basis for AGs to provide an additional layer of resiliency. For complete information, see the Microsoft article What is an Always On availability group.

Kubernetes (K8s) is a highly scalable platform for running containerized applications, and includes HA capabilities. K8s is becoming increasingly popular for running database applications such as SQL Server because of the enterprise-grade features that are included as part of the base environment. K8s integrated features such as scheduling, resource management, load balancing, auto restart, and others can increase workload availability. SQL Server is supported on many Kubernetes distributions and deployment models. SQL Server can be deployed on K8s as stand-alone instances with containers, with Azure Arc in Azure, Azure Arc on-premises using Azure Arc-enabled SQL Managed Instance, and many other ways.

The availability features within SQL Server offer various ways to minimize disruption to business applications. If a failover is required between SQL Server instances whether stand-alone or as part of an FCI or AG, it often results in a disruption of service. The following sections describe a few of the reasons and the possible results of these disruptions.

Dropped database connections

One of the main limitations of features in the Always On architecture is that when a failover occurs, active database connections are terminated and need to be reestablished. When using FCI, the active instance is terminated, and the SQL Server instance is started on another cluster node. With AGs, a role change occurs in which a secondary database assumes the primary role, and database connections reconnect to the SQL Server instance that is hosting the primary. Depending on the method used (whether they are read-only or read/write, and how the application connects to SQL Server) will determine whether the application experiences a disruption of service. Graceful handling of a failover scenario requires careful planning and application changes, which may not be possible when using third-party applications. For business continuity, it is best to avoid failovers whenever possible, especially unplanned failovers.

Failover and recovery time

When a SQL Server instance is started or restarted, crash recovery is performed on every database in the SQL Server instance. Depending on the database recovery model, the amount of activity in the database, and several other factors, database recovery time can take seconds, or even hours in extreme cases. Accelerated Database Recovery was a feature introduced in SQL Server 2019 to accelerate this process and improve the experience. However, the best way to maximize uptime is to avoid a failover event if possible.

External application data

There is often critical application data that lives outside of the database. This data can consist of configuration files, large image files (such as FILESTREAM), and so on. This critical application data that is stored outside of the database needs to be protected, but because it is not included with SQL Server HA features, another method must be used.

Resynchronization and failback

Following a failover event, the data needs to be synchronized to the original primary and possibly a failback event is required to the original primary and secondary roles. Often resynchronization needs to be done manually or through custom scripts.

The goal to increasing availability for SQL Server applications is to reduce the chances that applications will be disconnected from SQL Server. SQL Server host restarts, SQL Server FCI cluster failover, and Always On availability group failovers will cause the client application to disconnect and reconnect. Usually failover causes an interruption, error message, or outage to the application and should be avoided if possible. In this section, we will look at some basic configurations and use cases with PowerStore to increase availability for SQL Server.

The fundamental vSphere HA features, vMotion, HA, and DRS are recommended in either a single site, stretched cluster, or multisite design.

1. vSphere vMotion – When using multiple ESXi hosts in a VMware cluster, ensure that the VM and the host meet the requirements for vMotion. The online mobility provided by vMotion can increase availability during planned maintenance and enables other vSphere features.
2. vSphere HA – Consider enabling vSphere HA to restart SQL Server VMs in the case of a failure. When SQL Server is running as a stand-alone instance, availability increases by automatically restarting the virtual machine, eliminating the need for manual intervention.
3. vSphere DRS – Consider DRS to work with vMotion to place SQL Server VMs on a host with adequate resources. This can further increase availability by avoiding outages due to resource constraints.

As described in the section vSphere migration, Dell PowerStore supports shared datastores to eliminate the need for storage migrations.

In addition to vMotion, vSphere HA, and vSphere DRS, adding hosts increases availability if a single host fails and cannot be restarted. As described in the section SQL Server High Availability, there are two primary ways to add redundant hosts with SQL Server: SQL Server Failover Cluster Instances (FCI) and Always On availability groups. SQL Server FCI relies on a shared storage architecture while AG uses a shared nothing architecture with separate storage.

Implementing SQL Server FCI on vSphere and PowerStore increases availability by adding redundancy and additional failover options for a SQL Server instance while providing a single connection address to SQL Server clients.

PowerStore supports SQL Server FCI shared storage using clustered VMDK or vVol storage on a single PowerStore cluster. The VMware articles Clustered VMDK support for WSFC and Virtual Volumes (vVols) now supports WSFC detail how to configure these storage types for WSFC; the basis for SQL Server Failover Cluster Instances.

When SQL Server FCI is implemented on vSphere, some SQL Server failover scenarios such as hardware upgrades or host resource contention can be avoided. vMotion or DRS can be used to migrate hosts nondisruptively.

SQL Server AGs on vSphere and PowerStore increase availability by adding additional database copies. Secondary database copies can reside on the same or a different storage/vSphere datastore, raw device mapping (RDM), or any storage supported by both vSphere and SQL Server.

Like FCI, when SQL Server AGs are implemented on vSphere, some SQL Server failover scenarios such as hardware upgrades or host resource contention can be avoided by leveraging vMotion or DRS. If AG instances use different storage, the instances should be storage vMotion compatible. The design should also account for storage size and IO activity for storage vMotion activities. For example, the goal of avoiding a storage vMotion is a valid reason to use the same shared datastore for all VMs in the SQL AG.

PowerStore Metro volume is the key feature that enables dual-site or stretch cluster scenarios. Metro volume provides synchronous replication between two PowerStore clusters for load balancing and failover. This second synchronized copy allows for dual site or stretch cluster architectures either in the same data center or campus or separate geographic location as supported by PowerStore Metro volume.

SQL Server AGs can leverage Metro volume to move a workload between sites or PowerStore clusters without a failover by leveraging vMotion. A VMFS datastore hosted on a Metro volume is active and synchronized on both PowerStore clusters. Synchronized storage allows for a compute vMotion only.

Note: Currently PowerStore Metro volume does not support WSFC shared volumes and cannot be used with SQL Server FCI.

Because K8s provides auto restart and load balancing capabilities, it is unlikely that vSphere features such as vSphere HA and vSphere DRS would be configured. This would create a situation where there are two managers of the capabilities, which could result in potential conflicts. vMotion is still valuable in a K8s deployment on vSphere, allowing K8s nodes to be migrated to another host without disruption.

Kubernetes provides storage provisioning and orchestration through the Container Storage Interface (CSI). When running K8s on vSphere, the appropriate vSphere CSI driver is used, depending on the K8s distribution. VMware Tanzu installations may use a different CSI version than the general vSphere CSI driver used for most other K8s distributions.

Kubernetes stretched clusters

Because vSphere CSI drivers support VMFS datastores, PowerStore Metro volume can be used to enable K8s in a stretched cluster configuration with VMFS datastores shared between cluster nodes and two PowerStore clusters. This is an important capability. Stretched Kubernetes clusters are not supported by most CSI drivers and storage platforms. The combination of Dell PowerStore Metro volume and vSphere provides stretched cluster capabilities to a wide number of K8s distributions that do not have bare metal support for K8s stretched clusters.

Designing for HA is a complex task. One must prepare for planned and unplanned events. The design needs to balance business requirements with technical, resource, and budget constraints to achieve the right solution. Because there is no single best solution, Dell PowerStore provides several different capabilities to enable and integrate with VMware and SQL Server to maximize availability and achieve the correct custom solution.

The following Dell Technologies documentation provides other information related to this document. Access to these documents depends on your login credentials. If you do not have access to a document, contact your Dell Technologies representative.

• PowerStore Info Hub
• PowerStore Product Documentation and Videos
• PowerStore: VMware vSphere Best Practices
• PowerStore: Replication Technologies
• PowerStore: Microsoft SQL Server Best Practices
• PowerStore: Metro Volume
• PowerStore: VMware Site Recovery Manager Best Practices

See the following links for related VMware resources:

• VMware Documentation
• VMware vSphere Metro Storage Cluster Recommended Practices
• Microsoft SQL Server and VMware vSphere High Availability Features
• Clustered VMDK support for WSFC
• Virtual Volumes (vVols) now supports WSFC

See the following links for related Microsoft resources:

• Windows Server Failover Clustering
• Always On Failover Cluster Instances
• What is an Always On availability group
• Azure Arc-enabled SQL Managed Instance