Configure an ILB listener for availability groups on Azure SQL Server VMs

Article
05/02/2017

Overview

Important

Azure has two different deployment models for creating and working with resources: Azure Resource Manager and classic. This article covers the use of the classic deployment model. We recommend that most new deployments use the Resource Manager model.

To configure a listener for an Always On availability group in the Resource Manager model, see Configure a load balancer for an Always On availability group in Azure.

Your availability group can contain replicas that are on-premises only or Azure only, or that span both on-premises and Azure for hybrid configurations. Azure replicas can reside within the same region or across multiple regions that use multiple virtual networks. The procedures in this article assume that you have already configured an availability group but have not yet configured a listener.

Guidelines and limitations for internal listeners

The use of an internal load balancer (ILB) with an availability group listener in Azure is subject to the following guidelines:

The availability group listener is supported on Windows Server 2008 R2, Windows Server 2012, and Windows Server 2012 R2.
Only one internal availability group listener is supported for each cloud service, because the listener is configured to the ILB, and there is only one ILB for each cloud service. However, it is possible to create multiple external listeners. For more information, see Configure an external listener for Always On availability groups in Azure.

Determine the accessibility of the listener

It is important to realize that there are two ways to configure an availability group listener in Azure. The ways differ in the type of Azure load balancer you use when you create the listener. The following table describes the differences:

Load balancer type	Implementation	Use when:
External	Uses the public virtual IP address of the cloud service that hosts the virtual machines (VMs).	You need to access the listener from outside the virtual network, including from the Internet.
Internal	Uses an internal load balancer with a private address for the listener.	You can access the listener only from within the same virtual network. This access includes site-to-site VPN in hybrid scenarios.

Important

For a listener that uses the cloud service's public VIP (external load balancer), as long as the client, listener, and databases are in the same Azure region, you will not incur egress charges. Otherwise, any data returned through the listener is considered egress, and it is charged at normal data-transfer rates.

An ILB can be configured only on virtual networks with a regional scope. Existing virtual networks that have been configured for an affinity group cannot use an ILB. For more information, see Internal load balancer overview.

This article focuses on creating a listener that uses an ILB. If you need an public or external listener, see the version of this article that discusses setting up an external listener.

Create load-balanced VM endpoints with direct server return

You first create an ILB by running the script later in this section.

Create a load-balanced endpoint for each VM that hosts an Azure replica. If you have replicas in multiple regions, each replica for that region must be in the same cloud service in the same Azure virtual network. Creating availability group replicas that span multiple Azure regions requires configuring multiple virtual networks. For more information on configuring cross virtual network connectivity, see Configure virtual network to virtual network connectivity.

In the Azure portal, go to each VM that hosts a replica to view the details.
Click the Endpoints tab for each VM.
Verify that the Name and Public Port of the listener endpoint that you want to use are not already in use. In the example in this section, the name is MyEndpoint, and the port is 1433.
On your local client, download and install the latest PowerShell module.
Start Azure PowerShell.
A new PowerShell session opens, with the Azure administrative modules loaded.
Run Get-AzurePublishSettingsFile. This cmdlet directs you to a browser to download a publish settings file to a local directory. You might be prompted for your sign-in credentials for your Azure subscription.
Run the following Import-AzurePublishSettingsFile command with the path of the publish settings file that you downloaded:
```
Import-AzurePublishSettingsFile -PublishSettingsFile <PublishSettingsFilePath>
```
After the publish settings file is imported, you can manage your Azure subscription in the PowerShell session.
For ILB, assign a static IP address. Examine the current virtual network configuration by running the following command:
```
(Get-AzureVNetConfig).XMLConfiguration
```
Note the Subnet name for the subnet that contains the VMs that host the replicas. This name is used in the $SubnetName parameter in the script.
Note the VirtualNetworkSite name and the starting AddressPrefix for the subnet that contains the VMs that host the replicas. Look for an available IP address by passing both values to the Test-AzureStaticVNetIP command and by examining the AvailableAddresses. For example, if the virtual network is named MyVNet and has a subnet address range that starts at 172.16.0.128, the following command would list available addresses:
```
(Test-AzureStaticVNetIP -VNetName "MyVNet"-IPAddress 172.16.0.128).AvailableAddresses
```
Select one of the available addresses, and use it in the $ILBStaticIP parameter of the script in the next step.

Copy the following PowerShell script to a text editor, and set the variable values to suit your environment. Defaults have been provided for some parameters.

Existing deployments that use affinity groups cannot add an ILB. For more information about ILB requirements, see Internal load balancer overview.

Also, if your availability group spans Azure regions, you must run the script once in each datacenter for the cloud service and nodes that reside in that datacenter.

