Failover cluster instances with SQL Server on Azure Virtual Machines

  • Article

Applies to: SQL Server on Azure VM

This article introduces feature differences when you're working with failover cluster instances (FCI) for SQL Server on Azure Virtual Machines (VMs).

To get started, prepare your vm.

Overview

SQL Server on Azure VMs uses Windows Server Failover Clustering (WSFC) functionality to provide local high availability through redundancy at the server-instance level: a failover cluster instance. An FCI is a single instance of SQL Server that's installed across WSFC (or simply the cluster) nodes and, possibly, across multiple subnets. On the network, an FCI appears to be a single instance of SQL Server running on a single computer. But the FCI provides failover from one WSFC node to another if the current node becomes unavailable.

The rest of the article focuses on the differences for failover cluster instances when they're used with SQL Server on Azure VMs. To learn more about the failover clustering technology, see:

Quorum

Failover cluster instances with SQL Server on Azure Virtual Machines support using a disk witness, a cloud witness, or a file share witness for cluster quorum.

To learn more, see Quorum best practices with SQL Server VMs in Azure.

Storage

In traditional on-premises clustered environments, a Windows failover cluster uses a storage area network (SAN) that's accessible by all nodes as the shared storage. SQL Server files are hosted on the shared storage, and only the active node can access the files at one time.

SQL Server on Azure VMs offers various options as a shared storage solution for a deployment of SQL Server failover cluster instances:

Azure shared disks Premium file shares Storage Spaces Direct (S2D)
Minimum OS version All Windows Server 2012 Windows Server 2016
Minimum SQL Server version All SQL Server 2012 SQL Server 2016
Supported VM availability Premium SSD LRS: Availability Sets with or without proximity placement group
Premium SSD ZRS: Availability Zones
Ultra disks: Same availability zone		 Availability sets and availability zones Availability sets
Supports FileStream Yes No Yes
Supports MSDTC Yes No No

The rest of this section lists the benefits and limitations of each storage option available for SQL Server on Azure VMs.

Azure shared disks

Azure shared disks are a feature of Azure managed disks. Windows Server Failover Clustering supports using Azure shared disks with a failover cluster instance.

Supported OS: All
Supported SQL version: All

Benefits:

  • Useful for applications looking to migrate to Azure while keeping their high-availability and disaster recovery (HADR) architecture as is.
  • Can migrate clustered applications to Azure as is because of SCSI Persistent Reservations (SCSI PR) support.
  • Supports shared Azure Premium SSD and Azure Ultra Disk storage.
  • Can use a single shared disk or stripe multiple shared disks to create a shared storage pool.
  • Supports FILESTREAM.
  • Premium SSDs support availability sets.
  • Premium SSDs Zone Redundant Storage (ZRS) supports availability zones. VMs part of FCI can be placed in different availability zones.
  • Supports Microsoft Distributed Transaction Coordinator (MSDTC) starting with Windows Server 2019.

Note

While Azure shared disks also support Standard SSD sizes, we do not recommend using Standard SSDs for SQL Server workloads due to the performance limitations.

Limitations:

  • Premium SSD disk caching isn't supported.
  • Ultra disks don't support availability sets or Zone Redundant Storage (ZRS).
  • Availability zones are supported for Ultra Disks, but the VMs must be in the same availability zone, which reduces the availability of the virtual machine to 99.9%.

To get started, see Configure failover cluster instance with Azure shared disks.

Storage Spaces Direct

Storage Spaces Direct is a Windows Server feature that is supported with failover clustering on Azure Virtual Machines. It provides a software-based virtual SAN.

Supported OS: Windows Server 2016 and later
Supported SQL version: SQL Server 2016 and later

Benefits:

  • Sufficient network bandwidth enables a robust and highly performant shared storage solution.
  • Supports Azure blob cache, so reads can be served locally from the cache. (Updates are replicated simultaneously to both nodes.)
  • Supports FileStream.

Limitations:

  • Available only for Windows Server 2016 and later.
  • Availability zones aren't supported.
  • Requires the same disk capacity attached to both virtual machines.
  • High network bandwidth is required to achieve high performance because of ongoing disk replication.
  • Requires a larger VM size and double pay for storage, because storage is attached to each VM.
  • Microsoft Distributed Transaction Coordinator (MSDTC) isn't supported.

