Azure SQL Virtual Machines HA and DR configuration design options

SQL Server running on Azure virtual machines have some Azure infrastructure specific nuances when configuring HA & DR. For AG listener and FCI there is a dependency on Azure Load Balancer to get floating IP functionality within a single subnet, due to lack of Address Resolution Protocol (ARP) in public cloud.

Option 1:

Azure Load Balancer introduce additional management and operational overhead for connecting to AG listener and FCI. Azure Load Balancer also induces failover latency of 10 seconds for the load balancing probe (2 unhealthy threshold at 5 second interval being the minimum) to detect the new SQL Server primary.

Option 2:

Using Distributed Network Name (DNN) for AG listener and FCI that avoids the need of having Azure Load Balancer. But DNN does come with following limitations:

• Works only on SQL Server versions starting with either SQL Server 2019 CU8 and later, SQL Server 2017 CU25 and later, or SQL Server 2016 SP3 and later on Windows Server 2016 and later.
• DNN AG listener MUST be configured with a unique port. The port cannot be shared with any other connection on any replica.
• DNN AG listener cannot use SQL Server default port of 1433
• There are additional considerations when using DNN with AG.
• In case of FCI, even after creating DNN the original virtual network name (VNN) and virtual IP cannot be deleted as they are necessary components of the FCI infrastructure. In addition, there is another step needed to prevent the VNN virtual IP address from being assigned to another resource in Azure as a duplicate.
• There are additional considerations when using DNN with FCI.

Option 3:

Azure SQL VM HA & DR configuration by deploying VMs in multiple subnets and thus eliminate the need for an Azure Load Balancer. Multi subnet configuration helps to match on-premises experience for connecting to your AG listener or FCI. Multi subnet configuration works natively on all supported versions of SQL Server & Windows Server simplifying deployment, maintenance and improving failover time and is Generally Available (GA).

Reference architecture & link

Best practices for High Availability / Disaster Recovery

1. Deploy your SQL Server VMs to multiple subnets whenever possible to avoid the dependency on an Azure Load Balancer or a distributed network name (DNN) to route traffic to your HADR solution. Refer link.

2. Configure cluster quorum voting to use 3 or more odd number of votes to prevent a split-brain scenario. Don't assign votes to DR regions.
   a. Although a two-node cluster will function without a quorum resource, it is strictly required to use a quorum resource to have production support. Refer link.
   b. Quorum option for SQL Server on Azure Virtual Machines (VMs) - a disk witness, a cloud witness, and a file share witness. Refer link.

Refer link on the best practices.