Configure pipeline
# 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