# Define variables
$ServiceName = "<MyCloudService>" # the name of the cloud service that contains the availability group nodes
$AGNodes = "<VM1>","<VM2>","<VM3>" # all availability group nodes containing replicas in the same cloud service, separated by commas
$SubnetName = "<MySubnetName>" # subnet name that the replicas use in the virtual network
$ILBStaticIP = "<MyILBStaticIPAddress>" # static IP address for the ILB in the subnet
$ILBName = "AGListenerLB" # customize the ILB name or use this default value

# Create the ILB
Add-AzureInternalLoadBalancer -InternalLoadBalancerName $ILBName -SubnetName $SubnetName -ServiceName $ServiceName -StaticVNetIPAddress $ILBStaticIP

# Configure a load-balanced endpoint for each node in $AGNodes by using ILB
ForEach ($node in $AGNodes)
{
    Get-AzureVM -ServiceName $ServiceName -Name $node | Add-AzureEndpoint -Name "ListenerEndpoint" -LBSetName "ListenerEndpointLB" -Protocol tcp -LocalPort 1433 -PublicPort 1433 -ProbePort 59999 -ProbeProtocol tcp -ProbeIntervalInSeconds 10 -InternalLoadBalancerName $ILBName -DirectServerReturn $true | Update-AzureVM
}

After you have set the variables, copy the script from the text editor to your PowerShell session to run it. If the prompt still shows >>, press Enter again to make sure the script starts running.

Verify that KB2854082 is installed if necessary

Next, if any servers on the cluster are running Windows Server 2008 R2 or Windows Server 2012, you must verify that the hotfix KB2854082 is installed on each of the on-premises servers or Azure VMs that are part of the cluster. Any server or VM that is in the cluster, but not in the availability group, should also have this hotfix installed.

In the remote desktop session for each of the cluster nodes, download KB2854082 to a local directory. Then, install the hotfix on each cluster node sequentially. If the cluster service is currently running on the cluster node, the server is restarted at the end of the hotfix installation.

Warning

Stopping the cluster service or restarting the server affects the quorum health of your cluster and the availability group, and it might cause your cluster to go offline. To maintain the high availability of your cluster during installation, make sure that:

The cluster is in optimal quorum health.
Before you install the hotfix on any node, all cluster nodes are online.
Before you install the hotfix on any other node in the cluster, allow the hotfix installation to run to completion on one node, including fully restarting the server.

Open the firewall ports in availability group nodes

In this step, you create a firewall rule to open the probe port for the load-balanced endpoint (59999, as specified earlier) and another rule to open the availability group listener port. Because you created the load-balanced endpoint on the VMs that contain availability group replicas, you need to open the probe port and the listener port on the respective VMs.

On VMs that host replicas, start Windows Firewall with Advanced Security.
Right-click Inbound Rules, and then click New Rule.
On the Rule Type page, select Port, and then click Next.
On the Protocol and Ports page, select TCP, type 59999 in the Specific local ports box, and then click Next.
On the Action page, keep Allow the connection selected, and then click Next.
On the Profile page, accept the default settings, and then click Next.
On the Name page, in the Name text box, specify a rule name, such as Always On Listener Probe Port, and then click Finish.
Repeat the preceding steps for the availability group listener port (as specified earlier in the $EndpointPort parameter of the script), and then specify an appropriate rule name, such as Always On Listener Port.

Create the availability group listener

Create the availability group listener in two steps. First, create the client access point cluster resource and configure dependencies. Second, configure the cluster resources in PowerShell.

Create the client access point and configure the cluster dependencies

In this step, you manually create the availability group listener in Failover Cluster Manager and SQL Server Management Studio.

Open Failover Cluster Manager from the node that hosts the primary replica.
Select the Networks node, and then note the cluster network name. This name is used in the $ClusterNetworkName variable in the PowerShell script.
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, click Next twice, and then click Finish.
Do not bring the listener or resource online at this point.
Click the Resources tab, and then expand the client access point you just created. The IP address resource for each cluster network in your cluster is displayed. If this is an Azure-only solution, only one IP address resource is displayed.
Do either of the following:
- To configure a hybrid solution:
  
  a. Right-click the IP address resource that corresponds to your on-premises subnet, and then select Properties. Note the IP address name and network name.
  
  b. Select Static IP Address, assign an unused IP address, and then click OK.
- To configure an Azure-only solution:
  
  a. Right-click the IP address resource that corresponds to your Azure subnet, and then select Properties.
  
  Note
  
  If the listener later fails to come online because of a conflicting IP address selected by DHCP, you can configure a valid static IP address in this properties window.
  
  b. In the same IP Address properties window, change the IP Address Name.
  This name is used in the $IPResourceName variable of the PowerShell script. If your solution spans multiple Azure virtual networks, repeat this step for each IP resource.

