Configure Always On availability group for SQL Server on Linux

THIS TOPIC APPLIES TO: yesSQL Server (Linux only)noAzure SQL DatabasenoAzure SQL Data WarehousenoParallel Data Warehouse

This article describes how to create a SQL Server Always on availability group for high availability on Linux. There are two configuration types for availability groups. A high availability configuration uses a cluster manager to provide business continuity. This configuration can also include read-scale replicas. This document explains how to create the availability group high availability configuration.

You can also create a read-scale availability group without a cluster manager. This configuration only provides read-only replicas for performance scale-out. It does not provide high availability. To create a read-scale availability group, see Configure read-scale availability group for SQL Server on Linux.

Configurations that guarantee high availability and data protection require either two or three synchronous commit replicas. With three synchronous replicas the availability group can automatically recovery even if one server is not available. For more information, see High availability and data protection for availability group configurations.

All servers must be either physical or virtual, and virtual servers must be on the same virtualization platform. This requirement is because the fencing agents are platform specific. See Policies for Guest Clusters.

Roadmap

The steps to create an availability group on Linux servers for high availability are different from the steps on a Windows Server failover cluster. The following list describes the high level steps:

  1. Configure SQL Server on three cluster servers.

    Important

    All three servers in the availability group need to be on the same platform - physical or virtual - because Linux high availability uses fencing agents to isolate resources on servers. The fencing agents are specific for each platform.

  2. Create the availability group. This step is covered in this current article.

  3. Configure a cluster resource manager, like Pacemaker.

    The way to configure a cluster resource manager depends on the specific Linux distribution. See the following links for distribution specific instructions:

    Important

    Production environments require a fencing agent, like STONITH for high availability. The demonstrations in this documentation do not use fencing agents. The demonstrations are for testing and validation only.

    A Linux cluster uses fencing to return the cluster to a known state. The way to configure fencing depends on the distribution and the environment. Currently, fencing is not available in some cloud environments. For more information, see Support Policies for RHEL High Availability Clusters - Virtualization Platforms.

    For SLES, see SUSE Linux Enterprise High Availability Extension.

  4. Add the availability group as a resource in the cluster.

    The way to add the availability group as a resource in the cluster depends on the Linux distribution. See the following links for distribution specific instructions:

Prerequisites

Before you create the availability group, you need to:

  • Set your environment so all servers that will host availability replicas can communicate
  • Install SQL Server

Note

On Linux, you must create an availability group before adding it as a cluster resource to be managed by the cluster. This document provides an example that creates the availability group. For distribution specific instructions to create the cluster and add the availability group as a cluster resource, see the links under Next steps.

  1. Update the computer name for each host

    Each SQL Server name must be:

    • 15 characters or less
    • Unique within the network

    To set the computer name, edit /etc/hostname. The following script lets you edit /etc/hostname with vi.

    sudo vi /etc/hostname
    
  2. Configure the hosts file

Note

If hostnames are registered with their IP in the DNS server, there is no need to do the steps below. Validate that all nodes that are going to be part of the availability group configuration can communicate with each other (pinging the hostname should reply with the corresponding IP address). Also, make sure that /etc/hosts file does not contain a record that maps localhost IP address 127.0.0.1 with the hostname of the node.

The hosts file on every server contains the IP addresses and names of all servers that will participate in the availability group.

The following command returns the IP address of the current server:

sudo ip addr show

Update /etc/hosts. The following script lets you edit /etc/hosts with vi.

sudo vi /etc/hosts

The following example shows /etc/hosts on node1 with additions for node1, node2, and node3. In this document node1 refers to the server hosting the primary replica. node2, and node3 refer to servers hosting secondary replicas.

127.0.0.1   localhost localhost4 localhost4.localdomain4
::1       localhost localhost6 localhost6.localdomain6
10.128.18.12 node1
10.128.16.77 node2
10.128.15.33 node3

Install SQL Server

Install SQL Server. The following links point to SQL Server installation instructions for various distributions.

Enable Always On availability groups and restart sqlserver

Enable Always On availability groups on each node hosting a SQL Server instance, then restart mssql-server. Run the following script:

sudo /opt/mssql/bin/mssql-conf set hadr.hadrenabled  1
sudo systemctl restart mssql-server

Enable AlwaysOn_health event session

You can optionally enable Always On availability groups extended events to help with root-cause diagnosis when you troubleshoot an availability group. Run the following command on each each instance of SQL Server.

