Always On Availability Groups on Linux with Pacemaker

2020-03-07 · mssql, linux, high-availability

On Windows, Availability Groups rely on Windows Server Failover Clustering for quorum, health monitoring and automatic failover. On Linux there is no WSFC, so SQL Server 2017 and 2019 delegate those jobs to Pacemaker, the standard Linux cluster resource manager, through an ocf:mssql:ag resource agent that Microsoft ships. SQL Server handles the data movement; Pacemaker decides who is primary. The two are loosely coupled, which is why the AG is created with CLUSTER_TYPE = EXTERNAL.

This post builds a three-node synchronous AG on Ubuntu 16.04 with SQL Server 2017 (the same steps apply to 2019 on the Ubuntu and RHEL versions it supports). Three nodes is the practical minimum; with two, Pacemaker cannot tell a failed node from a network partition, and the AG will not fail over automatically.

The first time I tried this on two nodes I spent a day wondering why nothing failed over. Start with three Ubuntu 16.04 servers on the same mssql-server build, with mssql-tools, and hostnames that resolve from every node (/etc/hosts is fine). Open 1433 and 5022 between nodes, plus 2224/tcp, 3121/tcp and 5405/udp for Pacemaker. Have a test database in FULL recovery with a full backup on the intended primary.

Enable Always On on every node

sudo /opt/mssql/bin/mssql-conf set hadr.hadrenabled 1
sudo systemctl restart mssql-server

Certificates and endpoints

On the primary, create a certificate and copy it to the other nodes; there is no Active Directory to handle endpoint authentication:

CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'M@sterKey#Str0ng2020';
CREATE CERTIFICATE dbm_certificate WITH SUBJECT = 'dbm';
BACKUP CERTIFICATE dbm_certificate
   TO FILE = '/var/opt/mssql/data/dbm_certificate.cer'
   WITH PRIVATE KEY (FILE = '/var/opt/mssql/data/dbm_certificate.pvk',
                     ENCRYPTION BY PASSWORD = 'Pr1vateKey#Str0ng2020');

Copy both files to /var/opt/mssql/data/ on the secondaries, chown mssql:mssql them, and on each secondary:

CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'M@sterKey#Str0ng2020';
CREATE CERTIFICATE dbm_certificate
   FROM FILE = '/var/opt/mssql/data/dbm_certificate.cer'
   WITH PRIVATE KEY (FILE = '/var/opt/mssql/data/dbm_certificate.pvk',
                     DECRYPTION BY PASSWORD = 'Pr1vateKey#Str0ng2020');

Then on all three nodes:

CREATE ENDPOINT hadr_endpoint
    AS TCP (LISTENER_PORT = 5022)
    FOR DATABASE_MIRRORING (ROLE = ALL, AUTHENTICATION = CERTIFICATE dbm_certificate, ENCRYPTION = REQUIRED ALGORITHM AES);
ALTER ENDPOINT hadr_endpoint STATE = STARTED;

The AG, with an external cluster type

On the primary:

CREATE AVAILABILITY GROUP ag1
WITH (CLUSTER_TYPE = EXTERNAL)
FOR DATABASE ShopDB
REPLICA ON
  N'sqlnode1' WITH (ENDPOINT_URL = N'tcp://sqlnode1:5022', AVAILABILITY_MODE = SYNCHRONOUS_COMMIT,
                    FAILOVER_MODE = EXTERNAL, SEEDING_MODE = AUTOMATIC),
  N'sqlnode2' WITH (ENDPOINT_URL = N'tcp://sqlnode2:5022', AVAILABILITY_MODE = SYNCHRONOUS_COMMIT,
                    FAILOVER_MODE = EXTERNAL, SEEDING_MODE = AUTOMATIC),
  N'sqlnode3' WITH (ENDPOINT_URL = N'tcp://sqlnode3:5022', AVAILABILITY_MODE = SYNCHRONOUS_COMMIT,
                    FAILOVER_MODE = EXTERNAL, SEEDING_MODE = AUTOMATIC);
ALTER AVAILABILITY GROUP ag1 GRANT CREATE ANY DATABASE;

On each secondary:

ALTER AVAILABILITY GROUP ag1 JOIN WITH (CLUSTER_TYPE = EXTERNAL);
ALTER AVAILABILITY GROUP ag1 GRANT CREATE ANY DATABASE;

