Recommend a High Availability Solution for Relational Data — Lesson

A logistics platform takes orders all day from 12 countries and balances its books overnight. One Tuesday at $03{:}14$ Central European Time, the primary Azure SQL DB instance for the European order ledger becomes unreachable for nine minutes. Replicas in the same zone are unreachable too — the entire zone is offline. The CTO learns about it from a dashboard nine minutes after the event ended, because the database failed over automatically and no customer saw an outage. The architect who designed that database six months earlier had checked a single box: Zone Redundant. This lesson is about checking that box deliberately, on the right service, in the right tier, with the right replication topology.

We will work through Azure's high availability story for relational data the way AZ-305 expects you to: distinguishing in-region HA from cross-region failover, choosing between Azure SQL DB Business Critical, Hyperscale, and SQL Managed Instance, configuring Failover Groups, and understanding when AlwaysOn Availability Groups on IaaS VMs is still the right answer. Reference: the AZ-305 exam study guide, particularly the modules on database HA, Failover Groups, and Azure Database for PostgreSQL / MySQL Flexible Server.

Why This Matters

Databases are the single hardest piece of an architecture to make highly available because data has gravity and consistency. A web server that crashes can be replaced in seconds; a database that loses a transaction in flight can take a business down for days. Microsoft's tier-by-tier SLAs reflect that: a General Purpose Azure SQL DB lists 99.99%, Business Critical lists 99.995%, and Business Critical zone-redundant lists 99.995% with $RTO < 30$ seconds across a zone failure. The headline matters less than the underlying replication topology — what kind of failure does each tier actually survive?

This LO is also where most exam mistakes happen. Candidates confuse Failover Groups (cross-region) with auto-failover within a Business Critical tier (intra-region). They forget that Hyperscale only recently gained zone redundancy and named replicas. They pick AlwaysOn AG on Windows VMs when a PaaS service would have been simpler and cheaper. If you can confidently match a workload — banking ledger, multi-region e-commerce, on-prem migration, dev/test — to the right relational HA pattern, you will pass this slice of the exam and design data HA like a senior architect. The financial stakes are concrete: a 4-hour outage of a tier-1 OLTP database can cost the business between $$50{,}000$ and $$2{,}000{,}000$ depending on industry.

Prerequisites

Before working through this lesson, make sure you can answer each prompt below in one or two sentences.

• Azure SQL service flavours. Can you list the three: Azure SQL Database (single + elastic pool), SQL Managed Instance, SQL Server on Azure VMs? — Self-check: which one supports CLR and SQL Agent without restrictions?
• Service tiers within Azure SQL DB. Do you know General Purpose, Business Critical, Hyperscale at a sentence each? — Self-check: which one uses local SSD plus three secondary replicas?
• Replication terms. Are you fluent with synchronous vs asynchronous replication and the resulting RPO implications? — Self-check: which gives a non-zero RPO?
• Region pairs and zone redundancy. Can you explain what a region pair is and how zone redundancy differs from geo-replication? — Self-check: which protects against a regional outage?
• Composite SLA arithmetic. Can you multiply two SLAs? — Self-check: a 99.995% database plus a 99.99% application yields a composite of what?

If these feel shaky, pause and review the relational data and HA modules from Unit 2 and Unit 3 of the AZ-305 guide before continuing.

Learning Objectives

By the end of this lesson, you will be able to:

1. Analyse a relational workload's HA requirements (RPO, RTO, blast radius, regulatory geography) and translate them into a database HA pattern.
2. Evaluate the trade-offs between Azure SQL DB Business Critical zone-redundant, Hyperscale, SQL MI Business Critical, and AlwaysOn AG on Azure VMs.
3. Design an Azure SQL DB topology with Failover Groups that meets a cross-region RPO commitment without sacrificing read scale.
4. Recommend an HA configuration for Azure Database for PostgreSQL Flexible Server (zone-redundant vs same-zone) based on workload characteristics.
5. Recognise common topology traps — single-region BC, Failover Group without read endpoint, mismatched tier across primary/secondary — that silently violate the HA design.
6. Compute the recovery objectives (RPO, RTO) of a topology and defend them with the underlying replication mechanism.

Building Blocks

Read this section as a glossary. Each term follows the same shape: an everyday analogy, a formal definition, then the reason it matters for the exam.

