Creating SQL Server AGs on Linux: Part 2 – Enabling HADR on SQL Server

Introduction

Welcome to the second part of our series on setting up SQL Server availability groups on Linux. In the previous post, we covered installing and configuring Pacemaker on all nodes. This post will focus on enabling High Availability Disaster Recovery (HADR) on SQL Server, an essential step in creating our availability group.

Prerequisites

Before we begin, ensure that you have completed the steps from Part 1. You should have three Ubuntu 20.04 hosts with SQL Server for Linux installed, and Pacemaker configured and running on all nodes.

Step 1: Enable HADR on SQL Server

To enable HADR on SQL Server, follow these steps on each node:

1. Set HADR Enabled

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

2. Restart SQL Server

sudo systemctl restart mssql-server

3. Enable AlwaysOn Health Event Session

sqlcmd -S localhost -U sa -Q "ALTER EVENT SESSION AlwaysOn_Health ON SERVER WITH (STARTUP_STATE=ON)"

Repeat these steps on all three nodes.

Step 2: Create Master Key and Certificates

On the primary node (vm0), create a master key, a login, a user, and a certificate for database mirroring. These are necessary for secure communication between the nodes in the availability group.

1. Create Master Key

USE master;
GO
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'Password123';
GO

2. Create Login and User

CREATE LOGIN dbm_login WITH PASSWORD = 'Password123';
CREATE USER dbm_user FOR LOGIN dbm_login;
GO

3. Create Certificate

CREATE CERTIFICATE dbm_certificate AUTHORIZATION dbm_user WITH SUBJECT = 'Certificate for database mirroring';
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 = 'Password123');
GO

4. Create Endpoint

CREATE ENDPOINT [Hadr_endpoint] 
AS TCP (LISTENER_IP = ALL, 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];
GO

5. Copy Certificates to Other Nodes

sudo su
cd /home/username
cp /var/opt/mssql/data/dbm_certificate.* .
chown username dbm_certificate.*
exit

On vm1 and vm2:

sudo su
cd /var/opt/mssql/data
scp username@vm0:/home/username/dbm_certificate.* .
chown mssql:mssql dbm_certificate.*
exit

6. Create Master Key, Login, User, and Certificate on Other Nodes

Run the following SQL commands on vm1 and vm2:

USE master;
GO
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'Password123';
GO
CREATE LOGIN dbm_login WITH PASSWORD = 'Password123';
CREATE USER dbm_user FOR LOGIN dbm_login;
GO
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 = 'Password123');
GO
CREATE ENDPOINT [Hadr_endpoint] 
AS TCP (LISTENER_IP = ALL, 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];
GO

Step 3: Create the Availability Group

1. Create Availability Group on Primary Node (vm0)

CREATE AVAILABILITY GROUP [ag1] 
WITH (CLUSTER_TYPE = EXTERNAL) 
FOR REPLICA ON 
    N'vm0' WITH ( 
        ENDPOINT_URL = N'tcp://vm0:5022', 
        AVAILABILITY_MODE = SYNCHRONOUS_COMMIT, 
        FAILOVER_MODE = EXTERNAL, 
        SEEDING_MODE = AUTOMATIC
    ), 
    N'vm1' WITH ( 
        ENDPOINT_URL = N'tcp://vm1:5022', 
        AVAILABILITY_MODE = SYNCHRONOUS_COMMIT, 
        FAILOVER_MODE = EXTERNAL, 
        SEEDING_MODE = AUTOMATIC
    ), 
    N'vm2' WITH ( 
        ENDPOINT_URL = N'tcp://vm2:5022', 
        AVAILABILITY_MODE = SYNCHRONOUS COMMIT, 
        FAILOVER MODE = EXTERNAL, 
        SEEDING MODE = AUTOMATIC
    );
GO
ALTER AVAILABILITY GROUP [ag1] GRANT CREATE ANY DATABASE;
GO

2. Join Availability Group on Secondary Nodes (vm1 and vm2)

ALTER AVAILABILITY GROUP [ag1] JOIN WITH (CLUSTER_TYPE = EXTERNAL);
GO
ALTER AVAILABILITY GROUP [ag1] GRANT CREATE ANY DATABASE;
GO

Step 4: Add a Database to the Availability Group

1. Create Database on Primary Node (vm0)

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

Step 5: Configure Logins and Permissions

1. Create Pacemaker Login on All Nodes

CREATE LOGIN [pacemakerLogin] WITH PASSWORD= N'Password123';
GO
GRANT ALTER, CONTROL, VIEW DEFINITION ON AVAILABILITY GROUP::[ag1] TO [pacemakerLogin];
GO
GRANT VIEW SERVER STATE TO [pacemakerLogin];
GO

Conclusion

In this post, we’ve successfully enabled HADR on SQL Server, created the necessary certificates, and set up an availability group. These steps are essential for ensuring your SQL Server instances remain highly available. In the next post, we’ll focus on configuring Pacemaker resources and constraints to manage the availability group effectively. Stay tuned for more detailed guidance as we continue our journey toward robust SQL Server high availability on Linux.