ALTER EVENT SESSION  AlwaysOn_health ON SERVER WITH (STARTUP_STATE=ON);
GO

For more information about this XE session, see Always On Extended Events.

Create db mirroring endpoint user

The following Transact-SQL script creates a login named dbm_login, and a user named dbm_user. Update the script with a strong password. Run the following command on all SQL Server instances to create the database mirroring endpoint user.

CREATE LOGIN dbm_login WITH PASSWORD = '**<1Sample_Strong_Password!@#>**';
CREATE USER dbm_user FOR LOGIN dbm_login;

Create a certificate

The SQL Server service on Linux uses certificates to authenticate communication between the mirroring endpoints.

The following Transact-SQL script creates a master key and certificate. It then backs the certificate up and secures the file with a private key. Update the script with strong passwords. Connect to the primary SQL Server instance and run the following Transact-SQL to create the certificate:

CREATE MASTER KEY ENCRYPTION BY PASSWORD = '**<Master_Key_Password>**';
CREATE CERTIFICATE dbm_certificate WITH SUBJECT = 'dbm';
BACKUP CERTIFICATE dbm_certificate
   TO FILE = '/var/opt/mssql/data/dbm_certificate.cer'
   WITH PRIVATE KEY (
           FILE = '/var/opt/mssql/data/dbm_certificate.pvk',
           ENCRYPTION BY PASSWORD = '**<Private_Key_Password>**'
       );

At this point your primary SQL Server replica has a certificate at /var/opt/mssql/data/dbm_certificate.cer and a private key at var/opt/mssql/data/dbm_certificate.pvk. Copy these two files to the same location on all servers that will host availability replicas. Use the mssql user or give permission to mssql user to access these files.

For example on the source server, the following command copies the files to the target machine. Replace the values with the names of the SQL Server instances that will host the replicas.

cd /var/opt/mssql/data
scp dbm_certificate.* root@**<node2>**:/var/opt/mssql/data/

On each target server, give permission to mssql user to access the certificate.

cd /var/opt/mssql/data
chown mssql:mssql dbm_certificate.*

Create the certificate on secondary servers

The following Transact-SQL script creates a master key and certificate from the backup that you created on the primary SQL Server replica. The command also authorizes the user to access the certificate. Update the script with strong passwords. The decryption password is the same password that you used to create the .pvk file in a previous step. Run the following script on all secondary servers to create the certificate.

CREATE MASTER KEY ENCRYPTION BY PASSWORD = '**<Master_Key_Password>**';
CREATE CERTIFICATE dbm_certificate   
    AUTHORIZATION dbm_user
    FROM FILE = '/var/opt/mssql/data/dbm_certificate.cer'
    WITH PRIVATE KEY (
    FILE = '/var/opt/mssql/data/dbm_certificate.pvk',
    DECRYPTION BY PASSWORD = '**<Private_Key_Password>**'
            );

Create the database mirroring endpoints on all replicas

Database mirroring endpoints use Transmission Control Protocol (TCP) to send and receive messages between the server instances participating database mirroring sessions or hosting availability replicas. The database mirroring endpoint listens on a unique TCP port number. The TCP listener requires a listener IP address. The listener IP address must be an IPv4 address. You can also use 0.0.0.0.

The following Transact-SQL creates a listening endpoint named Hadr_endpoint for the availability group. It starts the endpoint, and gives connect permission to the user that you created. Before you run the script, replace the values between **< ... >**.

Update the following Transact-SQL for your environment on all SQL Server instances:

CREATE ENDPOINT [Hadr_endpoint]
    AS TCP (LISTENER_IP = (0.0.0.0), LISTENER_PORT = **<5022>**)
    FOR DATA_MIRRORING (
        ROLE = ALL,
        AUTHENTICATION = CERTIFICATE dbm_certificate,
        ENCRYPTION = REQUIRED ALGORITHM AES
        );
ALTER ENDPOINT [Hadr_endpoint] STATE = STARTED;
GRANT CONNECT ON ENDPOINT::[Hadr_endpoint] TO [dbm_login];

Note

If you are using SQL Server Express Edition on one node to host a configuration only replica, the only valid value for role is WITNESS. Run the following script on SQL Server Express Edition.

