Configure a load balancer & availability group listener (SQL Server on Azure VMs)

Applies to: SQL Server on Azure VM

This article explains how to create a load balancer for a SQL Server Always On availability group in Azure Virtual Machines within a single subnet that are running with Azure Resource Manager. An availability group requires a load balancer when the SQL Server instances are on Azure Virtual Machines. The load balancer stores the IP address for the availability group listener. If an availability group spans multiple regions, each region needs a load balancer.

To complete this task, you need to have a SQL Server Always On availability group deployed in Azure VMs that are running with Resource Manager. Both SQL Server virtual machines must belong to the same availability set.

If you prefer, you can manually configure an availability group.

This article requires that your availability groups are already configured.

View related articles:

• Configure Always On availability groups in Azure VM (GUI)
• Configure a VNet-to-VNet connection by using Azure Resource Manager and PowerShell

By walking through this article, you create and configure a load balancer in the Azure portal. After the process is complete, you configure the cluster to use the IP address from the load balancer for the availability group listener.

Create & configure load balancer

In this portion of the task, do the following steps:

1. In the Azure portal, create the load balancer and configure the IP address.
2. Configure the back-end pool.
3. Create the probe.
4. Set the load-balancing rules.

Step 1: Create the load balancer and configure the IP address

First, create the load balancer.

1. In the Azure portal, open the resource group that contains the SQL Server virtual machines.

2. In the resource group, select + Create.

3. Search for load balancer. Choose Load Balancer (published by Microsoft) in the search results.

4. On the Load Balancer pane, select Create.

5. Configure the following parameters for the load balancer.

   Setting	Field
   Subscription	Use the same subscription as the virtual machine.
   Resource Group	Use the same resource group as the virtual machine.
   Name	Use a text name for the load balancer, for example sqlLB.
   Region	Use the same region as the virtual machine.
   SKU	Standard
   Type	Internal

   The Azure portal pane should look like this:

   

6. Select Next: Frontend IP Configuration

7. Select Add a frontend IP Configuration

   

8. Set up the frontend IP using the following values:

   • Name: A name that identifies the frontend IP configuration
   • Virtual network: The same network as the virtual machines.
   • Subnet: The subnet as the virtual machines.
   • IP address assignment: Static.
   • IP address: Use an available address from subnet. Use this address for your availability group listener. Notice this is different from your cluster IP address.
   • Availability zone: Optionally choose and availability zone to deploy your IP to.

   The following image shows the Add frontend IP Configuration UI:

   

9. Select Add to create the frontend IP.

10. Choose Review + Create to validate the configuration, and then Create to create the load balancer and the frontend IP.

Azure creates the load balancer. The load balancer belongs to a specific network, subnet, resource group, and location. After Azure completes the task, verify the load balancer settings in Azure.

To configure the load balancer, you need to create a backend pool, a probe, and set the load balancing rules. Do these in the Azure portal.

Step 2: Configure the backend pool

Azure calls the back-end address pool backend pool. In this case, the backend pool is the addresses of the two SQL Server instances in your availability group.

1. In the Azure portal, go to your availability group. You might need to refresh the view to see the newly created load balancer.

   

2. Select the load balancer, select Backend pools, and select +Add.

3. Provide a Name for the Backend pool.

4. Select NIC for Backend Pool Configuration.

5. Select Add to associate the backend pool with the availability set that contains the VMs.

6. Under Virtual machine choose the SQL Server virtual machines that will host availability group replicas.

   Note

   If both virtual machines are not specified, connections will only succeed to the primary replica.

7. Select Add to add the virtual machines to the backend pool.

8. Select Save to create the backend pool.

Azure updates the settings for the back-end address pool. Now your availability set has a pool of two SQL Server instances.

Step 3: Create a probe

The probe defines how Azure verifies which of the SQL Server instances currently owns the availability group listener. Azure probes the service based on the IP address on a port that you define when you create the probe.

1. Select the load balancer, choose Health probes, and then select +Add.

2. Set the listener health probe as follows:

   Setting	Description	Example
   Name	Text	SQLAlwaysOnEndPointProbe
   Protocol	Choose TCP	TCP
   Port	Any unused port	59999
   Interval	The amount of time between probe attempts in seconds	5

