Configure an Azure load balancer for an AG VNN listener - SQL Server on Azure VMs

Applies to: SQL Server on Azure VM

On Azure virtual machines, clusters use a load balancer to hold an IP address that needs to be on one cluster node at a time. In this solution, the load balancer holds the IP address for the virtual network name (VNN) listener for the Always On availability group when the SQL Server VMs are in a single subnet.

This article teaches you to configure a load balancer by using the Azure Load Balancer service. The load balancer will route traffic to your availability group listener with SQL Server on Azure VMs for high availability and disaster recovery (HADR).

For an alternative connectivity option for customers who are on SQL Server 2019 CU8 and later, consider a distributed network name (DNN) listener instead. A DNN listener offers simplified configuration and improved failover.

Prerequisites

Before you complete the steps in this article, you should already have:

• Decided that Azure Load Balancer is the appropriate connectivity option for your availability group.
• Installed the latest version of PowerShell.

Create a load balancer

You can create either of these types of load balancers:

• Internal: An internal load balancer can be accessed only from private resources that are internal to the network. When you configure an internal load balancer and its rules, use the same IP address as the availability group listener for the frontend IP address.

• External: An external load balancer can route traffic from the public to internal resources. When you configure an external load balancer, you can't use the same IP address as the availability group listener because the listener IP address can't be a public IP address.

  To use an external load balancer, logically allocate an IP address in the same subnet as the availability group that doesn't conflict with any other IP address. Use this address as the frontend IP address for the load-balancing rules.

To create the load balancer:

1. In the Azure portal, go to the resource group that contains the virtual machines.

2. Select Add. Search Azure Marketplace for load balancer. Select Load Balancer.

3. Select Create.

4. In Create load balancer, on the Basics tab, set up the load balancer by using the following values:

   • Subscription: Your Azure subscription.
   • Resource group: The resource group that contains your virtual machines.
   • Name: A name that identifies the load balancer.
   • Region: The Azure location that contains your virtual machines.
   • SKU: Standard.
   • Type: Either Public or Internal. An internal load balancer can be accessed from within the virtual network. Most Azure applications can use an internal load balancer. If your application needs access to SQL Server directly over the internet, use a public load balancer.

   

5. Select Next: Frontend IP configuration.

6. Select Add a frontend IP configuration.

   

7. Set up the frontend IP address by using the following values:

   • Name: A name that identifies the frontend IP configuration.
   • Virtual network: The same network as the virtual machines.
   • Subnet: The same subnet as the virtual machines.
   • Assignment: Static.
   • IP address: The IP address that you assigned to the clustered network resource.
   • Availability zone: An optional availability zone to deploy your IP address to.

   

8. Select Add to create the frontend IP address.

9. Select Review + Create to create the load balancer.

Configure a backend pool

1. Return to the Azure resource group that contains the virtual machines and locate the new load balancer. You might need to refresh the view on the resource group. Select the load balancer.

2. Select Backend pools, and then select +Add.

3. For Name, provide a name for the backend pool.

4. For Backend Pool Configuration, select NIC.

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

6. Under Virtual machine, choose the virtual machines that will participate as cluster nodes. Be sure to include all virtual machines that will host the availability group.

   Add only the primary IP address of each VM. Don't add any secondary IP addresses.

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

8. Select Save to create the backend pool.

Configure a health probe

1. On the pane for the load balancer, select Health probes.

2. On the Add health probe pane, set the following parameters:

   • Name: A name for the health probe.
   • Protocol: TCP.
   • Port: The port that you created in the firewall for the health probe. In this article, the example uses TCP port 59999.
   • Interval: 5 Seconds.

3. Select Add.

Set load-balancing rules

1. On the pane for the load balancer, select Load-balancing rules.

2. Select Add.

3. Set these parameters:

   • Name: A name for the load-balancing rule.
   • Frontend IP address: The IP address that you set when you configured the frontend.
   • Backend pool: The backend pool that contains the virtual machines targeted for the load balancer.
   • HA Ports: Enables load balancing on all ports for TCP and UDP protocols.
   • Protocol: TCP.
   • Port: The SQL Server TCP port. The default is 1433.
   • Backend port: The same port as the Port value when you enable Floating IP (direct server return).
   • Health probe: The health probe that you configured earlier.
   • Session persistence: None.
   • Idle timeout (minutes): 4.
   • Floating IP (direct server return): Enabled.

4. Select Save.

Configure a cluster probe

Set the cluster probe's port parameter in PowerShell.

$ClusterNetworkName = "<Cluster Network Name>"
$IPResourceName = "<AG Listener IP Address Resource Name>" 
$ILBIP = "<n.n.n.n>" 
[int]$ProbePort = <nnnnn>

Import-Module FailoverClusters

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

Get-ClusterResource $IPResourceName | Get-ClusterParameter

$ClusterNetworkName = "<Cluster Network Name>"
$IPResourceName = "<AG Listener IP Address Resource Name>" 
$ELBIP = "<n.n.n.n>" 
[int]$ProbePort = <nnnnn>

Import-Module FailoverClusters

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

Get-ClusterResource $IPResourceName | Get-ClusterParameter

Modify the connection string

For clients that support it, add MultiSubnetFailover=True to the connection string. Although the MultiSubnetFailover connection option isn't required, it provides the benefit of a faster subnet failover. This is because the client driver tries to open a TCP socket for each IP address in parallel. The client driver waits for the first IP address to respond with success. After the successful response, the client driver uses that IP address for the connection.

If your client doesn't support the MultiSubnetFailover parameter, you can modify the RegisterAllProvidersIP and HostRecordTTL settings to prevent connectivity delays after failover.

Use PowerShell to modify the RegisterAllProvidersIp and HostRecordTTL settings:

Get-ClusterResource yourListenerName | Set-ClusterParameter RegisterAllProvidersIP 0  
Get-ClusterResource yourListenerName|Set-ClusterParameter HostRecordTTL 300 

To learn more, see the documentation about listener connection timeout in SQL Server.

Test failover

Test failover of the clustered resource to validate cluster functionality:

1. Open SQL Server Management Studio and connect to your availability group listener.
2. In Object Explorer, expand Always On Availability Group.
3. Right-click the availability group and select Failover.
4. Follow the wizard prompts to fail over the availability group to a secondary replica.

Failover succeeds when the replicas switch roles and are both synchronized.

Test connectivity

To test connectivity, sign in to another virtual machine in the same virtual network. Open SQL Server Management Studio and connect to the availability group listener.

Next steps

After the VNN is created, consider optimizing the cluster settings for SQL Server VMs.

To learn more, see:

• Windows Server failover cluster with SQL Server on Azure VMs
• Always On availability groups with SQL Server on Azure VMs
• Overview of Always On availability groups
• HADR settings for SQL Server on Azure VMs