9.3 Relational Data High Availability Design

Why This Topic Matters

Domain 3's high-availability subgroup explicitly names relational data as its own tested objective, and Domain 2 (Data Storage) already asked you to pick the right database service — this section asks the follow-up design question AZ-305 loves: once you've chosen Azure SQL Database or SQL Managed Instance, how do you architect it to survive a node failure, a zone outage, and a full regional outage, each with a different acceptable amount of downtime and data loss? Scenario questions routinely give you an RTO and RPO number and expect you to match it to the cheapest feature that satisfies both.

Core Terms and Rules

Local high availability (default, every tier). Every Azure SQL Database and SQL Managed Instance already runs on an availability architecture with zero RPO for local hardware/software failures — this is a built-in guarantee, not something you configure. The two purchasing-model families implement it differently:

• General Purpose (remote storage model): a stateless compute node plus data/log files held in Azure Blob Storage. On failure, Azure Service Fabric moves the database engine process to a new compute node; storage is untouched.
• Business Critical / Premium (local storage model): compute and storage are co-located per node, and high availability is implemented using technology similar to SQL Server Always On availability groups — a cluster with a single primary replica (read-write) and up to 3 secondary replicas that continuously receive committed changes. If the primary fails, Service Fabric fails over to a synchronized secondary automatically, and a new secondary is created to restore the cluster's replica count.

Read Scale-Out is a free byproduct of the Business Critical/Premium/Hyperscale replica architecture: you can redirect read-only connections (ApplicationIntent=ReadOnly) to a secondary replica, getting up to 100% extra read compute at no additional charge.

Zone redundancy upgrades local HA to survive an entire datacenter (zone) outage by placing the primary/secondary replicas (or, for General Purpose, the ZRS-backed data/log files) across 2 or 3 Availability Zones in the same region — Azure chooses the number of zones automatically, and all zone configurations carry the same SLA. It's supported on General Purpose, Business Critical, and Hyperscale (vCore) and on Premium (DTU); Basic and Standard tiers do not support it.

Auto-failover groups (FOGs) add cross-region disaster recovery on top of either configuration. A FOG wraps active geo-replication in a declarative abstraction with two DNS endpoints that never change across a failover:

• Read-write listener — <fog-name>.database.windows.net, always points at the current primary.
• Read-only listener — <fog-name>.secondary.database.windows.net, always points at the current secondary.

Both listener DNS records have a 30-second TTL. FOGs support two failover policies: Customer-managed (manual, recommended — you decide when to fail over, e.g. during a detected outage) and Microsoft-managed (automatic, only triggered after a configurable grace period of at least 1 hour, and only for a region-wide event — never for a single failover group in isolation). A forced failover (used during a real outage) can lose recent asynchronously-replicated writes; a planned failover fully synchronizes first and loses nothing but takes longer.

RTO/RPO Reference Table

SQL Managed Instance follows the same conceptual model as Business Critical: the General Purpose tier uses the remote storage model, and the Business Critical tier uses the local storage / Always-On-style replica cluster, with its own failover-group feature for cross-region DR.

Exam Scenarios

Scenario 1: A finance application requires an RPO of 0 and tolerance for at most 30 seconds of downtime if a single datacenter fails, but has no cross-region requirement. Design decision: enable zone redundancy on a Business Critical (or General Purpose) database — no failover group needed, and no extra cost for the zone-redundant configuration itself.

Scenario 2: The same application's leadership later adds a requirement to survive a full regional outage, tolerating up to 60 seconds of RTO. Design decision: add an auto-failover group with a customer-managed failover policy pointing at a secondary logical server in a paired region.

Scenario 3: A candidate assumes enabling a failover group automatically makes the secondary zone-redundant if the primary is. Trap: for non-Hyperscale databases, secondaries created through a failover group do not inherit zone redundancy by default — you must enable it separately on the secondary after the group is created. Only Hyperscale secondaries inherit the primary's zone-redundancy setting automatically.

Takeaways

• Memorize the three-tier RTO/RPO ladder: zone redundancy (<30s / RPO 0) → failover groups (<60s / RPO ≥0) → geo-restore (minutes-hours / minutes-hours) — AZ-305 scenario questions are frequently just this table in disguise.
• "Always On availability groups"-style replicas (primary + up to 3 secondaries) belong to Business Critical/Premium, not General Purpose — know which tier you're being asked about before recommending a fix.
• Read-write and read-only listener DNS names never change after failover — this is the single most exam-relevant fact about failover groups, since it's what lets an application avoid a connection-string change during DR.
• Customer-managed failover policy is the default recommendation; Microsoft-managed exists only for customers willing to delegate DR entirely and tolerate 1+ hour of downtime.