SQL Server on Azure Virtual Machines is the best option to migrate your SQL Server workloads maintaining complete SQL Server compatibility and operating system level access. It is ideal destination for lift and shift or rehost migration approach. At the outset, High Availability (HA) & Disaster Recovery (DR) technologies for Azure SQL VMs are no different than on-premises world with following options:
Choosing an HA & DR technology for the database depends on the application’s business requirements for Recovery Time Objective (RTO) and Recovery Point Objective (RPO).
While it’s the same SQL Server running on Azure virtual machines, there are some Azure infrastructure specific nuances when configuring HA & DR. Currently, 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. We do understand that Azure Load Balancer does 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. To address this challenge, we did release an alternative way of 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:
Now, you can simplify 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).
Here's a reference architecture for typical AG with two replicas SQL-VM-1 and SQL-VM-2 in two separate subnets SQL-Subnet-1 and SQL-Subnet-2 respectively thus achieving multi subnet configuration.
Figure 1: Typical multi subnet AG configuration with two replicas.
In multi subnet configuration each AG replica or FCI node will be in separate subnet. These subnets can be
Prerequisite for multi subnet approach is Secondary IP and having MultiSubnetFailover=True in connection string. By using Secondary IP, Windows Server Failover Cluster and SQL Server leverages OR Dependency for cluster IP and AG Listener IP/ FCI virtual IP as shown below thus completing eliminating the need for Azure Load Balancer.
Figure 2: Windows Server 2016 Failover Cluster leveraging OR Dependency between secondary IPs.
Figure 3: AG listener cluster resource leveraging OR Dependency between secondary IPs.
Figure 4: AG listener in SSMS is mapped to the two secondary IPs.
Figure 5: SQL Server FCI Virtual Network Name (VNN) leveraging OR Dependency between secondary IPs.
Here’s a table showing various approaches for Azure SQL VM HA & DR configuration and multi subnet is highly recommended as it offers simplicity without any limitations and failover latency.
To summarize, multi subnet configuration greatly simplifies HA and DR for Azure SQL VMs, and we hope you take advantage of it. Review following articles for detailed step by step guidance of setting up AG and FCI.
Tutorial: Prerequisites for AG in multiple subnets - SQL Server on Azure VM | Microsoft Docs
Tutorial: Configure availability group in multiple subnets - SQL Server on Azure VM | Microsoft Docs
Prepare virtual machines for an FCI - SQL Server on Azure VM | Microsoft Docs
Create an FCI with Azure shared disks - SQL Server on Azure VM | Microsoft Docs
Frequently asked questions reg secondary IP and multi subnet configuration
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.