SQL Server High Availability: Always On Availability Groups

SQL Server High Availability: Always On Availability Groups

Introduction

Always On Availability Groups (AG) deliver enterprise-grade high availability by replicating databases across multiple SQL Server instances with automatic failover (RPO=0, RTO <30s for synchronous replicas). Unlike legacy database mirroring or log shipping, AGs support multiple secondaries, read-scale-out workloads, and granular failover at the database level (not entire instance). This makes AGs ideal for mission-critical OLTP systems (ERP, CRM, financial trading) requiring 99.99% uptime, geographic disaster recovery, and workload offloading.

HA Solution Comparison:

Feature	Always On AG	Failover Cluster Instance	Database Mirroring	Log Shipping
Automatic failover	Yes (sync mode)	Yes (shared storage)	Yes (1 secondary)	No (manual)
Multiple secondaries	Yes (up to 8)	No	No	Yes (unlimited)
Read-scale-out	Yes	No	Limited	Yes (read-only)
Granularity	Database-level	Instance-level	Database-level	Database-level
RPO (data loss)	Zero (sync)	Zero	Zero (sync)	Minutes (async)
RTO (failover time)	<30 seconds	1-2 minutes	<10 seconds	10+ minutes
Shared storage	Not required	Required	Not required	Not required
Enterprise Edition	Required	No	No (deprecated)	No

This guide covers Windows Server Failover Clustering (WSFC) setup, AG creation with synchronous/asynchronous replicas, listener configuration for transparent connection redirection, read-scale-out routing, backup offloading, monitoring with DMVs and SQL Agent alerts, disaster recovery topology (3-node with DR site), patching strategies with zero downtime, and Azure hybrid scenarios (on-prem + Azure VM).

Prerequisites

• SQL Server Enterprise Edition (or Azure SQL Managed Instance)
• Windows Server Failover Clustering (on-prem) or Azure VMs
• Shared storage or distributed network name

Always On AG Architecture

Component	Purpose	HA Benefit
Primary Replica	Read/write workload	Active node
Secondary Replica(s)	Synchronous/async copy	Automatic failover target
Availability Group Listener	Virtual network name	Transparent connection redirect
Witness / Quorum	Cluster arbitration	Split-brain prevention

Step-by-Step Guide

Step 1: Configure Windows Server Failover Cluster (On-Prem)

Prerequisites:

• SQL Server Enterprise Edition on all nodes
• Windows Server 2019+ with Failover Clustering feature
• Network connectivity between nodes (dedicated cluster network recommended)
• Matching SQL Server versions and patch levels
• Domain-joined servers with service accounts in same AD domain

Install WSFC Feature:

# Run on each node
Install-WindowsFeature -Name Failover-Clustering -IncludeManagementTools
Restart-Computer

Create WSFC Cluster:

# Run on primary node
New-Cluster -Name SQLCluster01 -Node SQL01,SQL02,SQL03 -StaticAddress 10.0.0.10

# Configure quorum (Node and File Share Majority for 3-node)
Set-ClusterQuorum -NodeAndFileShareMajority \\FileServer\ClusterQuorum

Enable Always On Feature:

-- Run on each SQL instance via SSMS
USE master;
GO
ALTER SERVER CONFIGURATION SET HADR CLUSTER CONTEXT = 'SQLCluster01';
GO

Or via PowerShell (requires restart):

# Run on each node
Import-Module SQLPS
Enable-SqlAlwaysOn -ServerInstance 'SQL01' -Force
Restart-Service -Name MSSQLSERVER -Force

Enable-SqlAlwaysOn -ServerInstance 'SQL02' -Force
Restart-Service -Name MSSQLSERVER -Force

Verify cluster and Always On status:

Get-Cluster | Format-List *
Get-ClusterNode

SELECT SERVERPROPERTY('IsHadrEnabled') AS IsAlwaysOnEnabled;

Step 2: Create Database Mirroring Endpoint

On each SQL instance:

USE master;
GO

-- Create endpoint for Always On communication
CREATE ENDPOINT [Hadr_endpoint]
STATE = STARTED
AS TCP (
    LISTENER_PORT = 5022,
    LISTENER_IP = ALL
)
FOR DATABASE_MIRRORING (
    ROLE = ALL,
    AUTHENTICATION = WINDOWS NEGOTIATE,
    ENCRYPTION = REQUIRED ALGORITHM AES
);
GO

-- Grant connect permission to service account
GRANT CONNECT ON ENDPOINT::[Hadr_endpoint] TO [CONTOSO\SQLServiceAccount];
GO

Verify endpoints:

