Always On availability group on SQL Server on Azure VMs
This article introduces Always On availability groups for SQL Server on Azure Virtual Machines (VMs).
Always On availability groups on Azure Virtual Machines are similar to Always On availability groups on-premises. However, since the virtual machines are hosted in Azure, there are a few additional considerations as well, such as VM redundancy, and routing traffic on the Azure network.
The following diagram illustrates an availability group for SQL Server on Azure VMs:
An availability set is a grouping of resources which are configured such that no two land in the same availability zone. This prevents impacting multiple resources in the group during deployment roll outs.
In a traditional on-premises deployment, clients connect to the availability group listener using the virtual network name (VNN), and the listener routes traffic to the appropriate SQL Server replica in the availability group. However, there is an extra requirement to route traffic on the Azure network.
With SQL Server on Azure VMs, configure a load balancer to route traffic to your availability group listener, or, if you're on SQL Server 2019 CU8 and later, you can configure a distributed network name (DNN) listener to replace the traditional VNN availability group listener.
Use an Azure Load Balancer to route traffic from the client to the traditional availability group virtual network name (VNN) listener on the Azure network.
The load balancer holds the IP addresses for the VNN listener. If you have more than one availability group, each group requires a VNN listener. One load balancer can support multiple listeners.
To get started, see configure a load balancer.
SQL Server 2019 CU8 introduces support for the distributed network name (DNN) listener. The DNN listener replaces the traditional availability group listener, negating the need for an Azure Loud Balancer to route traffic on the Azure network.
The DNN listener is the recommended HADR connectivity solution in Azure as it simplifies deployment, reduces maintenance and cost, and reduces failover time in the event of a failure.
Use the DNN listener to replace an existing VNN listener, or alternatively, use it in conjunction with an existing VNN listener so that your availability group has two distinct connection points - one using the VNN listener name (and port if non-default), and one using the DNN listener name and port. This could be useful for customers who want to avoid the load balancer failover latency but still take advantage of SQL Server features that depend on the VNN listener, such as distributed availability groups, service broker or filestream. To learn more, see DNN listener and SQL Server feature interoperability
To get started, see configure a DNN listener.
There are multiple options for deploying an availability group to SQL Server on Azure VMs, some with more automation than others.
The following table provides a comparison of the options available:
|Azure portal||Azure CLI / PowerShell||Quickstart Templates||Manual|
|SQL Server version||2016 +||2016 +||2016 +||2012 +|
|SQL Server edition||Enterprise||Enterprise||Enterprise||Enterprise, Standard|
|Windows Server version||2016 +||2016 +||2016 +||All|
|Creates the cluster for you||Yes||Yes||Yes||No|
|Creates the availability group for you||Yes||No||No||No|
|Creates listener and load balancer independently||No||No||No||Yes|
|Possible to create DNN listener using this method?||No||No||No||Yes|
|WSFC quorum configuration||Cloud witness||Cloud witness||Cloud witness||All|
|DR with multiple regions||No||No||No||Yes|
|Support for an existing AD||Yes||Yes||Yes||Yes|
|DR with multizone in the same region||Yes||Yes||Yes||Yes|
|Distributed AG with no AD||No||No||No||Yes|
|Distributed AG with no cluster||No||No||No||Yes|
On an Azure IaaS VM guest failover cluster, we recommend a single NIC per server (cluster node) and a single subnet. Azure networking has physical redundancy, which makes additional NICs and subnets unnecessary on an Azure IaaS VM guest cluster. Although the cluster validation report will issue a warning that the nodes are only reachable on a single network, this warning can be safely ignored on Azure IaaS VM guest failover clusters.