Also create a login for Pacemaker on every node and grant it what the resource agent needs:

CREATE LOGIN pacemakerLogin WITH PASSWORD = 'P@cemaker#Str0ng2020';
ALTER SERVER ROLE sysadmin ADD MEMBER pacemakerLogin;

Store the credentials on each node where the agent reads them:

echo 'pacemakerLogin' | sudo tee /var/opt/mssql/secrets/passwd
echo 'P@cemaker#Str0ng2020' | sudo tee -a /var/opt/mssql/secrets/passwd
sudo chown root:root /var/opt/mssql/secrets/passwd
sudo chmod 400 /var/opt/mssql/secrets/passwd

Pacemaker

On all nodes:

sudo apt-get install -y pacemaker pcs fence-agents resource-agents mssql-server-ha
sudo passwd hacluster
sudo systemctl enable pcsd; sudo systemctl start pcsd

Then on one node:

sudo pcs cluster destroy   # clear any default config
sudo pcs cluster auth sqlnode1 sqlnode2 sqlnode3 -u hacluster -p 'the-hacluster-password'
sudo pcs cluster setup --name sqlcluster sqlnode1 sqlnode2 sqlnode3
sudo pcs cluster start --all
sudo pcs cluster enable --all
sudo pcs property set stonith-enabled=false   # lab only; production needs a fencing agent
sudo pcs property set cluster-recheck-interval=2min
sudo pcs property set start-failure-is-fatal=true

Fencing (STONITH) matters in production: without it a hung node may still be writing while another becomes primary. I only ever disable it in a lab. Use a fence agent for your platform (fence_vmware_soap, fence_ipmilan, cloud-specific agents).

AG resource and virtual IP

sudo pcs resource create ag_cluster ocf:mssql:ag ag_name=ag1 meta failure-timeout=60s \
    master notify=true
sudo pcs resource create virtualip ocf:heartbeat:IPaddr2 ip=10.0.0.80
sudo pcs constraint colocation add virtualip ag_cluster-master INFINITY with-rsc-role=Master
sudo pcs constraint order promote ag_cluster-master then start virtualip

The master keyword makes it a multi-state resource; Pacemaker promotes one instance to Master, which the agent maps to the SQL Server primary. The colocation and ordering constraints keep the virtual IP on the primary. Clients connect to 10.0.0.80 and the AG's read/write role follows it.

Does it hold up?

sudo pcs status

You should see ag_cluster with one Masters node and two Slaves, and virtualip started on the master. In SQL Server:

SELECT ar.replica_server_name, ars.role_desc, drs.synchronization_state_desc
FROM sys.dm_hadr_availability_replica_states ars
JOIN sys.availability_replicas ar ON ars.replica_id = ar.replica_id
JOIN sys.dm_hadr_database_replica_states drs ON ars.replica_id = drs.replica_id;

Test a controlled failover through Pacemaker, never with T-SQL, on an external cluster:

sudo pcs resource move ag_cluster-master sqlnode2
sudo pcs resource clear ag_cluster-master   # remove the location constraint the move created

Things that went wrong for me

ag_cluster stuck at Stopped with "not installed" in pcs status. The mssql-server-ha package is missing on that node or the /var/opt/mssql/secrets/passwd file is absent or has the wrong permissions.

"Msg 41131: Failed to bring availability group 'ag1' online" after a manual T-SQL failover. With CLUSTER_TYPE = EXTERNAL, ALTER AVAILABILITY GROUP ... FAILOVER is not allowed; Pacemaker owns the role. Use pcs resource move.

Secondary shows NOT SYNCHRONIZING after seeding. Check sys.dm_hadr_automatic_seeding on the primary; a common cause is a missing GRANT CREATE ANY DATABASE on the secondary or different data directory paths.

Write transactions hang after a node goes down. REQUIRED_SYNCHRONIZED_SECONDARIES_TO_COMMIT is set to 1 (which the resource agent sets automatically for three synchronous replicas), and with only one secondary left it cannot be met. This is by design for data safety; restore the third node or adjust the setting.

It's the same engine as on Windows with a different referee. Fencing isn't optional once real data is involved.