SQL Server Always On Availability Groups (AG) Topology
Database-level high availability and disaster recovery with readable secondary replicas to offload reporting queries.
📌 Microsoft SQL Server AlwaysOn Availability Groups HA & DR Reference Architecture Diagram (AG Listener, WSFC Quorum, Sync/Async Log Stream)
Clients connect to the single virtual network name (AG Listener: 10.0.2.100:1433).
Queries specifying `ApplicationIntent=ReadOnly` are automatically redirected to secondary replicas.
Primary awaits log block hardening on the synchronous secondary before acknowledging client commits.
Windows Server Failover Clustering (WSFC) detects primary node heartbeat loss.
The synchronous partner replica transitions to Primary role with zero data loss.
AG Listener VIP and DNS binding dynamically point to the promoted node in <10 seconds.
-- Always On AG Creation Script
CREATE AVAILABILITY GROUP [AG-PROD-CLUSTER]
WITH (AUTOMATED_BACKUP_PREFERENCE = SECONDARY, DB_FAILOVER = ON, HEALTH_CHECK_TIMEOUT = 30000)
FOR DATABASE [SalesDB], [InventoryDB]
REPLICA ON
N'SQL-NODE-01' WITH (ENDPOINT_URL = N'TCP://10.0.2.11:5022', AVAILABILITY_MODE = SYNCHRONOUS_COMMIT, FAILOVER_MODE = AUTOMATIC),
N'SQL-NODE-02' WITH (ENDPOINT_URL = N'TCP://10.0.2.12:5022', AVAILABILITY_MODE = SYNCHRONOUS_COMMIT, FAILOVER_MODE = AUTOMATIC);SELECT replica_server_name, role_desc, synchronization_health_desc, connected_state_desc FROM sys.dm_hadr_availability_replica_states JOIN sys.availability_replicas USING (replica_id);- •Read-only workload offloading
- •No shared storage (SAN) single-point-of-failure
- •Fast automated failover with AG Listener
- •Enterprise edition licensing costs
- •WSFC cluster quorum sensitivity
- Configure Cloud Witness (Azure Blob) or File Share Witness for WSFC quorum stability.
- Database recovery model must be set to FULL on all participating databases.
- Open dedicated mirroring endpoint port 5022 on internal firewalls.