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

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