Configure SQL Server AlwaysOn Availability Groups on Linux - Part 2

Install and Configure SQL Server on three cluster servers.

In Part 1, we saw how to install and configure pacemaker with corosync. In this post, we will walk through installation of SQL Server and Linux resource Agent.We will also prepare the security requirements for authenticating the replicas of the Always On Availability Group.

Step #1: Install SQL Server on Linux

Install SQL Server 2017 on Linux. For instructions, see Install SQL Server on Linux.


Run below command to check status of SQL Server service

sudo systemctl status mssql-server




Step #2: Enable AlwaysOn AG and restart mssql-server

NOTE: Perform these steps on all of the Linux servers. Remember to log in with super user (root) privileges when performing these steps.
Enable AlwaysOn availability groups on each node that hosts a SQL Server instance. Then restart mssql-server. Run the following script:

sudo /opt/mssql/bin/mssql-conf set hadr.hadrenabled 1
systemctl restart mssql-server



Step #3: Create a SQL Server login for Pacemaker

NOTE: Perform this step on all of the SQL Server instances configured as replicas in the Always On Availability Group.

--Run this on all replicas
--Create login with name pcslogin
Use [master]
GO
CREATE LOGIN pcslogin
WITH PASSWORD = 'P@ssw0rd'
GO

--Grant SQL login appropriate permissions to manage the alwayson availability group.
Use [master]
GO
GRANT ALTER, CONTROL, VIEW, DEFINITION ON AVAILABILITY_GROUP::alwaysonlinuxag to pcslogin
GO
GRANT VIEW SERVER STATE TO pcslogin
GO




Step #4: Install the Linux cluster resource agent for SQL Server AlwaysOn AG

NOTE: Perform these steps on all of the Linux servers. 
A cluster resource agent is an executable that manages a cluster resource. This will be used to coordinate the SQL Server AlwaysOn Availability Group with the Linux cluster resource manager, similar to how AlwaysOn Availability Groups is managed by the WSFC on Windows via the cluster resource DLL. This is the same cluster resource agent that you will use to configure a SQL Server failover clustered instance on Linux.

sudo yum install mssql-server-ha


Run the command below to verify the installation of the Linux cluster resource agent for SQL Server Always-On Availability Groups.

sudo yum info mssql-server-ha




Step #5: Enable Always On AG endpoint port on the firewall for all Linux hosts

NOTE: Perform these steps on all of the Linux servers. Remember to log in with super user (root) privileges when performing these steps.

sudo firewall-cmd –zone=public –add-port=5022/tcp –permanent
sudo firewall –cmd --reload



Step #6: Create a database master key on the primary replica

Note: Perform this step on primary replica
Each SQL Server instance that will function as a primary replica requires a database master key. Starting with ALWAYSONLINUX01 as the primary replica, create the database master key.

USE master
GO
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'P@ssw0rd';
GO




Step #7: Create the certificate that will be used to encrypt the Availability Group endpoint

The SQL Server service on Linux uses certificates to authenticate communication between the mirroring endpoints. Note that the primary replica will be sending transaction log records on the secondary replicas on a regular basis. Hence, the primary replica has to be authenticated on the secondary replicas.

USE [master]
GO
CREATE CERTIFICATE alwaysonlinux01_cert
WITH SUBJECT = 'alwaysonlinux01_cert certificate for Availability Group'
GO




Step #8: Create the Always On Availability Group endpoint on the primary replica using the certificate for authentication

The following SQL script creates a listening endpoint named Endpoint_AvailabilityGroup for the availability group. The endpoint type is DATABASE_MIRRORING. The default port number is used 5022. The certificate created in above step alwaysonlinux01_cert is used to authenticate the endpoint.

USE master 
GO
CREATE ENDPOINT Endpoint_AvailabilityGroup
STATE = STARTED
AS TCP
(
   LISTENER_PORT = 5022, LISTENER_IP = ALL
) 
FOR DATABASE_MIRRORING
(
   AUTHENTICATION = CERTIFICATE alwaysonlinux01_cert,
   ENCRYPTION = REQUIRED ALGORITHM AES,
   ROLE = ALL
); 
GO



