Domain 5 of 5 · Chapter 1 of 3

Plan an HA/DR strategy for database solutions

Downtime and data loss are two separate targets

Picture two recovery options for one order-processing database. The first restores last night's full backup, and because restoring from a backup usually involves losing the changes made after it was taken, it can cost up to a day of orders even if the restore itself is quick (What are business continuity, high availability, and disaster recovery?[1]). The second fails over to a replica that is seconds behind, yet the application still needs hours to reconnect, and Microsoft measures recovery time until the application fully recovers (Business continuity in Azure SQL Database[2]). Each option misses a different target.

The recovery point objective (RPO) is the maximum duration of acceptable data loss in a disaster, and the recovery time objective (RTO) is the maximum duration of acceptable downtime, where you define what counts as downtime; both are measured in units of time, such as "30 minutes of data" or "eight hours of downtime" (Disaster recovery requirements[1]). Each component or flow of a workload can have its own RPO and RTO.

The two targets constrain different parts of the recovery path:

  • The RPO constrains how current the recoverable copy is. With backups, Microsoft says the workload RPO must align with the backup interval (Backups[1]).
  • The RTO constrains how long the recovery path takes. Backup restoration often takes time, so the RTO must account for the time a restore takes, and a failover also needs time to detect the failed primary and switch to the secondary, which the RTO must cover as well (Failover and failback[1]).

A shorter backup interval therefore improves the RPO without making a slow restore any faster, and a database that is promoted while the application still can't reach it hasn't met the RTO. Microsoft also cautions that an RTO and RPO of zero are difficult and costly to implement, so technical and business stakeholders agree on realistic targets together (Disaster recovery requirements[1]).

The figure below places both targets on one timeline: the RPO reaches back from the disaster to the last recoverable data, and the RTO reaches forward to restored application service.

This topic covers recommending, evaluating and testing an HA/DR design: the Microsoft DP-300 study guide[3] lists 'Recommend HA/DR strategy based on Recovery Point Objective/Recovery Time Objective (RPO/RTO) requirements' through 'Plan a testing procedure for an HA/DR solution' under Plan an HA/DR strategy for database solutions. Planning backups for recovery is a different task: the study guide lists 'Recommend a database backup and restore strategy' under Plan and perform backup and restore of a database. Building and troubleshooting the chosen features is another: it lists 'Configure failover groups' and 'Troubleshoot an HA/DR solution' under Configure HA/DR for database solutions.

Judge every option twice: once for the data it can lose, and once for the time it takes to restore service.

Last recoverable dataRPO: data lostDisaster or outageRTO: downtimeApplication service restored
Recovery point objective and recovery time objective on one disaster timeline, modeled on Microsoft's reliability guidance

Four disruption scenarios set the scope of protection

Before comparing features, decide which failures the design must survive, because each protection covers only the scope its copies span. Microsoft separates the two halves of a plan by likelihood: high availability (HA) keeps a workload running through common, expected failures such as transient faults and restarts, while disaster recovery (DR) plans for uncommon, catastrophic events (Risk classification[1]). The split depends on the design: a full region outage is normally a DR risk, but a workload running active-active across regions with automatic region failover treats it as an HA risk (Risk classification[1]).

Microsoft's business continuity guidance for Azure SQL Database lists four major disruption scenarios from a database perspective (Features that provide business continuity[2]):

  1. Local hardware or software failure affecting the database node.
  2. Datacenter or availability zone outage, a rare event caused by a natural disaster, configuration change, software bug or hardware failure. An availability zone is a separated group of datacenters within an Azure region, with independent power, cooling and networking (What are availability zones?[4]).
  3. Regional outage affecting all the availability zones and datacenters of a region.
  4. Data corruption or deletion, typically caused by an application bug or human error, which is application-specific and typically can't be detected by the database service.

Microsoft's business continuity overview for Azure SQL Managed Instance lists the same four major disruption scenarios for an instance (Business continuity overview - Azure SQL Managed Instance[5]).

The first three scenarios are nested scopes: a database node sits in a datacenter or availability zone, and zones sit in a region. A copy on another node survives a node failure but not the loss of the datacenter both copies share, a copy in another zone survives a zone outage, and availability zones don't protect against a full-region outage (What are availability zones?[4]). The fourth scenario cuts across every scope. An Always On availability group (AG), the SQL Server feature introduced in the next section, sends each transaction to its replicas as it happens (Business continuity and database recovery - SQL Server[6]), so an accidental DELETE that commits on the primary reaches every synchronized copy.

The figure below draws the three nested scopes, with the fourth scenario beside them.

Name the largest scope the requirement must survive, and remember that logical damage needs a copy from before the error, not another live copy.

RegionDatacenter oravailability zoneDatabase nodeDatacorruptionor deletionreaches everysynchronizedcopy
The four disruption scenarios from Microsoft's Azure SQL business continuity guidance: three nested failure scopes and data corruption beside them

Availability modes: synchronous versus asynchronous commit

The first design choice inside an availability group is how long the primary waits for each secondary, because that wait decides how much committed work a failover to that secondary can lose, which is the RPO the replica can deliver. An Always On availability group (AG) provides database-level protection by sending each transaction of a set of user databases to other SQL Server instances, called replicas, that hold copies of those databases; one primary replica has the read/write copy, and secondary replicas can't receive transactions directly from applications (Business continuity and database recovery - SQL Server[6]). Always On is the umbrella term for both AGs and failover cluster instances, not the name of the AG feature (Business continuity and database recovery - SQL Server[6]).

Each replica's availability mode is synchronous commit, asynchronous commit, or configuration only; a configuration-only replica holds no user data and applies only to AGs that aren't on a Windows Server Failover Cluster (WSFC) (Availability modes for an availability group[7]). The two data-bearing modes differ in one thing: whether the primary waits for the secondary to write incoming transaction log records to disk, called hardening the log.

  • Synchronous commit. The transaction waits to send its confirmation to the client until the secondary has hardened the log, so a synchronized secondary holds every committed transaction; this mode emphasizes high availability over performance, at the cost of increased transaction latency (Availability modes[7]). When all databases on a synchronous secondary are synchronized, it supports planned manual failover and, optionally, automatic failover.
  • Asynchronous commit. The primary sends the confirmation right after writing the log record locally, so it runs with minimum transaction latency and problems on the secondary never affect it; the secondary can lag, and the only failover it supports is a forced failover, which loses any log the original primary hadn't yet sent (Availability modes[7]).

Synchronous commit needs both sides: both the primary and the secondary must be configured for it, and a primary configured for asynchronous commit commits asynchronously to every secondary (Availability modes[7]). The protection also lasts only while the secondary is connected. If a synchronous secondary times out without confirming that it hardened the log (the session timeout defaults to 10 seconds), the primary marks it failed, stops waiting for confirmation, and marks its synchronization state NOT SYNCHRONIZING until it reconnects (Availability modes[7]). That fallback is the default: the AG setting REQUIRED_SYNCHRONIZED_SECONDARIES_TO_COMMIT defaults to 0, and a value of 1 or more makes the primary wait for that many synchronous secondary replicas to commit, so commits fail instead when they aren't available (ALTER AVAILABILITY GROUP[8]).

Distance decides the mode. Microsoft describes asynchronous commit as a DR solution that works well when replicas are distributed over considerable distances (Availability modes[7]), and for SQL Server on Azure VMs it advises assuming periods of high network latency between on-premises and Azure and using asynchronous commit for replicas deployed to Azure (Business continuity for SQL Server on Azure VMs[9]). Inside one region, availability zones are linked by a network with a round-trip latency target of less than approximately 2 milliseconds, which Microsoft says allows synchronous replication across zones (What are availability zones?[4]). Microsoft's own five-replica example follows that pattern, with synchronous replicas for failover and asynchronous replicas typically deployed in a DR site (Availability modes[7]).

The figure below traces one synchronous commit in the order the vendor documents it; under asynchronous commit, the primary skips the wait in step 3 and confirms the commit right after writing the log record, while the secondary hardens the log whenever it catches up.

While a connected, participating secondary remains synchronized, synchronous commit protects every acknowledged transaction for lossless failover at the price of the replica round trip on every commit; asynchronous commit removes that price and accepts that the most recent transactions can be lost.

1. Client sendsa transaction2. Primary writes the log record,sends it to the secondary3. Primary waits forthe secondary's confirmation4. Secondary hardensthe log, acknowledges5. Primary confirmsthe commit to the client
The five-step synchronous-commit sequence of an Always On availability group, as documented for SQL Server

Database-level versus instance-level protection

The second choice is what fails over: a set of databases, or the whole SQL Server instance. The two Always On features answer it differently, and the difference decides what an application finds after failover.

An availability group protects databases. Because it sends transactions to replicas as they happen, Microsoft recommends AGs where requirements call for lower RPO and RTO, and each replica keeps its own copy of the data (Business continuity and database recovery - SQL Server[6]). Applications connect through the AG's listener, which lets them reach the primary without needing to know which instance hosts it (Business continuity and database recovery - SQL Server[6]). A conventional AG, the only kind before SQL Server 2022 added the contained AG described next (What is a contained availability group?[10]), protects only database-level objects: anything not captured in the transaction log or configured in the database, such as instance-level logins, linked servers and SQL Server Agent jobs, must be synchronized manually for each secondary (Business continuity and database recovery - SQL Server[6]).

