Edit

Tutorial: Configure an availability group listener on Linux virtual machines

  • Article

Applies to: SQL Server on Azure VM

This tutorial will go over steps on how to create an availability group (AG) listener for your SQL Servers on Linux virtual machines (VMs) in Azure, for Red Hat Enterprise Linux (RHEL), SUSE Linux Enterprise Server (SLES), and Ubuntu.

You'll learn how to:

  • Create a load balancer in the Azure portal
  • Configure the back-end pool for the load balancer
  • Create a probe for the load balancer
  • Set the load balancing rules
  • Create the load balancer resource in the cluster
  • Create the AG listener
  • Test connecting to the listener
  • Testing a failover

Note

Bias-free communication

This article contains references to the term slave, a term Microsoft considers offensive when used in this context. The term appears in this article because it currently appears in the software. When the term is removed from the software, we will remove it from the article.

Prerequisite

Create the load balancer in the Azure portal

The following instructions take you through steps 1 through 4 from the Create and configure the load balancer in the Azure portal section of the Configure a load balancer & availability group listener (SQL Server on Azure VMs) article.

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

  3. Search for load balancer and then, in the search results, select Load Balancer, which is published by Microsoft.

  4. On the Load Balancer pane, select Create.

  5. In the Create load balancer dialog box, configure the load balancer as follows:

    Setting Value
    Name A text name representing the load balancer. For example, sqlLB.
    Type Internal
    Virtual network The default virtual network that was created should be named VM1VNET.
    Subnet Select the subnet that the SQL Server instances are in. The default should be VM1Subnet.
    IP address assignment Static
    Private IP address Use the virtualip IP address that was created in the cluster.
    Subscription Use the subscription that was used for your resource group.
    Resource group Select the resource group that the SQL Server instances are in.
    Location Select the Azure location that the SQL Server instances are in.

Configure the back-end pool

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

  1. In your resource group, select the load balancer that you created.

  2. On Settings, select Backend pools.

  3. On Backend pools, select Add to create a back-end address pool.

  4. On Add backend pool, under Name, type a name for the back-end pool.

  5. Under Associated to, select Virtual machine.

  6. Select each virtual machine in the environment, and associate the appropriate IP address to each selection.

    Screenshot showing how to add a backend pool.

  7. Select Add.

Create a probe

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

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

  2. On the Health probes pane, select Add.

  3. Configure the probe on the Add probe pane. Use the following values to configure the probe:

    Setting Value
    Name A text name representing the probe. For example, SQLAlwaysOnEndPointProbe.
    Protocol TCP
    Port You can use any available port. For example, 59999.
    Interval 5
    Unhealthy threshold 2

  4. Select OK.

  5. Sign in to all your virtual machines, and open the probe port using the following commands:

    sudo firewall-cmd --zone=public --add-port=59999/tcp --permanent
sudo firewall-cmd --reload

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

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 three SQL Server instances owns the AG listener resource at a time.

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

  2. On the Load balancing rules pane, select Add.

  3. On the Add load balancing rules pane, configure the load-balancing rule. Use the following settings:

    Setting Value
    Name A text name representing the load-balancing rules. For example, SQLAlwaysOnEndPointListener.
    Protocol TCP
    Port 1433
    Backend port 1433. This value is ignored because this rule uses Floating IP (direct server return).
    Probe Use the name of the probe that you created for this load balancer.
    Session persistence None
    Idle timeout (minutes) 4
    Floating IP (direct server return) Enabled

    Screenshot showing how to add a load balancing rule.

  4. Select OK.

  5. 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 AG.

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

Create the availability group listener resource

Before creating a load balancer resource in Pacemaker, First create the listener resource:

sudo crm configure primitive virtualip \
ocf:heartbeat:IPaddr2 \
params ip=x.y.z.a

In the previous example, x.y.z.a refers to the load balancer front-end IP address.

Create the load balancer resource in the cluster

Follow the instructions for the distribution you're configuring.

  1. Sign in to the primary virtual machine. We need to create the resource to enable the Azure load balancer probe port (59999 is used in our example). Run the following command:

    sudo pcs resource create azure_load_balancer azure-lb port=59999

  2. Create a group that contains the virtualip and azure_load_balancer resource:

    sudo pcs resource group add virtualip_group azure_load_balancer virtualip