SELECT name, port, state_desc, encryption_algorithm_desc
FROM sys.database_mirroring_endpoints;

Firewall rule (each node):

New-NetFirewallRule -DisplayName "SQL Always On Endpoint" -Direction Inbound -Protocol TCP -LocalPort 5022 -Action Allow

Step 3: Create Availability Group

Backup database on primary (required for AG):

USE master;
GO

BACKUP DATABASE [MyDatabase] TO DISK = 'C:\Backup\MyDatabase.bak' WITH INIT;
BACKUP LOG [MyDatabase] TO DISK = 'C:\Backup\MyDatabase.trn' WITH INIT;

Create AG on primary replica (SQL01):

CREATE AVAILABILITY GROUP [ProductionAG]
WITH (
    AUTOMATED_BACKUP_PREFERENCE = SECONDARY,
    DB_FAILOVER = ON,  -- Automatic failover for database health issues
    DTC_SUPPORT = PER_DB  -- Distributed transaction support
)
FOR DATABASE [MyDatabase], [MyDatabase2]
REPLICA ON
    'SQL01' WITH (
        ENDPOINT_URL = 'TCP://SQL01.contoso.com:5022',
        AVAILABILITY_MODE = SYNCHRONOUS_COMMIT,
        FAILOVER_MODE = AUTOMATIC,
        BACKUP_PRIORITY = 30,
        SECONDARY_ROLE(ALLOW_CONNECTIONS = READ_ONLY),
        SESSION_TIMEOUT = 10
    ),
    'SQL02' WITH (
        ENDPOINT_URL = 'TCP://SQL02.contoso.com:5022',
        AVAILABILITY_MODE = SYNCHRONOUS_COMMIT,
        FAILOVER_MODE = AUTOMATIC,
        BACKUP_PRIORITY = 50,  -- Prefer SQL02 for backups
        SECONDARY_ROLE(ALLOW_CONNECTIONS = READ_ONLY),
        SESSION_TIMEOUT = 10
    ),
    'SQL03' WITH (
        ENDPOINT_URL = 'TCP://SQL03.contoso.com:5022',
        AVAILABILITY_MODE = ASYNCHRONOUS_COMMIT,  -- DR site, async to avoid latency
        FAILOVER_MODE = MANUAL,
        BACKUP_PRIORITY = 10,
        SECONDARY_ROLE(ALLOW_CONNECTIONS = READ_ONLY),
        SESSION_TIMEOUT = 10
    );
GO

Step 4: Create Availability Group Listener

Listener provides virtual network name for transparent connection redirection:

-- Run on primary replica
ALTER AVAILABILITY GROUP [ProductionAG]
ADD LISTENER 'ProductionAGListener' (
    WITH IP (
        ('10.0.1.100', '255.255.255.0'),  -- Primary site subnet
        ('10.0.2.100', '255.255.255.0')   -- DR site subnet (multi-subnet AG)
    ),
    PORT = 1433
);
GO

For DHCP (simpler but less control):

ALTER AVAILABILITY GROUP [ProductionAG]
ADD LISTENER 'ProductionAGListener' (
    WITH DHCP ON ('10.0.1.0', '255.255.255.0'),
    PORT = 1433
);
GO

Verify listener:

SELECT
    listener.dns_name,
    listener.port,
    ip.ip_address,
    ip.ip_subnet_mask,
    ip.state_desc
FROM sys.availability_group_listeners listener
JOIN sys.availability_group_listener_ip_addresses ip ON listener.listener_id = ip.listener_id;

Test connectivity:

Test-NetConnection -ComputerName ProductionAGListener -Port 1433
Invoke-Sqlcmd -ServerInstance "ProductionAGListener" -Query "SELECT @@SERVERNAME AS CurrentPrimary"

Connection string (multi-subnet failover):

Server=ProductionAGListener;Database=MyDatabase;Integrated Security=True;MultiSubnetFailover=True;ApplicationIntent=ReadOnly

Step 5: Join Secondary Replicas and Seed Databases

Option 1: Automatic Seeding (SQL 2016+, recommended for large DBs):

-- On primary replica, grant seeding permission
ALTER AVAILABILITY GROUP [ProductionAG]
GRANT CREATE ANY DATABASE;

-- On secondary replicas (SQL02, SQL03)
ALTER AVAILABILITY GROUP [ProductionAG] JOIN;
ALTER AVAILABILITY GROUP [ProductionAG] GRANT CREATE ANY DATABASE;

-- Automatic seeding initiates; monitor progress:
SELECT
    ag.name AS AvailabilityGroup,
    db_name(database_id) AS DatabaseName,
    current_state,
    performed_seeding,
    failure_state_desc
