15.3 Monitoring and Troubleshooting HA/DR Solutions

Monitoring Targets and the Key DMVs

Four things must be monitored for any HA/DR solution, and the DP-300 exam expects you to know which DMV or table answers each:

1. Replication health - is data flowing from primary to secondary, and is the secondary redoing it fast enough?
2. Failover readiness - could we fail over right now without data loss or a broken application?
3. Backup compliance - is the log backup chain intact so PITR and log shipping work?
4. RPO/RTO actual vs target - what is the real replication lag and how long did the last drill take?

The DMV map:

RPO/RTO Measurement from Monitoring Data

RPO for an AG is the lag between primary and secondary, calculated from log_send_queue_size + redo_queue_size on the synchronous secondary: with synchronous commit the lag should be near zero (RPO = 0) because the primary waits for the secondary; with asynchronous commit the lag is the size of the send queue divided by the send rate, which is the worst-case data loss window. RTO is the time from failover start to the new primary accepting connections; measure it from a real failover drill, not from theory. The dashboard gives a snapshot of health, but only a planned failover drill tells you whether your RTO target (say, 2 minutes) is actually achievable, including application reconnect time.

Always On Dashboard, Health Model, and Alerts

The Always On Availability Group Dashboard in SSMS surfaces replica state, database state, send/redo queues, and the Always On health model policy evaluations. The health model defines a set of policies (replica connected, data synchronization healthy, failover readiness, and so on) and rolls them up into a health state for each AG. You can configure alerts on AG state changes through SQL Server Agent (raise a SQL Agent alert on the WMI event for AG state change, with Database Mail or an operator action) or through Azure Monitor Action Groups when running on Azure VMs. Common alert-worthy events:

• AG state change (the AG changed role - a failover happened).
• Replica disconnected (the secondary is unreachable).
• Failover readiness degraded (the AG could not auto-failover right now).
• Backup job failure (the log backup chain is broken).
• Latency threshold exceeded (for log shipping).

A scenario asking how to proactively notify the team of a silent AG break points to configuring a SQL Agent alert on the AG state change WMI event plus an operator; a scenario asking for centralized cloud-native alerting on Azure VMs points to Azure Monitor Action Groups wired to the SQL IaaS Agent extension metrics.

Failover Testing and Drill Validation

Failover drills validate that the documented procedure works and that RTO is achievable. A drill should: (1) confirm the secondary is synchronized, (2) initiate a manual forced failover (do not test automatic failover in production - the unpredictability of the trigger makes the result meaningless), (3) measure the time from initiation to application reconnect, (4) verify the application still works against the new primary (logins, jobs, linked servers all present), and (5) document the result. Drills catch the common silent failures: a login that was never copied to the secondary, a SQL Agent job that was disabled on the secondary, a certificate expiring on the secondary endpoint, or a routing rule pointing at the wrong listener. Run drills on a cadence - quarterly is common - and after every infrastructure change that touches the cluster, networking, or storage.

Troubleshooting Scenario Catalog

The exam tests a recurring catalog of HA/DR failures. Below is the structured catalog with symptom, root cause, and first diagnostic step for each.

AG Not Failing Over

• Quorum loss: the WSFC dropped below quorum (lost the cloud witness, lost a node, or a partitioned network). Symptom: the AG resource shows Failed or Offline, and the cluster logs show loss of quorum. Diagnostic: Failover Cluster Manager > Quorum, and the Cluster Operational log. Fix: restore the witness or bring back enough nodes for quorum.
• Split-brain prevention: both nodes think they should be primary. The WSFC fencing prevents this; the AG goes into a RESOLVING state rather than running two primaries. Diagnostic: sys.dm_hadr_availability_replica_states shows role = 3 (RESOLVING) on both replicas.
• Lease timeout: the AG lease between SQL Server and the WSFC expired (often under SQL Server CPU pressure). Symptom: AG goes offline and the SQL error log shows 'The availability group lease is no longer valid'. Diagnostic: SQL Server error log and the lease timeout setting.
• Session timeout: the replica has not responded within the session timeout (default 10s). Symptom: secondary marked DISCONNECTED. Diagnostic: sys.dm_hadr_availability_replica_states.connected_state_desc and last_connect_error_description.

Secondary Behind

• Redo log queue growing: the secondary is receiving logs but not redoing them fast enough (heavy read workload, blocked redo thread by a lock conflict, under-provisioned CPU). Diagnostic: sys.dm_hadr_database_replica_states shows large redo_queue_size with small log_send_queue_size. Fix: reduce read workload on the secondary, size up the secondary, or investigate a blocking lock on the redo thread.
• Log send queue growing: the primary is not getting logs to the secondary (network bandwidth, endpoint down, or the secondary is paused). Diagnostic: large log_send_queue_size. Fix: check the endpoint state, NSG rules on port 5022, and whether the secondary is suspended.