RPO (Recovery Point Objective) — How much data, measured in time, you are willing to lose. Think of it as "how recent must the last transaction be?". Formally, the maximum acceptable age of data at recovery time. Synchronous replication gives RPO $= 0$; asynchronous gives RPO $> 0$. It matters because tier choice maps directly to RPO — Business Critical synchronous replicas give RPO $= 0$ within a region; geo-replication gives RPO $\sim 5$ seconds.

RTO (Recovery Time Objective) — How long the workload can be unavailable. Think of it as "how fast do we need to be back?". Formally, the maximum acceptable time between failure and full service restoration. Business Critical zone-redundant typically delivers RTO $< 30$ seconds; Failover Group cross-region typically delivers RTO $\sim 60$ seconds; manual restore from backup can be hours.

Zone-redundant configuration (Business Critical) — Three synchronous secondary replicas, each placed in a different availability zone. Formally, the Business Critical tier (and Hyperscale named replicas) can be deployed zoneRedundant: true, spreading replicas across zones 1, 2, 3. SLA: 99.995%. It matters because this is the single configuration setting that converts a regional service into a multi-zone one.

Failover Group — A named pairing between a primary and a secondary database (or MI instance) in a different region. Formally, a Failover Group is a logical object that manages geo-replication and provides a single read-write listener (<group>.database.windows.net) and an optional read-only listener (<group>.secondary.database.windows.net). RPO: $\le 5$ seconds; RTO: $\le 60$ seconds. It matters because it is the only PaaS-native way to fail over a SQL DB workload across regions without changing connection strings.

Geo-replication (active geo-replication) — Up to 4 readable secondaries of an Azure SQL DB, in any region. Formally, asynchronous transactional replication of database changes to standby copies that can be promoted to primary. Mostly superseded by Failover Groups, which wrap geo-replication with a listener. It matters because the exam still tests scenarios where a single secondary in a third region is needed for analytics workloads alongside the failover-group pair.

Hyperscale named replica — A separately addressable read replica of a Hyperscale database. Formally, a child resource of a Hyperscale database that shares the same page server tier and provides isolated compute for read workloads. Up to 30 per primary. It matters because Hyperscale's HA story is different from Business Critical — replicas are read-only and asynchronous, so RPO is not zero.

SQL Managed Instance Business Critical — The MI equivalent of Business Critical for Azure SQL DB. Formally, an MI deployed in the Business Critical service tier with 3 synchronous secondary replicas. Can be deployed zone-redundant in supported regions. SLA: 99.99% (zone-redundant). It matters because MI is the right choice for lift-and-shift workloads using cross-database queries, SQL Agent jobs, CLR, and Service Broker.

AlwaysOn Availability Group (AG) — SQL Server's built-in HA primitive, running on Azure VMs. Formally, a clustered group of one primary and up to 8 secondary replicas, with synchronous or asynchronous commit modes. Requires a Windows Server Failover Cluster (WSFC) and a listener. RPO and RTO depend on commit mode and cluster quorum design. It matters because some customers cannot migrate off this pattern (third-party software lock-in, SQL Agent jobs with extensions, complex CLR), so the AZ-305 exam tests it as the IaaS fallback.

Postgres / MySQL Flexible Server HA modes — Same-Zone HA colocates the standby; Zone-Redundant HA places the standby in a different zone. Formally, both modes use synchronous replication and automatic failover. It matters because zone-redundant has slightly higher latency (the synchronous write has to cross zones) but survives a full zone outage; same-zone has lower latency but only survives a single-node failure.

Deep Dive

1. Azure SQL DB tier and the HA topology each tier ships with

Choosing a tier is choosing an HA pattern. Each tier has a different underlying replication topology and that drives both the SLA and the failure modes the tier survives.

[!TIP] Pick Business Critical when the workload needs RPO $= 0$ within a region. Pick Hyperscale when the database is over 4 TB or you need fast point-in-time restore on a huge database. Pick General Purpose for cost-sensitive workloads where RPO seconds are acceptable.

[!WARNING] A Hyperscale named replica is asynchronous — promoting it after a primary failure can mean a small (typically sub-second) data loss. If you require RPO $= 0$ with Hyperscale, use it for read scale only and pair it with a separate Failover Group for cross-region DR.

2. Failover Groups — cross-region resilience without rewriting clients

Failover Groups are the single most important construct in this LO. They sit on top of geo-replication and add a stable DNS listener that lets clients keep one connection string across failover events.