CREATE ENDPOINT [Hadr_endpoint]
    AS TCP (LISTENER_IP = (0.0.0.0), LISTENER_PORT = **<5022>**)
    FOR DATA_MIRRORING (
        ROLE = WITNESS,
        AUTHENTICATION = CERTIFICATE dbm_certificate,
        ENCRYPTION = REQUIRED ALGORITHM AES
        );
ALTER ENDPOINT [Hadr_endpoint] STATE = STARTED;
GRANT CONNECT ON ENDPOINT::[Hadr_endpoint] TO [dbm_login];

The TCP port on the firewall needs to be open for the listener port.

Important

For SQL Server 2017 release, the only authentication method supported for database mirroring endpoint is CERTIFICATE. WINDOWS option will be enabled in a future release.

For complete information, see The Database Mirroring Endpoint (SQL Server).

Create the availability group

There are two supported availability group configurations for SQL Server on Linux.

For information, see High availability and data protection for availability group configurations.

Create the availability group for high availability on Linux. Use the CREATE AVAILABILITY GROUP with CLUSTER_TYPE = EXTERNAL.

  • Availability group - CLUSTER_TYPE = EXTERNAL Specifies that an external cluster entity manages the availability group. Pacemaker is an example of an external cluster entity. When the availability group cluster type is external,

  • Set Primary and secondary replicas FAILOVER_MODE = EXTERNAL. Specifies that the replica interacts with an external cluster manager, like Pacemaker.

The following Transact-SQL scripts creates an availability group for high availability named ag1. The script configures the availability group replicas with SEEDING_MODE = AUTOMATIC. This setting causes SQL Server to automatically create the database on each secondary server. Update the following script for your environment. Replace the **<node1>**, **<node2>**, or **<node3>** values with the names of the SQL Server instances that host the replicas. Replace the **<5022>** with the port you set for the data mirroring endpoint. To create the availability group, run the following Transact-SQL on the SQL Server instance that hosts the primary replica.

Run only one of the following scripts:

  • Create availability group with three synchronous replicas

    CREATE AVAILABILITY GROUP [ag1]
        WITH (DB_FAILOVER = ON, CLUSTER_TYPE = EXTERNAL)
        FOR REPLICA ON
            N'**<node1>**' 
             WITH (
                ENDPOINT_URL = N'tcp://**<node1>:**<5022>**',
                AVAILABILITY_MODE = SYNCHRONOUS_COMMIT,
                FAILOVER_MODE = EXTERNAL,
                SEEDING_MODE = AUTOMATIC
                ),
            N'**<node2>**' 
             WITH ( 
                ENDPOINT_URL = N'tcp://**<node2>**:**<5022>**', 
                AVAILABILITY_MODE = SYNCHRONOUS_COMMIT,
                FAILOVER_MODE = EXTERNAL,
                SEEDING_MODE = AUTOMATIC
                ),
            N'**<node3>**'
            WITH( 
               ENDPOINT_URL = N'tcp://**<node3>**:**<5022>**', 
               AVAILABILITY_MODE = SYNCHRONOUS_COMMIT,
               FAILOVER_MODE = EXTERNAL,
               SEEDING_MODE = AUTOMATIC
               );
    
    ALTER AVAILABILITY GROUP [ag1] GRANT CREATE ANY DATABASE;
    

    Important

    After you run the preceding script to create an availability group with three synchronous replicas, do not run the following script:

  • Create availability group with two synchronous replicas and a configuration replica:

    Important

    This architecture allows any edition of SQL Server to host the third replica. For example, the third replica can be hosted on SQL Server Enterprise Edition. On Enterprise Edition, the only valid endpoint type is WITNESS.

    CREATE AVAILABILITY GROUP [ag1] 
       WITH (CLUSTER_TYPE = EXTERNAL) 
       FOR REPLICA ON 
        N'**<node1>**' WITH ( 
           ENDPOINT_URL = N'tcp://**<node1>**:**<5022>**', 
           AVAILABILITY_MODE = SYNCHRONOUS_COMMIT, 
           FAILOVER_MODE = EXTERNAL, 
           SEEDING_MODE = AUTOMATIC 
           ), 
        N'**<node2>**' WITH (  
           ENDPOINT_URL = N'tcp://**<node2>**:**<5022>**',  
           AVAILABILITY_MODE = SYNCHRONOUS_COMMIT, 
           FAILOVER_MODE = EXTERNAL, 
           SEEDING_MODE = AUTOMATIC 
           ), 
        N'**<node3>**' WITH ( 
           ENDPOINT_URL = N'tcp://**<node3>**:**<5022>**', 
           AVAILABILITY_MODE = CONFIGURATION_ONLY  
           );
    ALTER AVAILABILITY GROUP [ag1] GRANT CREATE ANY DATABASE;
    

  • Create availability group with two synchronous replicas

    Include two replicas with synchronous availability mode. For example, the following script creates an availability group called ag1. node1 and node2 host replicas in synchronous mode, with automatic seeding and automatic failover.

    Important

    Only run the following script to create an availability group with two synchronous replicas. Do not run the following script if you ran either preceding script.

    CREATE AVAILABILITY GROUP [ag1]
       WITH (CLUSTER_TYPE = EXTERNAL)
       FOR REPLICA ON
       N'node1' WITH (
          ENDPOINT_URL = N'tcp://node1:5022',
          AVAILABILITY_MODE = SYNCHRONOUS_COMMIT,
          FAILOVER_MODE = EXTERNAL,
          SEEDING_MODE = AUTOMATIC
       ),
       N'node2' WITH ( 
          ENDPOINT_URL = N'tcp://node2:5022', 
          AVAILABILITY_MODE = SYNCHRONOUS_COMMIT,
          FAILOVER_MODE = EXTERNAL,
          SEEDING_MODE = AUTOMATIC
       );
    
    ALTER AVAILABILITY GROUP [ag1] GRANT CREATE ANY DATABASE;
    