3. Select Add to set the health probe.

Azure creates the probe and then uses it to test which SQL Server instance has the listener for the availability group.

Step 4: Set the load-balancing rules

The load-balancing rules configure how the load balancer routes traffic to the SQL Server instances. For this load balancer, you enable direct server return because only one of the two SQL Server instances owns the availability group listener resource at a time.

1. Select the load balancer, choose Load balancing rules, and select +Add.

2. Set the listener load balancing rules as follows.

   Setting	Description	Example
   Name	Text	SQLAlwaysOnEndPointListener
   Frontend IP address	Choose an address	Use the address that you created when you created the load balancer.
   Backend pool	Choose the backend pool	Select the backend pool containing the virtual machines targeted for the load balancer.
   Protocol	Choose TCP	TCP
   Port	Use the port for the availability group listener	1433
   Backend Port	This field isn't used when Floating IP is set for direct server return	1433
   Health Probe	The name you specified for the probe	SQLAlwaysOnEndPointProbe
   Session Persistence	Dropdown list	None
   Idle Timeout	Minutes to keep a TCP connection open	4
   Floating IP (direct server return)	A flow topology and an IP address mapping scheme	Enabled

   Warning

   Direct server return is set during creation. It cannot be changed.

   Note

   You might have to scroll down the pane to view all the settings.

3. Select Save to set the listener load balancing rules.

Azure configures the load-balancing rule. Now the load balancer is configured to route traffic to the SQL Server instance that hosts the listener for the availability group.

At this point, the resource group has a load balancer that connects to both SQL Server machines. The load balancer also contains an IP address for the SQL Server Always On availability group listener, so that either machine can respond to requests for the availability groups.

Add the cluster core IP address for the Windows Server Failover Cluster (WSFC)

The WSFC IP address also needs to be on the load balancer. If you're using Windows Server 2019, skip this process as the cluster creates a Distributed Server Name instead of the Cluster Network Name.

1. In the Azure portal, go to the same Azure load balancer. Select Frontend IP configuration and select +Add. Use the IP Address you configured for the WSFC in the cluster core resources. Set the IP address as static.

2. On the load balancer, select Health probes, and then select +Add.

3. Set the WSFC cluster core IP address health probe as follows:

   Setting	Description	Example
   Name	Text	WSFCEndPointProbe
   Protocol	Choose TCP	TCP
   Port	Any unused port	58888
   Interval	The amount of time between probe attempts in seconds	5

4. Select Add to set the health probe.

5. Set the load balancing rules. Select Load balancing rules, and select +Add.

6. Set the cluster core IP address load balancing rules as follows.

   Setting	Description	Example
   Name	Text	WSFCEndPoint
   Frontend IP address	Choose an address	Use the address that you created when you configured the WSFC IP address. This is different from the listener IP address
   Backend pool	Choose the backend pool	Select the backend pool containing the virtual machines targeted for the load balancer.
   Protocol	Choose TCP	TCP
   Port	Use the port for the cluster IP address. This is an available port that isn't used for the listener probe port.	58888
   Backend Port	This field isn't used when Floating IP is set for direct server return	58888
   Probe	The name you specified for the probe	WSFCEndPointProbe
   Session Persistence	Dropdown list	None
   Idle Timeout	Minutes to keep a TCP connection open	4
   Floating IP (direct server return)	A flow topology and an IP address mapping scheme	Enabled

   Warning

   Direct server return is set during creation. It cannot be changed.

7. Select OK to set the load balancing rules.

Configure the cluster to use the load balancer IP address

The next step is to configure the listener on the cluster, and bring the listener online. Do the following steps:

1. Create the availability group listener on the failover cluster.

2. Bring the listener online.

Step 5: Create the availability group listener on the failover cluster

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

The availability group listener is an IP address and network name that the SQL Server availability group listens on. To create the availability group listener:

1. Get the name of the cluster network resource:

   a. Use RDP to connect to the Azure virtual machine that hosts the primary replica.

   b. Open Failover Cluster Manager.

   c. Select the Networks node, and note the cluster network name. Use this name in the $ClusterNetworkName variable in the PowerShell script. In the following image, the cluster network name is Cluster Network 1:

   

