Availability Groups: Replicas, Synchronization, Endpoints, Listeners, and Failover Modes

Learning outcomes

ServiceHub now needs an independent database copy in a second failure domain. An Availability Group maintains one primary database and one or more secondary copies on separate SQL Server instances. The primary flushes log records locally, AG transport sends log blocks, and secondaries harden and redo those records. An AG therefore creates a data-movement pipeline with measurable queues and health—not a magical duplicated table.

1. The data path: generate → capture/send → harden → redo

The primary database commits through its local transaction log. For a synchronous-commit secondary, a transaction can wait for the secondary to harden relevant log records before commit completes; it does not wait for the secondary to finish redo for every reader. In asynchronous mode, the primary does not wait for remote hardening, reducing remote latency impact but increasing the amount of data potentially absent after forced failover.

SELECT SERVERPROPERTY('ProductVersion') AS product_version,       SERVERPROPERTY('ProductUpdateLevel') AS update_level,       SERVERPROPERTY('Edition') AS edition,       SERVERPROPERTY('EngineEdition') AS engine_edition,       SERVERPROPERTY('IsClustered') AS is_fci,       SERVERPROPERTY('IsHadrEnabled') AS hadr_enabled,       SERVERPROPERTY('MachineName') AS machine_name,       SERVERPROPERTY('ServerName') AS server_name;GOSELECT name, compatibility_level, recovery_model_descFROM sys.databasesWHERE name = N'ServiceHubLab';GO

2. Model the replicas before touching a real cluster

USE ServiceHubLab;GOIF OBJECT_ID(N'lab16.ReplicaModel',N'U') IS NULLBEGIN  CREATE TABLE lab16.ReplicaModel  (    replica_name sysname PRIMARY KEY, site_name varchar(30),    availability_mode varchar(30), failover_mode varchar(20), role_desc varchar(12),    synchronization_state varchar(30), log_send_queue_kb bigint, redo_queue_kb bigint  );END;TRUNCATE TABLE lab16.ReplicaModel;INSERT lab16.ReplicaModel VALUES (N'SQL-A','SiteA','SYNCHRONOUS_COMMIT','AUTOMATIC','PRIMARY','SYNCHRONIZED',0,0), (N'SQL-B','SiteA','SYNCHRONOUS_COMMIT','AUTOMATIC','SECONDARY','SYNCHRONIZED',0,640), (N'SQL-C','SiteB','ASYNCHRONOUS_COMMIT','MANUAL','SECONDARY','SYNCHRONIZING',16384,8192);SELECT * FROM lab16.ReplicaModel;GO

The model exposes two different kinds of lag. A log send queue represents log waiting to reach/harden on the secondary. A redo queue represents hardened log waiting for redo. A replica can therefore have its log hardened and still have a readable secondary that is behind for query-visible changes.

SELECT ag.name AS ag_name, ar.replica_server_name,       ars.role_desc, ars.operational_state_desc,       ar.availability_mode_desc, ar.failover_mode_desc,       ars.synchronization_health_descFROM sys.availability_groups AS agJOIN sys.availability_replicas AS ar ON ar.group_id=ag.group_idLEFT JOIN sys.dm_hadr_availability_replica_states AS ars  ON ars.replica_id=ar.replica_idORDER BY ag.name,ar.replica_server_name;GOSELECT DB_NAME(drs.database_id) AS database_name,       drs.is_local,drs.synchronization_state_desc,       drs.synchronization_health_desc,drs.log_send_queue_size,       drs.redo_queue_size,drs.last_hardened_time,drs.last_redone_timeFROM sys.dm_hadr_database_replica_states AS drs;GO

3. Failover mode is a separate axis from commit mode

Automatic failover requires an appropriate cluster-managed topology and an automatic-failover pair configured for synchronous commit, with the target secondary synchronized and healthy under the documented conditions. Planned manual failover without data loss likewise requires synchronized state. Forced failover can make an unsynchronized secondary primary and can lose transactions. Therefore “synchronous” is not shorthand for “safe to fail over under any failure.”

4. Health has layers: connection, synchronization, and database readiness