FROM sys.dm_hadr_automatic_seeding;

Option 2: Manual Backup/Restore (traditional):

-- On secondary replicas (SQL02, SQL03)
ALTER AVAILABILITY GROUP [ProductionAG] JOIN;

-- Restore database backups with NORECOVERY
RESTORE DATABASE [MyDatabase] FROM DISK = '\\FileShare\Backup\MyDatabase.bak' WITH NORECOVERY;
RESTORE LOG [MyDatabase] FROM DISK = '\\FileShare\Backup\MyDatabase.trn' WITH NORECOVERY;

-- Join database to AG
ALTER DATABASE [MyDatabase] SET HADR AVAILABILITY GROUP = [ProductionAG];
GO

Verify synchronization:

SELECT
    ag.name AS AvailabilityGroup,
    db_name(drs.database_id) AS DatabaseName,
    ar.replica_server_name,
    drs.synchronization_state_desc,
    drs.synchronization_health_desc
FROM sys.dm_hadr_database_replica_states drs
JOIN sys.availability_replicas ar ON drs.replica_id = ar.replica_id
JOIN sys.availability_groups ag ON ar.group_id = ag.group_id
ORDER BY ag.name, db_name(drs.database_id), ar.replica_server_name;

Step 6: Monitor Availability Group Health

-- Comprehensive health dashboard
SELECT
    ag.name AS AvailabilityGroup,
    ar.replica_server_name AS Replica,
    ars.role_desc AS Role,
    ars.operational_state_desc AS State,
    drs.database_name,
    drs.synchronization_state_desc AS SyncState,
    drs.synchronization_health_desc AS Health,
    drs.log_send_queue_size / 1024.0 AS LogSendQueueMB,
    drs.redo_queue_size / 1024.0 AS RedoQueueMB,
    drs.redo_rate / 1024.0 AS RedoRateMBPerSec,
    CASE WHEN drs.redo_rate > 0
         THEN drs.redo_queue_size / drs.redo_rate
         ELSE NULL
    END AS EstimatedRecoverySec
FROM sys.availability_groups ag
JOIN sys.availability_replicas ar ON ag.group_id = ar.group_id
JOIN sys.dm_hadr_availability_replica_states ars ON ar.replica_id = ars.replica_id
JOIN sys.dm_hadr_database_replica_states drs ON ar.replica_id = drs.replica_id
ORDER BY ag.name, ar.replica_server_name;

Step 7: Configure Backup Offloading Strategy

Automated backup preference (defined at AG level):

• PRIMARY: Always backup on primary replica
• SECONDARY_ONLY: Prefer secondary; if unavailable, skip backup
• SECONDARY: Prefer secondary; failover to primary if no secondary available
• NONE: No preference

Set backup priority per replica:

-- Higher priority = more likely to run backups
ALTER AVAILABILITY GROUP [ProductionAG]
MODIFY REPLICA ON 'SQL01' WITH (BACKUP_PRIORITY = 30);  -- Primary, lower priority

ALTER AVAILABILITY GROUP [ProductionAG]
MODIFY REPLICA ON 'SQL02' WITH (BACKUP_PRIORITY = 50);  -- Secondary, highest priority

ALTER AVAILABILITY GROUP [ProductionAG]
MODIFY REPLICA ON 'SQL03' WITH (BACKUP_PRIORITY = 10);  -- DR site, lowest priority

Maintenance plan: backup logic respects AG preference:

-- Check if current replica should run backup
IF sys.fn_hadr_backup_is_preferred_replica('MyDatabase') = 1
BEGIN
    BACKUP DATABASE [MyDatabase] TO DISK = 'C:\Backup\MyDatabase.bak'
    WITH COMPRESSION, CHECKSUM, STATS = 10;
END
ELSE
BEGIN
    PRINT 'Backup skipped: not preferred replica per AG policy';
END

Ola Hallengren backup script with AG support:

EXECUTE dbo.DatabaseBackup
    @Databases = 'USER_DATABASES',
    @Directory = 'C:\Backup',
    @BackupType = 'FULL',
    @Compress = 'Y',
    @CheckSum = 'Y',
    @Verify = 'Y',
    @AvailabilityGroupDirectoryStructure = 'Y',  -- Organizes by AG
    @CleanupTime = 168;  -- 7 days retention

Step 8: Configure Read-Scale-Out Routing

Define read-only routing URLs for each replica:

-- SQL01 (primary or secondary)
ALTER AVAILABILITY GROUP [ProductionAG]
MODIFY REPLICA ON 'SQL01'
WITH (SECONDARY_ROLE(READ_ONLY_ROUTING_URL = 'TCP://SQL01.contoso.com:1433'));

-- SQL02
ALTER AVAILABILITY GROUP [ProductionAG]
MODIFY REPLICA ON 'SQL02'
WITH (SECONDARY_ROLE(READ_ONLY_ROUTING_URL = 'TCP://SQL02.contoso.com:1433'));

-- SQL03 (DR site)
ALTER AVAILABILITY GROUP [ProductionAG]
MODIFY REPLICA ON 'SQL03'
WITH (SECONDARY_ROLE(READ_ONLY_ROUTING_URL = 'TCP://SQL03.contoso.com:1433'));

Configure routing list (priority order for read-only connections):

-- When SQL01 is primary, route read-only to SQL02 first, then SQL03
ALTER AVAILABILITY GROUP [ProductionAG]
MODIFY REPLICA ON 'SQL01'
WITH (PRIMARY_ROLE(READ_ONLY_ROUTING_LIST = ('SQL02', 'SQL03')));

-- When SQL02 is primary, route read-only to SQL01 first
ALTER AVAILABILITY GROUP [ProductionAG]
MODIFY REPLICA ON 'SQL02'
WITH (PRIMARY_ROLE(READ_ONLY_ROUTING_LIST = ('SQL01', 'SQL03')));

Connection string for read-only workloads:

Server=ProductionAGListener;Database=MyDatabase;Integrated Security=True;ApplicationIntent=ReadOnly

Test routing:

-- Connect to listener with ApplicationIntent=ReadOnly
-- Query will execute on SQL02 (first in routing list)
SELECT @@SERVERNAME AS ExecutingReplica;  -- Should return SQL02

Load balance read-only across multiple secondaries:

-- Round-robin: list replicas in parentheses
ALTER AVAILABILITY GROUP [ProductionAG]
MODIFY REPLICA ON 'SQL01'
WITH (PRIMARY_ROLE(READ_ONLY_ROUTING_LIST = (('SQL02', 'SQL03'))));
-- Each connection randomly picks SQL02 or SQL03

Failover Scenarios

Manual Planned Failover (Zero Data Loss)

Use case: Patching, hardware maintenance, testing DR procedures.

-- Run on target secondary replica (must be SYNCHRONOUS_COMMIT)
ALTER AVAILABILITY GROUP [ProductionAG] FAILOVER;

Failover sequence:

1. Primary waits for secondary to catch up (log send queue = 0)
2. Primary hardens transaction log on secondary
3. Roles swap: secondary becomes primary, primary becomes secondary
4. Listener DNS updates to new primary IP (TTL = 20 seconds default)
5. Applications reconnect automatically

Typical RTO: 5-15 seconds

Automatic Failover (Synchronous Mode)

Trigger conditions:

• Primary instance crashes (SQL Server service stops)
• Primary OS crash or network partition (cluster loses quorum)
• Database corruption detected (if DB_FAILOVER = ON)
• Health check timeout exceeds threshold

Cluster quorum voting:

• 3-node cluster: Node Majority (requires 2/3 votes)
• 2-node + file share witness: Node and File Share Majority
• If quorum lost, entire cluster stops (split-brain prevention)

Failover sequence:

1. WSFC detects primary failure (health check timeout: 30 seconds default)
2. Cluster initiates failover to highest-priority synchronous secondary
3. Secondary runs crash recovery (redo/undo log records)
4. Secondary comes online as new primary
5. Listener DNS updated; connections redirect

Typical RTO: 20-30 seconds (depends on redo queue size)

Forced Failover (Data Loss Possible - DR Scenario)

Use case: Primary site disaster (fire, flood, power outage); asynchronous DR replica only option.

WARNING: Only use when primary is confirmed permanently offline; otherwise risks split-brain.

-- Run on DR replica (SQL03)
ALTER AVAILABILITY GROUP [ProductionAG] FORCE_FAILOVER_ALLOW_DATA_LOSS;

Data loss calculation:

-- Check unsynchronized transactions before forced failover
SELECT
    database_name,
    log_send_queue_size / 1024.0 AS UnsyncedLogMB,
    CASE WHEN log_send_rate > 0
         THEN log_send_queue_size / log_send_rate
         ELSE NULL
    END AS EstimatedDataLossSeconds
FROM sys.dm_hadr_database_replica_states
WHERE is_local = 1 AND is_primary_replica = 0;

Post-forced-failover steps:

1. Resume data movement: ALTER DATABASE [MyDatabase] SET HADR RESUME;
2. Verify data integrity with DBCC CHECKDB
3. Assess data loss: compare last sync time with business records
4. When original primary recovers, remove from AG or rejoin as secondary

Performance Tuning

Replica Synchronization Mode

Synchronous commit (SYNCHRONOUS_COMMIT):

• Pros: Zero data loss (RPO=0); automatic failover
• Cons: Commit latency = 2x network RTT + secondary disk write
• Use case: Local HA replicas (<2ms RTT); mission-critical data

Asynchronous commit (ASYNCHRONOUS_COMMIT):

• Pros: No impact on primary commit latency; tolerates high network latency
• Cons: Potential data loss (RPO = seconds to minutes); manual failover only
• Use case: Distant DR replicas (>50ms RTT); reporting secondaries

Network Optimization

Dedicated cluster network:

# Configure cluster network priority (higher = preferred for AG traffic)
Get-ClusterNetwork | Where-Object {$_.Name -eq "ClusterNetwork1"} | Set-ClusterNetwork -Role 3 -Metric 1000
Get-ClusterNetwork | Where-Object {$_.Name -eq "ClusterNetwork2"} | Set-ClusterNetwork -Role 1 -Metric 2000

Enable compression for distant replicas:

ALTER AVAILABILITY GROUP [ProductionAG]
MODIFY REPLICA ON 'SQL03'
WITH (SEEDING_MODE = AUTOMATIC, AVAILABILITY_MODE = ASYNCHRONOUS_COMMIT);

Offload Read Workloads

Route reporting queries to secondaries:

• Saves 30-50% compute on primary
• Reporting queries can tolerate slight lag (seconds behind primary)
• Use ApplicationIntent=ReadOnly in connection string

Monitor read workload on secondaries:

SELECT
    DB_NAME(database_id) AS DatabaseName,
    SUM(num_of_reads) AS TotalReads,
    SUM(num_of_bytes_read) / 1024 / 1024 AS TotalReadMB
FROM sys.dm_io_virtual_file_stats(NULL, NULL)
GROUP BY database_id;

Redo Queue Management

Symptoms of redo bottleneck:

• High redo queue size (>500 MB)
• Low redo rate (<10 MB/sec)
• Long estimated recovery time (>60 seconds)

Solutions:

• Increase secondary replica CPU/cores (redo is single-threaded per DB)
• Use faster storage (SSD/NVMe) on secondary
• Reduce write workload on primary (batch updates, optimize indexes)
• Enable instant file initialization on secondary

SQL 2022+: Parallel redo:

ALTER AVAILABILITY GROUP [ProductionAG]
MODIFY REPLICA ON 'SQL02'
WITH (REDO_MAX_THREADS = 8);  -- Default: 0 (auto)

Monitoring & Alerting

Key Metrics to Monitor

1. Redo Queue Size (secondary lag):

SELECT
    ar.replica_server_name,
    drs.database_name,
    drs.redo_queue_size / 1024.0 AS RedoQueueMB,
    drs.redo_rate / 1024.0 AS RedoRateMBPerSec,
    CASE WHEN drs.redo_rate > 0
         THEN drs.redo_queue_size / drs.redo_rate
         ELSE 99999
    END AS EstimatedRecoverySec
FROM sys.dm_hadr_database_replica_states drs
JOIN sys.availability_replicas ar ON drs.replica_id = ar.replica_id
WHERE drs.is_local = 1 AND drs.is_primary_replica = 0;

Alert threshold: RedoQueueMB > 500 OR EstimatedRecoverySec > 60

2. Synchronization Health:

SELECT
    ag.name AS AvailabilityGroup,
    ar.replica_server_name,
    drs.database_name,
    drs.synchronization_state_desc,
    drs.synchronization_health_desc,
    drs.last_commit_time,
    DATEDIFF(SECOND, drs.last_commit_time, GETDATE()) AS SecondsBehindPrimary
FROM sys.availability_groups ag
JOIN sys.availability_replicas ar ON ag.group_id = ar.group_id
JOIN sys.dm_hadr_database_replica_states drs ON ar.replica_id = drs.replica_id
WHERE drs.synchronization_health_desc <> 'HEALTHY'
   OR DATEDIFF(SECOND, drs.last_commit_time, GETDATE()) > 30;

Alert threshold: synchronization_health_desc <> 'HEALTHY' OR SecondsBehindPrimary > 30

3. Failover Readiness:

SELECT
    ag.name,
    ar.replica_server_name,
    ar.failover_mode_desc,
    ars.is_local,
    ars.role_desc,
    ars.connected_state_desc,
    ars.operational_state_desc
