Set up Internal Load Balancer & SQL Server Availability Group Listener on Azure (Resource Manager Deployment)

In 2012, Azure announced the great news on supporting SQL Server AlwaysOn High Availability Group. And about a year later, a better news was released that Azure officially supports Availability Group Listeners for SQL Server 2012 (and higher) on Windows Server 2008 R2, 2012 and 2012 R2.

However, not like the ordinary Availability Group Listener in on-premises SQL environment, the Azure version is achieved by the use of load-balanced endpoints enabled on the Azure VMs that are availability group nodes. And it must be configured in special steps for the listeners to work for both client applications that are running in Azure as well as those running on-premises.

Recently, I have implemented few SQL Server AlwaysOn High Availability solution on Azure (and Amzon EC2. I shall save Amzon story for another time). I have to confess that configuring an Internal Load Balancer (ILB) for AlwaysOn Availability Groups on Azure new portal with Resource Manager (ARM) Deployment was THE CHALLENGE that I will never forget.

Firstly, there aren't many documents (either in Azure or in MSDN) related to this particular task. And secondly, majority of the “official” documents are still referring old classic portal and classic deployment, but "unluckily" as I was using new Azure Portal (HTML 5) with Azure Resource Manager (ARM) Deployment.

So, for other people’s benefit, I decided to constructed the following high level guidelines for Configuring ILB Listener with SQL AlwaysOn Availability Groups Listener on Azure Resource Manager (ARM). And hopefully, it would help others to bypass the confusion and aged instruction which you may read from Internet.

Before I start, here is the general information about my Azure environment and configured SQL AlwaysOn infrastructure:

• All resources are in the same Azure Resource Group, named as “ResourceGroup-01”
• All 2 SQL Servers are in the same VNet and they are all Windows Failover Cluster nodes. The VNet is called “VNet-01”
• All 2 SQL Server are in the same subnet. The subnet is called “Subnet-01”
• The Azure Location is “West Europe”
• 2 SQL Servers are “SQL-01” and “SQL-02”

* Please note, you will need to specify above configuration based on your Azure environment.

Also, I would recommend you read the following information about SQL High Availability in Azure VMs and Azure Internal Load Balancer. And I also assume you have general understanding on Azure PowerShell, Azure Resource Manager and Azure New Portal.

https://azure.microsoft.com/en-gb/documentation/articles/virtual-machines-sql-server-high-availability-and-disaster-recovery-solutions/

https://azure.microsoft.com/en-gb/documentation/articles/load-balancer-internal-overview/

https://azure.microsoft.com/en-us/documentation/articles/virtual-machines-sql-server-alwayson-availability-groups-gui-arm/

Ok, here is the fun part.

Step 1. Open Azure PowerShell command line window and connect to your Azure Service.

If you need to know how to install Azure PowerShell and connect to your Azure Service, some guidelines you can get from here, https://azure.microsoft.com/en-gb/documentation/articles/powershell-install-configure/

Step 2. Create Internal Load Balance with basic configurations

The following PowerShell Script would create an Azure Internal Load Balancer. Although, you can do most of configuration from Azure Portal front end, but I found the script is much easy and straightforward.

So, from here, we should have created an ILB in the right Resource Group with correct Subnet and VNet. Also, front-end and back-end pool are set with probe and relative rule.

Step 3. Adding VMs into ILB Backend Pool.

However, we are still need to finalizing backend pool by adding correct VMs from corresponding availability set. In order to do that, you need to jump to Azure Portal, select “Load Balancers” from left panel (if it is not there, press Browse at left bottom). Select the new Internal Load Balancer you created on above. Then press “Backend pools” from “Settings” panel. And finally, click “Add” for adding VMs from correct availability set. To clear the confusion, those VMs should be your SQL Server AlwaysOn Availability Group Replicas (a.k.a Windows Failover Cluster Nodes).

Step 4. Manually Create Client Access Point in WFC

In this step, you manually create the availability group listener in Failover Cluster.

Open Failover Cluster Manager from the node hosting the primary replica. Select the Networks node, and note the cluster network name. Expand the cluster name, and then click Roles.

In the Roles pane, right-click the availability group name and then select Add Resource -> Client Access Point.

In the Name box, create a name for this new listener, then click Next twice, and then click Finish. Do not bring the listener or resource online at this point.

Next, click the Resources tab, then expand the Client Access Point you just created. You will see the IP Address resource for each of the cluster networks in your cluster. If this is an Azure-only solution, you will only see one IP address resource.

Right-click the IP Address resource, then select Properties. Note the IP Address Name and network name. Select Static IP Address, assign the static IP address used for ILB and then click OK.

Step 5. Update Client Access Point (created on last step) Parameter, Set dependency and bring it Online

Now, we need to use PowerShell script to update newly created Client Access Point with ILB Probe TCP Port, 59999 and OverrideAddressMatch parameter.

And then, we bring Resource IP address Online. Bring Resource Name Online. And finally, right click on Availability Group Name (in Other Resources section, bottom of the window), select “Properties”, and “Insert” Client Access Point Name (Not IP) as “Resource” in “Dependencies” tab.

Step 6. Finalize Availability Group Listener in SQL Server Management Studio

Launch SQL Server Management Studio and connect to the primary replica.

Navigate to AlwaysOn High Availability -> Availability Groups -> Availability Group Listeners. You should now see the listener name that you created in Failover Cluster Manager. Right-click the listener name and click Properties. In the Port box, specify the port number for the availability group listener by using the Backend Port defined in above ILB rule (1433 in step 2), then click OK.

Step 7. Update Client Access Point (AG Listener) Parameter for better timeout performance (Optional)

After the availability group listener is created, it may be necessary to adjust the RegisterAllProvidersIP (default=0) and HostRecordTTL (default=1200) cluster parameters for the listener resource. These parameters may reduce reconnection time after a failover which may prevent connection timeouts.

Now, you should have a properly configured SQL Server AlwaysOn Availability Group Listener bind with Azure Internal Load Balancer. And all your Web/App server should be able to use the listener for SQL Server connections and take the benefit of automated failover between synchronised AlwaysOn Availability Group Replicas.

Last but not least, for now, you can ONLY have ONE ILB per Azure Subnet. In another words, only ONE SQL AlwaysOn Availability Group can utilize a proper Listener for SQL Connections. You still can create multiple Availability Group but they won’t have their own Listener which would only work properly with ILB in Azure IaaS environment.

So, good luck for your configuration and hopefully your SQL AlwasyOn journey on Azure IaaS will be more easier with this little help.