Listener Unreachable

• Azure Load Balancer probe failing: the probe port is not open on the owning node or the probe rule is misconfigured, so the LB stops sending traffic. Diagnostic: check the LB probe health in the portal and confirm the probe port is open in the OS firewall and SQL endpoint config.
• NSG blocking the listener port: a network security group rule denies 1433 or the listener port. Diagnostic: use Azure Network Watcher's connection troubleshoot or check effective security rules on the VM NIC.
• DNS stale or missing: the listener FQDN resolves to the wrong IP or does not resolve. Diagnostic: nslookup from the client and from the cluster nodes. Fix: register the listener's IP(s) in DNS and clear the client DNS cache.
• Client missing MultiSubnetFailover: covered above - clients cache one IP and reconnect slowly.

Geo-Replication and Failover Group Stuck

• Geo-replication link unhealthy: for Azure SQL Database active geo-replication, check sys.dm_geo_replication_link_status for replication_lag_sec, last_replication_datetime, and link_desc. A long lag or a NULL last_replication_datetime means the link is broken.
• Failover group stuck: auto-failover groups may refuse failover because of incompatible databases (a database is still seeding), pending operations (a seeding is in progress), or a grace period in effect. Diagnostic: check the failover group status in the portal or Get-AzSqlDatabaseFailoverGroup and the seeding state of the affected databases.

Backup Chain Broken

The most common root cause of AG and log-shipping replication stalls. The transaction log cannot be truncated when the backup chain is broken, the log fills up, and the primary stalls. Causes:

• Missing log backup: a scheduled log backup job failed or was disabled. The log keeps growing because the chain is incomplete.
• Recovery model changed to SIMPLE: SIMPLE recovery truncates the log automatically and breaks the log backup chain - an AG database must be in FULL recovery.
• Long-running active transaction: an open transaction holds the log truncation point.
• Replication, CDC, or change tracking holding the log.

Diagnostic: query sys.databases.log_reuse_wait_desc on the primary - values like LOG_BACKUP, ACTIVE_TRANSACTION, or REPLICATION tell you what is holding the log. Fix the holder (resume the log backup job, kill the long transaction, or bring the replication agent back online).

Restore Failures

• Backup media corrupt: RESTORE VERIFYONLY before the real restore to catch this early.
• Encryption keys missing: the certificate protecting the backup is not restored to the target. Symptom: 'Cannot find server certificate'. Fix: restore the certificate and private key to the target before the database restore.
• File paths not matching: restoring to a different path than the source. Fix: use WITH MOVE to remap file paths.
• NORECOVERY forgotten: the secondary needs to stay in RESTORING state to accept more logs; restoring WITH RECOVERY by mistake brings it online and breaks the log chain. Fix: reinitialize from a full backup.

Structured Troubleshooting Workflow and Checklist

When the exam presents a broken HA/DR scenario, follow this workflow in order:

1. Isolate the symptom: AG down? Secondary behind? Listener unreachable? Backup job failing? The symptom determines which branch to investigate.
2. Check quorum and witness: is the WSFC healthy? Does it have quorum? Is the cloud witness online?
3. Check replica and database state: run sys.dm_hadr_availability_replica_states and sys.dm_hadr_database_replica_states; look at connected_state_desc, synchronization_state_desc, log_send_queue_size, redo_queue_size.
4. Check the backup chain: query sys.databases.log_reuse_wait_desc; verify the last successful log backup in msdb.dbo.backupset.
5. Check listener and network: LB probe health, NSG effective rules, DNS resolution, client connection string (MultiSubnetFailover).
6. Check logs: SQL Server error log, Windows Application log, Cluster Operational log for the matching error event.
7. Verify the fix and re-run a drill: after applying the fix, validate with a planned failover drill to confirm the system is back to failover-ready state.

Pre-flight checklist before any change to an HA/DR system:

• WSFC has quorum and the cloud witness is online
• All replicas are CONNECTED and synchronization_health = HEALTHY
• Log send queue and redo queue are near zero
• Last log backup is recent (within RPO interval)
• log_reuse_wait_desc is NOTHING on the primary
• Listener resolves to the primary's IP and the LB probe is healthy
• SQL Agent alerts on AG state change are configured and tested
• A recent failover drill has been run and the result documented

A scenario describing a sudden AG break with no obvious network change should lead you to the backup chain and the log_reuse_wait_desc check first - a failed log backup job is the single most common silent root cause.