FROM sys.availability_groups ag
JOIN sys.availability_replicas ar ON ag.group_id = ar.group_id
JOIN sys.dm_hadr_availability_replica_states ars ON ar.replica_id = ars.replica_id
WHERE ar.failover_mode_desc = 'AUTOMATIC'
  AND (ars.connected_state_desc <> 'CONNECTED' OR ars.operational_state_desc <> 'ONLINE');

Alert threshold: Any automatic failover replica not connected or online

SQL Agent Alert Jobs

Create alert for synchronization issues:

USE msdb;
GO

EXEC sp_add_alert
    @name = 'AG_Synchronization_Not_Healthy',
    @message_id = 0,
    @severity = 0,
    @enabled = 1,
    @delay_between_responses = 900,  -- 15 minutes
    @include_event_description_in = 1,
    @category_name = N'[Uncategorized]',
    @performance_condition = N'SQLServer:Database Replica|Log Send Queue|ProductionAG|>|524288';  -- 512 MB

EXEC sp_add_notification
    @alert_name = 'AG_Synchronization_Not_Healthy',
    @operator_name = 'DBATeam',
    @notification_method = 1;  -- Email
GO

Azure Monitor Integration (Hybrid Scenarios)

Log Analytics query for AG health:

Event
| where Source == "MSSQLSERVER"
| where EventID in (1480, 35264, 35265)  -- AG failover events
| project TimeGenerated, Computer, EventID, RenderedDescription
| order by TimeGenerated desc

Azure SQL Managed Instance (built-in monitoring):

-- Managed Instance exposes AG DMVs for monitoring
SELECT * FROM sys.dm_hadr_database_replica_states;

Disaster Recovery Best Practices

Geographic Topology

3-node configuration:

• Node 1 (SQL01): Primary datacenter, synchronous commit, automatic failover
• Node 2 (SQL02): Primary datacenter (different rack/PDU), synchronous commit, automatic failover
• Node 3 (SQL03): DR site (different region/city), asynchronous commit, manual failover

Benefits:

• Local HA: Automatic failover within primary datacenter (RPO=0, RTO <30s)
• DR: Manual failover to DR site if entire primary site fails (RPO = seconds, RTO = minutes)

Quorum Configuration

For 3-node cluster with file share witness:

Set-ClusterQuorum -NodeAndFileShareMajority "\\FileServer\ClusterQuorum"

Voting: 3 nodes + 1 witness = 4 votes; requires 3/4 to maintain quorum

For Azure cloud witness (recommended for hybrid):

Set-ClusterQuorum -CloudWitness -AccountName "storageaccount" -AccessKey "<key>"

Patching Strategies (Zero Downtime)

Rolling patch workflow:

1. Patch SQL03 (DR site, asynchronous): Apply Windows/SQL updates, restart
2. Verify SQL03 synchronization: Wait for redo queue to clear
3. Patch SQL02 (secondary, synchronous):
   • Suspend AG: ALTER AVAILABILITY GROUP [ProductionAG] MODIFY REPLICA ON 'SQL02' WITH (SESSION_TIMEOUT = 3600);
   • Apply updates, restart
   • Resume AG: ALTER AVAILABILITY GROUP [ProductionAG] MODIFY REPLICA ON 'SQL02' WITH (SESSION_TIMEOUT = 10);
4. Manual failover to SQL02: ALTER AVAILABILITY GROUP [ProductionAG] FAILOVER; (on SQL02)
5. Patch SQL01 (now secondary): Apply updates, restart
6. Failover back to SQL01 (optional): Restore original primary

Downtime: Zero (assuming no application issues with temporary secondary as primary)

DR Testing

Quarterly DR drill checklist:

1. Announce drill to stakeholders (avoid false alarms)
2. Simulate primary site failure: Shut down SQL01 and SQL02
3. Force failover to SQL03:

   -- On SQL03
   ALTER AVAILABILITY GROUP [ProductionAG] FORCE_FAILOVER_ALLOW_DATA_LOSS;
   ALTER DATABASE [MyDatabase] SET HADR RESUME;
   

4. Test application connectivity to ProductionAGListener
5. Measure RTO/RPO: Time to failover completion, data loss (if any)
6. Restore primary site: Rejoin SQL01/SQL02 as secondaries
7. Document lessons learned: Update runbooks, adjust procedures

Runbook Documentation

DR failover runbook (keep in accessible location):

## Emergency DR Failover to SQL03

**Prerequisites:**
- Confirm primary site (SQL01, SQL02) is permanently offline
- Verify quorum: `Get-ClusterNode` (must have 2/4 votes: SQL03 + witness)





