SQL Server Always On Availability Groups

WSFC: The Clustering Foundation Underneath Always On

Always On Availability Groups are frequently described as a SQL Server feature, but the failover mechanics are not SQL Server's to control. An AG is built as a resource inside Windows Server Failover Clustering (WSFC), and every node that will host a replica must already belong to a validated WSFC cluster before CREATE AVAILABILITY GROUP will succeed. Run the cluster validation wizard (or Test-Cluster) first — SQL Server setup will let you enable the Always On Availability Groups feature on an instance with no cluster present, but the AG creation DDL will fail without an underlying cluster to register against.

Once created, the AG becomes a cluster resource managed by a resource DLL (hadrres.dll) that WSFC uses to monitor health and drive failover. SQL Server's own Always On health detection (governed by HealthCheckTimeout, default 30 seconds) feeds status up to the cluster, but the cluster's lease mechanism — a heartbeat between the SQL Server resource DLL and the WSFC resource host subsystem, governed by LeaseTimeout, default 20 seconds — is what actually determines whether a primary is still considered alive from the cluster's point of view. If the lease expires, WSFC treats the primary as down and initiates failover independent of whether SQL Server itself thinks it's healthy. This split is important: an AG can fail over even when the SQL Server process hasn't crashed, if it can't renew its lease in time (for example, due to resource starvation or an unresponsive OS).

Since SQL Server 2017, you can create an AG with cluster type NONE (read-scale, no automatic failover, no cluster dependency) or EXTERNAL (Pacemaker on Linux), but the classic Windows enterprise HA/DR deployment — the one with automatic failover — is a WSFC-clustered AG. Understanding that means understanding quorum, because quorum is what makes or breaks automatic failover safety.

Quorum: The Arbiter That Isn't SQL Server

WSFC uses quorum to decide, when nodes lose contact with each other, which subset (if any) is authorized to keep running cluster resources. Each node typically gets one vote; a witness (disk witness, file share witness, or cloud witness in Azure) can hold an additional vote to break ties in even-node configurations. The cluster stays online only while it can see a majority of the total votes. This exists specifically to prevent split-brain: without it, two isolated halves of a partitioned cluster could each decide they're in charge and both bring the AG online as primary, leading to two masters accepting writes independently.

Modern WSFC defaults to dynamic quorum and dynamic witness, which adjust vote counts as nodes leave or rejoin gracefully, and can automatically add or remove the witness's vote depending on whether the node count is odd or even. Check current vote assignment with Get-ClusterNode | ft Name, State, DynamicWeight and quorum configuration with Get-ClusterQuorum. For a two-node AG (a very common topology for a primary plus one DR secondary), a witness is mandatory in practice — without one, losing either node drops the survivor below a majority and takes the whole cluster offline, defeating the purpose of the AG.

This is the piece DBAs most often underestimate: an AG failover is not simply "SQL Server decided to switch primaries." It is WSFC quorum-arbitrated. A replica cannot become primary as the result of automatic failover unless the node it's running on is part of a cluster partition that holds quorum. That constraint is what makes automatic failover safe to leave enabled — it is impossible (barring bugs) for two partitions to simultaneously believe they hold quorum, so it is impossible for two replicas to simultaneously believe they're authorized to be primary.

Synchronous-Commit vs Asynchronous-Commit Availability Modes

Every replica in an AG runs in one of two availability modes, set per-replica and changeable independently for each secondary. The choice determines both your recovery point objective and your commit latency, and conflating the two is the single most common Always On misconfiguration.

In synchronous-commit mode, the primary hardens the transaction log locally, sends the log records to the secondary, and then waits for the secondary to harden those same records to its own log and send back an acknowledgment before the primary reports the commit as complete to the client. This is a real network round trip inserted into every write transaction's commit path — not just replication overhead absorbed asynchronously. That's why Microsoft's own guidance caps practical synchronous deployments at low-latency links: a metro cluster or two datacenters on the same campus with sub-few-millisecond round-trip time, not two regions separated by a continent. Push sync commit over a high-latency WAN link and you'll see commit latency (and therefore application throughput) degrade in direct proportion to the round trip, because every transaction pays it. SQL Server 2016 and later support up to three synchronous replicas total (one primary plus two synchronous secondaries) in a single AG, which is what enables three-node automatic-failover topologies without a manual step.

