Creating SQL Server AGs on Linux: Part 1 – Installing and Configuring Pacemaker

Introduction

Welcome to the first part of our series on setting up SQL Server availability groups on Linux. This series is designed to guide SQL Server DBAs, especially those with limited Linux experience, through the process of configuring a highly available SQL Server environment using Pacemaker on Ubuntu 20.04.

Our goal is to set up a three-node availability group (AG) with two synchronous replicas to ensure data redundancy and high availability. In this series, we will cover the following key steps:

• Configuring Pacemaker: Installing and setting up Pacemaker on all nodes to manage high availability.
• Creating the Availability Group: Enabling High Availability Disaster Recovery (HADR) on SQL Server and creating the availability group.
• Creating Resources: Configuring Pacemaker resources and constraints to manage the availability group.
• Failover Demonstration: Showing how to manually failover between the nodes to ensure proper functionality.
• Basic Troubleshooting: Discussing common issues and troubleshooting steps to maintain a healthy cluster.

By the end of this series, you will have a fully functional SQL Server availability group on Linux, providing robust high availability for your SQL Server instances. You will need three Ubuntu 20.04 hosts with SQL Server for Linux installed and initial configuration completed. This series does not cover the installation of SQL Server on Ubuntu because we have covered this in a previous post:

SQL Server on Linux: Getting Started with SQL Server 2022 on Ubuntu 22.04

Step 1: Install and Configure Pacemaker on All Nodes

To ensure high availability for SQL Server on Linux, we need to install Pacemaker on all our nodes. Pacemaker is a cluster resource manager that ensures your applications remain available in case of failures. Start by updating your package lists and installing the necessary Pacemaker packages. On each node, open a terminal and run the following command:

sudo apt update -y && sudo apt install -y pacemaker pcs pacemaker-cli-utils resource-agents fence-agents netcat

This command updates the package list and installs Pacemaker along with additional tools and agents needed for managing resources and cluster communication.

Next, set a password for the hacluster user. The hacluster user is a special user created by the Pacemaker installation, and we need to set a password for it to enable communication between cluster nodes. Run the following command:

sudo passwd hacluster

You will be prompted to enter a new password. For the purposes of this series, we will use ‘password’, but you should choose a secure password for your production environment.

After setting the password, enable and start the pcsd service, which is responsible for managing cluster communication and configuration. Execute the following commands:

sudo systemctl enable pcsd
sudo systemctl start pcsd

Finally, enable the Pacemaker service itself to ensure it starts on boot:

sudo systemctl enable pacemaker

Repeat these steps on all three nodes to ensure they are properly configured for Pacemaker.

Step 2: Create and Start the Pacemaker Cluster

With Pacemaker installed and configured on all nodes, the next step is to create and start the Pacemaker cluster. Begin by destroying any existing clusters to ensure a clean setup. Run this command on each node:

sudo pcs cluster destroy

This command ensures that any previous cluster configurations are removed, preventing potential conflicts.

Next, authenticate the nodes to allow them to communicate securely. This step is performed only on vm0, the primary node. Execute the following command:

sudo pcs host auth vm0 vm1 vm2 -u hacluster

You will be prompted to enter the hacluster password for each node. This command sets up authentication for all three nodes using the hacluster user.

Now, set up the cluster. Still on vm0, run:

sudo pcs cluster setup linuxclustersql vm0 vm1 vm2

This command initializes the cluster with the name linuxclustersql and includes vm0, vm1, and vm2 as the cluster nodes.

Start the cluster on all nodes with the following commands:

sudo pcs cluster start --all
sudo pcs cluster enable --all

These commands start the Pacemaker cluster services and ensure they are enabled on all nodes, making the cluster operational.

Step 3: Configure Cluster Properties

With the cluster up and running, we need to configure some properties to optimize its behavior. First, disable STONITH (Shoot The Other Node In The Head). STONITH is a fencing mechanism to ensure data integrity, but for simplicity, we will disable it in this tutorial. Run this command on vm0:

sudo pcs property set stonith-enabled=false

Next, set the cluster recheck interval to 2 minutes. This property determines how often the cluster checks its status and performs any necessary recovery actions:

sudo pcs property set cluster-recheck-interval=2min

These configurations help streamline the cluster’s operation, ensuring it runs efficiently without unnecessary checks or disruptions.

Conclusion

In this first part of our series, we have successfully installed and configured Pacemaker on all nodes, created the Pacemaker cluster, and set essential cluster properties. These steps lay the foundation for building a highly available SQL Server environment on Linux. In the following posts, we will delve into enabling High Availability Disaster Recovery (HADR) on SQL Server and creating the availability group. We will also demonstrate how to manually failover between the nodes and discuss basic troubleshooting techniques to keep your cluster healthy. Stay tuned for more detailed guidance as we continue this journey towards robust SQL Server high availability on Linux.