2. Add the client access point. The client access point is the network name that applications use to connect to the databases in an availability group.

   a. In Failover Cluster Manager, expand the cluster name, and then select Roles.

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

   

   c. In the Name box, create a name for this new listener. The name for the new listener is the network name that applications use to connect to databases in the SQL Server availability group.

   d. To finish creating the listener, select Next twice, and then select Finish. Don't bring the listener or resource online at this point.

3. Take the cluster role for the availability group offline. In Failover Cluster Manager, under Roles, right-click the role, and then select Stop Role.

4. Configure the IP resource for the availability group:

   a. Select the Resources tab, and then expand the client access point that you created. The client access point is offline.

   

   b. Right-click the IP resource, and then select Properties. Note the name of the IP address, and use it in the $IPResourceName variable in the PowerShell script.

   c. Under IP Address, select Static IP Address. Set the IP address as the same address that you used when you set the load balancer address on the Azure portal.

   

5. Make the SQL Server availability group dependent on the client access point:

   a. In Failover Cluster Manager, select Roles, and then select your availability group.

   b. On the Resources tab, under Other Resources, right-click the availability group resource, and then select Properties.

   c. On the Dependencies tab, add the name of the client access point (the listener).

   

   d. Select OK.

6. Make the client access point dependent on the IP address:

   a. In Failover Cluster Manager, select Roles, and then select your availability group.

   b. On the Resources tab, right-click the client access point under Server Name, and then select Properties.

   

   c. Select the Dependencies tab. Verify that the IP address is a dependency. If it isn't, set a dependency on the IP address. If multiple resources are listed, verify that the IP addresses have OR, not AND, dependencies. Then select OK.

   

   Tip

   You can validate that the dependencies are correctly configured. In Failover Cluster Manager, go to Roles, right-click the availability group, select More Actions, and then select Show Dependency Report. When the dependencies are correctly configured, the availability group is dependent on the network name, and the network name is dependent on the IP address.

7. Set the cluster parameters in PowerShell:

   a. Copy the following PowerShell script to one of your SQL Server instances. Update the variables for your environment.

   • $ClusterNetworkName find the name in the Failover Cluster Manager by selecting Networks, right-click the network and select Properties. The $ClusterNetworkName is under Name on the General tab.

   • $IPResourceName is the name given to the IP Address resource in the Failover Cluster Manager. This is found in the Failover Cluster Manager by selecting Roles, select the SQL Server AG or FCI name, select the Resources tab under Server Name, right-click the IP address resource and select Properties. The correct value is under Name on the General tab.

   • $ListenerILBIP is the IP address that you created on the Azure load balancer for the availability group listener. Find the $ListenerILBIP in the Failover Cluster Manager on the same properties page as the SQL Server AG/FCI Listener Resource Name.

   • $ListenerProbePort is the port that you configured on the Azure load balancer for the availability group listener, such as 59999. Any unused TCP port is valid.

   $ClusterNetworkName = "<MyClusterNetworkName>" # The cluster network name. Use Get-ClusterNetwork on Windows Server 2012 or later to find the name.
   $IPResourceName = "<IPResourceName>" # The IP address resource name.
   $ListenerILBIP = "<n.n.n.n>" # The IP address of the internal load balancer. This is the static IP address for the load balancer that you configured in the Azure portal.
   [int]$ListenerProbePort = <nnnnn>
   
   Import-Module FailoverClusters
   
   Get-ClusterResource $IPResourceName | Set-ClusterParameter -Multiple @{"Address"="$ListenerILBIP";"ProbePort"=$ListenerProbePort;"SubnetMask"="255.255.255.255";"Network"="$ClusterNetworkName";"EnableDhcp"=0}
   

   b. Set the cluster parameters by running the PowerShell script on one of the cluster nodes.

   Note

   If your SQL Server instances are in separate regions, you need to run the PowerShell script twice. The first time, use the $ListenerILBIP and $ListenerProbePort values from the first region. The second time, use the $ListenerILBIP and $ListenerProbePort values from the second region. The cluster network name and the cluster IP resource name are also different for each region.

8. Bring the cluster role for the availability group online. In Failover Cluster Manager, under Roles, right-click the role, and then select Start Role.