A contained availability group, available in SQL Server 2022 and later, adds its own master and msdb system databases to the group (in a contained AG named MyContainedAG, they are MyContainedAG_master and MyContainedAG_msdb), so users, logins, permissions and Agent jobs created while connected to the contained AG are still there after it fails over to another instance (What is a contained availability group?[10]). Microsoft notes that a contained AG keeps the execution environment consistent but isn't a security boundary (What is a contained availability group?[10]).

A failover cluster instance (FCI) protects the instance. When the underlying server has a problem, everything inside the instance moves to another node, including databases, SQL Server Agent jobs and linked servers, and applications connect to the FCI's name rather than to a node (Business continuity and database recovery - SQL Server[6]). Every FCI needs shared storage, and one node owns its resources at a time. An FCI loses no data, but because there is only one copy of the data, the shared storage is a single point of failure; a redundant copy needs an AG or log shipping on physically separate storage (Business continuity and database recovery - SQL Server[6]). Log shipping automatically sends transaction log backups from a primary database to one or more secondary databases on a separate server and applies them there (Log shipping[6]); What a hybrid DR site depends on below describes it in a hybrid design. After an FCI failover, the databases go through normal recovery before they're available, which Microsoft notes is generally longer than an AG failover (Business continuity and database recovery - SQL Server[6]).

The edition also limits the AG option. Standard edition provides a basic availability group, with two replicas (one primary and one secondary) and a single database. Enterprise edition allows multiple databases in one AG, up to nine replicas in total, up to five synchronous replicas including the primary in SQL Server 2019 and later (up to three in SQL Server 2017), readable secondary replicas and backups taken on a secondary replica (Business continuity and database recovery - SQL Server[6]).

Requirement Fits Why
Several application databases move together; other databases on the instance stay AG (Enterprise edition) An AG fails over only its own databases
Logins and Agent jobs must follow the application FCI, or a contained AG on SQL Server 2022 or later The FCI moves the instance; the contained AG replicates its own master and msdb
One database on Standard edition Basic availability group Two replicas and one database, with no readable secondary
Instance-level HA plus an independent copy of the data FCI combined with an AG or log shipping An FCI alone keeps one copy of the data on shared storage

The two features combine: an FCI can host the primary or a secondary replica of an AG, and on Azure VMs Microsoft describes that pairing as redundancy at both the database and the instance level (Business continuity for SQL Server on Azure VMs[9]).

Choose an AG when the requirement names databases and a separate copy of the data, an FCI when it names the instance, and remember that an FCI on its own keeps one copy of the data.

Readable secondaries for reporting

Readable secondaries put a replica to work, so it helps to know exactly what that replica can and can't be. In Enterprise edition, a readable secondary replica allows read-only access to all its secondary databases, in either synchronous-commit or asynchronous-commit mode, and in many circumstances its data trails the primary by only a few seconds (Offload read-only workload to a secondary replica[11]). Directing reports there conserves the primary's resources, but Microsoft advises running read workloads that can't tolerate latency on the primary (Offload read-only workload to a secondary replica[11]).

A readable secondary is a copy of the primary, not an independent reporting database. You can't make schema or data changes in the secondary databases, so an index that a report needs is created in the primary database and reaches the secondary through redo, the secondary's process of applying the log records it receives (Indexing[11]). That index becomes part of the primary's physical design. Statistics are the one exception: because the secondary can't create permanent statistics, it creates temporary statistics in tempdb, with the suffix _readonly_database_statistic (Statistics for read-only access databases[11]).

Reporting also costs the secondary something. Read-only queries consume CPU and I/O alongside the redo threads, and the schema stability (Sch-S) locks they take can block redo from applying DDL (data definition language) changes, such as dropping a table (Read-only workload impact[11]).

When a reporting copy needs only some rows or columns, transactional replication publishes selected tables to separate subscriber databases instead, as described in Transactional replication across sites below. A replica that serves reports also can't use the passive-replica licensing benefits described in Licensing passive DR replicas below, because those benefits require a replica without read workloads.

A readable secondary offloads reads to an exact, read-only copy whose indexes are designed on the primary.

Availability sets and availability zones for SQL Server VMs

On SQL Server on Azure VMs, the replicas of an AG and the nodes of an FCI are VMs, and where Azure places those VMs decides which disruption scenario the design survives. Azure offers two placement options, and a VM uses one or the other: availability sets and availability zones can't be combined (Describe Azure high availability and disaster recovery features[12]).

  • An availability set spreads VMs across fault domains, groups of VMs that share a common power source and network switch, and update domains, groups of VMs and underlying hardware that can be restarted at the same time; during planned maintenance, only one update domain is restarted at a time (Availability sets overview[13]). An availability set has up to 3 fault domains and 20 update domains, and two or more VMs in a set meet the 99.95% Azure service-level agreement (SLA). Availability sets remain susceptible to shared infrastructure failures such as datacenter-level network outages, which can affect multiple fault domains (Availability sets overview[13]).
  • Availability zones, the separated groups of datacenters defined in Four disruption scenarios set the scope of protection above, require you to deploy the participating SQL Server VMs explicitly across different zones. Spreading the SQL Server VMs across zones protects against datacenter failures, and Microsoft documents a 99.99% SLA for that placement (Business continuity for SQL Server on Azure VMs[9]).

A VM placed in a zone is a zonal resource, deployed to a single zone that you select. Azure doesn't make a zonal resource resilient on its own: you design separate resources in multiple zones, and you are responsible for failover to another zone, which is what an AG or FCI across zonal VMs provides (What are availability zones?[4]). A zone-redundant resource is the opposite case: the service replicates it across zones and manages the failover itself, which is how zone-redundant Azure SQL Database and Managed Instance work (see Zone redundancy for Azure SQL Database and Managed Instance below).

Zone numbers need one check across subscriptions. Each datacenter belongs to a physical zone, physical zones are mapped to logical zones in each subscription, and different subscriptions can have a different mapping, so "zone 1" in two subscriptions isn't necessarily the same physical zone (What are availability zones?[4]).

Neither placement covers the third scenario: availability zones don't protect against a full-region outage, so regional DR needs a replica in another region (What are availability zones?[4]).

An availability set limits hardware, power and network failures inside one datacenter, availability zones survive the loss of a datacenter, and only another region survives a regional outage.

VM-level redundancy is not SQL Server availability

Azure keeps VMs running, but a running VM doesn't mean SQL Server is serving queries. Built-in mechanisms such as service healing and failure recovery detection for VMs help protect the VM's availability, but they don't protect the availability of SQL Server inside it, and the SQL Server instance can fail while the VM is online and healthy (Business continuity for SQL Server on Azure VMs[9]). Availability sets and zones likewise don't protect against in-guest failures, such as an operating system or database engine crash, which is why Microsoft points to AGs or FCIs to meet RTOs and RPOs (Describe Azure high availability and disaster recovery features[12]).

Storage replication has a second gap. SQL Server relies on write-ahead logging (WAL), which guarantees that no data modification is written to disk before its log record (Transaction log architecture and management guide[14]). Geo-redundant storage (GRS) for Azure disks replicates each disk independently and asynchronously, which guarantees write order within one disk but not across several disks (Geo-replication support[9]). If a database keeps its data file and its log file on separate disks, the recovered disks after a disaster can hold a data file that is more current than the log file, which breaks SQL Server's write-ahead log and the ACID properties (atomicity, consistency, isolation and durability) of transactions (Geo-replication support[9]). If geo-replication can't be disabled on the storage account, keep all data and log files for a database on the same disk, or, when its size requires several disks, use one of the SQL Server DR solutions instead (Geo-replication support[9]).

Infrastructure redundancy protects the VM and its disks; database continuity needs a solution that understands SQL Server.

Azure Site Recovery for SQL Server VMs

Azure Site Recovery replicates whole machines, which makes it a DR option when the unit to recover is a server rather than a database. Site Recovery replicates workloads running on physical servers and VMs from a primary site to a secondary location, including Azure VMs between regions and on-premises VMware VMs, Hyper-V VMs and physical servers to Azure; after an outage you fail over to the secondary location, and you fail back once the primary site is running again (About Site Recovery[15]). For SQL Server, Microsoft lists it as a DR option in both Azure-only and hybrid designs (Replicate with Azure Site Recovery[9]).

Three capabilities matter most for a database plan (About Site Recovery[15]):

  • Application-consistent recovery points. Replication can use snapshots that capture disk data, all data in memory and all transactions in process.
  • Recovery plans. You group machines, sequence their failover and recovery, and add scripts and manual actions, so a SQL Server VM can come up before the application VMs that depend on it.
  • Testing without disruption. Disaster recovery drills run without affecting ongoing replication.