Step #9: Export the certificate to a file

Export the certificate to a file using a command as shown below.

USE master 
GO
BACKUP CERTIFICATE alwaysonlinux01_cert
TO FILE = '/var/opt/mssql/data/alwaysonlinux01_cert.cer'; 
GO




Step #10: Copy the certificate file to the secondary replicas

Run the command below to copy the certificate file from the primary replica to the secondary replicas’ default SQL Server data folder.

sudo scp /var/opt/mssql/data/alwaysonlinux01_cert.* root@alwaysonlinux02.dbera:/var/opt/mssql/data/
sudo scp /var/opt/mssql/data/alwaysonlinux01_cert.* root@alwaysonlinux03.dbera:/var/opt/mssql/data/


Repeat steps #4 to #8 on all secondary replicas
Since all of the replicas will function as a primary replica in the event of either an automatic or manual failover, repeat steps #4 to #8 on all of them.
A complete script for the secondary replica ALWAYSONLINUX02 is shown below.

--Run below script on AlwaysonLinux02 server
--Create Master Key
Use master
GO
Create master key encryption by password ='P@ssw0rd'
GO

--Create Certificate
Use master
GO
Create certificate alwaysonlinux02_cert with subject = 'alwaysonlinux02 certificate for Availability Group'
GO

--Create Endpoint
Create ENDPOINT Endpoint_AvailabilityGroup
STATE=STARTED
AS TCP
(
LISTENER_PORT=5022, LISTENER_IP=ALL
)
FOR DATABASE_MIRRORING
(
AUTHENTICATION = CERTIFICATE alwaysonlinux02_cert,
ENCRYPTION = REQUIRED ALGORITHM AES,
ROLE =ALL
);

--Backup Certificate
Backup Certificate alwaysonlinux02_cert to file ='/var/opt/mssql/data/alwaysonlinux02_cert.cer';
GO
--Copy Certificate to other replicas
--Execute the below command on terminal
sudo scp /var/opt/mssql/data/alwaysonlinux02_cert.* root@alwaysonlinux01.dbera:/var/opt/mssql/data/
sudo scp /var/opt/mssql/data/alwaysonlinux02_cert.* root@alwaysonlinux03.dbera:/var/opt/mssql/data/

A complete script for the secondary replica ALWAYSONLINUX03 is shown below.

--Create Master Key
Use [master]
GO
CREATE MASTER KEY ENCRYPTION BY PASSWORD='P@ssw0rd'
GO

--Create Certificate
USE [MASTER]
GO
CREATE CERTIFICATE alwaysonlinux03_cert
WITH SUBJECT = 'alwaysonlinux03 certificate for Availability Group'
GO

--Create Endpoint
USE [MASTER]
GO
CREATE ENDPOINT Endpoint_AvailabilityGroup
STATE=STARTED
AS TCP
(
LISTENER_PORT=5022,LISTENER_IP=ALL
)
FOR DATABASE_MIRRORING
(
AUTHENTICATION = CERTIFICATE alwaysonlinux03_cert,
ENCRYPTION = REQUIRED ALGORITHM AES,
ROLE=ALL
);
GO

--Backup Certificate
BACKUP CERTIFICATE alwaysonlinux03_cert
TO FILE = '/var/opt/mssql/data/alwaysonlinux03_cert.cer'
GO

--Copy Certificate to other replicas
--Execute the below command on terminal
sudo scp /var/opt/mssql/data/alwaysonlinux03_cert.* root@alwaysonlinux01.dbera:/var/opt/mssql/data/
sudo scp /var/opt/mssql/data/alwaysonlinux03_cert.* root@alwaysonlinux02.dbera:/var/opt/mssql/data/

Once completed, there should be three (3) certificate files on all SQL Server instances. Run the command below to list the contents of the default SQL Server data directory.



Step #11: Grant the SQL Server account permissions on certificate files

NOTE: Perform these steps on all of the Linux servers. Remember to log in with super user (root) privileges when performing these steps.

chown mssql:mssql /var/opt/mssql/data/alwaysonlinux*