If necessary, repeat the preceding steps to set the cluster parameters for the IP address of the Windows Server failover cluster:

1. Get the IP address name of the Windows Server failover cluster. In Failover Cluster Manager, under Cluster Core Resources, locate Server Name.

2. Right-click IP Address, and then select Properties.

3. Copy the name of the IP address from Name. It might be Cluster IP Address.

4. Set the cluster parameters in PowerShell:

   a. Copy the following PowerShell script to one of your SQL Server instances. Update the variables for your environment.

   • $ClusterCoreIP is the IP address that you created on the Azure load balancer for the Windows Server failover cluster's core cluster resource. It's different from the IP address for the availability group listener.

   • $ClusterProbePort is the port that you configured on the Azure load balancer for the Windows Server failover cluster's health probe. It's different from the probe for the availability group listener.

   $ClusterNetworkName = "<MyClusterNetworkName>" # The cluster network name. Use Get-ClusterNetwork on Windows Server 2012 or later to find the name.
   $IPResourceName = "<ClusterIPResourceName>" # The IP address resource name.
   $ClusterCoreIP = "<n.n.n.n>" # The IP address of the cluster IP resource. This is the static IP address for the load balancer that you configured in the Azure portal.
   [int]$ClusterProbePort = <nnnnn> # The probe port from WSFCEndPointprobe in the Azure portal. This port must be different from the probe port for the availability group listener.
   
   Import-Module FailoverClusters
   
   Get-ClusterResource $IPResourceName | Set-ClusterParameter -Multiple @{"Address"="$ClusterCoreIP";"ProbePort"=$ClusterProbePort;"SubnetMask"="255.255.255.255";"Network"="$ClusterNetworkName";"EnableDhcp"=0}
   

   b. Set the cluster parameters by running the PowerShell script on one of the cluster nodes.

If any SQL resource is configured to use a port between 49152 and 65536 (the default dynamic port range for TCP/IP), add an exclusion for each port. Such resources might include:

• SQL Server database engine
• Always On availability group listener
• Health probe for the failover cluster instance
• Database mirroring endpoint
• Cluster core IP resource

Adding an exclusion will prevent other system processes from being dynamically assigned to the same port. For this scenario, configure the following exclusions on all cluster nodes:

• netsh int ipv4 add excludedportrange tcp startport=58888 numberofports=1 store=persistent
• netsh int ipv4 add excludedportrange tcp startport=59999 numberofports=1 store=persistent

It's important to configure the port exclusion when the port is not in use. Otherwise, the command will fail with a message like "The process cannot access the file because it is being used by another process." To confirm that the exclusions are configured correctly, use the following command: netsh int ipv4 show excludedportrange tcp.

Verify the configuration of the listener

If the cluster resources and dependencies are correctly configured, you should be able to view the listener in SQL Server Management Studio. To set the listener port, do the following steps:

1. Start SQL Server Management Studio, and then connect to the primary replica.

2. Go to Always On High Availability > Availability Groups > Availability Group Listeners.

   You should now see the listener name that you created in Failover Cluster Manager.

3. Right-click the listener name, and then select Properties.

4. In the Port box, specify the port number for the availability group listener by using the $EndpointPort you used earlier (1433 was the default), and then select OK.

You now have an availability group in Azure virtual machines running in Resource Manager mode.

Test the connection to the listener

Test the connection by doing the following steps:

1. Use remote desktop protocol (RDP) to connect to a SQL Server instance that's in the same virtual network, but doesn't own the replica. This server can be the other SQL Server instance in the cluster.

2. Use sqlcmd utility to test the connection. For example, the following script establishes a sqlcmd connection to the primary replica through the listener with Windows authentication:

   sqlcmd -S <listenerName> -E
   

The SQLCMD connection automatically connects to the SQL Server instance that hosts the primary replica.

Create an IP address for an additional availability group

Each availability group uses a separate listener. Each listener has its own IP address. Use the same load balancer to hold the IP address for additional listeners. Add only the primary IP address of the VM to the back-end pool of the load balancer as the secondary VM IP address doesn't support floating IP.

To add an IP address to a load balancer with the Azure portal, do the following steps:

1. In the Azure portal, open the resource group that contains the load balancer, and then select the load balancer.

