SQL Server Always On AG operations (Windows and Linux)

Not an installation: day-to-day operation and failure scenarios of an installed Always On Availability Group: health check, diagnosing lag and log growth, planned failover, forced failover (FORCE_FAILOVER_ALLOW_DATA_LOSS) and its aftermath, suspending/resuming data movement, synchronous/asynchronous mode, adding databases (automatic seeding or backup/restore), adding/removing replicas, read-only routing and listener changes. With WSFC commands on Windows and Pacemaker (pcs) commands on Linux.

Choose what to do from the scenario card; you can also open the relevant scenario directly by typing the error number (e.g. 9002, 35264, 41142) into the catalog search. If there is no AG yet: AG (Windows) or AG (Linux).

Topology: AG with 2–9 replicas (Windows WSFC or Linux Pacemaker)

Scenarios

• AG health check (dashboard queries) — Is everything fine? Replica / database state, failover readiness, the cluster and recent role changes. Changes nothing.
• Secondary falling behind / log file growing (lag diagnosis) — The send/redo queue is growing, data on the secondary is stale, the log on the primary is about to fill up (log_reuse_wait = AVAILABILITY_REPLICA) or commits are slow (HADR_SYNC_COMMIT).
• Planned failover (switchover, no data loss) — Moves the primary to a synchronous, SYNCHRONIZED secondary for maintenance, patching or testing; a few seconds of downtime, no data loss.
• Primary lost: forced failover (FORCE_FAILOVER_ALLOW_DATA_LOSS) — The primary (or the whole site / cluster quorum) is lost and you cannot wait: a secondary is made primary, accepting data loss; then the data is compared and the other replicas are resumed.
• Suspend / resume data movement (SUSPEND / RESUME) — Temporarily stop sending data to a secondary (network maintenance, bulk operations) or resume a database that is stuck in SUSPENDED.
• Change synchronous / asynchronous mode — Switch a replica between SYNCHRONOUS_COMMIT and ASYNCHRONOUS_COMMIT (remote site, slow network, DR drill) and configure commit protection (REQUIRED_SYNCHRONIZED_SECONDARIES_TO_COMMIT).
• Add a database to the AG (automatic seeding or backup/restore) — Bring a new or existing database into the AG: automatic seeding for small/medium databases, from backup for large databases or over a slow network.
• Add a replica (node) — Add a new secondary to the AG: the node joins the cluster, the endpoint is prepared, ADD REPLICA + JOIN, and the databases arrive by seeding.
• Remove a replica (node) — Remove a secondary that will be decommissioned or rebuilt from the AG and from the cluster.
• Read-only routing — Route ApplicationIntent=ReadOnly connections through the listener to readable secondaries (offloading reporting from the primary).
• Create or modify the listener (IP, port) — Create the listener if there is none, change its port or (on Windows) add an IP for a second subnet.

Before you start

• sqlcmd (/opt/mssql-tools18/bin/sqlcmd) and … as sysadmin on every node; root privileges (pcs). Password: read -rsp 'Password: ' SQLCMDPASSWORD; export SQLCMDPASSWORD and sudo --preserve-env=SQLCMDPASSWORD bash <script>.sh.
• PowerShell is opened as administrator with a domain account that is sysadmin in SQL Server and an administrator in WSFC; sqlcmd and the FailoverClusters module are available.
• Row 1 of the replica list in the form is the current primary (verify with the first query of the health check); the steps are placed on the right node according to this order.
• The change window is approved and the application team has been informed…: the listener is interrupted for a few seconds and open transactions are rolled back……: a port/IP change affects the connection strings….
• The decision for a forced failover was made by an authorized person and data loss has been accepted: the primary is unreachable and will not come back within a reasonable time. If the primary can come back, waiting or a planned failover is always better.

Phases of the guide

• AG health check — AG dashboard: replicas, databases, failover readiness · Cluster status (WSFC) · Cluster status (Pacemaker) · Recent role changes (AlwaysOn_health)
• Diagnosing lag and log growth — Diagnosis: queues, rates and estimated catch-up time (primary) · Diagnosis: commit waits (primary) · Diagnosis: is redo blocked? (on the secondaries) · Resolution options and log check
• Planned failover — Diagnosis: is the target ready for failover? (…) · Safety net: any long transactions or running jobs? (primary) · Failover: make … the primary (T-SQL) · Failover: make … the primary (pcs) · Verify: new primary and listener
• Forced failover (data loss possible) — Diagnosis: target state and possible data loss (…) · Force-start the WSFC quorum (…) · Stop Pacemaker from managing the AG resource (…) · Forced failover: make … the primary · Verify: new primary open, the others SUSPENDED · Take a snapshot on the old primary to recover the lost data (…, when it comes back) …
• Suspend / resume data movement — Diagnosis: suspended databases and the reason · …Suspend data movement (SUSPEND)…Resume data movement (RESUME)… · Verify and monitor
• Changing the availability mode — Diagnosis: current modes and commit protection · … → … · Configure commit protection (Windows) · Configure commit protection (Pacemaker) · Verify
• Add a database to the AG — Diagnosis: is the database eligible for the AG? (primary) · Prepare: FULL recovery and backup (primary) · Switch the replicas to manual seeding (primary) · Add the database to the AG (primary) · Monitor seeding (primary) · Prepare from backup on the secondary (NORECOVERY): … …
• Add replica — Add the new node to WSFC (primary) · Enable Always On (new node) · Create the endpoint and grant permissions (new node) · Prepare the new node: HA agent, Always On, cluster software · Copy the endpoint certificate to the new node (primary) · Certificate, endpoint and Pacemaker login (new node) …
• Remove a replica — Diagnosis: the replica to remove and the remaining configuration · Remove the node from the Pacemaker cluster (primary) · Remove the replica from the AG (primary) · Remove the node from WSFC (optional) · Clean up the RESTORING databases on the removed node (…)
• Read-only routing — Diagnosis: read access and current routing · Define the routing URLs and lists (primary) · Verify: routing list · Test: where does a ReadOnly connection go? · Test: where does a ReadOnly connection go?
• Listener — Diagnosis: current listeners · Create the virtual IP resource and the constraints (Pacemaker) · …Create the listener…Change the listener port…Add an IP to the listener… (primary) · Firewall and DNS settings · Firewall settings · Verify: connection through the listener