AG monitoring should separate replica connectivity from database synchronization. A connected secondary can still be unhealthy for one database, suspended, far behind, or unable to redo efficiently. Conversely, a brief disconnected state does not by itself tell you how much data is at risk. Build an incident timeline from role, connected state, synchronization state, log-send queue, redo queue, last hardened/redone times, SQL error log and cluster events. Correlate those observations with network/storage metrics rather than declaring the first nonzero queue to be the root cause.

For synchronous commit, a growing log-send queue can increase commit latency or signal transport/hardening pressure. A redo queue primarily affects how quickly a secondary catches up for readable workloads and how quickly recovery completes after role change. The same queue size can represent very different time-to-catch-up depending on the workload's current log generation and the secondary's send/redo rates; avoid static universal queue thresholds.

SELECT DB_NAME(database_id) AS database_name,       synchronization_state_desc, synchronization_health_desc,       log_send_queue_size AS log_send_queue_kb, log_send_rate AS log_send_kb_per_sec,       redo_queue_size AS redo_queue_kb, redo_rate AS redo_kb_per_sec,       CASE WHEN log_send_rate>0 THEN log_send_queue_size*1.0/log_send_rate END AS approx_send_seconds,       CASE WHEN redo_rate>0 THEN redo_queue_size*1.0/redo_rate END AS approx_redo_seconds,       last_hardened_time,last_redone_timeFROM sys.dm_hadr_database_replica_statesWHERE is_local=1;GO-- Approximate ratios are diagnostic context, not an SLA or a promise of catch-up time.

Endpoint security is also part of health. Database mirroring endpoints authenticate replica traffic according to the deployment model; firewall rules and service-account/certificate configuration must be consistent across instances. Do not open endpoint ports broadly merely to clear a connectivity error. Prove name resolution, port reachability and endpoint authentication from the intended replica identities.

5. Edition shapes the architecture

SQL Server 2025 Standard supports Basic Availability Groups: one database, two replicas, no read access on the secondary and no backups on the secondary. Enterprise supports the advanced AG feature set, including more replicas and readable secondaries. SQL Server 2025 provides separate Standard Developer and Enterprise Developer editions for free non-production development/testing; choose the Developer edition that matches the production feature envelope you want to test.

-- Enterprise/Enterprise Developer style topology template; placeholders required.-- CREATE ENDPOINT [Hadr_endpoint]--   STATE = STARTED AS TCP (LISTENER_PORT = 5022)--   FOR DATABASE_MIRRORING (ROLE = ALL);---- CREATE AVAILABILITY GROUP [ServiceHubAG]-- FOR DATABASE [ServiceHubLab]-- REPLICA ON-- N'SQL-A' WITH-- (ENDPOINT_URL=N'TCP://sql-a.example.test:5022',--  AVAILABILITY_MODE=SYNCHRONOUS_COMMIT,FAILOVER_MODE=AUTOMATIC,--  SEEDING_MODE=AUTOMATIC),-- N'SQL-B' WITH-- (ENDPOINT_URL=N'TCP://sql-b.example.test:5022',--  AVAILABILITY_MODE=SYNCHRONOUS_COMMIT,FAILOVER_MODE=AUTOMATIC,--  SEEDING_MODE=AUTOMATIC);

6. SQL Server 2025 operational note: faster failover is still governed

SQL Server 2025 adds a WSFC RestartThreshold option for AG resources: setting it to 0 can skip a local resource restart attempt and proceed to failover for persistent health issues. This is a specialized availability policy, not a reason to lower health checks blindly. Validate failure detection, application behavior and false-positive risk before changing it.

The next lesson uses a functioning advanced AG conceptually to show that readable secondaries, backup offload and automatic seeding create their own capacity, consistency and routing contracts.

Authoritative references

• CREATE AVAILABILITY GROUP — AG options and SQL Server 2025 capabilities
• Failover modes for availability groups — automatic/planned/forced failover semantics and SQL Server 2025 RestartThreshold
• Basic Availability Groups — Standard-edition limitations
• Monitor performance for Availability Groups — send/redo pipeline and metrics