Site Recovery remains VM recovery. When failover occurs, Azure VMs are created from the replicated data (About Site Recovery[15]), which is different from an AG's synchronous secondary that hardens each transaction's log before the commit is confirmed (Availability modes[7]). Site Recovery provides continuous replication for Azure VMs and VMware VMs and a replication frequency as low as 30 seconds for Hyper-V, and planned failovers for expected outages have zero data loss, while unplanned failovers have minimal data loss that depends on the replication frequency (About Site Recovery[15]). It can also work alongside SQL Server: it integrates with Always On to manage the failover of availability groups, and it can protect Windows Server Failover Clusters on Azure VMs that use shared disks, such as a SQL Server FCI (About Site Recovery[15]).

Choose Site Recovery when the requirement is to recover and sequence whole servers; choose an AG when committed transactions must already exist on a running secondary.

What a hybrid DR site depends on

A hybrid design places some replicas on-premises and some in Azure, and its DR site works only if everything those replicas depend on also survives. In a hybrid configuration, some AG replicas run on Azure VMs and others on-premises for cross-site disaster recovery, and the production site can be either one (Availability groups[9]).

A traditional AG, unlike the distributed availability group described next, requires all replicas to be in the same failover cluster, so a hybrid AG needs a cluster that spans both networks (a multi-subnet failover cluster) and a virtual private network (VPN) connection between Azure and the on-premises network (Availability groups[9]). In this domain-based Windows design, the cluster also depends on the directory: a domain controller is needed for the Active Directory domain, and Microsoft advises installing a replica domain controller at the DR site for successful disaster recovery (Availability groups[9]). If the only domain controller sits in the failed site, the surviving replicas lose that dependency with it. A cluster stretched across sites also brings its own work, such as the quorum witness and node votes (Distributed availability groups[16]).

Log shipping has the same dependency in another form. It automatically sends transaction log backups from a primary database on a primary server to one or more secondary databases on a separate server, and in a hybrid design it depends on Windows file sharing, so it needs a VPN connection between the Azure virtual network and the on-premises network, plus a replica domain controller at the DR site (Log shipping[9]). Log shipping is configured per database, its role change is always manual, and it has no abstraction like a listener for that change, so applications must handle the redirection (Business continuity and database recovery - SQL Server[6]).

A hybrid DR site needs the network link, the cluster and the directory services to survive alongside the replica.

Distributed availability groups: two clusters, one link

When each site must keep its own cluster, a distributed availability group replaces the stretched cluster from the previous section. It's a special type of AG, available since SQL Server 2016, that spans two separate AGs, and the participating AGs can be on-premises, in the public cloud, in different domains or even on different platforms, as long as they can communicate (Distributed availability groups[16]).

Three properties set it apart from a traditional AG (Distributed availability groups[16]):

  • Nothing in the cluster. A distributed AG configures nothing in the underlying cluster, a WSFC or the Linux cluster manager Pacemaker; everything about it is maintained within SQL Server, so each site's cluster maintains its own availability.
  • Listeners below, none above. Each underlying AG needs a listener, which you specify with the ENDPOINT_URL parameter when you create the distributed AG; the distributed AG itself has no listener.
  • A forwarder. Only one copy of the databases accepts inserts, updates and deletions: the one on the primary replica of the primary AG, called the global primary (What is a contained availability group?[10]). It sends transactions to its own secondary replicas and to the primary replica of the second AG, the forwarder, which forwards them to the secondary replicas of its own AG.

The figure below traces that flow across the two clusters.

Data movement between the two AGs can be synchronous or asynchronous, and Microsoft recommends asynchronous data movement for this DR use, with both underlying AGs in the same availability mode (Distributed availability groups[16]). Because a distributed AG is a SQL Server-only construct, the WSFCs that house the two AGs can even run different major versions of Windows Server (Distributed availability groups[16]).

Use a distributed AG when the requirement keeps the clusters, and their administration, separate; use a single multi-subnet AG only when one cluster may span both sites.

Cluster A: AG 1 (primary AG)Global primary(read-write)SecondaryCluster B: AG 2Forwarder(AG 2 primary)Secondarydistributed AG
Data flow in a distributed availability group spanning two clusters, modeled on Microsoft's distributed availability group overview

Managed Instance link for hybrid disaster recovery

The Managed Instance link applies the distributed AG model to Azure SQL Managed Instance: it creates a distributed availability group between SQL Server and Managed Instance and replicates data in near real time (Managed Instance link overview[17]). It supports read scale-out, migration and disaster recovery (Managed Instance link overview[17]); this section describes the DR use.

The SQL Server version decides whether that DR is reversible (Managed Instance link overview[17]):

SQL Server version DR with the link
SQL Server 2016, 2017 or 2019 One-way replication to Managed Instance. You can fail over manually to Managed Instance in a disaster, but doing so breaks the link, and failing back isn't supported
SQL Server 2022 or SQL Server 2025 Two-way DR: fail over to the secondary in a disaster and fail back after the disaster is mitigated; either SQL Server or Managed Instance can be the initial primary
SQL Server 2014 and earlier Not supported, because these versions lack built-in support for distributed availability groups

The reversible case also depends on the Managed Instance update policy, an instance setting that controls access to the latest SQL engine features: the SQL Server 2022 and SQL Server 2025 policies align the instance's internal database format with that SQL Server version, while the Always-up-to-date policy lets the format evolve beyond it (Update policy for Azure SQL Managed Instance[18]). Two-way replication with failback to SQL Server requires Managed Instance on the update policy that corresponds to the SQL Server version, and replication and failover from Managed Instance to SQL Server isn't supported on Always-up-to-date (Managed Instance link overview[17]). A higher policy is enough only for one-way work: for one-way replication and cutover to Managed Instance, the destination policy can match or exceed the source SQL Server version, but you can't replicate data or fail back to SQL Server after cutover if the policies don't match (Managed Instance link overview[17]).

A Managed Instance replica used only for passive DR, with no workloads, can also be designated so that Microsoft doesn't charge SQL Server licensing for the vCores it uses (Managed Instance link overview[17]); Licensing passive DR replicas below compares the licensing options.

A reversible SQL Server to Managed Instance DR design needs SQL Server 2022 or SQL Server 2025 and the matching update policy.

Transactional replication across sites

Transactional replication copies selected tables rather than whole databases, so it fits reporting copies and data distribution more than full-database failover. After an initial snapshot, it delivers data changes from the Publisher to Subscribers in near real time, in the same order and within the same transaction boundaries as at the Publisher, and by default Subscribers should be treated as read-only (Transactional replication[19]). Microsoft notes that replication isn't officially designated an Always On availability feature, although it's often used to make data redundant (Business continuity and database recovery - SQL Server[6]). Its three roles are the Publisher, which publishes changes made to tables (articles); the Distributor, which collects those changes; and the Subscriber, which receives them (Transactional replication with Azure SQL Managed Instance[20]).

Filtering what a publication sends. A static row filter is a WHERE clause that selects the rows to publish, and every Subscriber to that publication receives the same subset, so a Subscriber that needs a different subset needs another publication (Filter published data[21]). A column filter selects the columns to publish. Two rules constrain the filters: primary key columns are required for all tables in transactional publications, and every column referenced in a row filter must be included in the publication, so a column filter can't exclude it (Filter published data[21]). Row filters in transactional publications can add significant overhead, because the filter clause is evaluated for each log row written for a published table, and Microsoft advises avoiding them when every replication node can support the full data load and the data set is reasonably small (Filter published data[21]).

Placing the roles across on-premises and Azure. In general, the Publisher and the Distributor must both be in the cloud or both on-premises (Common configurations[20]). Supported configurations include a Publisher with a local Distributor on one managed instance; a Publisher with a remote Distributor on another managed instance, where both instances must be on the same virtual network and in the same location; and an on-premises Publisher and Distributor with a subscriber in Azure SQL Database or Managed Instance (Common configurations[20]). Managed Instance replication also requires (Requirements[20]):

  • SQL authentication for connectivity between replication participants.
  • An Azure Storage account share for the replication working directory, with outbound TCP port 445 open in the subnet security rules.
  • Outbound TCP port 1433 when the managed instance is the Publisher or Distributor and the Subscriber isn't a managed instance, which may also require changing the allow_linkedserver_outbound rule's destination service tag from virtualnetwork to internet.

Subscription types have limits too: a SQL Server instance or a managed instance can be a push or a pull subscriber, but a pull subscription isn't supported when the Distributor is a managed instance and the Subscriber isn't, and a database in Azure SQL Database can only be a push subscriber (Transactional replication with Azure SQL Managed Instance[20]).

Transactional replication suits filtered, table-level copies; plan its filters around the required columns, and keep the Publisher and Distributor on the same side.

Built-in availability architecture in Azure SQL

Azure SQL Database and Azure SQL Managed Instance handle the first disruption scenario, a local hardware or software failure, without any configuration, but the architecture behind it decides how the workload resumes after a failover. Azure SQL Database uses three availability models (Availability through local and zone redundancy - Azure SQL Database[22]):

Model Service tiers How a local failover proceeds
Remote storage Basic, Standard, General Purpose Azure Service Fabric moves the stateless database engine process to another compute node and attaches the data and log files kept in Azure Blob storage; the new process starts with a cold cache, so a heavy workload might see some performance degradation
Local storage Premium, Business Critical Compute and locally attached SSD storage are replicated to up to three secondary replicas, and the primary persists each change on enough secondaries before committing, so a fully synchronized replica is always available
Hyperscale Hyperscale A distributed system of compute nodes, page servers, the log service and persistent storage, each with its own redundancy and resiliency

