Creating a Listener for a Multi-Subnet Hybrid or Azure Availability Group

A listener is an important component of a SQL Server Availability Group (AG). It enables applications to connect to a DNS name, regardless of which AG replica is the primary. While SQL Server Management Studio can be used to set up a listener, that only works if all replicas are within the same subnet. There are multiple reasons you may have an AG that spans subnets. The primary reason I’ve encountered is an AG that includes Azure VMs. This may be a primarily on-premises AG with one replica in Azure for DR. It may also be an AG that is in one Azure region, with one or more replicas in a separate region. If you encounter one of these scenarios, you need to use Failover Cluster Manager, Windows PowerShell, and Azure PowerShell to create your Listener. The basic instructions are found at https://msdn.microsoft.com/en-us/library/azure/dn425027.aspx, but I found this be confusing because it tries to encompass both private and public listeners. I’ve created this article to help you create a private, Azure-load-balanced listener for your multi-subnet AG. First, if you’ve created a listener, remove it from the AG. Second, you’ll need to open Notepad, a command prompt, Windows PowerShell, Azure PowerShell, Failover Cluster Manager, and SQL Server Management Studio. Azure PowerShell must be downloaded and configured – it’s not built into Windows. You can find instructions here: https://azure.microsoft.com/en-us/documentation/articles/powershell-install-configure/. The process is complicated, and it may take you a few tries to have it set up the way you want it – but be persistent, this will work!

Create an internal load balancer and endpoints on your Azure VMs

This has to be done for each subnet that is in Azure. If you have an on-prem network and an Azure network, you’ll do it once – for the Azure network. If you have two Azure networks, you’ll do this in each.

Open the Azure Portal. Connect to each VM that is a replica and check that the endpoints don’t already exist and the port you want to use isn’t already taken.

On the server, open Azure PowerShell and run:

Log in with your credentials.

Use Get-AzureSubscription to find what subscriptions you have. Select the one that includes the vnet and subnet you want to use by running:

Examine the VNet configuration.

In the node, locate VirtualNetworkSite name= for the correct subnet. Find its . Enter this in your Notepad file.

In the node, find Subnet name = “Name” for the subnet you want the listener to be in. Enter into Notepad:

Find an available IP address in that virtual network and subnet by executing:

Write down an available IP.

Copy the below script into Notepad. Note: turn off word wrap. Each command has to go into PowerShell as one line.

Copy into Azure PowerShell and execute.

To verify success, run this command.

Remember, you need to do this on every subnet that is in Azure.

Updates

Make sure KB2854082 is installed on all Windows Server 2008 R2 and Windows Server 2012 cluster nodes. If you’re using Windows Server 2012R2, this doesn’t apply.

Firewall

If Windows Firewall is enabled, follow the instructions to allow port 59999.

Create listener in Failover Cluster Manager

Open Failover Cluster Manager. Click on Networks on the left. Click on the Azure subnet network. Note the name – here, “Azure West US”.

The New Resource Wizard opens. On the Client Access Point screen, enter the Listener Name.

If this is a listener for an AG that spans both on-prem and Azure subnets, you should see one Network listed (on-prem). Enter the listener IP for that network. (If you don’t see it here, your on-prem network is DHCP. You can assign a static IP later.) The other network’s IP (Azure) will be assigned via DHCP and we will add it later.

On the Confirmation screen, click Next. The resource should be created. Click Finish.

On the Roles page, at the bottom, click the Resources tab.

Expand the listener name you just created.

For an on-prem network, right-click the IP address of that network, and click Properties. Verify the IP Address is set to Static IP Address and verify it’s a unique IP. (If your network is DHCP, here’s the place to add the static IP.)

Go back to Azure PowerShell. Find the ILB IP address using this command:

Write down the IP address in Notepad.

Using Windows PowerShell, run the command Get-ClusterResource. Find the ResourceType “IP Address” for the correct AG. Write down the name.

Copy this into Notepad, fill in the cluster network name, the IP address name, and the IP address. Then, copy into Windows PowerShell and execute. You’ll do this on Azure nodes, but not on-premises nodes.

Go back to FCM. Select Roles on the left. Click on the AG name. Click the Resources tab at the bottom. Right-click the listener name and select Properties. In the Properties window, click the Dependencies tab. Make sure there is an OR dependency between the networks. Click OK.

Right-click the listener name and click Bring Online. The listener and one IP address should be online.

Now, right-click the AG name in Resources and click Properties. Click the Dependencies tab. Click Insert. Choose the listener name under Resource. Click OK.

Open SSMS. Connect to the primary replica. Expand AlwaysOn High Availability > Availability Groups > AG name > Availability Group Listeners. You should see the listener here.

Configure the HostRecordTTL and RegisterAllProvidersIP

This is an optional step, to be implemented if your applications cannot use the MultiSub netFailover connection string keyword.

Test listener

Open SSMS and test connecting to the listener. When the AG primary node is on-premises, you should be able to connect to the listener name from any server.When the AG primary node is in Azure, you should be able to connect to the listener name from any server.

Success

Your listener should now be available, regardless of which subnet hosts the primary replica. The steps are complicated, but by keeping a list of what you’re working with in that Notepad file, you’ll easily be able to follow along!