Overview
Availability groups fail over cleanly only when their replicas are configured and monitored for it. A group that has run for years without a failure often has settings nobody has checked since the day it was built. This scenario describes how an AceMQ review of such a group runs.
Challenge
The operator ran its order and dispatch database in an availability group with a local synchronous replica and an asynchronous replica at a second site. The team believed that failover was automatic everywhere, had never rehearsed one, and had seen the transaction log drive fill twice after the second-site link degraded.
Environment
Two SQL Server instances in the primary data centre and one at a disaster recovery site, on a Windows Server failover cluster with a file share witness, clients connecting through an availability group listener.
Approach
The review starts read-only. AceMQ checks the availability mode and failover mode of every replica against Microsoft's rules for automatic failover, the quorum configuration, the listener and client connection settings, and what log_reuse_wait_desc reports on the primary. Replica lag is measured from last_commit_time and compared with the recovery point the business states. Findings are written before any change, and the failover itself is rehearsed on a restored non-production copy.
Solution
- Mapped each replica's availability mode, failover mode and synchronization state, and showed which replicas can take over automatically and which only by forced failover
- Reviewed cluster quorum and witness settings for the loss of the primary site
- Defined lag alerts on last_commit_time tied to the stated recovery point objective
- Compared logins, SQL Server Agent jobs and linked servers across every replica, since they must exist on the new primary after a failover
- Wrote and rehearsed failover, forced-failover and log-full runbooks on a non-production copy
Outcome
The engagement delivers a written findings report, the replica and quorum changes recommended in priority order, lag alerts with agreed thresholds, and runbooks the operator's own team has rehearsed. The main risk it removes is learning what a forced failover loses during a real outage.
PostgreSQL
Docker
dbt
Elasticsearch