To get started, see Configure failover cluster instance with Storage Spaces Direct.

Premium file share

Premium file shares are a feature of Azure Files. Premium file shares are SSD backed and have consistently low latency. They're fully supported for use with failover cluster instances for SQL Server 2012 or later on Windows Server 2012 or later. Premium file shares give you greater flexibility, because you can resize and scale a file share without any downtime.

Supported OS: Windows Server 2012 and later
Supported SQL version: SQL Server 2012 and later

Benefits:

  • Shared storage solution for virtual machines spread over multiple availability zones.
  • Fully managed file system with single-digit latencies and burstable I/O performance.
  • Not all SQL Server features are supported - such as database snapshots, filestream, and CHECKDB without TABLOCK. Review Limitations for details.

Limitations:

  • Available only for Windows Server 2012 and later.
  • FileStream isn't supported.
  • Microsoft Distributed Transaction Coordinator (MSDTC) isn't supported.

To get started, see Configure failover cluster instance with Premium file share.

Partner

There are partner clustering solutions with supported storage.

Supported OS: All
Supported SQL version: All

One example uses SIOS DataKeeper as the storage. For more information, see the blog entry Failover clustering and SIOS DataKeeper.

iSCSI and ExpressRoute

You can also expose an iSCSI target shared block storage via Azure ExpressRoute.

Supported OS: All
Supported SQL version: All

For example, NetApp Private Storage (NPS) exposes an iSCSI target via ExpressRoute with Equinix to Azure VMs.

For shared storage and data replication solutions from Azure partners, contact the vendor for any issues related to accessing data on failover.

Connectivity

To match the on-premises experience for connecting to your failover cluster instance, deploy your SQL Server VMs to multiple subnets within the same virtual network. Having multiple subnets negates the need for the extra dependency on an Azure Load Balancer, or a distributed network name (DNN) to route your traffic to your FCI.

If you deploy your SQL Server VMs to a single subnet, you can configure a virtual network name (VNN) and an Azure Load Balancer, or a distributed network name (DNN) to route traffic to your failover cluster instance. Review the differences between the two and then deploy either a distributed network name or a virtual network name for your failover cluster instance.

The distributed network name is recommended, if possible, as failover is faster, and the overhead and cost of managing the load balancer is eliminated.

Most SQL Server features work transparently with FCIs when using the DNN, but there are certain features that might require special consideration. For more information, see FCI and DNN interoperability.

Note

If you have multiple AGs or FCIs on the same cluster and you use either a DNN or VNN listener, then each AG or FCI needs its own independent connection point.

Limitations

Limited extension support

At this time, SQL Server failover cluster instances on Azure virtual machines registered with the SQL IaaS Agent extension only support a limited number of features available through basic registration, and not those that require the agent, such as automated backup, patching and advanced portal management. See the table of benefits to learn more.

If your SQL Server VM has already been registered with the SQL IaaS Agent extension and you've enabled any features that require the agent, you need to delete the extension from the SQL Server VM by deleting the SQL virtual machine resource for the corresponding VMs, and then registering it with the SQL IaaS Agent extension again. When you're deleting the SQL virtual machine resource by using the Azure portal, clear the check box next to the correct virtual machine to avoid deleting the virtual machine.

MSDTC

Azure Virtual Machines supports Microsoft Distributed Transaction Coordinator (MSDTC) on Windows Server 2019 with storage on Clustered Shared Volumes (CSVs) and Azure Standard Load Balancer or on SQL Server VMs that are using Azure shared disks.

On Azure Virtual Machines, MSDTC isn't supported for Windows Server 2016 or earlier with Clustered Shared Volumes because:

  • The clustered MSDTC resource can't be configured to use shared storage. On Windows Server 2016, if you create an MSDTC resource, it doesn't show any shared storage available for use, even if storage is available. This issue has been fixed in Windows Server 2019.
  • The basic load balancer doesn't handle RPC ports.

Related content