Configure the cluster resources in PowerShell

For ILB, you must use the IP address of the ILB that was created earlier. To obtain this IP address in PowerShell, use the following script:

# Define variables
$ServiceName="<MyServiceName>" # the name of the cloud service that contains the AG nodes
(Get-AzureInternalLoadBalancer -ServiceName $ServiceName).IPAddress

On one of the VMs, copy the PowerShell script for your operating system to a text editor, and then set the variables to the values you noted earlier.

For Windows Server 2012 or later, use the following script:

# Define variables
$ClusterNetworkName = "<MyClusterNetworkName>" # the cluster network name (Use Get-ClusterNetwork on Windows Server 2012 of higher to find the name)
$IPResourceName = "<IPResourceName>" # the IP address resource name
$ILBIP = "<X.X.X.X>" # the IP address of the ILB

Import-Module FailoverClusters

Get-ClusterResource $IPResourceName | Set-ClusterParameter -Multiple @{"Address"="$ILBIP";"ProbePort"="59999";"SubnetMask"="255.255.255.255";"Network"="$ClusterNetworkName";"EnableDhcp"=0}

For Windows Server 2008 R2, use the following script:

# Define variables
$ClusterNetworkName = "<MyClusterNetworkName>" # the cluster network name (Use Get-ClusterNetwork on Windows Server 2012 of higher to find the name)
$IPResourceName = "<IPResourceName>" # the IP address resource name
$ILBIP = "<X.X.X.X>" # the IP address of the ILB

Import-Module FailoverClusters

cluster res $IPResourceName /priv enabledhcp=0 address=$ILBIP probeport=59999  subnetmask=255.255.255.255

After you have set the variables, open an elevated Windows PowerShell window, paste the script from the text editor into your PowerShell session to run it. If the prompt still shows >>, Press Enter again to make sure that the script starts running.
Repeat the preceding steps for each VM.
This script configures the IP address resource with the IP address of the cloud service and sets other parameters, such as the probe port. When the IP address resource is brought online, it can respond to the polling on the probe port from the load-balanced endpoint that you created earlier.

Bring the listener online

In Failover Cluster Manager, expand Roles, and then highlight your availability group.
On the Resources tab, right-click the listener name, and then click Properties.
Click the Dependencies tab. If multiple resources are listed, verify that the IP addresses have OR, not AND, dependencies.
Click OK.
Right-click the listener name, and then click Bring Online.
After the listener is online, on the Resources tab, right-click the availability group, and then click Properties.
Create a dependency on the listener name resource (not the IP address resources name), and then click OK.
Start SQL Server Management Studio, and then connect to the primary replica.
Go to AlwaysOn High Availability > Availability Groups > <AvailabilityGroupName> > Availability Group Listeners.
The listener name that you created in Failover Cluster Manager should be displayed.
Right-click the listener name, and then click Properties.
In the Port box, specify the port number for the availability group listener by using the $EndpointPort that you used earlier (in this tutorial, 1433 was the default), and then click OK.

Follow-up items

After you create the availability group listener, it might be necessary to adjust the RegisterAllProvidersIP and HostRecordTTL cluster parameters for the listener resource. These parameters can reduce reconnection time after a failover, which might prevent connection timeouts. For more information about these parameters, as well as sample code, see Create or configure an availability group listener.

Test the availability group listener (within the same virtual network)

In this step, you test the availability group listener by using a client application that's running on the same network.

Client connectivity has the following requirements:

Client connections to the listener must come from machines that reside in a different cloud service than the one that hosts the Always On availability replicas.
If the Always On replicas are in different subnets, clients must specify MultisubnetFailover=True in the connection string. This condition results in parallel connection attempts to replicas in the various subnets. This scenario includes a cross-region Always On availability group deployment.

One example is to connect to the listener from one of the VMs in the same Azure virtual network (but not one that hosts a replica). An easy way to complete this test is to try to connect SQL Server Management Studio to the availability group listener. Another simple method is to run SQLCMD.exe, as follows:

sqlcmd -S "<ListenerName>,<EndpointPort>" -d "<DatabaseName>" -Q "select @@servername, db_name()" -l 15

Note

If the EndpointPort value is 1433, you are not required to specify it in the call. The previous call also assumes that the client machine is joined to the same domain and that the caller has been granted permissions on the database by using Windows authentication.

When you test the listener, be sure to fail over the availability group to make sure that clients can connect to the listener across failovers.

Next steps

In addition to automatically connecting clients to the primary replica, a listener can be used to redirect read-only workloads to the secondaries. This use can improve the performance and scalability of your overall solution. For more information, see Use ReadIntent Routing with Azure Always On availability group listener.