The read-write listener routes to whichever database is the current primary; the read-only listener routes to the secondary. After a planned or unplanned failover, the listener resolves to the new primary automatically — clients keep the same connection string. The cost is asynchronous replication: typical RPO is well under 5 seconds in practice but is contractually $\le 5$ seconds.

[!IMPORTANT] Set failoverWithDataLossGracePeriodMinutes deliberately. The default of 60 minutes means the automatic failover will wait an hour before forcing a fail-over with possible data loss when the primary is unreachable. Lower it for workloads with tight RTOs; raise it for workloads where every transaction must be replicated before failing over.

3. SQL Managed Instance — the lift-and-shift HA story

Before stepping into MI specifics, it helps to anchor the three Azure SQL flavours and what HA each one ships with.

SQL MI exists for the workloads that need an instance-scoped SQL Server (with SQL Agent, CLR, cross-database transactions, Service Broker, and database mail) but want PaaS HA, patching, and backup. Like Azure SQL DB, MI offers General Purpose and Business Critical tiers; Business Critical has the same 1-primary-plus-3-synchronous-replicas topology and supports zone redundancy in a growing list of regions.

For cross-region DR, MI now offers two distinct mechanisms: MI auto-failover groups (analogous to Azure SQL DB's) and MI link (a near-real-time replica between an on-prem SQL Server instance and Azure MI, originally designed for migration but valuable for hybrid DR). Choose Failover Groups for two MI instances in different Azure regions; choose MI link when the secondary is on-prem.

4. Postgres and MySQL Flexible Server — zone-redundant vs same-zone

Azure Database for PostgreSQL and MySQL (Flexible Server) deliver HA differently from Azure SQL DB. There is no "Business Critical" tier; instead, HA is a flag with two modes.

Synchronous replication in both HA modes means RPO is zero, but the network hop in zone-redundant mode adds a few milliseconds to every commit. For chatty OLTP workloads with very small transactions and very tight latency budgets, that is occasionally a reason to pick same-zone — but only for workloads that genuinely tolerate losing the AZ.

5. AlwaysOn AG on Azure VMs — the IaaS fallback

When migrating an existing SQL Server AG on-prem and the customer cannot adopt PaaS, you build an AG on Azure VMs. The pattern is unchanged from on-prem: a Windows Server Failover Cluster spans the VMs, an AG replicates between them, and an Azure Internal Load Balancer provides the listener IP. The catch is that the cluster requires careful zone-aware placement of all components.

[!NOTE] The AG listener on Azure VMs must be implemented behind an Internal Load Balancer because Azure does not support gratuitous ARP. Forgetting to configure the ILB is the single most common exam-style mistake on the IaaS HA path.

6. RPO / RTO matrix — what each pattern actually delivers

Worked Examples

Easy — pick the right tier

Problem. Fabrikam runs an order ledger on Azure SQL DB. The workload is 200 GB, mostly OLTP, with an RPO of 0 within the region. Cost is moderately important. Which tier is right?

Solution. Business Critical is the only tier that delivers RPO $= 0$ inside a single region (synchronous replicas), and at 200 GB the database fits comfortably under the Hyperscale break-even threshold. Choose Business Critical zone-redundant to also survive a zone failure. Cost is moderate but justified: BC ZR is $1.$7{-}2\times$$ the cost of General Purpose for the same vCore count.

[!NOTE] If RPO had allowed a few seconds, General Purpose zone-redundant would have been a cheaper answer; the storage is durable across zone failures even without sync replicas.

Medium — cross-region failover with read scale

Problem. A globally distributed e-commerce app needs a primary Azure SQL DB in West Europe with a hot standby in North Europe. The BI team also runs read-heavy queries against the database; those queries should not impact the primary. Design the HA topology.

Solution. Use a Failover Group between West Europe and North Europe. Configure the read-write listener for transactional traffic and enable the read-only listener on the secondary for BI workloads. This gives a cross-region RPO of $\le 5$ s and RTO of $\le 60$ s while offloading read traffic from the primary. Each database should be deployed as Business Critical zone-redundant so each region also survives a zone failure.

[!TIP] Add customers and inventory databases to the same failover group only if they need to fail over together. If they have independent recovery requirements, give each its own failover group to avoid a single primary loss cascading more than necessary.

Hard — Hyperscale plus cross-region DR

Problem. A 6 TB analytics-heavy database currently runs in Business Critical and is hitting tier limits at 4 TB. The architect wants to migrate to Hyperscale but the workload also needs cross-region DR with an RPO of $\le 60$ seconds and RTO of $\le 5$ minutes. Design the topology.

Solution. Migrate the database to Hyperscale and configure a Failover Group to a paired-region Hyperscale secondary. Hyperscale geo-replication is asynchronous, with measured RPO typically well under 60 seconds in steady state. For read scale, add up to 4 Hyperscale named replicas in the primary region, deployed across zones for additional zone fault isolation. Verify that all clients can tolerate a brief (~1 minute) interruption during a planned failover; if not, add a retry policy.

[!NOTE] Hyperscale's named replicas are zone-redundant but asynchronous; do not advertise an in-region RPO of 0 for Hyperscale-only deployments. If RPO $= 0$ is required, the workload belongs in Business Critical.

Visual Explanations

Figure 1 — Decision flow for a relational HA recommendation

Notice the structure: the first decision is about in-region RPO; the second is about size; the third is about cross-region need. Walking this tree avoids the most common architect error — recommending Hyperscale for a small workload that does not need it.

Figure 2 — Synchronous replication topology of Business Critical zone-redundant

The primary in zone 1 commits every transaction synchronously to replicas in zones 2 and 3. Quorum requires 2 of the 3 acknowledgments, so the loss of any single zone still allows the system to commit. Failover takes seconds; the listener address does not change.

Figure 3 — Pattern selector for relational HA

Common Mistakes

❌ Myth: Business Critical automatically protects against a region outage. ✅ Reality: Business Critical is an in-region HA tier. Zone-redundant deployment protects against a zone outage. Cross-region protection requires a Failover Group to a paired-region secondary. Why it's tricky: Customers see "Business Critical" and assume "critical-grade resilience everywhere". The tier name does not describe geography.

❌ Myth: A Failover Group is the same as active geo-replication. ✅ Reality: A Failover Group builds on geo-replication and adds a stable listener and automatic policy. You can still create raw geo-replicas without a failover group for more flexible read-scale patterns. Why it's tricky: Both produce a readable secondary; only the failover group gives clients a stable DNS endpoint that survives failover.

❌ Myth: Hyperscale named replicas give RPO $= 0$ for read scale. ✅ Reality: Named replicas are asynchronous. They can lag the primary by a small but non-zero amount. They are excellent for read scale but they cannot be promoted with strict zero data loss in a planned failover. Why it's tricky: The official docs describe Hyperscale's storage as "zero data loss" durable, which is true at the storage layer — but the compute replica catching up is async.

❌ Myth: Failover Group and MI link are the same thing. ✅ Reality: Failover Group pairs two Azure MI instances in different Azure regions. MI link pairs an on-prem SQL Server with an Azure MI for hybrid scenarios (migration or hybrid DR). They are not interchangeable. Why it's tricky: Both produce a "secondary copy" of a database, but the secondary is in different places.

Practice Exercises

🟢 Exercise 1. A workload runs on Azure SQL DB General Purpose (not zone-redundant) in East US. Zone 2 in East US fails. What happens to the workload?

▶💡 Hint

GP has durable storage and stateless compute, but the compute node is in a single zone unless ZR is enabled.

▶✅ Solution

If the compute node is in zone 2, the workload is briefly unavailable while Azure restarts the compute node on hardware in another zone. The storage tier remains accessible because it is regional Premium storage. Total downtime is typically $1{-}2$ minutes. With zoneRedundant: true, the impact would have been a few seconds rather than $1{-}2$ minutes.

🟡 Exercise 2. A team configures a Failover Group with readOnlyEndpoint: Disabled. The BI team complains they can no longer query the secondary. What happened?

▶💡 Hint

The read-only listener is what BI clients connect to.

▶✅ Solution

With readOnlyEndpoint: Disabled, the `fg-

.secondary.database.windows.net` listener returns an error to clients. The secondary database is still being replicated and is readable directly via its server name, but clients lose the failover-resilient routing. Re-enable the read-only endpoint to restore BI access without changing the BI team's connection strings.

🟡 Exercise 3. Two Azure SQL DB instances are paired in a Failover Group with failoverWithDataLossGracePeriodMinutes: 5. The primary region is unreachable for 8 minutes due to an upstream network issue. What is the outcome?

▶💡 Hint

The grace period is the max time the system will wait before forcing a failover.

▶✅ Solution

After 5 minutes of unreachability, the failover group initiates an automatic failover with possible data loss. The secondary becomes the new primary. The unreplicated transactions in the last $\le 5$ seconds before the outage are lost. When the original primary recovers, it becomes the new secondary. Total RTO is $\sim 5$ minutes plus the failover transition ($\sim 30$ s).

🔴 Exercise 4. A customer needs a 6 TB OLTP database with 99.995% in-region SLA and RPO $\le 5$ seconds across regions. They are evaluating Hyperscale and Business Critical. Recommend a topology.

▶💡 Hint

Business Critical tops out around 4 TB; Hyperscale handles the size but is async in-region.

▶✅ Solution

At 6 TB, Business Critical is not an option. Recommend Hyperscale zone-redundant (achieves 99.99% in-region with zone protection) and pair it with a Failover Group to a Hyperscale secondary in the paired region for cross-region DR. The in-region RPO is effectively 0 for storage failures (page servers are durable) but is not contractually 0 for compute failures. If the customer truly requires RPO $= 0$ for compute, the right answer is to split the workload across two BC databases under 4 TB each.

🔴 Exercise 5. A migration architect is moving an on-prem SQL Server AlwaysOn AG workload to Azure. The workload uses cross-database transactions, SQL Agent, and CLR. Recommend the Azure target.

▶💡 Hint

Cross-database transactions and SQL Agent steer you toward Managed Instance, not single Azure SQL DBs.

▶✅ Solution

Migrate to SQL Managed Instance Business Critical zone-redundant. MI supports cross-database queries, SQL Agent jobs, CLR, and Service Broker — features that single Azure SQL DB does not support. Business Critical ZR gives the same 3-synchronous-replica HA topology as the on-prem AG, with PaaS patching and backup. If cross-region DR is also needed, add an MI Failover Group.

🟢 Exercise 6. True or false: a Postgres Flexible Server with Zone-Redundant HA has the same write latency as Same-Zone HA.

▶💡 Hint

Both modes are synchronous; the difference is the network distance between primary and standby.

▶✅ Solution

False. Both modes are synchronous, so commits wait for an acknowledgement from the standby. In Same-Zone HA, the standby is on the same physical fabric (typically $< 1$ ms round trip). In Zone-Redundant HA, the standby is in a different AZ ($1{-}2$ ms round trip). Most workloads do not notice the difference; chatty OLTP workloads with very short transactions sometimes do.

🟡 Exercise 7. Design a Bicep resource for a Postgres Flexible Server with zone-redundant HA in West Europe, 4 vCores, 128 GB storage, retention 35 days.

▶💡 Hint

Look at the highAvailability block — mode is ZoneRedundant and standbyAvailabilityZone is explicit.

▶✅ Solution

bicepCopy

resource pg 'Microsoft.DBforPostgreSQL/flexibleServers@2023-06-01-preview' = { name: 'pg-prod-weu' location: 'westeurope' sku: { name: 'Standard_D4ds_v4', tier: 'GeneralPurpose' } properties: { version: '15' storage: { storageSizeGB: 128 } backup: { backupRetentionDays: 35, geoRedundantBackup: 'Enabled' } highAvailability: { mode: 'ZoneRedundant', standbyAvailabilityZone: '2' } availabilityZone: '1' } }

The availabilityZone and standbyAvailabilityZone should differ. Enable geoRedundantBackup to also keep a paired-region backup copy for ransomware recovery beyond zone HA.

Summary & Concept Map

The headline takeaways from this lesson:

• Tier choice is HA topology choice. Business Critical ships with 3 synchronous replicas, zone-redundant when configured. Hyperscale ships with async named replicas. General Purpose ships with durable remote storage and stateless compute.
• Failover Groups are the only PaaS-native cross-region pattern that keeps the connection string stable. Use them whenever a workload must survive a regional outage.
• SQL Managed Instance is the right answer for lift-and-shift — cross-database transactions, SQL Agent, CLR, Service Broker need MI, not single Azure SQL DB.
• Postgres / MySQL Flexible Server HA is a flag, not a tier. Pick Zone-Redundant HA unless the workload is exceptionally latency-sensitive.
• AlwaysOn AG on Azure VMs is the IaaS fallback for SQL Server workloads that cannot migrate to PaaS. Remember the Internal Load Balancer for the listener.
• Hyperscale named replicas are async — they are excellent for read scale but do not deliver in-region RPO $= 0$ for the workload as a whole.

The concept map links the workload's recovery objectives to the patterns that meet them, then chains each pattern to the cross-region question. Walk this tree top-down during the exam: start with RPO, narrow to a tier, then ask the cross-region question last.