Add constraints

  1. A colocation constraint must be configured to ensure the Azure load balancer IP address and the AG resource are running on the same node. Run the following command:

    sudo pcs constraint colocation add azure_load_balancer ag_cluster-master INFINITY with-rsc-role=Master

  2. Create an ordering constraint to ensure that the AG resource is up and running before the Azure load balancer IP address. While the colocation constraint implies an ordering constraint, this enforces it.

    sudo pcs constraint order promote ag_cluster-master then start azure_load_balancer

  3. To verify the constraints, run the following command:

    sudo pcs constraint list --full

    You should see the following output:

    Location Constraints:
Ordering Constraints:
  promote ag_cluster-master then start virtualip (kind:Mandatory) (id:order-ag_cluster-master-virtualip-mandatory)
  promote ag_cluster-master then start azure_load_balancer (kind:Mandatory) (id:order-ag_cluster-master-azure_load_balancer-mandatory)
Colocation Constraints:
  virtualip with ag_cluster-master (score:INFINITY) (with-rsc-role:Master) (id:colocation-virtualip-ag_cluster-master-INFINITY)
  azure_load_balancer with ag_cluster-master (score:INFINITY) (with-rsc-role:Master) (id:colocation-azure_load_balancer-ag_cluster-master-INFINITY)
Ticket Constraints:

Create the availability group listener

  1. On the primary node, run the following command in sqlcmd or SSMS. Replace the IP address used below with the virtualip IP address.

    • SQL Server 2022 and later versions:

      ALTER AVAILABILITY GROUP [ag1]
ADD LISTENER 'ag1-listener' (
    WITH IP((
        '10.0.0.7',
        '0.0.0.0'
    )),
    PORT = 1433
);
GO

    • SQL Server 2017 and SQL Server 2019:

      ALTER AVAILABILITY GROUP [ag1]
ADD LISTENER 'ag1-listener' (
    WITH IP((
        '10.0.0.7',
        '255.255.255.255'
    )),
    PORT = 1433
);
GO

  2. Sign in to each VM node. Use the following command to open the hosts file and set up host name resolution for the ag1-listener on each machine.

    sudo vi /etc/hosts

    In the vi editor, enter i to insert text, and on a blank line, add the IP of the ag1-listener. Then add ag1-listener after a space next to the IP.

    <IP of ag1-listener> ag1-listener

    To exit the vi editor, first hit the Esc key, and then enter the command :wq to write the file and quit. Do this on each node.

Test the listener and a failover

This section covers logging into a SQL Server AG listener, and testing a failover.

Test logging in to SQL Server using the availability group listener

  1. Use sqlcmd to sign in to the primary node of SQL Server using the AG listener name:

    • Use a login that was previously created and replace <YourPassword> with the correct password. The following example uses the sa login that was created with the SQL Server.
    sqlcmd -S ag1-listener -U sa -P <YourPassword>

  2. Check the name of the server that you're connected to. Run the following command in sqlcmd:

    SELECT @@SERVERNAME;

    Your output should show the current primary node. This should be VM1 if you have never tested a failover.

    Exit the SQL Server session by typing the exit command.

Test a failover

  1. Run the following command to manually fail over the primary replica to <VM2> or another replica. Replace <VM2> with the value of your server name.

    sudo pcs resource move ag_cluster-master <VM2> --master

  2. If you check your constraints, you'll see that another constraint was added because of the manual failover:

    sudo pcs constraint list --full

    You see that a constraint with ID cli-prefer-ag_cluster-master was added.

  3. Remove the constraint with ID cli-prefer-ag_cluster-master using the following command:

    sudo pcs constraint remove cli-prefer-ag_cluster-master

  4. Check your cluster resources using the command sudo pcs resource, and you should see that the primary instance is now <VM2>.

    Note

    This article contains references to the term slave, a term that Microsoft no longer uses. When the term is removed from the software, we'll remove it from this article.

    [<username>@<VM1> ~]$ sudo pcs resource
Master/Slave Set: ag_cluster-master [ag_cluster]
    Masters: [ <VM2> ]
    Slaves: [ <VM1> <VM3> ]
Resource Group: virtualip_group
    azure_load_balancer        (ocf::heartbeat:azure-lb):      Started <VM2>
    virtualip  (ocf::heartbeat:IPaddr2):       Started <VM2>

  5. Use sqlcmd to sign in to your primary replica using the listener name:

    • Use a login that was previously created and replace <YourPassword> with the correct password. The following example uses the sa login that was created with the SQL Server.
    sqlcmd -S ag1-listener -U sa -P <YourPassword>

  6. Check the server that you're connected to. Run the following command in sqlcmd:

    SELECT @@SERVERNAME;

    You should see that you're now connected to the VM that you failed-over to.

Next step

In order to utilize an availability group listener for your SQL Server instances, you need to create and configure a load balancer.

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