Azure SQL Managed Instance uses the remote storage model for General Purpose and Next-gen General Purpose and the local storage model for Business Critical, with the same cold-cache effect after a General Purpose move (Availability through local and zone redundancy - Azure SQL Managed Instance[23]). The local storage model also offers Read Scale-Out, which redirects read-only connections to one of the secondary replicas (Availability through local and zone redundancy - Azure SQL Database[22]).

Locally redundant availability (this built-in availability architecture without zone redundancy) keeps the RPO at zero, but it stays inside one datacenter: without zone redundancy, failovers happen within the same datacenter, so the database might be unavailable until a zone outage is resolved, or until a DR solution such as a failover group or geo-restore (both described in Regional recovery: ready replicas versus geo-restore below) recovers it (Availability through local and zone redundancy - Azure SQL Database[22]).

The service tier decides how smoothly a local failover resumes the workload; zone redundancy, described next, decides whether the database survives a datacenter loss.

Zone redundancy for Azure SQL Database and Managed Instance

Zone redundancy extends the built-in availability architecture across availability zones, and it must be enabled. With it, Azure SQL Database spreads its compute and storage components across two or three zones that Azure selects, and every service tier's implementation keeps the RPO at zero for committed data if a single availability zone becomes unavailable (Availability through local and zone redundancy - Azure SQL Database[22]). Microsoft's comparison lists a typical RTO of less than 30 seconds for HA through zone redundancy (RTO and RPO[2]).

Support depends on the service tier:

Zone redundancy places live components; backup storage redundancy is a separate setting that decides how backups are replicated (Automated backups in Azure SQL Database[25]). Changing a Standard database's backup redundancy therefore leaves the database itself without zone redundancy, since that tier doesn't support it. Zone redundancy has a performance side as well: on Managed Instance, replicas in datacenters some distance apart might increase transaction commit time for some online transaction processing (OLTP) workloads (Availability through local and zone redundancy - Azure SQL Managed Instance[23]).

Zone placement is also separate from geographic replication. You can create an active geo-replication secondary (a continuously synchronized readable copy of the database, described in Regional recovery: ready replicas versus geo-restore next) in the same region as the primary for read scale-out, but it adds no resilience to catastrophic failures or large-scale outages, isn't a suitable DR failover target, and doesn't guarantee availability zone isolation; zone isolation comes from the zone-redundant configuration (Active geo-replication[26]).

For a zone-outage requirement, pick a supporting tier and enable zone redundancy; a second copy in the same region is not the same thing.

Regional recovery: ready replicas versus geo-restore

A regional outage leaves Azure SQL Database with two kinds of options: a secondary that is already running in another region, or a new database restored from backups that were copied to another region (Business continuity in Azure SQL Database[2]).

  • Active geo-replication maintains a continuously synchronized readable secondary for an individual database in any region; a primary can have up to four geo-secondaries, and a geo-failover is initiated by the application or by the user (Active geo-replication[26]). Because the new primary is on a different logical server, the Azure resource that hosts the databases, the connection endpoint changes after a failover (Active geo-replication[26]).
  • Failover groups, despite the similar name a different feature from Always On availability groups (configured between SQL Server instances), manage the replication and failover of some or all databases on a logical server to a secondary server in another region, and their read-write and read-only listener endpoints stay unchanged, so connection strings don't need updating after a failover (Business continuity in Azure SQL Database[2]). A failover group's failover policy decides who starts a failover during an outage: under the customer-managed policy, which Microsoft recommends, you do, and under the Microsoft-managed policy Microsoft does (Failover policy[27]). A failover group's secondary can't be in the primary's region (Business continuity in Azure SQL Database[2]). For Azure SQL Managed Instance, a failover group replicates the user databases of one managed instance to another managed instance in a different Azure region (Failover groups - Azure SQL Managed Instance[28]).
  • Geo-restore creates a new database on any server in any Azure region from the most recent geo-replicated backups, and you can request it even when an outage has made the region inaccessible; it requires geo-redundant or geo-zone-redundant backup storage (Geo-restore[29]).

Microsoft's comparison gives the typical targets for Azure SQL Database (RTO and RPO[2]):

Option Typical RTO Typical RPO
Failover groups with a customer-managed failover policy, or active geo-replication Less than 60 seconds Zero or more, depending on changes not yet replicated before the disruption
Geo-restore Minutes or hours, depending on Azure storage replication Minutes or hours, depending on the size of the database backup

These are typical values, not guarantees for a particular database. Restore time depends on the database size, its compute size, the transaction log backups involved, the activity to replay, the network bandwidth to another region and the number of concurrent restore requests in the target region, and a prolonged regional outage can cause many geo-restore requests that lengthen each recovery (Recover using automated backups[29]). Geo-restore also doesn't guarantee that the target region will have capacity to restore your databases after a regional outage; Microsoft therefore positions it for relatively small databases that aren't critical to the business, and failover groups for business-critical applications with large databases, because their capacity is always guaranteed (Geo-restore considerations[29]).

A ready secondary delivers its RTO only if it can carry the load. The primary and the geo-secondary must have the same service tier, and Microsoft strongly recommends the same compute size; an undersized secondary might not keep up with a heavy write workload, and after failover it might need a scale-up that can take significant time (Configure geo-secondary[26]).

Preprovision a secondary when the RTO is short or must be predictable; accept geo-restore when the RTO tolerates hours and the database is small and not business-critical.

Recovering the application, not only the database

A regional failover finishes when the application works again, so the plan covers every component the service needs. Microsoft notes that database regional redundancy is only part of full business continuity: recovering a service end to end requires recovering all its components and dependent services, such as client software, web front ends, storage and DNS, and all of them must be resilient to the same failures and available within the application's RTO (Active geo-replication[26]).

Plan these items together with the database:

  • Connection routing. Failover group listeners keep one endpoint name, and after a Managed Instance geo-failover the DNS record is updated automatically to redirect the endpoints to the new region (Failover groups - Azure SQL Managed Instance[28]). With active geo-replication alone, the endpoint changes (Active geo-replication[26]).
  • Security on the secondary. Validate that authentication and network access for the secondary server are configured, so the application can reach the new primary immediately (Active geo-replication[26]).
  • Settings that aren't replicated. The backup retention policy isn't part of the database; a geo-secondary gets a default point-in-time restore (PITR) retention of seven days unless you configure it to match the primary (Active geo-replication[26]).
  • Preparation. An unprepared secondary region makes bringing applications online take additional time and likely troubleshooting, which delays the RTO (Prepare for a region outage[2]).

Components that don't move cost something too. Microsoft's failover group guidance warns that a failover made without considering the application's dependency on other Azure services or components can cause performance degradation or unavailability of the application (Failover groups - Azure SQL Managed Instance[28]).

Measure regional RTO from the outage to a working application, and list every dependency that must move or reconnect with the database.

Replicas do not undo logical errors

The fourth disruption scenario, data corruption or deletion, is the one replication can't handle. As Four disruption scenarios set the scope of protection above explains, a committed DELETE reaches every synchronized AG replica (Business continuity and database recovery - SQL Server[6]), and active geo-replication keeps its secondaries continuously synchronized too (Business continuity in Azure SQL Database[2]). Microsoft describes this scenario as damage, typically from an application bug or human error, that is application-specific and typically can't be detected by the database service, and it answers it with automated backups (Features that provide business continuity[2]).

Recovery from a logical error therefore goes back in time:

Keep restorable backups beside every replica design, and answer a logical error with a restore to a point before it, not with a failover.

Accelerated database recovery and engine recovery time

Some downtime comes from the database engine itself, because a database must finish recovery before it's available, for example after an FCI failover (Business continuity and database recovery - SQL Server[6]). With traditional recovery, the time to recover from an unexpected restart is roughly proportional to the size of the longest active transaction, and accelerated database recovery (ADR) removes that dependency (Accelerated database recovery[31]). ADR versions physical database modifications and undoes them through logical revert, an asynchronous process that performs row-level, version-based undo, so recovery and rollback no longer depend on undoing every change from the transaction log (Accelerated database recovery[31]). The versions live in the persistent version store (PVS), which persists row versions in the database itself instead of the traditional version store in tempdb (Accelerated database recovery[31]). ADR also lets the transaction log be truncated aggressively as checkpoints and backups occur, even while long-running transactions are active (Accelerated database recovery[31]).

ADR is always enabled in Azure SQL Database and Azure SQL Managed Instance (Accelerated database recovery[31]). It speeds database engine recovery; it doesn't create a copy anywhere else, so it doesn't itself provide cross-region redundancy or regional failover.

Its capacity planning happens inside the user database (Accelerated database recovery[31]):

  • Keep enough space in the database for PVS to grow; if there isn't room, ADR might fail to generate versions, and DML (data manipulation language) statements fail. Because PVS lives in the user database, adding tempdb space doesn't help.
  • Long-running transactions and a high rate of aborted transactions delay PVS cleanup and increase its size.
  • Transactional replication, snapshot replication and change data capture (CDC) disable ADR's aggressive log truncation so their log readers can collect changes, so the transaction log must be large enough for them.