**Steps:**
1. Connect to SQL03 via SSMS (server name: SQL03.contoso.com)
2. Execute: `ALTER AVAILABILITY GROUP [ProductionAG] FORCE_FAILOVER_ALLOW_DATA_LOSS;`
3. Resume data movement: `ALTER DATABASE [MyDatabase] SET HADR RESUME;`
4. Test listener: `Test-NetConnection ProductionAGListener -Port 1433`
5. Notify application teams: "ProductionAGListener now points to SQL03"
6. Run DBCC CHECKDB on all databases
7. Assess data loss: Compare last_commit_time with business records

**Post-failover:**
- Monitor redo queue on SQL03
- Update DNS TTL if needed (listener may cache old IP)
- When primary site recovers, remove SQL01/SQL02 from AG or rejoin as secondaries

Troubleshooting

Issue: Listener not resolving or connecting to wrong replica
Solution:

1. Check DNS registration: nslookup ProductionAGListener (should return current primary IP)
2. Verify listener resource online in WSFC: Get-ClusterResource | Where-Object ResourceType -eq "Network Name"
3. Check firewall rules on all nodes: Get-NetFirewallRule -DisplayName "SQL*"
4. Reduce DNS TTL for faster failover: Get-ClusterResource "ProductionAGListener" | Get-ClusterParameter RegisterAllProvidersIP
5. Enable MultiSubnetFailover=True in connection strings for multi-subnet AG
6. Clear client DNS cache: ipconfig /flushdns

Issue: Secondary replica not synchronizing (INITIALIZING stuck)
Solution:

1. Verify endpoint connectivity: Test-NetConnection SQL02.contoso.com -Port 5022
2. Check endpoint status: SELECT name, state_desc FROM sys.database_mirroring_endpoints; (should be STARTED)
3. Verify service account has CONNECT permission on endpoint
4. Check Windows Firewall on port 5022
5. Review SQL error log for certificate trust errors
6. If automatic seeding, check: SELECT * FROM sys.dm_hadr_automatic_seeding; for failure_state_desc
7. Manually restore database with NORECOVERY and rejoin AG

Issue: High redo queue (>1 GB) on secondary
Solution:

1. Check secondary CPU usage: SELECT * FROM sys.dm_os_ring_buffers WHERE ring_buffer_type = 'RING_BUFFER_SCHEDULER_MONITOR';
2. Increase secondary CPU cores (redo is CPU-bound, single-threaded per DB)
3. Use faster storage on secondary (redo writes to data/log files)
4. Enable instant file initialization: EXEC xp_cmdshell 'whoami /priv' (check for SeManageVolumePrivilege)
5. Reduce write workload on primary (batch updates, optimize indexes)
6. SQL 2022+: Increase parallel redo threads: ALTER AVAILABILITY GROUP [ProductionAG] MODIFY REPLICA ON 'SQL02' WITH (REDO_MAX_THREADS = 8);

Issue: Failover succeeds but applications can't connect
Solution:

1. Verify listener DNS updated: nslookup ProductionAGListener (should return new primary IP)
2. Check application connection string includes MultiSubnetFailover=True
3. Verify new primary is ONLINE: SELECT role_desc FROM sys.dm_hadr_availability_replica_states WHERE is_local = 1;
4. Check logins exist on new primary: SELECT name FROM sys.server_principals WHERE type = 'S'; (sync logins across replicas)
5. Test connectivity: Invoke-Sqlcmd -ServerInstance ProductionAGListener -Query "SELECT @@SERVERNAME"

Issue: "The availability group is not ready for automatic failover"
Solution:

1. Check synchronization state: SELECT synchronization_health_desc FROM sys.dm_hadr_database_replica_states; (must be HEALTHY)
2. Verify both replicas set to SYNCHRONOUS_COMMIT and AUTOMATIC failover mode
3. Check for suspended data movement: SELECT suspend_reason_desc FROM sys.dm_hadr_database_replica_states;
4. Ensure redo queue near zero: SELECT redo_queue_size FROM sys.dm_hadr_database_replica_states; (<100 MB)
5. Verify cluster quorum: Get-ClusterNode | Select Name, State (majority of votes online)

Issue: Forced failover completes but data loss unknown
Solution:

1. Query last_commit_time on new primary (formerly secondary):

   SELECT database_name, last_commit_time
   FROM sys.dm_hadr_database_replica_states
   WHERE is_local = 1;
   

