Workshop on MS SQL Server On Linux Day 3
Environment:
Domain Name: [Link]
Domain server FQDN:[Link]
Domain Ip: [Link]
Today Agenda:
Create certificates for node21 and node22
Create endpoints and configure always on.
Install pace maker and add ag into pace maker
Manually failover to another Node.
Step By step:
[Link] SELINUX and Firewall
vi /etc/sysconfig/selinux
Change SELINUX=enforcing to Permissive and save the file on both
nodes and
setenforce 0;systemctl stop firewalld;systemctl disable firewalld
[Link] Pacemaker software on both nodes
yum install pacemaker pcs fence-agents-all -y
[Link]: Once pacemaker was installed, you will see user created named
hacluster - we need reset the password for this user on both nodes
To reset the password for hacluster
passwd hacluster
4. Start and Enable PaceMaker Services on both nodes
systemctl start pcsd;systemctl enable pcsd
5. Add membership of the cluster using hacluster username
pcs cluster auth node21 node22 -u hacluster
6. create Cluster named "LinuxSQLCluster" in my case
pcs cluster setup --name LinuxSQLCluster node21 node22
[Link] Configuration of the Cluster on both nodes
cat /etc/corosync/[Link]
8. start the cluster on any one node.
pcs cluster start --all
9. check the status of the cluster and enable the cluster
pcs status
pcs cluster enable --all
[Link]: To disable the stonith device configuration in cluster
pcs property set stonith-enabled=false
[Link] SQL Server High Availability Package on both nodes
yum install mssql-server-ha
[Link] Always on Availability Groups and restart SQL Server on both
nodes
/opt/mssql/bin/mssql-conf set [Link] 1;systemctl restart
mssql-server
[Link] SSMS and create Certificate for each node
# Node Name : node21
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'Pass@123';
GO
CREATE CERTIFICATE node21_Cert
WITH SUBJECT = 'Node21 AG Certificate';
GO
BACKUP CERTIFICATE node21_Cert
TO FILE = '/var/opt/mssql/data/node21_Cert.cer';
GO
CREATE ENDPOINT SQLAG
STATE = STARTED
AS TCP (
LISTENER_PORT = 5022,
LISTENER_IP = ALL)
FOR DATABASE_MIRRORING (
AUTHENTICATION = CERTIFICATE node21_Cert,
ROLE = ALL);
GO
[Link] SSMS and create Certificate for each node
# Node Name : node22
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'Pass@123';
GO
CREATE CERTIFICATE node22_Cert
WITH SUBJECT = 'node22 AG Certificate';
GO
BACKUP CERTIFICATE node22_Cert
TO FILE = '/var/opt/mssql/data/node22_Cert.cer';
GO
CREATE ENDPOINT SQLAG
STATE = STARTED
AS TCP (
LISTENER_PORT = 5022,
LISTENER_IP = ALL)
FOR DATABASE_MIRRORING (
AUTHENTICATION = CERTIFICATE node22_Cert,
ROLE = ALL);
GO
[Link] Certificate of one node to other using SCP
# on Node21
scp -r /var/opt/mssql/data/node21_Cert.cer
root@node22:/var/opt/mssql/data/node21_Cert.cer
# On Node 22
scp -r /var/opt/mssql/data/node22_Cert.cer
root@node21:/var/opt/mssql/data/node22_Cert.cer
[Link] Ownership of certificate to mssql on each node
cd /var/opt/mssql/data/;chown mssql:mssql node21_Cert.cer;chown
mssql:mssql node22_Cert.cer;ls -ltr
17.
Create instance Level SQL User (AGUser in my case on each node) using
SSMS and Open SSMS and create User
USE [master]
GO
CREATE LOGIN [AGUser] WITH PASSWORD=N'123',
DEFAULT_DATABASE=[master], CHECK_EXPIRATION=OFF,
CHECK_POLICY=OFF
GO
ALTER SERVER ROLE [sysadmin] ADD MEMBER [AGUser]
GO
USE [master]
GO
CREATE USER [AGUser] FOR LOGIN [AGUser]
GO
USE [master]
GO
ALTER ROLE [db_owner] ADD MEMBER [AGUser]
GO
[Link] certificate of Other Nodes into the present node using SSMS
below: Login to Node21
CREATE CERTIFICATE node22_Cert
AUTHORIZATION AGUser
FROM FILE = '/var/opt/mssql/data/node22_Cert.cer';
GO
--Grant permission to connect to the endpoint of node21
GRANT CONNECT ON ENDPOINT::SQLAG TO AGUser;
--Login to Node22
CREATE CERTIFICATE node21_Cert
AUTHORIZATION AGUser
FROM FILE = '/var/opt/mssql/data/node21_Cert.cer';
Go
--Grant permission to connec to the endpoint of node22
GRANT CONNECT ON ENDPOINT::SQLAG TO AGUser;
19. Copy the back up from primary node to secondary node
scp -r /var/opt/mssql/data/[Link]
root@node22:/var/opt/mssql/data/
chown mssql:mssql /var/opt/mssql/data/[Link]; ls -ltr
20. Create Availability Group using SSMS with Cluster type External
[Link] 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
echo 'AGUser' >> ~/testpasswd;echo '123'>> ~/testpasswd
mv ~/testpasswd /var/opt/mssql/secrets/passwd;chmod 400
/var/opt/mssql/secrets/passwd
cat /var/opt/mssql/secrets/passwd
[Link] the AG resource in the Pacemaker cluster
pcs resource create LinuxRG ocf:mssql:ag ag_name=linuxag meta
failure-timeout=30s --master meta notify=true
[Link] IP resource for Listener
sudo pcs resource create ListenerAg ocf:heartbeat:IPaddr2 ip=[Link]
cidr_netmask=8
[Link] 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 LinuxRG-master then start
ListenerAg
# Let's Test Failover
================================
[Link] from running node to another node.
Check the resources running on which node by using below command
pcs status
pcs resource move LinuxRG-master node21 --master
# Check Constraints
pcs constraint list --full
[Link] to location constraint to make successfully automatic
failover
pcs constraint remove cli-prefer-LinuxRG-master
