Configure SQL Server AlwaysOn Availability Groups on Linux - Part 4

Creating the SQL Server AlwaysOn Availability Group Resources on Pacemaker

Pacemaker concept is very similar to Windows Server Failover Cluster (WSFC).The cluster resource agent that is installed in Part 2 - mssql-server-ha - is the interface between Pacemaker and SQL Server. It tells Pacemaker about the status and health of the resource – in this case, the SQL Server Always On Availability Group - so the cluster can decide how to deal with it to maintain high availability.
When SQL Server Always On Availability Group is created on a WSFC, a cluster resource is created automatically. This is done thru the tight integration between SQL Server and the WSFC.

With Pacemaker, we need to to manually create the cluster resources. Here’s a high-level overview of the steps to integrate SQL Server and Pacemaker. 
  • Create the Always On Availability Group resource on Pacemaker
  • Create the virtual IP address resource for the Always On Availability Group listener
  • Configure the Always On Availability Group resource to run on the same machine as the virtual IP address resource
  • Configure the order in which the cluster resources should start/stop

Step #1: Create the AlwaysOn AG Resource on Pacemaker

NOTE: Perform this step on ANY of the Linux servers. No need to run it on all nodes. Run the command below to create the Always On Availability Group resource on Pacemaker.

sudo pcs resource create alwaysonlinuxag ocf:mssql:ag ag_name= alwaysonlinuxag master notify=true 


Step #2: Create the virtual IP address resource for the Always On Availability Group listener

NOTE: Perform this step on ANY of the Linux servers. No need to run it on all nodes. Remember to log in with super user (root) privileges when performing these steps. This example uses AVGListener to run the command.

sudo pcs resource create AVGListener ocf:heartbeat:IPaddr2 ip=172.26.58.124 cidr_netmask=25


Step #3: Configure the AlwaysOn AG resource to run on the same machine as the virtual IP address resource

NOTE: Perform this step on ANY of the Linux servers. No need to run it on all nodes. 
Run the command below to add colocation constraint to make sure that the Always On Availability Group resource run on the same machine as the virtual IP address resource.

sudo pcs constraint colocation add AVGListener alwaysonlinuxag-master INFINITY with-rsc-role=Master


Step #4: Configure the order in which the cluster resources should start/stop

NOTE: Perform this step on ANY of the Linux servers. No need to run it on all nodes. 
Run the command below to configure the order in which the cluster resources should start/stop.

sudo pcs constraint order promote alwaysonlinuxag-master then start AVGListener
sudo pcs status


NOTE: Once the AlwaysOn Availability Group is added as a cluster resource on Pacemaker, ALTER AVAILABILITY GROUP command and SQL Server Management Studio can no longer be used to initiate manual failover. The proper way to initiate a manual failover is thru the use of the pcs resource move command.




Testing Automatic Failover

An easy way to test automatic failover is to stop cluster service at node that is currently running the primary replica. Run the command below to initiate an immediate shutdown.

pcs cluster stop


No comments:

Post a Comment