ADR reduces engine recovery and rollback time, and it charges for that with version storage inside the database.

Licensing passive DR replicas

Licensing can change which replica design is affordable, and each benefit below applies only to a replica that serves no production workload.

  • SQL Server on Azure VMs. With Software Assurance, hybrid DR plans can include a passive DR instance without additional SQL Server licensing costs, and pay-as-you-go licensing also qualifies for license-free DR replicas when all replicas are hosted in Azure; for example, with all three replicas in Azure, the two passive secondaries are free (Free DR replica in Azure[9]). You enable the benefit on the SQL virtual machine resource by choosing HA/DR under SQL Server License (Free DR replica in Azure[9]).
  • Azure SQL Managed Instance failover groups. If the secondary instance has no read workloads or connected applications, you can designate it a standby replica, for example by setting the SecondaryType parameter to Standby; Microsoft then doesn't charge SQL Server licensing for its vCores, but you're still billed for its compute and storage (Configure a license-free standby replica[32]).
  • Azure SQL Database. A geo-secondary used only for DR, with no read or write workloads, can be designated for standby when you configure a new active geo-replication relationship (Save on costs with a standby replica[2]).

A Managed Instance standby replica must be used only for DR: no production applications can connect to it, and the only permitted activities are running backups, maintenance operations such as DBCC CHECKDB, connecting monitoring applications and running DR drills (Configure a license-free standby replica[32]). It provides the same RPO and RTO as a readable secondary and supports every planned failover scenario, including recovery drills and failback (Configure a license-free standby replica[32]). A Managed Instance failover group has one replica, which is either readable or designated standby (Configure a license-free standby replica[32]), so if reports must run on the secondary before any failover, it has to be the readable kind, without the standby benefit.

A passive replica can remove SQL Server licensing costs; a replica that serves production reads can't use those benefits.

Testing local failover handling

Consider starting with a local failover to test client reconnection logic before a regional DR drill. Microsoft lets you trigger the automatic failover of Azure SQL Database manually, through an API that restarts a database or an elastic pool (a set of databases that share one pool of resources), so you can see how planned or unplanned failovers affect the application before production (Test application fault resiliency[22]):

Target PowerShell Azure portal
Database Invoke-AzSqlDatabaseFailover Settings > Maintenance > Restart
Elastic pool Invoke-AzSqlElasticPoolFailover Settings > Maintenance > Restart

The REST API offers the same operations, and the Azure CLI can call them through az rest (Test application fault resiliency[22]). For a zone-redundant General Purpose database or elastic pool, the call redirects client connections to a new primary in a different availability zone, so you can also check whether the change in network latency affects end-to-end performance (Test application fault resiliency[22]). Because the restart is intrusive, only one failover call is allowed every 15 minutes for each database or elastic pool, so space repeated tests accordingly (Test application fault resiliency[22]).

This test isn't a geo-failover. It keeps the database in its region and exercises existing sessions and reconnection, whereas a forced geo-failover switches the primary to another server and can lose transactions that weren't yet replicated (Active geo-replication[26]), adding the risk of data loss to the exercise. On Azure SQL Managed Instance, the user-initiated manual failover typically only restarts the SQL process locally rather than moving to another node, with a few documented exceptions (Availability through local and zone redundancy - Azure SQL Managed Instance[23]).

Use the local failover API to prove reconnection and retry handling; keep geo-failovers for the DR drills that follow.

Regional failover drills and failback

A regional drill proves that the DR region can run the application, and the failover type chosen decides whether the drill itself risks data. Microsoft describes a disaster recovery drill as three steps: simulate a data tier outage, recover, and validate application integrity after recovery (Disaster recovery drills[33]).

Geo-replication and failover groups offer two failover types (Active geo-replication[26]):

  • Planned failover (no data loss). A full data synchronization completes before the primary and the geo-secondary switch roles, so no data is lost; the duration depends on how much transaction log must still be synchronized. Microsoft designs it for DR drills in production when data loss isn't acceptable, for relocating a database to another region, and for failback.
  • Forced failover (potential data loss). The geo-secondary switches to the primary role immediately, without waiting for synchronization, and transactions committed on the primary but not yet replicated are lost. It's the recovery method for an outage when the primary isn't accessible.

For a failover group drill, simulate the outage, for example by disabling the web application or VM connected to the database; make sure the application configuration in the DR region points to the former secondary; initiate a planned failover of the failover group from the secondary server; and then verify connectivity and basic functionality (Failover groups drill[33]). Because planned failover doesn't lose data, Microsoft says this drill can be performed in the production environment (Disaster recovery drills[33]). For Managed Instance failover groups, Microsoft recommends manual planned failover for drills and doesn't recommend forced failover, which provides no guardrails against data loss (Disaster recovery drills - Azure SQL Managed Instance[28]).

The figure below shows the drill sequence, including the failback that closes it.

Failback. After a geo-failover, the recovered original primary doesn't take back the writable role: it reconnects and is brought up to date as a secondary of the new primary, and returning the primary to the original region takes a manual failover (Active geo-replication[26]). Plan that return as a planned failover once replication is healthy. For Managed Instance, Microsoft strongly recommends planned failover for a lossless failback, says a forced failback is expected to fail unless the failover group status is Synchronizing on both instances, and advises using the failover group status to confirm that both instances have changed roles before clients reconnect (Disaster recovery drills - Azure SQL Managed Instance[28]).

Drill with planned failover, validate the application in the DR region, and treat failback as a second, planned role change.

1. Simulate the outage2. Point the applicationat the DR secondary3. Planned failoverof the failover group4. Validate theapplication5. Planned failback whenreplication is healthy
A failover group disaster recovery drill and its failback, following Microsoft's Azure SQL Database drill guidance

Geo-restore drills in a test copy

A geo-restore drill can't run against production safely, because the restored database is older than production. Microsoft advises performing it in a test environment, a copy of the production environment, to prevent potential data loss, and following the same three steps as any drill (Geo-restore drill[33]):

  1. Simulate the outage, for example by renaming the source database so that application connections fail.
  2. Recover by geo-restoring the database to a different server, then change the application configuration to connect to the recovered database and complete the post-recovery configuration.
  3. Validate application integrity: connection strings, logins, basic functionality and any other checks in the standard application signoff.

The validation has two jobs. It shows that the procedure works, and it shows whether the application tolerates the age of the data: Microsoft recommends periodic drills of a geo-restore plan to verify application tolerance to any loss of recent data modifications, along with all operational aspects of the recovery procedure (Geo-restore considerations[29]). A geo-restored database comes from the most recent geo-replicated backups, and geo-redundant storage replicates asynchronously, so the restored data can lack recent changes (Geo-restore[29]). That gap is why Microsoft places the drill in a copy of production (Geo-restore drill[33]): running a first rehearsal as a production cutover would discard changes that production clients had already seen committed.

A geo-restore drill must prove that the procedure works and that the application tolerates the age of the restored data, without exposing production to that gap.

Proving that backups restore

A successful backup job proves that a file was written, not that the database can be recovered from it. RESTORE VERIFYONLY verifies a backup without restoring it: it checks that the backup set is complete, that all volumes are readable and any checksums present on the media, but it doesn't attempt to verify the structure of the data in the backup (RESTORE VERIFYONLY[34]).

A recovery rehearsal goes further. Microsoft states that you don't have a restore strategy until you test your backups: restore a copy of each database onto a test system, test restoring every type of backup you intend to use, and run DBCC CHECKDB against the restored database to confirm the backup media isn't damaged (Test your backups[35]). The strategy holds only once you successfully restore backups in every combination your restore strategy includes and test each restored database for physical consistency (Back up and restore of SQL Server databases[35]). Record the time each test restore takes in the database's operations manual, so the RTO rests on measured restores (Test your backups[35]); Microsoft's reliability guidance makes the same point, that testing restores verifies their integrity and shows how long restoration takes (Backups[1]).

The listing below turns those steps into one rehearsal for a SQL Server database that has a full backup and one log backup.

Rehearsal script: verify, restore a full and a log backup to a test copy, then check its integrity

-- Teaching listing: database, logical file and path names are placeholders.
RESTORE VERIFYONLY FROM DISK = N'D:\Backups\Sales_full.bak';  -- media check only

RESTORE DATABASE Sales_Test FROM DISK = N'D:\Backups\Sales_full.bak'
    WITH MOVE N'Sales'     TO N'E:\Data\Sales_Test.mdf',      -- new file locations, so the
         MOVE N'Sales_log' TO N'F:\Log\Sales_Test.ldf',       -- copy can't collide with Sales
         NORECOVERY;                                         -- a log backup still follows

RESTORE LOG Sales_Test FROM DISK = N'D:\Backups\Sales_log1.trn'
    WITH RECOVERY;                                           -- last step: bring it online

DBCC CHECKDB (N'Sales_Test') WITH NO_INFOMSGS;               -- structural integrity check

