15.1 Always On Availability Groups on SQL Managed Instance and Azure VMs

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:

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:

1. 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.
2. The Always On availability groups feature enabled on each SQL Server instance (ALTER SERVER CONFIGURATION SET HADR ON or the SSMS server properties page).
3. An endpoint on each replica for log replication (typically port 5022) and a matching Azure Network Security Group rule permitting traffic between the nodes.
4. 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.

AG Limits, Readable Secondaries, and Read-Only Routing

Hard limits to remember for the exam:

• One primary replica at a time; up to eight secondary replicas per AG (SQL Server 2019 and later; earlier versions support fewer).
• Enterprise Edition is required for readable secondaries on SQL Server VMs. Standard Edition supports AGs but the secondaries are not readable and you are limited to one primary and one secondary per AG.
• The maximum number of availability databases per AG is 100 (one AG can protect many databases at once).
• Cross-database transactions are not supported through DTC with AGs unless DTC is coordinated; an exam scenario describing cross-database atomicity requirements may point to an FCI instead.
• Readable secondaries stay readable whether the AG is in synchronous or asynchronous commit mode, but the data on an asynchronous secondary lags behind the primary by the replication latency, so reporting queries may see near-but-not-current data.

Read-only routing directs read-only connections (those with ApplicationIntent=ReadOnly) to a readable secondary instead of the primary. You configure it by defining a read-only routing list on the primary (ALTER AVAILABILITY GROUP ... MODIFY REPLICA ON ... WITH (READ_ONLY_ROUTING_LIST = [...])) and a read-only routing URL on each secondary (the listener URL the replica will accept on). When the listener receives a read-only connection, it consults the routing list and sends the client to the first available readable secondary. A scenario describing OLTP performance degradation from reporting workloads on the primary - where the team already runs AGs but forgot to configure routing - points to enabling read-only routing rather than scaling up the primary.

Configuring an AG via SSMS, T-SQL, and the Wizard

The fastest path is the New Availability Group Wizard in SSMS, which validates prerequisites, lets you pick databases, choose replicas, set availability and failover mode, optionally create the listener, and generate a T-SQL script. For repeatable infrastructure-as-code deployments, use T-SQL:

CREATE AVAILABILITY GROUP [AG_Sales]
  FOR DATABASE [SalesDB]
  REPLICA ON N'NODE1'\SQLPROD
    (ENDPOINT_URL = 'TCP://NODE1.corp.local:5022',
     AVAILABILITY_MODE = SYNCHRONOUS_COMMIT,
     FAILOVER_MODE = AUTOMATIC,
     SECONDARY_ROLE (ALLOW_CONNECTIONS = READ_ONLY)),
  N'NODE2'\SQLPROD
    (ENDPOINT_URL = 'TCP://NODE2.corp.local:5022',
     AVAILABILITY_MODE = SYNCHRONOUS_COMMIT,
     FAILOVER_MODE = AUTOMATIC,
     SECONDARY_ROLE (ALLOW_CONNECTIONS = READ_ONLY));

Join secondaries with ALTER AVAILABILITY GROUP [AG_Sales] JOIN, then create the listener with ALTER AVAILABILITY GROUP [AG_Sales] ADD LISTENER 'AG_Sales_LSN' (WITH IP (('10.10.0.10','255.255.255.0')), PORT = 1433);. The same operations are exposed through PowerShell (New-SqlAvailabilityGroup, New-SqlAvailabilityReplica, New-SqlAvailabilityGroupListener) and Azure CLI / ARM / Bicep for VM-based deployments.

Monitoring AG Health with DMVs and the Dashboard

Two DMVs do most of the work:

• sys.dm_hadr_availability_replica_states exposes per-replica health: is_local, role (1 primary, 2 secondary, 3 resolver), operational_state, connected_state_desc (CONNECTED/DISCONNECTED), last_connect_error_description, and synchronization_health. When a replica shows connected_state_desc = DISCONNECTED and synchronization_health = NOT_HEALTHY, you have a replication break.
• sys.dm_hadr_database_replica_states shows per-database movement: log_send_queue_size (KB waiting to send), log_send_rate, redo_queue_size (KB waiting to be redone on the secondary), redo_rate, and synchronization_state_desc (SYNCHRONIZED when synchronous commit is caught up).

The Always On Availability Group Dashboard (right-click the AG in SSMS) surfaces a GUI version of these DMVs, including per-replica send and redo queues, and the Always On health model policies evaluate rules like 'replica is connected', 'data synchronization is healthy', and 'failover readiness' to produce a rollup health state. Configure alerts on these policies through the WSFC Failover Cluster Manager or SQL Server Agent to fire on AG state changes. For a scenario describing a lagging secondary, the right diagnostic is the redo queue size on the secondary: if redo_queue_size is growing while log_send_queue_size is near zero, the secondary is receiving logs but cannot keep up with redo (often because of a blocking read workload or under-provisioned compute), not because of network send issues.