In asynchronous-commit mode, the primary hardens its own log and reports the commit to the client immediately, sending log records to the secondary without waiting for acknowledgment. Throughput on the primary is unaffected by the secondary's distance or link latency, which is exactly why async is the standard choice for DR replicas in a different region: you get geographic separation without taxing every local transaction. The tradeoff is a nonzero RPO — if the primary fails before the secondary has received and hardened the most recent log records, those transactions are lost on failover to that replica. The size of that gap is visible in sys.dm_hadr_database_replica_states via log_send_queue_size (KB not yet sent) and, on the secondary side, how far redo has applied via redo_queue_size.

Also watch synchronization_state_desc in that same DMV. A synchronous replica isn't actually eligible for automatic, no-data-loss failover just because its mode says SYNCHRONOUS_COMMIT — it has to be in the SYNCHRONIZED state. If it's still catching up it will show SYNCHRONIZING, and a failover attempted in that window (or forced) can still lose data even though the replica is nominally "synchronous." Asynchronous replicas never report SYNCHRONIZED; they cap out at SYNCHRONIZING, which is normal and expected.

The Availability Group Listener

Without a listener, an application connecting to an AG has to know which physical instance currently holds the primary role, and that changes across failovers. The Availability Group Listener solves this by giving the AG a single, stable network identity independent of which node is primary. Under the hood, a listener is a WSFC client access point resource: it consists of a virtual network name (VNN) registered in DNS and one or more virtual IP addresses (one per subnet, for a multi-subnet cluster). When failover occurs, WSFC moves the listener resource — and therefore the name-to-IP binding — to the node hosting the new primary, then triggers a DNS update if the IP itself changed (as it does in a multi-subnet topology).

Create one with CREATE AVAILABILITY GROUP LISTENER 'AGListener01' (WITH IP (('10.10.1.50','255.255.255.0')), PORT=1433) in T-SQL, or Add-SqlAvailabilityGroupListener / New-SqlAvailabilityGroupListener in PowerShell. Applications then connect using the listener name in the connection string, never a physical instance name — Server=AGListener01,1433 — so failovers are transparent as long as the client reconnects.

Two operational details matter here. First, for multi-subnet AGs, the client driver needs MultiSubnetFailover=True in the connection string; without it, a client can stall for the full default TCP connect timeout while it tries a now-stale cached IP before falling through to the next one, turning what should be a sub-second reconnect into tens of seconds. Second, the listener is also the mechanism read-intent routing rides on: a client connecting through the listener with ApplicationIntent=ReadOnly gets transparently redirected to a readable secondary according to the AG's READ_ONLY_ROUTING_LIST, rather than landing on the primary.

Automatic vs Manual Failover, and Why Quorum Makes It Safe

Each replica has a FAILOVER_MODE setting of AUTOMATIC or MANUAL. Automatic failover between two replicas requires all of the following simultaneously: both replicas configured for synchronous-commit availability mode, both configured with FAILOVER_MODE = AUTOMATIC, the secondary currently in the SYNCHRONIZED state, and the WSFC cluster holding quorum at the moment of failure. Miss any one of those and an automatic failover simply won't fire — which is the intended, conservative behavior, because firing it anyway is precisely how you'd end up with either data loss or a split-brain primary.

That quorum requirement is what actually makes automatic failover trustworthy rather than reckless. A naive design could let any replica promote itself the moment it stops hearing from the primary, but a network partition would then let an isolated secondary and a still-running (but unreachable) primary both serve writes. Gating promotion behind cluster quorum means a replica can only become primary via automatic failover if its node is in the partition that a majority of cluster votes agree is authoritative — the isolated minority partition, even if it desperately wants to promote, cannot, because it can't reach quorum.

