How to configure SQL Server High Availability Groups in Linux CentOS 7- SQL Server on Linux Tutorial

How to configure SQL Server High Availability Groups in Linux CentOS 7 explains all below steps to configuration SQL Server High Availability installed on Linux.

Installing SQL Server High Availability Package
Installing and Enabling SQL Server Agent if its not installed and enabled already
Enable SQL server High Availability on each Node
Creating AG Group EndPoint and Certificates
Copy Certificates of each node into all other Nodes
Change ownership and group association to mysql(User)
Restore each Certificate with authenticated user ( create user if you don’t have already one)
Grant AG Group using SSMS
Create SQL Server Login and Permission for Pacemaker
Create Availability Group resource in pacemaker
Create IP for Listener in PackeMaker
Create Listener using same IP
Test Failover

Script used in this video:

# Install SQL Server High Availability Package

sudo yum install mssql-server-ha

# Enable AlwaysOn Avaiability Groups and resetart SQL Server on both nodes

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

# Open SSMS and create Certificate for each node

# Node Name : TBSLinuxNode1

CREATE MASTER KEY ENCRYPTION BY PASSWORD = ‘Pass@123’;

GO

CREATE CERTIFICATE TBSLinuxNode1_Cert
WITH SUBJECT = ‘TBSLinuxNode1 AG Certificate’;

GO

BACKUP CERTIFICATE TBSLinuxNode1_Cert
TO FILE = ‘/var/opt/mssql/data/TBSLinuxNode1_Cert.cer’;

GO

CREATE ENDPOINT TBSSQLAG
STATE = STARTED
AS TCP (
LISTENER_PORT = 5022,
LISTENER_IP = ALL)
FOR DATABASE_MIRRORING (
AUTHENTICATION = CERTIFICATE TBSLinuxNode1_Cert,
ROLE = ALL);

GO

#Now samething on Node2 (TBSLinuxNode2)

CREATE MASTER KEY ENCRYPTION BY PASSWORD = ‘Pass@123’;

GO

CREATE CERTIFICATE TBSLinuxNode2_Cert
WITH SUBJECT = ‘TBSLinuxNode2 AG Certificate’;

GO

BACKUP CERTIFICATE TBSLinuxNode2_Cert
TO FILE = ‘/var/opt/mssql/data/TBSLinuxNode2_Cert.cer’;

GO

CREATE ENDPOINT TBSSQLAG
STATE = STARTED
AS TCP (
LISTENER_PORT = 5022,
LISTENER_IP = ALL)
FOR DATABASE_MIRRORING (
AUTHENTICATION = CERTIFICATE TBSLinuxNode2_Cert,
ROLE = ALL);

GO

# Copy Certificate of one node to other using SCP
# on Node1

scp -r root@TBSLinuxNode1:/var/opt/mssql/data/TBSLinuxNode1_Cert.cer

root@TBSLinuxNode2:/var/opt/mssql/data/TBSLinuxNode1_Cert.cer

# On Node 2

scp -r root@TBSLinuxNode2:/var/opt/mssql/data/TBSLinuxNode2_Cert.cer

root@TBSLinuxNode1:/var/opt/mssql/data/TBSLinuxNode2_Cert.cer

# Change Ownership of certificate to mssql on each node(In my case I have only two nodes)

sudo chown mssql:mssql TBSLinuxNode2_Cert.cer
sudo chown mssql:mssql TBSLinuxNode1_Cert.cer

# Create instance Level SQL User (TBSAGUser in my case on each node) using SSMS
Open SSMS and create User
# Restore certificate of Other Nodes into the present node using SSMS below: Login to TBSLinuxNode1

CREATE CERTIFICATE TBSLinuxNode2_Cert
AUTHORIZATION TBSAGUser
FROM FILE = ‘/var/opt/mssql/data/TBSLinuxNode2_Cert.cer’;

# Grant permission to connec to the endpoint of TBSLinuxNode1

GRANT CONNECT ON ENDPOINT::TBSSQLAG TO TBSAGUser;

# Let’s do the same thing by connecting to TBSLinuxNode2 and restore TBSLinuxNode1.cert

CREATE CERTIFICATE TBSLinuxNode1_Cert
AUTHORIZATION TBSAGUser
FROM FILE = ‘/var/opt/mssql/data/TBSLinuxNode1_Cert.cer’;

# Grant permission to connec to the endpoint of TBSLinuxNode2

GRANT CONNECT ON ENDPOINT::TBSSQLAG TO TBSAGUser;

# Create Availability Group using SSMS with Cluster type External

# Create a new login or use the same login to give Pacemaker permission and provide view server permission, I will give

#sysadmin to this user just for this demo

# On all Nodes Edit vi /var/opt/mssql/secrets/passwd using emacs and update with user and password that you created for

Pacemaker and save it

TBSAGUser
Pass@123
# Hold down the CTRL key and then press X, then C, to exit and save the file
# setup right permission

sudo chmod 400 /var/opt/mssql/secrets/passwd

# Create the AG resource in the Pacemaker cluster

sudo pcs resource create TBSLinuxRG ocf:mssql:ag ag_name=TBSLinuxAG meta failure-timeout=30s –master meta notify=true

# Create IP resource for Listener
sudo pcs resource create LinuxSQLProdList ocf:heartbeat:IPaddr2 ip=192.168.1.104 cidr_netmask=24

# Create an ordering constraint to ensure that the AG resource is up and running before the IP address. While the colocation

#constraint implies an ordering constraint, this enforces it

sudo pcs constraint order promote TBSLinuxRG-master then start LinuxSQLProdList

# Let’s Test Failover

source

centos 7