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

Author by Concurrency Blog

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 AzurePowerShell and run:

Add-AzureAccount

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:

Select-AzureSubscription -SubscriptionName "Your Subscription Name"

Examine the VNet configuration.

(Get-AzureVNetConfig).XMLConfiguration

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

VirtualNetworkSites name = “vnet name”
IP Address 192.168.74.0

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

$SubnetName = “Subnet-SubnetName”

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

(Test-AzureStaticVNetIP -VNetName "vnet name" -IPAddress 192.168.74.0).AvailableAddresses

Write down an available IP.

$ILBStaticIP = 192.168.74.254

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

$ServiceName = "SQL2014AG" #The VM's DNS name in the portal
$AGNodes = "SQL2014AG3" #The VM name
$EndpointName = "AGLE" #The desired endpoint name
$EndpointPort = 1433 #The desired endpoint port
$ILBName = "ILBAG" #The desired internal load balancer name
$SubnetName = "Subnet-SubnetName" #The subnet name from above
$ILBStaticIP = "192.168.74.254" #The IP address from above
Add-AzureInternalLoadBalancer -InternalLoadBalancerName $ILBName -SubnetName $SubnetName –ServiceName $ServiceName -StaticVNetIPAddress $ILBStaticIP
# Configure a load balanced endpoint for each node in $AGNodes using ILB
ForEach ($node in $AGNodes)
{Get-AzureVM -ServiceName $ServiceName -Name $node | Add-AzureEndpoint -Name $EndpointName -LBSetName "$EndpointName-LB" -Protocol tcp -LocalPort $EndpointPort -PublicPort $EndpointPort -ProbePort 59999 -ProbeProtocol tcp -ProbeIntervalInSeconds 10  -InternalLoadBalancerName $ILBName -DirectServerReturn $true | Update-AzureVM}

Copy into Azure PowerShell and execute.

To verify success, run this command.

Get-AzureVM -ServiceName "SQL2014AG" -Name "SQL2014AG3" | Get-AzureEndpoint

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".

AGL 1

Click on Roles on the left. Right-click the appropriate AG name and click Add Resource, then click Client Access Point.

AGL 2

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.

AGL3

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

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

AGL4

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.)

AGL5

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

$ServiceName = "SQL2014AG"
(Get-AzureInternalLoadBalancer -ServiceName $ServiceName).IPAddress

Write down the IP address in Notepad.

$ILBIP = “192.168.74.254”

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

$IPResourceName = “IP Address 192.168.74.0”

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.

$ClusterNetworkName = "Azure West US" #The cluster network name
$IPResourceName = “IP Address 192.168.74.0” # the IP Address resource name
$ILBIP = “192.168.74.254” # the IP Address of the Internal Load Balancer (ILB)
Import-Module FailoverClusters
Get-ClusterResource $IPResourceName | Set-ClusterParameter -Multiple @{"Address"="$ILBIP";"ProbePort"="59999";"SubnetMask"="255.255.255.255";"Network"="$ClusterNetworkName";"OverrideAddressMatch"=1;"EnableDhcp"=0}

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.

AGL6

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

AGL7

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.

AGL8

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 MultiSubnetFailover connection string keyword.

Get-ClusterResource #Use that to find the AG name and Listener Name
$AGname = "AG-AdventureWorks"
$ListenerName = "LI-AdventureWor"
#Change RegisterAllProvidersIP and HostRecordTTL settings
Get-ClusterResource $ListenerName | Set-ClusterParameter RegisterAllProvidersIP 0
Get-ClusterResource $ListenerName | Set-ClusterParameter HostRecordTTL 60 #Set to one minute - you may need to adjust
#Remove dependency
Remove-ClusterResourceDependency -Resource $AGname -Provider $ListenerName
#Force DNS update
Get-ClusterResource $ListenerName | Update-ClusterNetworkNameResource
#Add dependency
Add-ClusterResourceDependency -Resource $AGname -Provider $ListenerName
#Restart cluster resources
Stop-ClusterResource $ListenerName
Start-ClusterResource $ListenerName
Start-ClusterResource $AGname
#Trust, but verify
Get-ClusterResourceDependency $AGname
Get-ClusterResource $ListenerName | Get-ClusterParameter HostRecordTTL, RegisterAllProvidersIP

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!

Author

Concurrency Blog

The latest about Concurrency