Step #12: Create a login on the primary replica for use by the secondary replicas

The SQL Server login created on the primary replica is used by the secondary replicas to connect to the Always On Availability Group via the endpoint.

--Create a login on the primary replica
USE [MASTER]
GO
CREATE LOGIN AVG_LOGIN
WITH PASSWORD = 'P@ssw0rd'; 
GO

--Create a user for the login
USE [MASTER]
GO
CREATE USER AVG_LOGIN
FOR LOGIN AVG_LOGIN
GO



Step #13: Associate the certificate created in Step #5 with the user

The secondary replicas will use the database user created in Step #11 to connect to the primary replica. Since each secondary replica has a corresponding certificate, you need to associate those certificates with the user. The script below creates two certificates based on the ones generated by the secondary replicas and assigns both to the user.

--Associate certificate from alwaysonlinux03 with user
CREATE CERTIFICATE alwaysonlinux02_cert 
AUTHORIZATION AVG_LOGIN 
FROM FILE = '/var/opt/mssql/data/alwaysonlinux02_cert.cer' 
GO

 --Associate certificate from alwaysonlinux03 with user
CREATE CERTIFICATE alwaysonlinux03_cert 
AUTHORIZATION AVG_LOGIN 
FROM FILE = '/var/opt/mssql/data/alwaysonlinux03_cert.cer' 
GO




Step #14: Grant the CONNECT permission on the login

Grant CONNECT permissions on the login to successfully establish the communication between Always On Availability Group replicas.

--Run this on primary replica alwaysonlinux01
--Grant the CONNECT permission on the login
USE [MASTER] 
GO
GRANT CONNECT ON ENDPOINT::Endpoint_AvailabilityGroup
TO [AVG_LOGIN];   
GO




Repeat steps #10 to #13 on all secondary replicas
All of the replicas will function as a primary replica in the event of either an automatic or manual failover, you need to repeat steps #10 to #13 on all of them.
A complete script for the secondary replica ALWAYSONLINUX02 is shown below.

--Run this on the secondary replica alwaysonlinux02.
--Repeat the same process for steps #10 to #13 on the secondary replicas
USE master 
CREATE LOGIN AVG_login
WITH PASSWORD = 'P@ssw0rd'; 
GO
CREATE USER AVG_login 
FOR LOGIN AVG_login 
GO

--Associate certificate from LINUXHA-SQLAG1 with user
CREATE CERTIFICATE alwaysonlinux01_cert 
AUTHORIZATION AVG_login 
FROM FILE = '/var/opt/mssql/data/alwaysonlinux01_cert.cer' 
GO
--Associate certificate from LINUXHA-SQLAG3 with user
CREATE CERTIFICATE alwaysonlinux03_cert 
AUTHORIZATION AVG_login 
FROM FILE = '/var/opt/mssql/data/alwaysonlinux03_cert.cer' 
GO

GRANT CONNECT ON ENDPOINT::Endpoint_AvailabilityGroup
TO AVG_login; 
GO

A complete script for the secondary replica ALWAYSONLINUX03 is shown below.

--Run this on the secondary replica/alwaysonlinux03.
--Repeat the same process for steps #10 to #13 on the secondary replicas
USE master 
CREATE LOGIN AVG_login
WITH PASSWORD = 'P@ssw0rd'; 
GO
CREATE USER AVG_login 
FOR LOGIN AVG_login 
GO

--Associate certificate from LINUXHA-SQLAG1 with user
CREATE CERTIFICATE alwaysonlinux01_cert 
AUTHORIZATION AVG_login 
FROM FILE = '/var/opt/mssql/data/alwaysonlinux01_cert.cer' 
GO
--Associate certificate from LINUXHA-SQLAG3 with user
CREATE CERTIFICATE alwaysonlinux02_cert 
AUTHORIZATION AVG_login 
FROM FILE = '/var/opt/mssql/data/alwaysonlinux02_cert.cer' 
GO

GRANT CONNECT ON ENDPOINT::Endpoint_AvailabilityGroup
TO AVG_login; 
GO

No comments:

Post a Comment