In the listing, each MOVE relocates one logical file from the backup to a new operating-system file, which avoids collisions with existing files when you copy a database on the same server (RESTORE arguments[36]). NORECOVERY on every restore except the last lets the next log backup be applied, and RECOVERY rolls back uncommitted transactions so the database is ready for use (RESTORE arguments[36]). NO_INFOMSGS suppresses informational messages from DBCC CHECKDB, so only problems are reported (DBCC CHECKDB[37]).

Verify media with RESTORE VERIFYONLY if you like, but prove recoverability by restoring, checking with DBCC CHECKDB and timing the result.

Testing performance on the surviving topology

A recovery that accepts connections but can't carry the production load hasn't restored service, so tests measure throughput as well as success. Three effects of replica placement and sizing change performance:

  • Replication distance. Under synchronous commit, the replica round trip is part of every commit, as Availability modes: synchronous versus asynchronous commit above explains; zone-redundant Managed Instance replicas in different datacenters might increase transaction commit time for some OLTP workloads (Availability through local and zone redundancy - Azure SQL Managed Instance[23]); and for a workload sensitive to inter-zone latency, Microsoft advises testing the latency between the selected zones with your actual protocols and configuration (What are availability zones?[4]).
  • Replica capacity. An undersized geo-secondary can lag behind a heavy write workload and need a slow scale-up after failover, as Regional recovery: ready replicas versus geo-restore above explains; that scale-up ends with a failover that can interrupt workloads (Configure geo-secondary[26]).
  • Local failover effects. A General Purpose failover starts the new engine process with a cold cache, so a heavy workload might see some degradation during the transition, and a test failover of a zone-redundant General Purpose database shows whether the new zone changes end-to-end performance (Availability through local and zone redundancy - Azure SQL Database[22]).

Run the drills from the previous sections under representative write and reporting load, and keep the workload running on the recovered primary to show sustained throughput, not only a successful connection.

Size the secondary like the primary, and test the recovered topology under production-like load.

Recognizing the exam scenarios

For this objective, compare each option with the recovery requirement: does it cover the right disruption scenario, protect the right unit, and address the right target? The table below pairs common scenarios with the answer and the mechanism that rules out the tempting wrong option; each row restates a rule explained in an earlier section.

Scenario Right answer Why the tempting option fails
Backups meet the RPO, but restoring takes longer than the RTO A running secondary: an AG replica or a failover group A shorter backup interval improves only the RPO; the restore still takes as long
No acknowledged commit may be lost if a node fails Synchronous-commit AG replicas in the same region or across its zones, with a synchronized secondary and REQUIRED_SYNCHRONIZED_SECONDARIES_TO_COMMIT = 1 to block commits if protection is lost Asynchronous commit can lose log the secondary never received
A DR replica sits across a high-latency hybrid link Asynchronous commit for that replica Synchronous commit adds the round trip to every transaction
Instance logins and Agent jobs must fail over with the application An FCI A conventional AG moves only its user databases
On-premises and Azure must keep separate clusters A distributed availability group A multi-subnet AG puts both sites into one cluster
SQL Server 2019 to Managed Instance must support failback Upgrade to SQL Server 2022 or 2025 with the matching update policy With SQL Server 2019, failover to Managed Instance breaks the link
SQL Server VMs must survive the loss of a datacenter VMs in different availability zones Fault domains of an availability set stay inside one datacenter
SQL Server must fail over when the engine fails inside a running VM An AG or FCI VM service healing protects the VM, not SQL Server inside it
A Standard-tier database must survive a zone outage Move to a tier that supports zone redundancy, then enable it Basic and Standard don't support zone redundancy; backup redundancy doesn't change that
An otherwise eligible managed instance can't enable zone redundancy because its backup storage uses neither ZRS nor GZRS Set backup storage redundancy to ZRS or GZRS first The instance setting requires zone-redundant or geo-zone-redundant backups
A large business-critical database needs a predictable regional RTO A failover group Geo-restore depends on backup age, restore demand and target capacity
A same-region geo-secondary is proposed as the DR copy A secondary in another region A same-region secondary survives no regional outage and doesn't guarantee zone isolation
A committed DELETE removed the wrong rows Point-in-time restore to before the deletion Every synchronized replica already has the deletion
Application retry logic must be tested before go-live Invoke-AzSqlDatabaseFailover or Invoke-AzSqlElasticPoolFailover A forced geo-failover adds a role change and possible data loss
A production DR drill must lose no data Planned failover of the failover group Forced failover skips synchronization
Backups must be proven recoverable Restore them to a test system and run DBCC CHECKDB RESTORE VERIFYONLY doesn't verify the data structure
Reports must run on the Managed Instance secondary A readable, normally licensed secondary A standby replica permits only DR activities
A SQL Server VM keeps data and log files on separate geo-replicated disks A SQL Server DR solution, or data and log on one disk Disk geo-replication keeps write order only within each disk
Whole application servers must recover in sequence with SQL Server An Azure Site Recovery recovery plan An AG protects databases, not the application VMs

Before choosing, name the disruption scenario the requirement must survive and the target, RPO or RTO, that it constrains.

Four disruption scenarios and the protection each platform uses

Disruption scenarioAzure SQL DatabaseAzure SQL Managed InstanceSQL Server on Azure VMs
Local hardware or software failureBuilt-in availability architecture: remote storage, local storage or Hyperscale modelBuilt-in availability architecture: remote storage (General Purpose) or local storage (Business Critical)AG or FCI; Azure VM recovery alone doesn't detect a failed SQL Server instance
Datacenter or availability zone outageZone redundancy on General Purpose, Business Critical, Hyperscale or Premium; not Basic or StandardZone redundancy on General Purpose or Business Critical, after backup storage is zone-redundant or geo-zone-redundantReplicas on VMs in different availability zones; an availability set stays in one datacenter
Regional outageFailover group or active geo-replication to another region; geo-restore for slower, capacity-dependent recoveryFailover group with a secondary instance in a different regionAsynchronous-commit replica or distributed availability group in another region, log shipping, or Azure Site Recovery
Data corruption or deletionPoint-in-time restore from automated backups; replicas already carry the changePoint-in-time restore from automated backupsRestore from native backups; log shipping with a restore delay can keep a copy from before the error

Decision tree

Azure SQL DatabaseData corruptionor deletioncommitted?Point-in-timerestore to beforethe errorYesNoRegional outagewith a short,predictable RTO?Failover groupor activegeo-replicationYesNoRegional outagewith an RTO ofhours acceptable?Geo-restoreYesNoDatacenter oravailability zoneoutage?Zone redundancyon a supportingtierYesNoBuilt-in availabilityarchitectureAlways: drill the plan, and measureRTO to a working application

Sharp facts the exam loves — give these one last read before exam day.

Cheat sheet

Sharp facts the exam loves — scan these before test day.

Recovery time and recovery point objectives constrain different parts of recovery

Recovery Time Objective (RTO) expresses how long the application can be unavailable, whereas Recovery Point Objective (RPO) expresses the tolerable time window of lost data. Select a recovery method against both requirements: frequent recoverable copies can reduce data loss without making a lengthy restore fast enough. Measure recovery through restored application service, not merely through promotion of a database replica.

Trap Using a shorter backup interval to satisfy a downtime target when restoring and reconnecting the application still take too long.

5 questions test this
Synchronous commit trades commit latency for protection of acknowledged transactions

Synchronous-commit availability replicas make the primary wait for the participating secondary to harden transaction log records before acknowledging a commit. A synchronized secondary can therefore preserve committed work, but network and log-write latency become part of transaction latency. Asynchronous commit avoids that remote acknowledgement wait and suits distant DR replicas, at the cost of possible loss of transactions not yet hardened there.

Trap Choosing synchronous commit across a high-latency hybrid link solely to avoid data loss without accounting for its effect on transaction response time.

5 questions test this
Availability groups protect selected databases while failover cluster instances protect an instance

Use an Enterprise-edition Always On availability group when selected application databases must move together without moving unrelated databases on the instance. Use an Always On failover cluster instance when the SQL Server instance, including its system databases and SQL Server Agent jobs, must move as one service. An FCI uses shared storage, so instance failover alone does not create an independent remote database copy for disaster recovery. SQL Server Standard's basic availability group instead protects one database with one primary and one secondary, without secondary reads or backup offload. On SQL Server 2022 and later, a contained availability group can also replicate AG-scoped logins and Agent jobs through its contained master and msdb databases, rather than requiring those objects to be maintained separately on every conventional AG replica.

Trap Selecting an FCI for an application-only database failover requirement because its instance-wide failover also moves unrelated databases.

5 questions test this
Readable availability-group secondaries cannot have an independent index design

Create reporting indexes on the primary database when reports use a readable Always On availability-group secondary, because the secondary is a read-only copy and cannot maintain its own independent indexes. The added index therefore becomes part of the primary's physical design rather than a secondary-only optimization. Choose transactional replication for a reporting copy that needs independent indexing or data shaping instead of requiring those changes on the transactional primary.

Trap Using a readable AG secondary as an independently indexed reporting database because its read-only replicated copy preserves the primary's database structure.

A distributed availability group connects two availability groups without extending one cluster

