SQL Server Always On Availability Groups: What They Protect, What They Cost, Where They Break

An Always On availability group keeps copies of your SQL Server databases on two or more servers and moves the workload to a copy when the primary fails. That's the brochure version.

Whether it actually saves you at 2am depends on details the brochure skips: which edition you run, whether your secondary is really synchronized, what the cluster can and can't see, and whether anyone is watching the queues. This guide covers the current rules for SQL Server 2016 through SQL Server 2025, checked against Microsoft's documentation in October 2026, plus the checks we'd run before trusting an availability group with production.

The short version

• An availability group (AG) protects a set of user databases, not the whole instance. Ordinary AGs don't replicate logins, SQL Agent jobs, linked servers or other instance-level objects. Contained AGs (SQL Server 2022 and later) carry logins, permissions and Agent jobs that you create inside them.
• Enterprise Edition: one primary plus up to eight secondaries. Up to five replicas can run synchronous commit, counting the primary (SQL Server 2019 and later).
• Standard Edition: basic AGs only. Two replicas, one database per AG, no reads and no backups on the secondary.
• Synchronous commit protects committed transactions only while the secondary stays SYNCHRONIZED. If it drops out, the primary keeps committing without it (unless you've told it not to).
• Licensing: with Software Assurance or SQL Server subscription licenses, passive secondaries are license-free (one for HA, one for DR, one for DR in Azure, per licensed OSE). The moment a secondary serves reports, it needs its own licenses.
• An AG is not a backup. A bad DELETE replicates too, usually within seconds.

What an Always On availability group actually is

"Always On" is Microsoft's umbrella name for two different features: availability groups and failover cluster instances (FCIs). When someone says "we run AlwaysOn," ask which one. This guide is about availability groups, sometimes shortened to AG or AAG.

An availability group is a container for a set of user databases that fail over together. One SQL Server instance, the primary replica, holds the read-write copies. Each secondary replica holds its own copy and replays the primary's transaction log records into it. Data moves per database, so one database can fall behind while the others stay current.

Three moving parts make it work:

• A cluster manager. On Windows, that's a Windows Server Failover Cluster (WSFC), and every replica sits on a different node of the same cluster. On Linux, it's Pacemaker. The cluster decides when the primary is unhealthy and keeps quorum. (This guide describes the common Windows and WSFC setup. Pacemaker-managed and clusterless AGs use different failover settings and procedures; Microsoft documents those separately.)
• A database mirroring endpoint on each instance, which carries the log stream between replicas.
• A listener: a virtual network name, one or more IP addresses and a port that always point at the current primary, so applications don't need new connection strings after a failover.

Unlike an FCI, an AG needs no shared storage. Every replica has its own copy of the data on its own disks. Microsoft introduced availability groups in SQL Server 2012 as the replacement for database mirroring, which is now deprecated.

If you're still choosing between AGs, FCIs, log shipping and replication, start with our SQL Server high availability options overview, then come back here.

The numbers that matter, by version and edition

That last row deserves a second look. Ten AGs and 100 databases isn't a ceiling, but past it you're the test lab. The usual symptom of an overloaded server is worker thread exhaustion: each primary database uses a log capture thread, and each secondary database it feeds uses a log send thread.

Synchronous vs asynchronous commit: what each one costs

Every replica has an availability mode.

Synchronous commit. The primary waits until the secondary has written (hardened) the log records to disk before it confirms the commit to your application. Fail over to a synchronized secondary and you lose nothing. You pay for that on every transaction, in commit latency. Keep synchronous replicas close: same data center, or same cloud region.

Asynchronous commit. The primary commits without waiting for the secondary, so the secondary's speed stays out of your commit path. The secondary lags, though, and failing over to it can lose whatever hadn't been hardened there yet. This is the right mode for a disaster recovery (DR) replica in another region. One catch: a secondary that falls far behind can still hold up log truncation on the primary, so the primary's transaction log grows until the secondary catches up or the disk fills.

Here's the detail most teams miss. Synchronous commit only protects you while the secondary is SYNCHRONIZED. If a synchronous secondary misses the session timeout (10 seconds by default), the primary marks it NOT SYNCHRONIZING and stops waiting for it. Your application keeps committing. If that was your only synchronized secondary, new commits no longer have a synchronous copy anywhere (an asynchronous replica may or may not have them yet), which Microsoft describes as the primary running "exposed." Nothing pages you unless you built the alert.

Since SQL Server 2017 (SQL Server 2022 for distributed AGs), you can close that gap with REQUIRED_SYNCHRONIZED_SECONDARIES_TO_COMMIT. Set it to 1 and the primary won't commit a transaction until at least one synchronous secondary has it. The trade-off is blunt: if no synchronous secondary is available, commits on the primary fail. You've swapped a data-loss risk for an availability risk, so make that choice on purpose.

Your recovery point objective (RPO, how much data you can afford to lose) and recovery time objective (RTO, how long you can be down) should decide the mode, not the defaults. Our RPO vs RTO guide walks through setting both.

Failover: automatic, planned and forced

Three kinds of failover exist, and your availability mode decides which ones you get. (These are the rules for WSFC-based AGs on Windows. Pacemaker uses FAILOVER_MODE = EXTERNAL and the cluster tools for failover.)

• Automatic failover (no data loss). Requires synchronous commit and failover mode set to automatic on both the primary and the target, a synchronized target, and WSFC quorum.
• Planned manual failover (no data loss). A DBA runs the command against a synchronized, synchronous secondary. This is how you patch with near-zero downtime; see our AlwaysOn patching steps.
• Forced failover (possible data loss). The only option for an asynchronous secondary. A disaster recovery move, not a maintenance tool.

Two traps here:

1. A database problem doesn't trigger failover by default. The AG fails over when the instance or replica is unhealthy. A database that goes suspect or offline won't move the AG unless you turn on database-level health detection (DB_FAILOVER = ON, available since SQL Server 2016). Microsoft recommends it, and it's still off by default. Even when it's on, SQL Server doesn't watch disks directly, so a lost data file may not trigger anything until a read or write hits it.
2. FCI-hosted replicas can't be automatic AG failover partners. If an FCI hosts one of your replicas, the AG can only fail over to or from it manually. (The FCI itself still fails over between its own nodes automatically.)

The listener and your connection strings

The listener gives applications one stable address to reconnect to after a failover. Existing connections still drop, so your applications need connection retry and sensible handling of failed transactions. Two connection-string settings matter.

MultiSubnetFailover=True. Microsoft recommends it for every listener connection, even when the AG lives in a single subnet. The driver tries all listener IPs in parallel and connects to whichever answers, which speeds up reconnects after a failover.

ApplicationIntent=ReadOnly. This sends a connection to a readable secondary, but only if read-only routing is configured, the connection goes through the listener, and the connection string names a database in the AG (or the login's default database is one).

Server=tcp:AGListener,1433;Database=Sales;Integrated Security=SSPI;MultiSubnetFailover=True
Server=tcp:AGListener,1433;Database=Sales;Integrated Security=SSPI;MultiSubnetFailover=True;ApplicationIntent=ReadOnly

An application that connects straight to a server name instead of the listener won't find the new primary on its own, unless you've deliberately set up another supported redirection, such as read/write routing (SQL Server 2019 and later). That's worth five minutes of grepping config files before you need it.

Standard Edition vs Enterprise Edition

Standard Edition doesn't get full availability groups. It gets basic availability groups, which replaced database mirroring on Standard starting with SQL Server 2016.

The one-database limit is where basic AGs bite. If your application uses three databases, you build three basic AGs, and each one fails over on its own. Your app can end up with one database on Server A and two on Server B. Plan for that, or plan for Enterprise.

SQL Server 2025 made Standard more capable, raising the per-instance limits to the lesser of 4 sockets or 32 cores, and 256 GB of buffer pool memory (from 24 cores and 128 GB in SQL Server 2022). The availability group rules didn't change. If you're weighing the edition decision, read do you really need SQL Server Enterprise? and our Enterprise vs Standard cost breakdown.

Licensing: when secondaries are free and when they aren't

This is the section that changes budgets, so here are Microsoft's rules as written in the SQL Server licensing guide.

With active Software Assurance or SQL Server subscription licenses, each licensed operating system environment (OSE, meaning a physical server or a VM) can run these passive replicas without extra core licenses:

• one passive replica for high availability (the guide defines this as a synchronous replica with automatic failover),
• one passive replica for disaster recovery (an asynchronous replica with manual failover),
• one passive replica for disaster recovery in Azure.

The licenses a passive replica would otherwise need can't exceed what the primary OSE needs.

"Passive" means the replica isn't serving data to clients or running active workloads. It may only synchronize and run these maintenance tasks: database consistency checks, log backups, full backups and monitoring resource usage. You can also run the primary and its DR replica together for brief DR tests every 90 days.

Without Software Assurance or a qualifying subscription (for example, perpetual licenses with SA lapsed), there are no failover rights. Every replica needs its own licenses.

A worked example. Your primary runs 16 cores of Enterprise with Software Assurance. You add a synchronous HA secondary with automatic failover and an asynchronous DR secondary, also 16 cores each.

• Both secondaries passive: you license 16 cores.
• Turn on reporting against the HA secondary: you license 32 cores. That secondary is now active.
• Same setup on perpetual licenses without SA: you license 48 cores.

So "let's offload reports to the secondary" is a licensing decision, not just a configuration change. Sometimes it pays for itself by taking load off the primary. Sometimes tuning the reports costs less than licensing another server. Run the numbers before you flip ALLOW_CONNECTIONS. Our post on Software Assurance HA and DR rights has the diagrams.

What an availability group doesn't protect

An AG replicates user databases. Plenty of what your application needs lives elsewhere.

• Instance-level objects. Logins, SQL Agent jobs, linked servers, server-level permissions and similar objects live in master and msdb, which can't join an AG. After a failover, a login that only existed on the old primary fails, and a job that only existed there doesn't run. Someone has to keep them in sync on every replica.
• Your data, from you. Secondaries aren't backups. A mistaken DELETE or a bad deployment replicates too, usually within seconds on a healthy AG. Unlike log shipping, AGs can't delay applying changes. Keep real backups, and test restores.
• Encryption keys. With certificate-based TDE, the certificate and its private key have to be on every replica before you seed the encrypted secondary. With EKM-based TDE, every replica needs the provider and access to the key.
• Cross-database consistency. Databases in the same AG fail over together, but that alone doesn't guarantee transactional consistency across them. Cross-database and distributed transactions need a supported MSDTC setup with DTC_SUPPORT = PER_DB (fully supported from SQL Server 2016 SP2 and 2017). Even then, a forced failover can lose data.

Contained availability groups (SQL Server 2022 and later, Enterprise) fix most of the first problem. Each contained AG gets its own master and msdb, so logins, users, permissions and Agent jobs you create inside it move with it. The catches:

• It starts empty. Apart from sysadmin logins, nothing from the instance copies in, so you re-create what the application needs inside the contained AG.
• You only see that environment when you connect through the contained AG's listener, or to a database in the AG. Use the listener for applications.
• Replication isn't supported, and neither are SSIS packages or maintenance plans.
• Anything the application depends on outside that context still needs its own inventory.

Distributed, contained and read-scale AGs: when you'd use each

Distributed availability groups (SQL Server 2016+, sometimes called a DAG). An AG of AGs: two separate availability groups, each in its own cluster, linked together. Good for three jobs:

• DR to another site without stretching one cluster across data centers,
• migrations to new hardware, a new OS or a newer SQL Server version (2017 and later can mix major versions, with the newer version on the receiving side),
• more readable copies than one AG allows, up to 18 replicas.

Failover between the two AGs is manual only. The distributed AG has no listener of its own; you point it at each underlying AG's listener.

Contained availability groups (SQL Server 2022+) carry their own logins and jobs, covered above. SQL Server 2025 added support for distributed AGs between two contained AGs.

Read-scale availability groups (SQL Server 2017+, CLUSTER_TYPE = NONE). No cluster manager, so no automatic failover and no high availability. They exist to copy databases to other instances for read-only work.

Migrating with an availability group

AGs are one of the best tools for moving a busy database with minimal downtime.

• Conventional AG: add the new server as a replica, set it and the primary to synchronous commit, wait until every database on it shows SYNCHRONIZED, stop writes, then fail over.
• Distributed AG (new cluster, new OS or newer SQL Server version): link a new AG to the old one and let it catch up. The cutover follows Microsoft's version-specific distributed AG failover procedure. On SQL Server 2019 and earlier, you stop transactions and confirm each database's last_hardened_lsn matches on both sides before you demote the old side and promote the new one. SQL Server 2022 and later add a safer path using REQUIRED_SYNCHRONIZED_SECONDARIES_TO_COMMIT = 1 on the distributed AG. Either way, it's a different sequence from a plain AG failover, so rehearse it.

A rehearsed cutover can take minutes. Measure yours, because recovery, redo backlog, version upgrades and application reconnects all add time.

When the target runs a newer SQL Server version, distributed AGs need manual seeding (backup and restore), not automatic seeding. You can't read from the new side until you fail over to it, and once you do, you can't fail back to the older version.

In our Integrated Dermatology case study, implementing AlwaysOn and disaster recovery was one of the key objectives of a migration from on-premises to Microsoft Azure. All of the client's goals were achieved with only a few minutes of downtime. If you're planning one, our SQL Server migration services page explains how we run them.

What's new for availability groups in SQL Server 2025

• Full and differential backups on secondaries. Earlier versions allowed only log backups and copy-only full backups on a secondary.
• Fast failover for persistent health issues. Set the AG's RestartThreshold to 0 and the cluster fails over immediately instead of trying to restart in place.
• Availability group commit time is now a server setting. It controls how long the primary batches log before sending it to secondaries. The effective default is 10 ms, and the configuration value 0 selects that default. Lowering it can cut commit latency, at the cost of less efficient batching.
• TLS 1.3 (TDS 8.0 strict encryption) for communication between the cluster and AG replicas.
• Distributed AG improvements: faster synchronization when the forwarder is asynchronous, plus distributed AGs between contained AGs.
• Smaller fixes: remove a single IP address from a listener without dropping it, and set read-only or read-write routing back to NONE.

A health-check query you can run today

Run this on the primary replica. On the primary, sys.dm_hadr_database_replica_states returns a row for each local database and one for each of its secondary copies, so you see every replica from one place.

-- Always On availability group health, run on the primary replica.
-- Permissions: VIEW SERVER STATE (SQL Server 2019 and earlier)
-- or VIEW SERVER PERFORMANCE STATE (SQL Server 2022 and later) for the DMVs,
-- plus VIEW ANY DEFINITION for the catalog views (sys.availability_replicas, sys.availability_groups).
SELECT
    ag.name                          AS ag_name,
    ar.replica_server_name,
    DB_NAME(drs.database_id)         AS database_name,
    drs.is_primary_replica,
    ar.availability_mode_desc,
    ar.failover_mode_desc,
    drs.synchronization_state_desc,
    drs.synchronization_health_desc,
    drs.is_suspended,
    drs.suspend_reason_desc,
    drs.log_send_queue_size          AS log_send_queue_kb,
    drs.redo_queue_size              AS redo_queue_kb,
    drs.secondary_lag_seconds,
    drs.last_commit_time
FROM sys.dm_hadr_database_replica_states AS drs
JOIN sys.availability_replicas AS ar
    ON ar.replica_id = drs.replica_id
JOIN sys.availability_groups AS ag
    ON ag.group_id = drs.group_id
ORDER BY ag.name, database_name, drs.is_primary_replica DESC, ar.replica_server_name;

What to look for, on the secondary rows (is_primary_replica = 0):

• synchronization_state_desc should read SYNCHRONIZED for synchronous replicas and SYNCHRONIZING for asynchronous ones. A synchronous secondary showing NOT SYNCHRONIZING has stopped protecting your commits. If no synchronized secondary is left, new commits either have no synchronous copy (the default) or fail (if you set REQUIRED_SYNCHRONIZED_SECONDARIES_TO_COMMIT).
• log_send_queue_kb is log the primary hasn't sent yet. It measures unsent log, not lost business data, and committed log that was sent but not yet hardened on the secondary can also be lost in a forced failover. A zero send queue doesn't prove zero exposure.
• redo_queue_kb is log the secondary has received but not replayed. A large redo queue means a slow failover, because the secondary has to finish redo before the database comes online.
• is_suspended = 1 means data movement stopped. It doesn't resume on its own.
• secondary_lag_seconds reads 0 when data movement is suspended. Never read it without is_suspended and the sync state next to it.

This one estimates current data loss exposure per secondary database, following the method Microsoft documents: zero if the secondary is failover-ready, otherwise the gap between the primary's last commit time and the last commit the secondary has reported.

-- Estimated RPO (seconds) per secondary database, run on the primary replica.
-- 0 = failover-ready (synchronized); NULL = can't be measured right now.
SELECT
    ag.name                      AS ag_name,
    ar.replica_server_name       AS secondary_replica,
    DB_NAME(p.database_id)       AS database_name,
    s.synchronization_state_desc,
    s.is_suspended,
    CASE
        WHEN cs.is_failover_ready IS NULL THEN NULL
        WHEN cs.is_failover_ready = 1 THEN 0
        WHEN s.last_commit_time IS NULL
          OR p.last_commit_time IS NULL
          OR s.last_commit_time > p.last_commit_time THEN NULL
        ELSE DATEDIFF(SECOND, s.last_commit_time, p.last_commit_time)
    END                          AS est_data_loss_seconds
FROM sys.dm_hadr_database_replica_states AS s
JOIN sys.dm_hadr_database_replica_states AS p
    ON  p.group_database_id = s.group_database_id
    AND p.is_primary_replica = 1
    AND p.is_local = 1
LEFT JOIN sys.dm_hadr_database_replica_cluster_states AS cs
    ON  cs.replica_id = s.replica_id
    AND cs.group_database_id = s.group_database_id
JOIN sys.availability_replicas AS ar
    ON ar.replica_id = s.replica_id
JOIN sys.availability_groups AS ag
    ON ag.group_id = s.group_id
WHERE s.is_primary_replica = 0;

Treat the number as an estimate. For databases that aren't failover-ready, it assumes redo on the secondary is caught up and its progress reports are current. A redo backlog or stale reports can make it misleading, so read it next to the redo queue from the first query.

Finally, check the AG-level settings that decide how failover behaves (SQL Server 2017 and later, because of the last two columns):

SELECT name,
       db_failover,                                  -- 0 = database health detection off
       failure_condition_level,
       health_check_timeout,
       automated_backup_preference_desc,
       required_synchronized_secondaries_to_commit,
       cluster_type_desc
FROM sys.availability_groups;

Run these once and you know where you stand today. Run them every few minutes with alerts on the thresholds and you have monitoring. Our SQL Server monitoring and alerts guide covers what to alert on and why most teams alert on the wrong things.

Common admin tasks in T-SQL

These are for WSFC-based AGs. Pacemaker-managed and clusterless AGs, and distributed AGs, have their own failover procedures.

Planned manual failover. Run it on the target secondary, which must be synchronous and SYNCHRONIZED:

ALTER AVAILABILITY GROUP [SalesAG] FAILOVER;

Forced failover (possible data loss). Run it on the target secondary, only when the primary is gone and waiting costs more than the lost data:

ALTER AVAILABILITY GROUP [SalesAG] FORCE_FAILOVER_ALLOW_DATA_LOSS;

After a forced failover, data movement to the other secondaries (and to the old primary, when it comes back as a secondary) is suspended. Resume each database on each of those secondaries, not on the new primary, with ALTER DATABASE [Sales] SET HADR RESUME;. Resuming can throw away transactions that only reached the old primary, so preserve anything you might need to recover first.

Remove a database from an availability group. Run it on the primary:

ALTER AVAILABILITY GROUP [SalesAG] REMOVE DATABASE [Sales];

The primary copy stays online. Every secondary copy drops into the RESTORING state. Drop them, or recover one only if you're sure no client can reach two live copies of the same database.

When an availability group is the wrong tool

• You're on Standard Edition and your app spans several databases. Basic AGs fail over one database at a time. An FCI protects the whole instance, logins and jobs included, and Standard supports two-node FCIs.
• You need protection from human error. Log shipping with a restore delay, or plain tested backups, give you a point to go back to. AGs replicate the mistake.
• Nobody owns it. An AG adds a cluster, quorum, endpoints, listeners and synchronization states. Unmonitored, it can fail quietly and leave you less protected than a single well-backed-up server. The checklist below is the minimum.

Before you trust your AG: a 10-point checklist

1. Every synchronous secondary shows SYNCHRONIZED right now.
2. DB_FAILOVER is on, unless you've decided otherwise.
3. Alerts fire on NOT SYNCHRONIZING, suspended data movement and send or redo queues past your RPO and RTO.
4. The logins, jobs, linked servers and credentials your application needs are available on every replica after failover. In a contained AG, the supported objects you created inside it move with it; everything else is still maintained separately.
5. Applications connect through the listener with MultiSubnetFailover=True.
6. Backups run, somewhere, and a restore was tested this quarter.
7. If you use TDE: with certificate-based TDE, the certificate and private key are on every replica; with EKM-based TDE, every replica can reach the provider and key.
8. If your agreed HA/DR scope includes surviving a site loss: cluster quorum and a replica still work with that site gone.
9. Every secondary serving reads or running non-permitted work is licensed.
10. You did a planned failover in the last six months and the application came back.

Mark an item "not applicable" only when it truly doesn't apply to your setup. If you can't tick or rule out all ten, you have a project, not a failover plan. Our read-only 145-point SQL Server Health Check covers failover and HA configuration, disaster recovery readiness, and backup and restore testing. If you want senior DBAs to design, build or rescue your availability groups, here's how our SQL Server high availability and disaster recovery consulting works.