SQL Server Always On Availability Group (Linux, Pacemaker)

Builds a Pacemaker-managed Always On AG from three Linux servers (RHEL 9 or Ubuntu 22.04) with SQL Server 2022 installed: mssql-server-ha, hadr.hadrenabled, database mirroring endpoints authenticated by certificate, a CLUSTER_TYPE = EXTERNAL AG (3 synchronous replicas or 2 synchronous + configuration-only), automatic seeding, the pacemaker/corosync/pcs cluster, fencing (STONITH), the ocf:mssql:ag resource, the listener IP resource and constraints, and listener and planned failover tests. The result is an AG where Pacemaker fails over automatically when the primary goes down and the listener IP moves with the primary.

Install every node first with SQL Server installation on Linux. For Windows and WSFC see Always On AG (Windows); for day-to-day operation and failures of an existing AG see AG operations.

Topology: 3 Linux nodes: 3 synchronous replicas or 2 synchronous + a configuration-only replica

Before you start

• SQL Server 2022 is installed on all three nodes with the same version and CU (installation on Linux)…; the configuration-only node may also run Express…, and … is sysadmin. Password: read -rsp 'Password: ' SQLCMDPASSWORD; export SQLCMDPASSWORD and sudo --preserve-env=SQLCMDPASSWORD bash <script>.sh.
• On every node the output of hostname matches @@SERVERNAME in SQL Server exactly and matches the name in the node list. If not, the Pacemaker resource cannot find the replica (RESOLVING).
• The nodes resolve each other by name (DNS or /etc/hosts) and these ports are open: SQL …/tcp, endpoint …/tcp, Pacemaker 2224/tcp, 3121/tcp, 5403/tcp, 21064/tcp, 5404–5405/udp. The listener IP (…) is not in use.
• The RHEL subscription and the High Availability Add-On are enabled on every node (rhel-9-for-x86_64-highavailability-rpms repository).
• The node clocks are synchronized by NTP (chronyd / systemd-timesyncd); corosync and certificates are sensitive to clock skew.
• …The BMC (iLO/iDRAC/IPMI) address of every node is reachable from the nodes; the fencing user has power off/on rights.…vCenter (…) is reachable from the nodes on 443/tcp; the fencing account has power operation rights on these VMs.… Red Hat supports only supported fencing methods (for the same virtualization platform).
• Passwords are ready and stored in the vault: SQL master key, certificate private key backup, the … login, the hacluster user…, the fencing account…. The steps prompt for passwords without echoing them.
• Maintenance window: the SQL Server services are restarted once (hadr.hadrenabled) and the failover test interrupts the listener for a few seconds.

Phases of the guide

• Prepare the nodes — Add the node names to the hosts file · Open the SQL, endpoint and Pacemaker ports in the firewall
• SQL Server: HA agent, Always On and endpoints — Install the SQL Server HA agent (mssql-server-ha) · Enable Always On and restart SQL Server · Start the AlwaysOn_health session at startup · Create the SQL Server login and password file for Pacemaker · Create and back up the endpoint certificate (node 1) · Copy the certificate files to the other nodes (node 1) …
• Availability Group (CLUSTER_TYPE = EXTERNAL) — Create the AG (node 1) · Join the secondary to the AG: … · Switch the database to FULL recovery, back it up and add it to the AG (node 1) · Monitor automatic seeding and synchronization (node 1)
• Pacemaker cluster — Install Pacemaker, corosync and pcs · Set the hacluster password and start pcsd · Remove Ubuntu's default cluster configuration · Create and start the cluster (node 1) · Configure fencing (STONITH) (node 1) · Set the cluster properties (node 1) …
• Listener — Create the listener (node 1) · Test the connection through the listener
• Verification and failover test — Check AG health · Planned failover test: make … the primary · Fail back: make … the primary again
• Post-installation — Synchronize logins, jobs and server objects · Set up backups and monitoring