When an on-premises site and Azure must retain separate cluster infrastructures, use a distributed availability group to link their underlying availability groups. The distributed group is managed in SQL Server rather than as an additional WSFC or Pacemaker cluster resource; its underlying groups need listeners, but the distributed group has no listener of its own. The secondary group's primary acts as a forwarder, receiving log records and distributing them to its local secondaries while the global primary remains the writable copy.

Trap Stretching a single WSFC across sites when the design requires independent clusters and separate administrative boundaries.

5 questions test this
A hybrid recovery site needs surviving connectivity and identity services

A conventional domain-based Windows availability group spanning on-premises and Azure needs its cluster nodes to communicate across the networks, commonly through a VPN connection. Plan DNS resolution and a replica domain controller at the recovery site so that loss of the production site does not also remove authentication dependencies. Hybrid log shipping additionally depends on access to the Windows backup share across that connection.

Trap Placing the only domain controller and DNS service at the production site while treating Azure database copies as a complete recovery site.

5 questions test this

Managed Instance link uses distributed availability group technology for near-real-time replication between SQL Server and Azure SQL Managed Instance. Supported SQL Server 2016, 2017, and 2019 builds support one-way replication and cutover to Managed Instance, but that cutover breaks the link and does not support failback. Bidirectional DR and failback require SQL Server 2022 or 2025 with the matching Managed Instance SQL Server update policy; using a higher destination policy for one-way cutover does not establish reverse compatibility.

Trap Choosing a SQL Server 2019 link as a reversible DR arrangement because it can replicate successfully to Managed Instance before cutover.

4 questions test this
Azure Site Recovery provides VM-level disaster recovery for SQL Server workloads

Use Azure Site Recovery when SQL Server disaster recovery must replicate and recover a supported VM rather than maintain only database copies in an availability group. Application-consistent recovery points capture application state as well as disk data; this is VM recovery rather than a SQL backup-and-log restore chain. Recovery plans can sequence recovery of the SQL VM and dependent application VMs. Test failover rehearses recovery without interrupting ongoing production replication.

Trap Assuming a Site Recovery recovery plan supplies a synchronized SQL secondary because the plan orchestrates VM recovery rather than synchronous transaction-log hardening.

5 questions test this
Transactional publication filters must preserve required columns

Transactional publications must retain primary-key columns and every column referenced by a row filter, even when column filtering excludes other data. A static WHERE row filter selects rows, whereas a column filter selects columns, and all subscribers to a filtered publication receive the same row subset. Use separate transactional publications for different fixed row subsets rather than assuming a static filter changes for each subscriber. These filters limit published articles rather than providing a whole-database HA replica. Avoid row filters when every replication node can support a reasonably small full data set, because evaluating the filter clause for each log row written for a published table can add significant overhead.

Trap Using a column filter to remove a sensitive column that is also referenced by the article's row filter because row-filter columns must remain in the publication.

Managed Instance transactional replication requires supported distribution placement and share access

For transactional replication with a Managed Instance Publisher and a remote Managed Instance Distributor, place both in the same virtual network and region. Use SQL authentication between replication participants and an Azure Storage file share for the replication working directory, with outbound TCP 445 allowed from the instance subnet. Delivery from a Managed Instance Publisher or Distributor to a non-Managed-Instance Subscriber also requires outbound TCP 1433; where the existing rule excludes that destination, the allow_linkedserver_outbound NSG rule's Destination Service tag may need changing from virtualnetwork to internet. An on-premises Publisher and Distributor can instead push changes to an Azure subscriber, but splitting the Publisher and Distributor between on-premises and Azure is not a supported arrangement. A Managed Instance Distributor must use push delivery to a subscriber that is not another managed instance; a Managed Instance subscriber can instead use either push or pull delivery.

Trap Splitting an on-premises Publisher from an Azure Distributor to simplify hybrid recovery because supported placement keeps the Publisher and Distributor together in the cloud or on-premises.

Passive DR licensing benefits do not cover production reporting workloads

Reserve passive DR licensing benefits for replicas without production read workloads; choose a normally licensed readable secondary when reports must run before failover. Eligible SQL Server VM replicas can use the SQL Server License HA/DR designation: hybrid deployments require Software Assurance, while pay-as-you-go licensing qualifies when all replicas are hosted in Azure. In a Managed Instance failover group, setting SecondaryType to Standby removes qualifying secondary SQL licensing costs but not compute or storage charges. That standby still permits monitoring, maintenance such as DBCC CHECKDB, backups, and DR drills rather than production application reads.

Trap Keeping a Managed Instance failover-group secondary designated Standby while using it for production reports because the failover licensing benefit restricts the secondary to DR and permitted operational activities.

Availability zones protect against a larger failure boundary than availability sets

Spread SQL Server HA virtual machines across availability zones when the design must survive the loss of an entire datacenter within a region. An availability set isolates equipment failures within a datacenter through fault domains, whose VMs share a power source and network switch, and planned maintenance through update domains, whose VMs can restart together; Azure restarts only one update domain at a time, and neither domain type provides regional DR. A VM is configured for an availability set or an availability zone, not both, and protection against a whole-region outage requires another region. A zonal VM is still a single-zone resource: the operator must deploy SQL VMs across zones and configure replication and failover, unlike a service-managed zone-redundant deployment. Logical zone numbers can map to different physical zones in different subscriptions, so verify the mappings rather than inferring cross-subscription isolation from zone labels alone.

Trap Selecting an availability set for a datacenter-loss requirement because its fault domains do not provide the physical isolation of availability zones.

6 questions test this
Infrastructure redundancy alone does not guarantee SQL Server continuity

Azure VM recovery mechanisms protect the virtual machine infrastructure, but SQL Server can fail while its host VM remains online. Add a database-aware HA solution such as an availability group or FCI when application continuity depends on SQL Server failure detection and failover. Ordinary asynchronous storage geo-replication of separate SQL data and log disks preserves write order within each disk, not across disks, so recovered data files can be ahead of their transaction log and violate SQL Server write-ahead logging. Use a SQL-aware or application-consistent DR method for that multi-disk layout rather than assuming that infrastructure replication supplies a recoverable database.

Trap Relying on Azure VM service healing as SQL Server HA because it does not necessarily detect an unavailable database engine inside a running VM.

4 questions test this
A same-region geo-secondary is a read-scale option rather than regional disaster protection

An Azure SQL Database geo-secondary can reside in the same region to serve read-only traffic, but that placement neither survives loss of the entire region nor guarantees separation into another availability zone. Choose a different region for a regional DR copy and explicitly enable supported zone redundancy for zonal isolation. Geographic replication and zone placement must therefore be evaluated separately.

Trap Treating a second logical server in the same region as guaranteed zone isolation because logical server separation does not control physical zone placement.

5 questions test this
Managed SQL storage architecture determines how local recovery resumes the workload

General Purpose managed SQL uses separate compute and remote storage, allowing a replacement compute node to attach preserved database files but resume with cold caches. Business Critical, and Azure SQL Database Premium, keep local database copies on replicated engine nodes so a synchronized node can take over with less performance disruption. Azure SQL Database Hyperscale instead distributes resilience across compute replicas, page servers, the log service, and persistent storage; choose and test the architecture against recovery performance rather than assuming all tiers recover identically.

Trap Expecting General Purpose failover to preserve a warm compute cache because its durable remote storage does not preserve the failed node's memory state.

4 questions test this
Azure SQL Database zone redundancy must be enabled on a supporting service tier

Azure SQL Database supports zone-redundant availability in General Purpose, Business Critical, and Hyperscale vCore tiers and in the Premium DTU tier, subject to the region and deployment's support. Basic and Standard DTU databases do not support it. Zone redundancy protects committed data through a zone failure with zero RPO, whereas a locally redundant deployment can remain unavailable during a zone-wide outage.

Trap Keeping a Standard DTU database and changing only its backup redundancy to meet a live zone-failure continuity requirement.

4 questions test this
Managed Instance needs zone redundancy enabled to withstand a whole-zone outage

Enable zone redundancy for Azure SQL Managed Instance when local HA must survive a whole-zone outage; the default locally redundant deployment can remain unavailable during that outage. General Purpose and Business Critical support zone redundancy in eligible regions, while Next-gen General Purpose zone redundancy is in preview and additionally requires regional Elastic SAN ZRS support. Set backup storage redundancy to zone-redundant storage (ZRS) or geo-zone-redundant storage (GZRS) first, then enable the instance setting, for example with az sql mi update --zone-redundant true. This setting distributes service components across zones rather than merely increasing backup durability.

Trap Assuming a locally redundant Business Critical instance survives a whole-zone outage because its additional database-engine replicas can remain in the same datacenter.

6 questions test this
A preprovisioned geo-replica avoids the provisioning uncertainty of backup-based regional recovery

Use a continuously maintained geo-replica or failover group for critical applications that cannot tolerate waiting for backup restoration and target capacity after a regional outage. Geo-restore avoids maintaining an additional running database, but its completion depends on backup availability, database size, regional restore demand, and available capacity. Treat published typical RTO and RPO values as planning guidance, not a guarantee that any database restore will meet a short application deadline.

Trap Selecting geo-restore solely because backups are geo-redundant when the application requires a ready failover target and predictable short downtime.