2. Under Settings, select Frontend IP configuration, and then select + Add.

3. Under Add frontend IP address, assign a name for the front end.

4. Verify that the Virtual network and the Subnet are the same as the SQL Server instances.

5. Set the IP address for the listener.

   Tip

   You can set the IP address to static and type an address that is not currently used in the subnet. Alternatively, you can set the IP address to dynamic and save the new front-end IP pool. When you do so, the Azure portal automatically assigns an available IP address to the pool. You can then reopen the front-end IP pool and change the assignment to static.

6. Save the IP address for the listener by selecting Add.

7. Add a health probe selecting Health probes under Settings and use the following settings:

   Setting	Value
   Name	A name to identify the probe.
   Protocol	TCP
   Port	An unused TCP port, which must be available on all virtual machines. It can't be used for any other purpose. No two listeners can use the same probe port.
   Interval	The amount of time between probe attempts. Use the default (5).

8. Select Add to save the probe.

9. Create a load-balancing rule. Under Settings, select Load balancing rules, and then select + Add.

10. Configure the new load-balancing rule by using the following settings:

    Setting	Value
    Name	A name to identify the load-balancing rule.
    Frontend IP address	Select the IP address you created.
    Backend pool	The pool that contains the virtual machines with the SQL Server instances.
    Protocol	TCP
    Port	Use the port that the SQL Server instances are using. A default instance uses port 1433, unless you changed it.
    Backend port	Use the same value as Port.
    Health probe	Choose the probe you created.
    Session persistence	None
    Idle timeout (minutes)	Default (4)
    Floating IP (direct server return)	Enabled

Configure the availability group to use the new IP address

To finish configuring the cluster, repeat the steps that you followed when you made the first availability group. That is, configure the cluster to use the new IP address.

After you've added an IP address for the listener, configure the additional availability group by doing the following steps:

1. Verify that the probe port for the new IP address is open on both SQL Server virtual machines.

2. In Cluster Manager, add the client access point.

3. Configure the IP resource for the availability group.

   Important

   When you create the IP address, use the IP address that you added to the load balancer.

4. Make the SQL Server availability group resource dependent on the client access point.

5. Make the client access point resource dependent on the IP address.

6. Set the cluster parameters in PowerShell.

If you're on the secondary replica VM, and you're unable to connect to the listener, it's possible the probe port was not configured correctly.

You can use the following script to validate the probe port is correctly configured for the availability group:

Clear-Host
Get-ClusterResource `
| Where-Object {$_.ResourceType.Name -like "IP Address"} `
| Get-ClusterParameter `
| Where-Object {($_.Name -like "Network") -or ($_.Name -like "Address") -or ($_.Name -like "ProbePort") -or ($_.Name -like "SubnetMask")}

Add load-balancing rule for distributed availability group

If an availability group participates in a distributed availability group, the load balancer needs an additional rule. This rule stores the port used by the distributed availability group listener.

1. On each server that participates in the distributed availability group, create an inbound rule on the distributed availability group listener TCP port. In many examples, documentation uses 5022.

2. In the Azure portal, select the load balancer and select Load balancing rules, and then select +Add.

3. Create the load balancing rule with the following settings:

   Setting	Value
   Name	A name to identify the load balancing rule for the distributed availability group.
   Frontend IP address	Use the same frontend IP address as the availability group.
   Backend pool	The pool that contains the virtual machines with the SQL Server instances.
   Protocol	TCP
   Port	5022 - The port for the distributed availability group endpoint listener. Can be any available port.
   Backend port	5022 - Use the same value as Port.
   Health probe	Choose the probe you created.
   Session persistence	None
   Idle timeout (minutes)	Default (4)
   Floating IP (direct server return)	Enabled

Repeat these steps for the load balancer on the other availability groups that participate in the distributed availability groups.

If you have an Azure Network Security Group to restrict access, make sure that the allow rules include:

• The backend SQL Server VM IP addresses
• The load balancer floating IP addresses for the AG listener
• The cluster core IP address, if applicable.

Related content

• Windows Server Failover Cluster with SQL Server on Azure VMs
• Always On availability groups with SQL Server on Azure VMs
• Always On availability groups overview
• HADR settings for SQL Server on Azure VMs