2. Compare with business logs/application audit tables
3. Check backup timestamps: RESTORE HEADERONLY FROM DISK = 'C:\Backup\MyDatabase.bak';
4. Run DBCC CHECKDB to verify consistency
5. Document data loss window for incident report

Best Practices

• Use Synchronous Commit for Local HA, Async for DR: Sync within datacenter (RPO=0); async for distant DR (avoid latency impact)
• Offload Reporting to Secondary Replicas: Saves 30-50% primary CPU; use ApplicationIntent=ReadOnly in connection strings
• Automate AG Health Monitoring: SQL Agent alerts + Azure Monitor; alert on redo queue >500 MB, sync health <> HEALTHY
• Test Failover Quarterly: Measure RTO/RPO, validate application connectivity, update runbooks
• Sync Logins and Jobs Across Replicas: Use linked server scripts or PowerShell to copy logins, SQL Agent jobs, linked servers
• Document Patching Procedures: Rolling patch workflow for zero-downtime updates; test in non-prod first
• Use Cloud Witness for Hybrid Clusters: Azure storage account witness more reliable than file share for geo-distributed nodes
• Tune Session Timeout: Default 10 seconds; increase to 30 for WAN links to avoid false failovers
• Monitor Network Latency: Sync commit performance degrades >10ms RTT; use dedicated cluster network
• Enable Database Health Detection: DB_FAILOVER = ON triggers automatic failover for corruption (SQL 2016+)

Architecture Decision and Tradeoffs

When designing data management solutions with SQL Server, consider these key architectural trade-offs:

Approach	Best For	Tradeoff
Managed / platform service	Rapid delivery, reduced ops burden	Less customisation, potential vendor lock-in
Custom / self-hosted	Full control, advanced tuning	Higher operational overhead and cost

Recommendation: Start with the managed approach for most workloads and move to custom only when specific requirements demand it.

Validation and Versioning

• Last validated: April 2026
• Validate examples against your tenant, region, and SKU constraints before production rollout.
• Keep module, CLI, and SDK versions pinned in automation pipelines and review quarterly.

Security and Governance Considerations

• Apply least-privilege access using RBAC roles and just-in-time elevation for admin tasks.
• Store secrets in managed secret stores and avoid embedding credentials in scripts or source files.
• Enable audit logging, data protection policies, and periodic access reviews for regulated workloads.

Cost and Performance Notes

• Define budgets and alerts, then monitor usage and cost trends continuously after go-live.
• Baseline performance with synthetic and real-user checks before and after major changes.
• Scale resources with measured thresholds and revisit sizing after usage pattern changes.

Official Microsoft References

• https://learn.microsoft.com/sql/
• https://learn.microsoft.com/azure/azure-sql/
• https://learn.microsoft.com/fabric/database/

Public Examples from Official Sources

• These examples are sourced from official public Microsoft documentation and sample repositories.
• Documentation examples: https://learn.microsoft.com/sql/
• Sample repositories: https://github.com/microsoft/sql-server-samples
• Prefer adapting these examples to your tenant, subscriptions, and governance requirements before production use.

Key Takeaways

• Always On AG Provides Automatic Failover with Zero Data Loss: Synchronous commit + automatic failover = RPO 0, RTO <30 seconds
• Read-Scale-Out Improves Workload Distribution: Route reporting queries to secondaries; reduces primary load by 30-50%
• Listener Abstraction Simplifies Connection Management: Applications connect to virtual network name; transparent failover redirection
• Monitoring Redo Queue and Sync State Ensures Health Visibility: High redo queue (>500 MB) or sync lag (>30 sec) signals issues
• DR Site with Async Replication Balances Protection and Performance: Third replica in DR site; async mode avoids latency impact on primary
• Zero-Downtime Patching via Rolling Updates: Patch secondaries first, failover, patch primary; no application downtime

Next Steps

• Implement distributed AG for multi-region failover (AG spanning multiple WSFC clusters)
• Integrate with Azure Backup for point-in-time restore (automated backup to Azure Blob Storage)
• Pilot Azure SQL Managed Instance (fully managed AG; no WSFC management required)
• Configure Azure Site Recovery for entire VM-level DR (supplements AG database-level DR)
• Enable Query Store on AG databases for performance troubleshooting across failovers
• Implement Extended Events session for AG failover event tracking and root cause analysis
• Explore SQL Server 2022 contained AG (logins/jobs included in AG; simplifies sync)
• Test hybrid AG with on-prem primary + Azure VM secondary for cloud DR

Additional Resources

• Always On Availability Groups
• Configure Read-Scale-Out
• Monitor Availability Groups

---

Is your database ready for zero-downtime deployments?