Manual failover comes in two forms with very different risk profiles. A planned manual failover (ALTER AVAILABILITY GROUP [AG1] FAILOVER, run against the target secondary) is only permitted against a synchronous-commit, synchronized replica and guarantees no data loss — it's the mechanism for patching or maintenance windows where you want a controlled role swap. A forced manual failover (ALTER AVAILABILITY GROUP [AG1] FORCE_FAILOVER_ALLOW_DATA_LOSS) is the only way to promote an asynchronous-commit replica, and it exists for the genuine DR scenario where the primary site is gone and waiting for it to come back isn't an option. As the syntax makes explicit, this can and often does lose whatever transactions hadn't shipped to that replica yet — it should never be scripted into routine automation, only invoked deliberately during an actual disaster.

Readable Secondary Replicas

Beyond redundancy, secondary replicas can absorb read workload that would otherwise compete with OLTP traffic on the primary. Each secondary's readability is set independently via ALLOW_CONNECTIONS: NO (default-equivalent, no direct reads), READ_ONLY (only read-intent connections permitted), or ALL (any connection, read-intent or not, can read — useful for ad hoc troubleshooting but rarely what you want for a production reporting tier).

Connections against a readable secondary are automatically placed under snapshot isolation regardless of what the session requested — SQL Server overrides READ COMMITTED and similar levels because the secondary has no meaningful way to take locks that would block against an active redo thread. This is transparent to applications but has a real side effect: because reads rely on row versioning, and because the secondary must preserve the versions those long-running reads need, ghost record cleanup on the secondary can be deferred while a long report is open, and in some cases this backs up into redo, growing redo_queue_size and increasing replication lag. A reporting query that runs for twenty minutes on the secondary can measurably widen the gap between primary and secondary data currency for the duration.

Routing traffic there deliberately (versus clients hardcoding a physical secondary name) requires both a listener and a configured READ_ONLY_ROUTING_LIST per replica, plus ApplicationIntent=ReadOnly on the client connection string — connecting directly to a secondary's instance name bypasses routing logic entirely and just gets you whatever that replica's ALLOW_CONNECTIONS setting permits. And because a secondary is only ever as current as its redo queue has processed, readable secondaries are appropriate for reporting and analytics that can tolerate some staleness, not for read-after-write consistency within the same business transaction.

Monitoring Replica Health and Common Failure Modes

The core diagnostic view is sys.dm_hadr_database_replica_states, joined to sys.availability_replicas and sys.dm_hadr_availability_replica_states for replica-level role and connection state. Key columns: synchronization_state_desc (SYNCHRONIZED / SYNCHRONIZING / NOT SYNCHRONIZING), synchronization_health_desc (a rollup: HEALTHY, PARTIALLY_HEALTHY, NOT_HEALTHY), log_send_queue_size, and redo_queue_size. A replica stuck at NOT SYNCHRONIZING almost always traces back to the database mirroring endpoint: a certificate mismatch, an endpoint owner without CONNECT permission, or a firewall blocking the endpoint port (5022 by default) between nodes. Check endpoint state with SELECT * FROM sys.database_mirroring_endpoints and connectivity with SELECT * FROM sys.dm_hadr_instance_node_map.

A second common failure mode is initial data movement: adding a database to an AG requires seeding the secondary, either via full backup/restore (manual seeding) or AUTOMATIC_SEEDING, which streams the database directly over the endpoint. Automatic seeding is convenient but slower over a WAN for large databases and offers no native compression control the way a backup-based seed does with COMPRESSION — for multi-hundred-gigabyte databases across a DR link, a manual backup/restore seed is often still faster to get the replica synchronized initially. Finally, remember that adding, removing, or changing the availability mode of a replica is itself a quorum-relevant cluster operation — running these changes during a period of marginal cluster health (a node flapping, a witness unreachable) is a common way to accidentally trigger an unplanned failover rather than the intended configuration change.