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 systemctl restart mssql-server

# Open SSMS and create Certificate for each node

# Node Name : TBSLinuxNode1

GO

CREATE CERTIFICATE TBSLinuxNode1_Cert
WITH SUBJECT = 'TBSLinuxNode1 AG Certificate';

GO

BACKUP CERTIFICATE TBSLinuxNode1_Cert

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)

GO

CREATE CERTIFICATE TBSLinuxNode2_Cert
WITH SUBJECT = 'TBSLinuxNode2 AG Certificate';

GO

BACKUP CERTIFICATE TBSLinuxNode2_Cert

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

# On Node 2

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

# 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

# Grant permission to connec to the endpoint of TBSLinuxNode1

GRANT CONNECT ON ENDPOINT::TBSSQLAG TO TBSAGUser;

CREATE CERTIFICATE TBSLinuxNode1_Cert
AUTHORIZATION TBSAGUser

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

[aswinikumar]

Nice one, Pre-requisite for this to start is like, Add 2 Node serves to Cluster?. what could be the reason of failure " using scp to copy the cert".

[TranDat]

Hi. This video is amazing. But I'm facing a problem with my two nodes cluster. When primary node goes down, the cluster failover to secondary node successfully. But I can't access the database. It in "Not synchonizing" state until the failed node go up again. Can you help me?

[raghavacademy]

Hi @TechBrothers,
i face this issue, my secondary replica is disconnected state.
can you help me?
A connection timeout has occurred while attempting to establish a connection to availability replica 'SQLSERVERCLUS02' with id Either a networking or firewall issue exists, or the endpoint address provided for the replica is not the database mirroring endpoint of the host server instance.

[ThierryC]

just a tiny remark, when you are logged in as root on a linux terminal, there is no need to sudo. Sudo is used when root permissions are required when logged in as a normal user. Too bad, there that the step about the listener and virtual IP are never explained in any Microsoft documentation so, people create a virtual IP without creating the listener and dns entries and it does not work. Now, the failover does not work since the database becomes unavailable to any of the hosts. I am looking forward to a way of making it working.

[alexanderarandaduran]

Thank you for the tutorial. But, I understand not the steps of creation the Listener... Can you, help me? I need more detail. What is the IP 192.168.1.104/24? A physical machine? Another instance? Thank you in advance.

[munny]

Hi TechGuru,


Could you please suggest on how to bring LinuxSQLProdLis in start state..upon checking pcs status, it says that LinuxSQLProdLis is in Stop state?
Here is the result :
2 nodes configured
5 resources configured

Online: [ NodeA NodeB ]

Full list of resources:

VirtIP (ocf::heartbeat:IPaddr): Started NodeA
Httpd (ocf::heartbeat:apache): Started NodeA
Master/Slave Set: LinuxRG-master [LinuxRG]
Stopped: [ NodeA NodeB ]
LinuxSQLProdLis (ocf::heartbeat:IPaddr2): Stopped

Daemon Status:
corosync: active/enabled
pacemaker: active/enabled
pcsd: active/enabled


Thanks in advance !!