How to configure SQL Server AlwaysON on Ubuntu 22.04

This article describes how to create a SQL Server Always On availability group (AG) for high availability on Ubuntu 22.04.

Install SQL Server

1. Download the public key and import GPG keys:

curl -fsSL https://packages.microsoft.com/keys/microsoft.asc | gpg --dearmor -o /usr/share/keyrings/microsoft-prod.gpg
curl https://packages.microsoft.com/keys/microsoft.asc | tee /etc/apt/trusted.gpg.d/microsoft.asc

2. Download and register the SQL Server Ubuntu repository:

curl -fsSL https://packages.microsoft.com/config/ubuntu/22.04/mssql-server-2022.list | tee /etc/apt/sources.list.d/mssql-server-2022.list

3. Update repository and install SQL Server:

apt update
apt install -y mssql-server

4. Configure SQL Server:

/opt/mssql/bin/mssql-conf setup

5. Check the SQL Server service state:

systemctl status mssql-server

Install the SQL Server command-line tools

1. Register the Microsoft Ubuntu repository:

curl https://packages.microsoft.com/config/ubuntu/22.04/prod.list | tee /etc/apt/sources.list.d/mssql-release.list

2. Update repository and install packages:

apt update
apt install mssql-tools18 unixodbc-dev

Enable Always On availability groups

1. Run the following script:

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

Create a certificate on primary server

1. Connect to the primary SQL Server instance. To create the certificate, run the following Transact-SQL script:

CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'P@$$w0rd';
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 = 'P@$$w0rd'
        );

2. Copy a certificate and private key to the secondary server

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

Create a certificate on secondary server

1. Give permission to the mssql user to access the certificate:

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

2. Connect to the secondary SQL Server instance. To create the certificate, run the following Transact-SQL script:

CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'P@$$w0rd';
CREATE CERTIFICATE dbm_certificate
    FROM FILE = '/var/opt/mssql/data/dbm_certificate.cer'
    WITH PRIVATE KEY (
           FILE = '/var/opt/mssql/data/dbm_certificate.pvk',
           DECRYPTION BY PASSWORD = 'P@$$w0rd'
        );

Create the database mirroring endpoints on all replicas

1. Run the following Transact-SQL script for your environment on all SQL Server instances:

CREATE ENDPOINT [Hadr_endpoint]
    AS TCP (LISTENER_PORT = 5022)
    FOR DATABASE_MIRRORING (
        ROLE = ALL,
        AUTHENTICATION = CERTIFICATE dbm_certificate,
        ENCRYPTION = REQUIRED ALGORITHM AES
        );

ALTER ENDPOINT [Hadr_endpoint] STATE = STARTED;

Done. Next you can create Availability Groups from Microsoft SQL Server Management Studio