Study guide
15.1 Always On Availability Groups on SQL Managed Instance and Azure VMs
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.1 Always On Availability Groups on SQL Managed Instance and Azure VMs
Key Takeaways Explain This Topic AI
An Always On availability group protects databases by maintaining up to eight secondary replicas of a primary database set; synchronous-commit replicas enable automatic failover with zero data loss, asynchronous-commit replicas accept some data loss to extend distance
On SQL Server on Azure VMs, an AG requires a Windows Server Failover Cluster; the AG listener needs an Azure Load Balancer (or distributed network name) and a health probe port so the cluster detects which replica owns the listener
SQL Managed Instance implements auto-failover groups and Business Critical readable secondaries on Always On AG technology under the hood; you do not build a WSFC yourself on MI
Readable secondaries are reached by setting ApplicationIntent=ReadOnly and configuring a read-only routing list; Enterprise Edition is required for readable secondaries on SQL Server VMs
Key monitoring DMVs are sys.dm_hadr_availability_replica_states (replica health and connected state) and sys.dm_hadr_database_replica_states (per-database log and redo queue, synchronization health)
Last updated: August 2026
AG Architecture and Why It Matters for DP-300
An Always On availability group (AG) is a database-level high-availability and disaster-recovery technology. One database (or a group of databases, called an availability database set ) is hosted on a primary replica ; the same databases are replicated to one to eight secondary replicas . The exam weights AGs heavily because they are the primary HA/DR mechanism on SQL Server on Azure VMs and because the same technology powers several platform-managed features of Azure SQL Managed Instance. The first thing to lock in is the distinction between an AG and an FCI: an AG protects databases , while an FCI protects the instance . An AG can itself run on top of an FCI, combining instance-level and database-level protection.
Three interrelated per-replica settings define the behavior of an AG:
Setting Values What it controls
Availability mode SYNCHRONOUS_COMMIT or ASYNCHRONOUS_COMMIT Whether the primary waits for the secondary to harden log records (zero data loss) or ships them asynchronously (some data loss acceptable)
Failover mode AUTOMATIC or MANUAL Whether failover is self-initiated by the cluster (requires synchronous commit) or human-initiated
Session timeout seconds (default 10) How long replicas can be disconnected before the primary marks the secondary as failed
RPO (recovery point objective) is set by the availability mode and the log shipment rate: synchronous-commit replicas deliver RPO = 0 (the primary commits only after the secondary hardens the log), while asynchronous-commit replicas accept a small lag in exchange for cross-region distance tolerance. RTO (recovery time objective) is governed by the failover mode and the time to bring the secondary online: automatic failover with a healthy synchronous secondary minimizes RTO; manual failover introduces human action time. A common exam trap is pairing asynchronous commit with automatic failover - that combination is invalid because automatic failover requires synchronous commit; you can configure it, but the cluster will not trigger automatic failover against an async secondary.
Listeners and Client Redirect
An AG listener is a virtual network name plus a virtual IP that clients connect to instead of connecting directly to a replica. On failover, the listener follows the primary role, so applications keep connecting to the same name and are routed to the new primary. Inside the WSFC, the listener is a clustered resource; the replica that currently owns the primary role hosts the listener resource and answers on its IP. On Azure VMs the listener IP cannot float with a gratuitous ARP as it would on a physical network, so an Azure Load Balancer (Standard Load Balancer) or a Distributed Network Name (DNN) resource distributes incoming listener traffic to the node that owns the role. The load balancer uses a health probe that hits a probe port (commonly the AG endpoint port or a dedicated port) to decide which VM is live. Without the probe, the load balancer sends traffic to the wrong node and the listener appears unreachable.
AG on SQL Server on Azure VMs
Running an AG on a SQL Server on Azure VM gives you the full on-premises feature set but requires you to build the cluster plumbing yourself. Prerequisites:
A Windows Server Failover Cluster (WSFC) spanning the VM nodes. The nodes do not need to be domain-joined (workgroup clusters are supported), but if the cluster spans subnets you must use a multi-subnet configuration and the listener must be a multi-IP DNN or ILB listener with each subnet's IP registered in DNS.
The Always On availability groups feature enabled on each SQL Server instance ( ALTER SERVER CONFIGURATION SET HADR ON or the SSMS server properties page).
An endpoint on each replica for log replication (typically port 5022) and a matching Azure Network Security Group rule permitting traffic between the nodes.
An Azure Load Balancer (or DNN) fronting the listener IP and probing a configured port.
Multi-Subnet and Distributed AGs
Multi-subnet AGs span subnets (often across Azure availability zones or regions). The listener holds one IP per subnet; clients include MultiSubnetFailover=True in the connection string so the SQL Client parallel-resolves all IPs and connects to whichever answers first, removing the DNS-cache delay that used to slow multi-subnet failovers. The exam typically tests MultiSubnetFailover=True as the right answer when a scenario describes slow client reconnect after a cross-zone or cross-region failover.
A distributed availability group (distributed AG) spans two separate AGs on two separate WSFCs, each with its own primary and secondaries. It is the pattern for scaling an AG across regions or for migrations that cannot tolerate a single-cluster span. The distributed AG does not require shared storage between the two clusters; each AG replicates independently, and the distributed AG forwards log blocks from the upstream AG primary to the downstream AG primary. Because the downstream AG has its own WSFC, you avoid the cluster-node-count and latency limits of one large cluster. The tradeoff: distributed AGs do not support automatic failover of the distributed AG itself - you manually fail over the downstream AG, then optionally reverse the distributed AG direction.
AG on SQL Managed Instance
On SQL Managed Instance , the platform manages the AG plumbing for you. Two built-in constructs sit on top of AG technology:
Business Critical tier replicas : the Business Critical service tier runs an Always On availability group with replicas on local SSD, one of which is a readable secondary you reach with ApplicationIntent=ReadOnly . You do not configure the cluster, the listener, or the load balancer - the service does that.
Auto-failover groups : a managed-instance-level feature (also available for Azure SQL Database) that uses AG replication between instances in two regions and presents a single read-write listener endpoint plus a read-only listener endpoint. The read-write listener stays stable across failover, so applications reconnect without connection-string changes. Auto-failover groups support a grace period (e.g., 1 hour) that delays failover on transient outages, and they can fail over a single database or all databases on the instance.
MI Link and MI-to-MI Distributed AGs
MI link is the feature that lets a SQL Server 2022+ instance replicate one or more databases into SQL Managed Instance using AG technology, supporting hybrid migrations and ongoing replication. The on-premises server acts as the AG primary and the managed instance hosts the secondary; you can later cut over by failing over and updating application connection strings. For MI-to-MI replication, distributed AGs span the AG of one MI to the AG of another MI, useful for multi-region BC configurations. Prerequisites to keep in mind: both instances must be in different subnets with the correct VNet peering, DNS zones must resolve each instance's FQDN, and port 5022 (or the configured endpoint port) must be allowed between the two VNets.