5 questions test this
Database regional redundancy is only one part of application continuity

Plan recovery for the web tier, identity dependencies, storage, DNS, and client routing alongside the database because a promoted database alone cannot restore an unavailable application. Components left in the original region can also add cross-region latency after database failover. Document how those components move or reconnect, and assess the combined recovery path against the application's RTO.

Trap Using database promotion time as the application's recovery time while its web front end remains dependent on the failed region.

5 questions test this
A user-initiated local failover tests client resilience without changing the DR region

Use Invoke-AzSqlDatabaseFailover or Invoke-AzSqlElasticPoolFailover to exercise the effect of an Azure SQL Database local failover on existing sessions and reconnection behavior. This is different from promoting a geo-secondary and is useful for validating retry handling before deployment. The service permits only one failover call per database or elastic pool every 15 minutes, so space repeated tests accordingly.

Trap Using a cross-region forced failover to test ordinary transient connection retries because it adds an unnecessary role change and data-loss risk.

5 questions test this
Replication does not replace historical recovery from backups

Availability replicas propagate committed changes, including an accidental destructive update or deletion, so another current copy is not automatically a pre-error recovery point. Retain recoverable backups for logical mistakes even when HA and regional replication are configured. Choose recovery to an earlier retained state for a logical error, rather than treating a healthy current replica as historical protection.

Trap Failing over to a synchronized replica after an accidental committed table deletion because the same deletion has already been replicated.

5 questions test this
Accelerated database recovery shortens engine recovery through persistent row versions

Accelerated database recovery (ADR) reduces restart-recovery and long-transaction rollback time by using persistent row versions and logical revert instead of undoing every row change from the transaction log. Its persistent version store (PVS) occupies space inside the database, so reserve capacity for retained versions; insufficient room to generate versions can make DML fail. ADR is always enabled in Azure SQL Database and Azure SQL Managed Instance, but changes engine recovery behavior rather than creating a remote DR copy. ADR can truncate the log aggressively despite long-running transactions as checkpoints and backups occur, but transactional replication, snapshot replication, and change data capture (CDC) disable this aggressive truncation so their log readers can collect changes. Frequent transaction aborts can also delay PVS cleanup, so fast rollback does not remove the need for version-storage capacity.

Trap Increasing only tempdb capacity to accommodate ADR versions for a user database because its persistent version store resides inside that user database.

A planned geo-failover is the appropriate drill when data loss is unacceptable

When both Azure SQL geo-replication partners are reachable, use planned failover to finish synchronization before swapping roles. A forced failover promotes the target without waiting for the inaccessible primary and can discard committed changes that were not replicated. Production failover-group drills should normally exercise the planned path; validation must include successful application reads and writes through the recovered environment, not just a completed control-plane operation.

Trap Choosing forced failover for a no-data-loss production drill because its faster role change bypasses the synchronization safeguard.

6 questions test this
Geo-restore drills should validate recovery and data-loss tolerance in a test environment

For a geo-restore drill, create a test copy of the production environment so that simulating loss of the source does not expose production changes to the recovery gap. Exercise restoration to another server, change the test application's connection target, and verify logins, basic functionality, and the recovered data state. A successful test must show both that the recovery procedure works and that the application tolerates the age of the available backup.

Trap Running a production cutover to a recent geo-backup as the first recovery rehearsal because the backup may omit changes already acknowledged to production clients.

5 questions test this
Returning service to the original region requires a deliberate failback procedure

After an Azure SQL geo-failover, the recovered original primary becomes a secondary and catches up to the new primary rather than automatically reclaiming the writable role. Plan a separate manual planned failback after replication is healthy so the return does not introduce another avoidable loss of data. For Managed Instance failover groups, check that both instances have completed the role change and returned to their normal replication status before attempting another switch.

Trap Using a second forced failover immediately after regional recovery because the original primary may not yet contain the changes accepted in the recovery region.

5 questions test this
Readable backup media does not prove that the full recovery procedure succeeds

RESTORE VERIFYONLY checks backup completeness, readability, and available checksums without restoring the database or fully checking its internal data structure. A recovery rehearsal must actually restore the required backup sequence, run integrity checks such as DBCC CHECKDB, and confirm usable application data. Rehearse the combinations in the recovery runbook and record elapsed recovery time rather than treating a backup job's success status as proof of recoverability.

Trap Accepting a successful RESTORE VERIFYONLY result as evidence that the database's structural integrity and end-to-end recovery time have been tested.

5 questions test this
An HA/DR test must demonstrate usable performance on the surviving topology

Test the proposed replica placement under representative write and reporting load because cross-zone or cross-site latency can change replication delay and commit performance. After failover, verify that the surviving compute, storage, and network can handle the production workload rather than merely accept a connection. Comparable replica capacity is especially important when the secondary must immediately become the primary without a time-consuming scale-up.

Trap Approving an undersized recovery replica after an idle connection test because the exercise never measured sustained production throughput.

6 questions test this

References

  1. https://learn.microsoft.com/en-us/azure/reliability/disaster-recovery-overview
  2. https://learn.microsoft.com/en-us/azure/azure-sql/database/business-continuity-high-availability-disaster-recover-hadr-overview
  3. https://learn.microsoft.com/en-us/credentials/certifications/resources/study-guides/dp-300
  4. https://learn.microsoft.com/en-us/azure/reliability/availability-zones-overview
  5. https://learn.microsoft.com/en-us/azure/azure-sql/managed-instance/business-continuity-high-availability-disaster-recover-hadr-overview
  6. https://learn.microsoft.com/en-us/sql/database-engine/sql-server-business-continuity-dr
  7. https://learn.microsoft.com/en-us/sql/database-engine/availability-groups/windows/availability-modes-always-on-availability-groups
  8. https://learn.microsoft.com/en-us/sql/t-sql/statements/alter-availability-group-transact-sql
  9. https://learn.microsoft.com/en-us/azure/azure-sql/virtual-machines/windows/business-continuity-high-availability-disaster-recovery-hadr-overview
  10. https://learn.microsoft.com/en-us/sql/database-engine/availability-groups/windows/contained-availability-groups-overview
  11. https://learn.microsoft.com/en-us/sql/database-engine/availability-groups/windows/active-secondaries-readable-secondary-replicas-always-on-availability-groups
  12. https://learn.microsoft.com/en-us/training/modules/describe-high-availability-disaster-recovery-strategies/4-describe-azure-high-availability-disaster-recovery-features
  13. https://learn.microsoft.com/en-us/azure/virtual-machines/availability-set-overview
  14. https://learn.microsoft.com/en-us/sql/relational-databases/sql-server-transaction-log-architecture-and-management-guide
  15. https://learn.microsoft.com/en-us/azure/site-recovery/site-recovery-overview
  16. https://learn.microsoft.com/en-us/sql/database-engine/availability-groups/windows/distributed-availability-groups
  17. https://learn.microsoft.com/en-us/azure/azure-sql/managed-instance/managed-instance-link-feature-overview
  18. https://learn.microsoft.com/en-us/azure/azure-sql/managed-instance/update-policy
  19. https://learn.microsoft.com/en-us/sql/relational-databases/replication/transactional/transactional-replication
  20. https://learn.microsoft.com/en-us/azure/azure-sql/managed-instance/replication-transactional-overview
  21. https://learn.microsoft.com/en-us/sql/relational-databases/replication/publish/filter-published-data
  22. https://learn.microsoft.com/en-us/azure/azure-sql/database/high-availability-sla-local-zone-redundancy
  23. https://learn.microsoft.com/en-us/azure/azure-sql/managed-instance/high-availability-sla-local-zone-redundancy
  24. https://learn.microsoft.com/en-us/azure/azure-sql/managed-instance/instance-zone-redundancy-configure
  25. https://learn.microsoft.com/en-us/azure/azure-sql/database/automated-backups-overview
  26. https://learn.microsoft.com/en-us/azure/azure-sql/database/active-geo-replication-overview
  27. https://learn.microsoft.com/en-us/azure/azure-sql/database/failover-group-sql-db
  28. https://learn.microsoft.com/en-us/azure/azure-sql/managed-instance/failover-group-sql-mi
  29. https://learn.microsoft.com/en-us/azure/azure-sql/database/recovery-using-backups
  30. https://learn.microsoft.com/en-us/azure/azure-sql/managed-instance/point-in-time-restore
  31. https://learn.microsoft.com/en-us/sql/relational-databases/accelerated-database-recovery-concepts
  32. https://learn.microsoft.com/en-us/azure/azure-sql/managed-instance/failover-group-standby-replica-how-to-configure
  33. https://learn.microsoft.com/en-us/azure/azure-sql/database/disaster-recovery-drills
  34. https://learn.microsoft.com/en-us/sql/t-sql/statements/restore-statements-verifyonly-transact-sql
  35. https://learn.microsoft.com/en-us/sql/relational-databases/backup-restore/back-up-and-restore-of-sql-server-databases
  36. https://learn.microsoft.com/en-us/sql/t-sql/statements/restore-statements-arguments-transact-sql
  37. https://learn.microsoft.com/en-us/sql/t-sql/database-console-commands/dbcc-checkdb-transact-sql