SQL Server Clustering on Linux(Ubuntu)--> Part 4

Welcome Back for the part-4

Before proceeding with the installation of pacemaker there are few steps that we need to follow given below are the same.

1) On both cluster nodes, create a file to store the SQL Server username and password for the Pacemaker login. The following command creates and populates this file:

sudo touch /var/opt/mssql/secrets/passwd

sudo echo '<loginName>' >> /var/opt/mssql/secrets/passwd  
sudo echo '<loginPassword>' >> /var/opt/mssql/secrets/passwd
sudo chown root:root /var/opt/mssql/secrets/passwd

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

2) Install pacemaker package on both the nodes by running the below commands

    sudo apt-get update

    sudo apt-get install pacemaker pcs fence-agents resource-agents

3)Set the password for for the default user that is created when installing Pacemaker and Corosync packages. Use the same password on both nodes.

 sudo passwd hacluster

4)Enable and start pcsd service and Pacemaker.This will allow nodes to rejoin the cluster after the reboot. Run the following command on both nodes.

  sudo systemctl enable pcsd 

  sudo systemctl start pcsd 
  sudo systemctl enable pacemaker(In case if you get errors kindly ignore them)

5)Install the FCI resource agent for SQL Server. Run the following commands on both nodes.

   sudo apt-get  install mssql-server-ha

6)We are now ready to setup the cluster with the help of below commands

    sudo pcs cluster auth **<nodeName1 nodeName2 …>** -u hacluster

   sudo pcs cluster auth ubusqlclnod1nfs ubusqlclnod2nfs -u hacluster -p 
 (Enter the password of what we have created  for hacluster as part of step 3)

 sudo pcs cluster setup --name **<clusterName>** **<nodeName1 nodeName2 …>**

 sudo pcs cluster setup --name sriramlinux ubusqlclnod1nfs ubusqlclnod2nfs

 sudo pcs cluster start --all 

Note: There are chances for getting errors when running the above commands. 

As Running 'sudo apt-get install pcs' installs pacemaker, corosync, and pcs at the same time and starts running all 3 of the services. Starting corosync generates a template '/etc/corosync/corosync.conf' file. 

There are 2 ways to overcome this 

Method 1:To have above steps succeed this file should not exist - so the workaround is to stop pacemaker / corosync and delete '/etc/cluster/corosync.conf', 

sudo systemctl stop corosync
sudo systemctl stop pacemaker
rm /etc/corosync/corosync.conf

Method 2:  Running the below command which does the same thing, and you can use it as one one time initial cluster setup step.

sudo pcs cluster destroy

7) Configure the cluster resources for SQL Server, File System and virtual IP resources and push the configuration to the cluster. You will need the following information:
  • SQL Server Resource Name: A name for the clustered SQL Server resource.
  • Floating IP Resource Name: A name for the virtual IP address resource.
  • IP Address: THe IP address that clients will use to connect to the clustered instance of SQL Server.
  • File System Resource Name: A name for the File System resource.
  • device: The NFS share path
  • device: The local path that it's mounted to the share
  • fstype: File share type (i.e. nfs)

sudo pcs cluster cib cfg
sudo pcs -f cfg resource create **<sqlServerResourceName>** ocf:mssql:fci op defaults timeout=**<timeout_in_seconds>**
sudo pcs -f cfg resource create **<floatingIPResourceName>** ocf:heartbeat:IPaddr2 ip=**<ip Address>**
sudo pcs -f cfg resource create **<fileShareResourceName>** Filesystem device=**<networkPath>** directory=**<localPath>**         fstype=**<fileShareType>**
sudo pcs -f cfg constraint colocation add **<virtualIPResourceName>** **<sqlResourceName>**
sudo pcs -f cfg constraint colocation add **<fileShareResourceName>** **<sqlResourceName> **

sudo pcs cluster cib-push cfg

Example as follows

sudo pcs cluster cib cfg
sudo pcs -f cfg resource create mssqlha ocf:mssql:fci op defaults timeout=60s
sudo pcs -f cfg resource create virtualip ocf:heartbeat:IPaddr2 ip=
sudo pcs -f cfg resource create fs Filesystem device="" directory="/var/opt/mssql/data" fstype="nfs"
sudo pcs -f cfg constraint colocation add virtualip mssqlha
sudo pcs -f cfg constraint colocation add fs mssqlha

sudo pcs cluster cib-push cfg

Note: Run all the commands on your primary node. In my case it is ubusqlclnod1nfs.

8) After the configuration is complete, SQL Server will run on one of the nodes in Cluster

9)Use the following command to confirm that the related SQL Server service in the Cluster is working properly:

 sudo pcs status

In the final part of the post we would see how to fail-over the resources from one node to another & also will give you the commands to make use for them.