How to configure SQL Server High Availability Groups in Linux CentOS 7
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
How to configure SQL Server High Availability Group in Linux CentOS 7
No comments:
Post a Comment