You can also configure an availability group with CLUSTER_TYPE=EXTERNAL using SQL Server Management Studio or PowerShell.

Join secondary replicas to the availability group

The following Transact-SQL script joins a SQL Server instance to an availability group named ag1. Update the script for your environment. On each SQL Server instance that hosts a secondary replica, run the following Transact-SQL to join the availability group.

ALTER AVAILABILITY GROUP [ag1] JOIN WITH (CLUSTER_TYPE = EXTERNAL);

ALTER AVAILABILITY GROUP [ag1] GRANT CREATE ANY DATABASE;

Add a database to the availability group

Ensure the database you are adding to the Availability group is in full recovery mode and has a valid log backup. If this is a test database or a new database created, take a database backup. On the primary SQL Server, run the following Transact-SQL to create and back up a database called db1.

CREATE DATABASE [db1];
ALTER DATABASE [db1] SET RECOVERY FULL;
BACKUP DATABASE [db1] 
   TO DISK = N'/var/opt/mssql/data/db1.bak';

On the primary SQL Server replica, run the following Transact-SQL to add a database called db1 to an availability group called ag1.

ALTER AVAILABILITY GROUP [ag1] ADD DATABASE [db1];

Verify that the database is created on the secondary servers

On each secondary SQL Server replica, run the following query to see if the db1 database has been created and is synchronized.

SELECT * FROM sys.databases WHERE name = 'db1';
GO
SELECT DB_NAME(database_id) AS 'database', synchronization_state_desc FROM sys.dm_hadr_database_replica_states;

Important

After you create the availability group, you must configure integration with a cluster technology like Pacemaker for high availability. For a read-scale configuration using availability groups, starting with SQL Server 2017, setting up a cluster is not required.

If you followed the steps in this document, you have an availability group that is not yet clustered. The next step is to add the cluster. This configuration is valid for read-scale/load balancing scenarios, it is not complete for high availability. For high availability, you need to add the availability group as a cluster resource. See Next steps for instructions.

Notes

Important

After you configure the cluster and add the availability group as a cluster resource, you cannot use Transact-SQL to fail over the availability group resources. SQL Server cluster resources on Linux are not coupled as tightly with the operating system as they are on a Windows Server Failover Cluster (WSFC). SQL Server service is not aware of the presence of the cluster. All orchestration is done through the cluster management tools. In RHEL or Ubuntu use pcs. In SLES use crm.

Important

If the availability group is a cluster resource, there is a known issue in current release where forced failover with data loss to an asynchronous replica does not work. This will be fixed in the upcoming release. Manual or automatic failover to a synchronous replica succeeds.

Next steps

Configure Red Hat Enterprise Linux Cluster for SQL Server Availability Group Cluster Resources

Configure SUSE Linux Enterprise Server Cluster for SQL Server Availability Group Cluster Resources

Configure Ubuntu Cluster for SQL Server Availability Group Cluster Resources