Study guide
15.3 Monitoring and Troubleshooting HA/DR Solutions
DP-300: Administering Microsoft Azure SQL Solutions Exam
Track your progress as you read
Finished sections get a check mark.
1 Introduction & Exam Overview
1.1 Current DP-300 Exam Facts 1.2 Skills Outline and Domain Weights 1.3 Study Strategy and Official Resources
2 Planning and Deploying Azure SQL Solutions
2.1 Choosing and Deploying Azure SQL Database Offerings 2.2 Automated Deployment, Azure Arc, and SQL Database in Microsoft Fabric 2.3 Table Partitioning and Sharding Strategy 2.4 Hybrid SQL Server and IaaS Patching
3 Configuring Resources for Scale and Performance
3.1 Configuring Azure SQL Database for Scale and Performance 3.2 Configuring Azure SQL Managed Instance for Scale and Performance 3.3 Configuring SQL Server on Azure VMs for Scale and Performance 3.4 Configuring Table Partitioning and Data Compression
4 Planning and Implementing a Migration Strategy
4.1 Evaluating Migration Requirements and Strategies 4.2 Implementing Online, Offline, and Azure Migrations 4.3 Migrating Between Azure SQL Services and Troubleshooting
5 Configuring Database Authentication and Authorization
5.1 Microsoft Entra ID Authentication for Azure SQL 5.2 Authentication for SQL on Azure VMs and Managed Instance; Troubleshooting 5.3 Security Principals and Entra-Backed Users 5.4 Permissions, Least Privilege, and T-SQL Management
6 Implementing Security for Data at Rest and in Transit
6.1 Transparent Data Encryption and Object-Level Encryption 6.2 Always Encrypted and VBS Enclaves 6.3 Firewall Rules, Private Links, and Service Endpoints
7 Implementing Compliance Controls for Sensitive Data
7.1 Data Classification and Server/Database Auditing 7.2 Change Data Tracking and Dynamic Data Masking 7.3 Row-Level Security and Ledger in Azure SQL
8 Monitoring Resource Activity and Performance
8.1 Operational Baselines and Performance Metric Sources 8.2 Interpreting Metrics and Configuring Activity Monitoring 8.3 Monitoring with Database Watcher (Preview) 8.4 Monitoring with Extended Events
9 Monitoring and Optimizing Query Performance
9.1 Configuring and Monitoring with Query Store 9.2 Session Blocking and Dynamic Management Views 9.3 Index Changes and Execution Plan Review 9.4 Query Construct Modifications and Intelligent Insights
10 Configuring Database Solutions for Optimal Performance
10.1 Index, Statistics, and Integrity Maintenance 10.2 Automatic Tuning and Database-Scoped Configuration 10.3 Server Settings and Resource Governor 10.4 Compute/Storage Scaling and Intelligent Query Processing
11 Creating and Managing SQL Server Agent Jobs
11.1 Schedules for Regular Maintenance Jobs 11.2 Job Alerts, Notifications, and Agent Troubleshooting 11.3 Automating Deployment with ARM and Bicep Templates
12 Automating Deployments and Database Tasks in Azure
12.1 Azure PowerShell, Azure CLI, and Deployment Monitoring 12.2 Elastic Jobs for Cross-Database Automation 12.3 Azure Automation Runbooks, Alerts, and Troubleshooting Automated Tasks
13 Planning an HA/DR Strategy and Backup and Restore
13.1 Recommending HA/DR Strategy from RPO and RTO 13.2 Hybrid and Azure-Specific HA/DR Solutions and Testing 13.3 Backup and Restore Strategy with Native Tools 13.4 Point-in-Time Restore and Recovery Operations
14 Configuring HA/DR: Long-Term Retention, Geo-Replication, and Failover Groups
14.1 Long-Term Retention, T-SQL Backup/Restore, and Cloud Storage 14.2 Active Geo-Replication 14.3 Failover Groups
15 Advanced HA/DR: Availability Groups, FCIs, Log Shipping, and Troubleshooting
15.1 Always On Availability Groups on SQL Managed Instance and Azure VMs 15.2 Always On Failover Cluster Instances and Log Shipping 15.3 Monitoring and Troubleshooting HA/DR Solutions
15.3 Monitoring and Troubleshooting HA/DR Solutions
Key Takeaways Explain This Topic AI
The four monitoring targets for HA/DR are replication health, failover readiness, backup compliance, and actual RPO/RTO measured against the agreed targets
sys.dm_hadr_availability_replica_states and sys.dm_hadr_database_replica_states cover AG replica and database health; sys.geo_replication_links and sys.dm_geo_replication_link_status cover Azure SQL Database active geo-replication
RPO is measured from the log send and redo queue sizes on the secondary; RTO is measured from drill tests that record how long a planned or unplanned failover takes end to end
AG failover failures almost always trace to quorum loss, a missing or offline witness, lease or session timeout, or split-brain prevention; the WSFC and SQL Server error logs identify which
A broken backup chain - a missing log backup, a recovery model change, or a full log - is the most common root cause of AG and log-shipping replication stalls
Last updated: August 2026
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:
Replication health - is data flowing from primary to secondary, and is the secondary redoing it fast enough?
Failover readiness - could we fail over right now without data loss or a broken application?
Backup compliance - is the log backup chain intact so PITR and log shipping work?
RPO/RTO actual vs target - what is the real replication lag and how long did the last drill take?
The DMV map:
- Question Source
- Is the AG replica connected? What is its role and sync health? sys.dm_hadr_availability_replica_states
- What is the per-database log send queue and redo queue? sys.dm_hadr_database_replica_states
- What does the WSFC see for the AG cluster resource? sys.dm_hadr_availability_group_cluster
Is the Azure SQL Database geo-replication link healthy? sys.geo_replication_links, sys.dm_geo_replication_link_status
- When was the last successful backup of each type? msdb.dbo.backupset, msdb.dbo.backupmediafamily
- Is log shipping behind? msdb.dbo.log_shipping_monitor_primary, msdb.dbo.log_shipping_monitor_secondary
- 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.