Network Configuration of SQL Server Always On Availability Groups in Azure

As we pointed out in our article recently published on this forum, implementing SQL Server Failover Clustering in Azure virtual machines differs in several aspects from its on-premises implementations. These differences reflect some of the unique characteristics of the storage and network infrastructure services in the Microsoft cloud environment. In this article, we will focus on the networking aspects of clustered deployments of SQL Server 2016 in Azure. For more information regarding storage-related considerations, refer to the aforementioned article.

As we already have pointed out in that article, the primary difference between an on-premises and cloud-based Failover Clustering implementation from the networking standpoint is that the latter requires a load balancer for every client access point that must be accessible from outside of the cluster. The underlying reason for this is a lack of support for gratuitous Reverse Address Resolution Protocol (RARP) and a distinct method of managing IP address assignments to Azure virtual machines. This applies to all Failover Clustering roles that must be accessible via a network name, which means that a load balancer must be part of your implementation of Failover Cluster Instances and SQL Server Always On availability groups. The majority of Azure-based deployments rely on the built-in, highly-available, and free of charge Internal Load Balancer (ILB) for this purpose.

The most fundamental SQL Server clustering functionality that you must implement in this manner is the Availability Group listener. The listener represents the point of access to one or more clustered SQL Server databases, which are managed together as a single unit. The primary role of an Azure internal load balancer in this case is to facilitate distribution of incoming TCP/UDP traffic across a pool of Azure virtual machines hosting SQL Server installations. This distribution is supposed to target the cluster node that hosts the active instance of the Always On availability group. To accomplish this, as part of the load balancer setup, you need to designate the port representing the load balanced service (TCP 1433 when using the default SQL Server instances). You also have to configure health probes, which will allow the Azure platform to detect which instance hosts the primary, writeable replica of the availability group. The choice of the health probe TCP port is arbitrary. You simply need to ensure that this port is not currently being used on any of the load balanced Azure virtual machines.

Note that in order to use the Azure load balancer, you need to ensure that the Azure virtual machines in the back end pool are part of the same availability set. It is critical to plan ahead for this requirement, since the assignment of an availability set must take place during virtual machine provisioning (it is not possible to add an existing virtual machine to an availability set). For virtual machines to reside in the same availability set, they must also reside on the same subnet of an Azure virtual network and must be part of the same Azure Resource Manager resource group. The IP address of the Azure load balancer (which effectively becomes the IP address of the listener) must belong to the same subnet.

Once you provision virtual machines, you need to install the Failover Clustering Windows server feature and a non-clustered instance of SQL Server on each of them. In order to facilitate network connectivity necessary for Always On Availability Group operations, you also need to create Windows Firewall with Advanced Security rules to allow the following types of inbound traffic:

• SQL Server (TCP 1433 assuming the use of default instances) originating from clients or applications
• Database mirroring (TCP 5022) originating from other instances
• Azure Internal Load Balancer health probe – custom TCP port you chose when configuring the load balancer

If you decide to protect access to your Azure virtual network by using Network Security Groups, then you need to also make sure that they include rules allowing the three types of traffic listed above.

Note that the load balancer is not required for cluster operations. You can implement a functional cluster without it, including support for failover and failback between the nodes. You can also connect to each of the clustered SQL Server instances. However, the load balancer is necessary in order to provision a listener, which facilitates resilient connectivity to the availability group via an IP address that remains the same regardless of changes to the placement of the corresponding clustered role.

Once you create the load balancer, you need to perform the following steps to associate its front-end IP address with the listener of the Always On Availability Group hosted on Azure virtual machines in the back-end pool:

• in the Failover Cluster Manager console, identify the name of the cluster network to which both nodes are connected
• in the Failover Cluster Manager console, create a Client Access Point representing the listener
• in the Failover Cluster Manager console, identify the name of the IP Address resource of the newly created Client Access Point
• in the Failover Cluster Manager console, set the Static IP Address property of the IP Address resource you identified in the previous step to the front-end IP address of the Azure load balancer
• in the Failover Cluster Manager console, create a dependency of the availability group role on the name resource of Client Access Point
• in the Failover Cluster Manager console, bring the Client Access Point resource online
• use Windows PowerShell to configure cluster parameters by running the following script:

  $clusterNetworkName = '&ltcluster network name you identified in the first step&gt' 
  $ipName = '&ltIP address name you identified in the third step&gt'
  $ipILB = '&ltfront-end IP address of the Azure load balancer&gt'
  [int]$probePort = '&lthealth probe port of the Azure load balancer&gt'
  
  Get-ClusterResource $ipName | 
  	Set-ClusterParameter -Multiple @{"Address"="$ipILB";"ProbePort"=$probePort;
  	"SubnetMask"="255.255.255.255";"Network"="$clusterNetworkName";"EnableDhcp"=0}
  

• in the SQL Server Management Studio, on the AlwaysOn High Availability dashboard, set the port of the availability group listener (1433).

For detailed procedures regarding this configuration, refer to Configure Always On Availability Group in Azure VM manuallyMicrosoft Docs article.

See all articles by Marcin Policht