Configure SQL Server AlwaysOn Availability Groups on Linux - Part 1

Install and configure a cluster resource manager - Pacemaker

SQL Server 2017 introduces two different architectures for availability groups. Always On availability groups provide high availability, disaster recovery, and read-scale balancing. These availability groups require a cluster manager. In Windows, failover clustering provides the cluster manager. In Linux, you can use Pacemaker.
Pacemaker is an open source cluster manager software that achieves maximum high availability for services. It's an advanced and scalable HA cluster manager distributed by ClusterLabs. Corosync is a group communication system with additional features for implementing High Availability within applications.

Step #1: Install CentOS operating system

The configuration used in this tip consists of three (3) Linux servers running CentOS 7 is a free, enterprise-class, community-supported distribution based on Red Hat Enterprise Linux (RHEL). And because the distribution is based on RHEL, the instructions for installing and configuring SQL Server on Linux RHEL apply to CentOS 7.
The Linux servers were installed using the Infrastructure Server option and including the High Availability Add-Ons.

Step #2: Configure the hosts file.

NOTE: Perform this step on each node in a cluster.
The hosts file on every server contains the IP addresses and names of all servers that will participate in the availability group. Update /etc/hosts on all the servers.
The following script lets you edit /etc/hosts

sudo vi /etc/hosts

The following screenshot shows /etc/hosts on AlwaysOnLinux01.



Step #3 Disable SELinux setting

NOTE: Perform this step on each node in a cluster.
SELinux allows administrators and users more control over access controls. It allows access based on SELinux policy rules. SELinux policy rules specify how processes and users interact with each other as well as how processes and users interact with files.Disable SELinux policy as shown below:



Step #4 Setting up SSH trust between servers

To configure password-less login on CentOS 7 using ssh keys to connect to remote servers please refer to the link Setting up SSH Passwordless Login Using SSH Keygen

Step #5: Install the Pacemaker packages

NOTE: Perform this step on each node in a cluster.
Run the command below to install Pacemaker and all of the related packages. This is similar to installing the Fail-over Clustering feature in Windows.

sudo yum install pacemaker pcs fence-agents-all resource-agents



Step #6: Start the pcs daemon and force it to start on system boot

NOTE: Perform this step on each node in a cluster.
Before the cluster can be configured, the pcsd daemon must be started and enabled to boot on startup on each node. This daemon works with the pcs command to manage configuration across the nodes in the cluster.
Execute the following commands to start the pcsd service and to enable pcsd at system start.

sudo systemctl start pcsd
sudo systemctl enable pcsd 


Step #7: Configure the Linux firewall to allow pacemaker communications

NOTE: Perform this step on each node in a cluster.
Run the command below to allow Pacemaker communications between cluster nodes. By default, FirewallD is the firewall solution available on RHEL/CentOS.

sudo firewall-cmd --zone=public --add-port=2224/tcp --permanent
sudo firewall-cmd --zone=public --add-port=3121/tcp –permanent
sudo firewall-cmd --zone=public --add-port=5405/udp --permanent 
sudo firewall-cmd --reload 



Step #8: Force the Pacemaker and Corosync daemons to start on system boot

NOTE: Perform this step on each node in a cluster.
Before the cluster can be configured, the pcsd daemon must be started and enabled to boot on startup on each node. This daemon works with the pcs command to manage configuration across the nodes in the cluster.
Execute the following commands to start the pcsd service and to enable pcsd at system start.

sudo systemctl enable pacemaker.service
sudo systemctl enable corosync.service   


Step #9: Assign a password to the pcs administrator account

NOTE: Perform this step on each node in a cluster.
In order to use pcs to configure the cluster and communicate among the nodes, we need to set a password on each node for the user hacluster, which is the pcs administration account. It is recommended that the password for user hacluster be the same on each node.

sudo passwd hacluster  



Step #10: Setup authentication between the Linux cluster nodes

NOTE: Perform this step on ANY of the node in a cluster.
Authenticate the pcs user hacluster for each node in the cluster. The following command authenticates user hacluster on all three of the nodes in the cluster.

sudo pcs cluster auth alwaysonlinux01.dbera alwaysonlinux02.dbera alwaysonlinux03.dbera -u hacluster  


Step #11: Create the Linux cluster

NOTE: Perform this step on ANY of the node in a cluster.
Execute the following command to create the three-node cluster DBERACLUSTER that consists of nodes ALWAYSONLINUX01.dbera, ALWAYSONLINUX02.dbera and ALWAYSONLINUX03.dbera. This will propagate the cluster configuration files to all the nodes in the cluster.

sudo pcs cluster setup –name dberacluster alwaysonlinux01.dbera alwaysonlinux02.dbera alwaysonlinux03.dbera


Step #12: Start the Linux cluster

NOTE: Perform this step on ANY of the node in a cluster.
Run the command below to start the cluster service on all nodes.

sudo pcs cluster start --all


Step #13: Configure fencing

NOTE: Perform this step on each node in a cluster.
This is not recommended in a production environment. Properly configure a STONITH device and keep it enabled.Run the command below to disable STONITH.

sudo pcs property set stonith-enabled=false



Step #14: Configure resource-level policies

NOTE: Perform this step on all of the Linux servers. 
Run the command below to set the cluster property start-failure-is-fatal to false

sudo pcs property set start-failure-is-fatal=false



Step #15: Save credentials for the Pacemaker login on the local file system

NOTE: Perform this step on all of the Linux servers. Remember to log in with super user (root) privileges when performing these steps.
Run the command below to create the file named passwd using the vi command.

sudo vi /var/opt/mssql/secrets/passwd



Run the command below to restrict access to the file by only allowing the file owner (root) read-only permissions.

sudo chmod 400 /var/opt/mssql/secrets/passwd



All you’ve done up to this point is to install and configure Pacemaker in preparation for configuring SQL Server Always On Availability Group to run on top of the cluster. Run the command below to check the status of the cluster.

sudo pcs status --full




Refer the link below to configure SQL Server Always On Availability Group on Linux

Note: Perform these steps in order.

SQL Server AlwaysOn Availability Groups on Linux - Part 1

SQL Server AlwaysOn Availability Groups on Linux - Part 2

SQL Server AlwaysOn Availability Groups on Linux - Part 3

SQL Server AlwaysOn Availability Groups on Linux - Part 4




No comments:

Post a Comment