Always On Availability Groups
Always On Availability Groups (AGs) provide database-level high availability by maintaining one or more secondary replicas of a set of databases, kept in sync with a primary replica via a dedicated data movement channel rather than shared storage. Replicas can run in synchronous-commit mode, where the primary waits for a secondary to harden the transaction log before committing (guaranteeing zero data loss but adding latency), or asynchronous-commit mode, where the primary commits without waiting (lower latency but potential data loss on failover). A listener provides a single virtual network name that applications connect to, automatically redirecting to whichever replica is currently primary after a failover, so connection strings don't need to change.
Cricket analogy: Synchronous-commit is like a batsman waiting for the third umpire's confirmation before celebrating a boundary — slower, but zero risk of a wrong call standing, while asynchronous-commit is like celebrating immediately and risking a reversal on review.
Failover Cluster Instances and Log Shipping
A Failover Cluster Instance (FCI) provides instance-level high availability using Windows Server Failover Clustering with shared storage — all nodes attach to the same underlying disk (typically a SAN or S2D volume), so failover means the SQL Server service simply starts on a different node pointing at the same data files, rather than copying data between nodes. This makes FCI simpler for protecting an entire instance including all its databases and logins, but it doesn't protect against storage failure itself since there's only one copy of the data. Log shipping, by contrast, is the simplest and oldest HA/DR option: it periodically backs up the transaction log on a primary server, copies the backup file to one or more secondary servers, and restores it there with a configurable delay, trading a higher RPO and manual failover for simplicity and the ability to ship over long distances with minimal bandwidth.
Cricket analogy: An FCI is like a stadium with a single pitch but two changing rooms for two competing teams — whichever team takes the field plays on the exact same surface, unlike AGs where each team effectively has its own separately maintained pitch copy.
Choosing an HA/DR Strategy
Choosing between these options comes down to Recovery Point Objective (RPO, how much data loss is acceptable), Recovery Time Objective (RTO, how fast you must be back online), and topology needs. Synchronous AGs offer the best RPO (near zero) with automatic failover but require low-latency network links between replicas, typically limiting them to the same or nearby datacenters. Asynchronous AGs and log shipping suit geographically distant disaster recovery sites where synchronous latency would be unacceptable, accepting a higher RPO in exchange for distance. FCIs address instance-level protection against server hardware failure but not storage failure, and are often combined with AGs in a hybrid topology for organizations that need both local instance failover and geographically distributed disaster recovery.
Cricket analogy: Choosing an HA strategy is like a captain choosing between an aggressive review policy (near-zero risk of a wrong decision standing, like sync AG) versus trusting the on-field umpire's immediate call to keep the game moving (like async AG), depending on how much risk the team can tolerate.
-- Check Availability Group health and replica roles
SELECT
ag.name AS ag_name,
ar.replica_server_name,
ars.role_desc,
ars.synchronization_health_desc,
adc.database_name,
drs.synchronization_state_desc
FROM sys.availability_groups AS ag
JOIN sys.availability_replicas AS ar
ON ag.group_id = ar.group_id
JOIN sys.dm_hadr_availability_replica_states AS ars
ON ar.replica_id = ars.replica_id
JOIN sys.availability_databases_cluster AS adc
ON ag.group_id = adc.group_id
JOIN sys.dm_hadr_database_replica_states AS drs
ON ar.replica_id = drs.replica_id AND adc.group_database_id = drs.group_database_id
ORDER BY ag.name, ar.replica_server_name;
-- Manually fail over an AG to a synchronous secondary
ALTER AVAILABILITY GROUP [SalesAG] FAILOVER;Asynchronous-commit replicas can silently accumulate a large data-loss gap if network latency spikes go unmonitored. Always monitor the send/redo queue size via sys.dm_hadr_database_replica_states and alert on it — an unmonitored asynchronous replica gives a false sense of security until a real failover exposes how much data was actually lost.
FCIs and AGs are not mutually exclusive: a common enterprise topology runs an FCI for local instance-level failover within a datacenter, with that FCI itself acting as a replica in an AG that stretches to a secondary datacenter for disaster recovery.
- Always On Availability Groups provide database-level HA/DR with synchronous (zero data loss) or asynchronous (lower latency, some risk) commit modes.
- An AG listener gives applications a stable virtual network name that follows the current primary replica after failover.
- Failover Cluster Instances provide instance-level HA using shared storage, protecting against server failure but not storage failure.
- Log shipping is the simplest, longest-distance-tolerant option but has higher RPO and typically manual failover.
- RPO and RTO requirements, along with network latency and distance, should drive the choice of HA/DR technology.
- Synchronous replicas require low-latency links, generally limiting them to nearby datacenters.
- FCIs and AGs are often combined in hybrid topologies for both local failover and geographic disaster recovery.
Practice what you learned
1. What is the key difference between synchronous-commit and asynchronous-commit modes in an Availability Group?
2. What does an Availability Group listener provide?
3. What is a key limitation of a Failover Cluster Instance (FCI)?
4. Which HA/DR option is generally best suited for a disaster recovery site located a great distance away with limited bandwidth?
5. Why is it important to monitor the send/redo queue size on asynchronous-commit AG replicas?
Was this page helpful?
You May Also Like
Backup and Restore
How SQL Server backup types, the recovery model, and restore sequences work together to protect data and meet recovery objectives.
SQL Server Agent Jobs
How SQL Server Agent automates scheduled tasks, jobs, steps, schedules, alerts, and operator notifications.
Security and Permissions
The principal-securable-permission model in SQL Server, from logins and users to roles and the principle of least privilege.
Related Reading
Related Study Notes in Programming
Browse all study notesApache Spark Study Notes
Programming · 30 topics
ProgrammingApache Flink Study Notes
Programming · 30 topics
ProgrammingHadoop Study Notes
Programming · 30 topics
ProgrammingSnowflake Study Notes
Programming · 30 topics
ProgrammingApache Airflow Study Notes
Programming · 30 topics
Programmingdbt (Data Build Tool) Study Notes
Programming · 30 topics