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.
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]):
- Local hardware or software failure affecting the database node.
- 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]).
- Regional outage affecting all the availability zones and datacenters of a region.
- 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.
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.
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_URLparameter 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.
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_outboundrule's destination service tag fromvirtualnetworktointernet.
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:
- Azure SQL Database: General Purpose, Business Critical and Hyperscale in the vCore (virtual core) purchasing model, and Premium in the DTU (database transaction unit) purchasing model; the Basic and Standard tiers don't support zone redundancy (Availability through local and zone redundancy - Azure SQL Database[22]). Because Business Critical and Premium place their existing replicas in different zones, enabling it there costs nothing extra, and Hyperscale additionally requires at least one high availability compute replica and zone-redundant or geo-zone-redundant backup storage (Availability through local and zone redundancy - Azure SQL Database[22]).
- Azure SQL Managed Instance: General Purpose and Business Critical; zone redundancy for Next-gen General Purpose is in preview and requires Azure Elastic SAN zone-redundant storage (ZRS), which some regions don't offer (Availability through local and zone redundancy - Azure SQL Managed Instance[23]). Before you enable it, the instance's backup storage redundancy must be zone-redundant or geo-zone-redundant; then you enable the setting, for example with
az sql mi update --zone-redundant true(Configure zone redundancy - Azure SQL Managed Instance[24]).
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:
- Azure SQL Database. PITR creates a new database on the same server that represents the state of the data before the corrupting event (Business continuity in Azure SQL Database[2]).
- Azure SQL Managed Instance. PITR serves incidents caused by error or failure, including deleted crucial data (Point-in-time restore - Azure SQL Managed Instance[30]).
- SQL Server. Microsoft states that the availability features don't replace a robust, well-tested backup and restore strategy, the most fundamental building block of any availability solution (Business continuity and database recovery - SQL Server[6]). Log shipping can add a delayed copy: because you can delay applying transaction log backups, the log-shipping secondary database might not yet contain an
UPDATEissued without aWHEREclause (Log shipping[6]).
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
SecondaryTypeparameter toStandby; 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.
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]):
- Simulate the outage, for example by renaming the source database so that application connections fail.
- 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.
- 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 scenario | Azure SQL Database | Azure SQL Managed Instance | SQL Server on Azure VMs |
|---|---|---|---|
| Local hardware or software failure | Built-in availability architecture: remote storage, local storage or Hyperscale model | Built-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 outage | Zone redundancy on General Purpose, Business Critical, Hyperscale or Premium; not Basic or Standard | Zone redundancy on General Purpose or Business Critical, after backup storage is zone-redundant or geo-zone-redundant | Replicas on VMs in different availability zones; an availability set stays in one datacenter |
| Regional outage | Failover group or active geo-replication to another region; geo-restore for slower, capacity-dependent recovery | Failover group with a secondary instance in a different region | Asynchronous-commit replica or distributed availability group in another region, log shipping, or Azure Site Recovery |
| Data corruption or deletion | Point-in-time restore from automated backups; replicas already carry the change | Point-in-time restore from automated backups | Restore from native backups; log shipping with a restore delay can keep a copy from before the error |
Decision tree
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
- You have an Azure SQL Database named db1 in a failover group named FG1 with a geo-secondary in another region. You create an Azure Monitor alert that fires when the geo-secondary falls more than 30 se
- You are reviewing a proposal for SQL1, which has a one-hour RTO that is currently missed because restoring backups takes three hours. The proposal changes log backups from every 30 minutes to every 5
- App1 has the following requirements: it must be available within 2 hours of an outage, at most 15 minutes of transactions may be lost, backups must be kept for 35 days, and maintenance runs in a weekl
- You run a DR test for App1, which uses a failover group named FG1. The database failover completes in 40 seconds, but App1 doesn't serve users again until its web tier restarts in the DR region 25 min
- You are writing the DR plan for App1, which uses an Azure SQL Database failover group named FG1. The business sets a recovery time objective of two hours. The database team says the two hours end when
- 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
- You change the Azure secondary replica of AG1, named SQLVM6, from asynchronous commit to synchronous commit. Since the change, commit times on the primary replica, SQLVM5, have roughly doubled. You ne
- You plan a replica for db1 in a second datacenter 900 km away. Round-trip latency is 25 ms. The business requests zero data loss but also requires that commit response times don't increase. You need t
- An availability group named AG1 has its primary replica on SQL1 on-premises and an asynchronous-commit secondary replica on SQLVM5 in Azure. The on-premises site fails and will stay offline for severa
- You have an availability group named AG1 with a synchronous-commit secondary on SQLVM2 in the same region. The secondary is in the SYNCHRONIZED state. You need to describe what this configuration guar
- You have an on-premises availability group named AG1 for an OLTP application that is sensitive to commit latency. You plan to add a DR replica on an Azure VM. The network round-trip time to Azure is 4
- 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
- You have SQL Server Enterprise edition on SQLVM1. The instance hosts App2's two databases and 14 databases for other applications. A colleague proposes a failover cluster instance so that App2's datab
- You have SQL Server 2022 Standard edition on SQLVM1 and SQLVM2. You need to protect a database named db1 with automatic failover. A reporting team also asks for read-only queries against the secondary
- You have SQL Server 2019 Standard edition on two Azure VMs named SQLVM3 and SQLVM4. A single database named Sales must fail over automatically to SQLVM4 if SQLVM3 fails. No secondary reads are needed,
- You plan an availability group named AG1 on SQL Server 2022 for App1. The App1 databases rely on several SQL logins and SQL Server Agent jobs. You need those logins and jobs to be available on whichev
- You have a two-node failover cluster instance named FCI1 on Azure VMs in one region that uses Azure shared disks. A new requirement states that the databases must be recoverable from an independent co
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
- You have a distributed availability group named DAG1. AG1 on-premises is the global primary, and AG2 in Azure has a primary replica on SQLVM3 and a secondary replica on SQLVM4. You need to determine h
- Your company runs an availability group named AG1 on Linux with Pacemaker on-premises and plans an availability group named AG2 on Windows Server failover clusters in Azure. The on-premises team and t
- You manage a distributed availability group named DAG1 that links AG1 and AG2. Each availability group runs on its own Windows Server failover cluster. You need to view and change the configuration of
- You have an availability group named AG1 on an on-premises Windows Server failover cluster and an availability group named AG2 on a separate cluster in Azure. You need to link AG1 and AG2 by running C
- You have a distributed availability group named DAG1. AG1 in your datacenter is the global primary, and AG2 in Azure is the secondary availability group. A reporting app in Azure connects through AG2'
- 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
- You plan log shipping for db4 from SQL4 in your on-premises datacenter to SQLVM4 on an Azure VM for cross-site DR. The backup job will write log backups to a share on SQL4's network. The network team
- You plan to add an Azure replica to an on-premises domain-based availability group named AG1. The solution must meet the following requirements: - The cluster nodes must communicate privately across s
- You plan to add a replica in an Azure virtual network to an on-premises domain-based availability group named AG1. The cluster nodes on-premises and in Azure must communicate privately across the two
- You have a hybrid availability group named AG1 with a listener named AG1-L. DNS is hosted only on two on-premises DNS servers. During a DR test, AG1 fails over to the Azure replica, but clients in Azu
- You plan to extend an on-premises availability group named AG1 on Windows by adding a secondary replica on an Azure VM, instead of creating a second availability group. The on-premises nodes and the A
- Managed Instance link recovery capabilities depend on the SQL Server version and update policy
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
- You use Managed Instance link to replicate db1 from SQL1, which runs SQL Server 2019, to an Azure SQL Managed Instance named SQLMI1. You fail over db1 to SQLMI1 to complete a migration. You need to id
- You have SQL2, which runs SQL Server 2025 Standard edition and hosts db2. An existing Azure SQL Managed Instance named SQLMI2 in the General Purpose tier uses the SQL Server 2022 update policy. You pl
- You need to design DR between an on-premises SQL Server and Azure SQL Managed Instance by using Managed Instance link. The solution must meet the following requirements: - Allow failover to Azure and
- You have SQL1, which runs SQL Server 2022, and an existing Azure SQL Managed Instance named SQLMI1 that uses the Always-up-to-date update policy. You plan to replicate db1 to SQLMI1 by using Managed I
- 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
- You have a SQL Server VM named SQLVM1 that runs Standard edition and has custom OS settings and installed agents. You need DR in another region that recovers the whole VM, not only the databases. The
- You protect SQLVM1, which runs SQL Server, by using Azure Site Recovery replication to a DR region. The DR plan requires that each recovery point used for a failover captures the disk data together wi
- A consultant proposes Azure Site Recovery with a recovery plan for SQLVM1 and its app VMs and claims that it gives db1 a synchronized secondary with no data loss on failover. db1 has a business RPO of
- You use Azure Site Recovery to replicate SQLVM1, which runs SQL Server, and two web VMs named Web1 and Web2 to a DR region. During a regional failover, SQLVM1 must start first, a script must then upda
- You protect SQLVM1 and its application VMs with Azure Site Recovery and a recovery plan named RP1. Auditors require a quarterly rehearsal of RP1 in the DR region. Production replication must continue
- 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.
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
- You have one SQL Server VM named SQLVM7 that is pinned to availability zone 1 and hosts db7. The operations lead assumes that a zonal VM fails over to another zone on its own. You need db7 to stay ava
- SQLVM8 and SQLVM9 host an availability group in an availability set with two fault domains. A new policy requires surviving the loss of a whole datacenter in the same region, which supports availabili
- You have an availability group named AG1 with synchronous-commit replicas on SQLVM1 in zone 1 and SQLVM2 in zone 2 of the West Europe region. A new requirement states that the databases must remain re
- Your company is building a new SQL Server deployment on two Azure VMs in the Central US region for an availability group named AG2. The design must keep running if power, cooling, or networking fails
- You plan an availability group for App4 on two SQL Server VMs in an Azure region that has no availability zones. The design must reduce the chance that a hardware failure in one rack or a planned host
- You are planning SQL Server HA/DR on Azure VMs for an availability group named AG3 in North Europe. The solution must meet the following requirements: - Fail over automatically with no data loss if on
- 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
- Yesterday the SQL Server service on SQLVM2 stopped responding for 45 minutes. Azure reported SQLVM2 as running and healthy the whole time, and no automatic recovery started. You need to explain why Az
- A project manager states that placing SQLVM1 and SQLVM2 in an availability set gives a 99.95 percent SLA, so the SQL Server instances on them need no further HA configuration to meet App2's RTO. Each
- SQLVM2 hosts one database named db2. Last week, the SQL Server service on SQLVM2 hung for 30 minutes while Azure reported the VM as healthy. You need db2 to fail over automatically to a second VM when
- SQLVM1 and SQLVM2 in East US run the SQL Server instance for App9, which relies on instance-level logins and many SQL Server Agent jobs. Last month, the SQL Server service failed while its VM stayed r
- 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
- You have a General Purpose Azure SQL Database named db10 in Canada Central. A reporting app in Canada Central needs a read-only copy of db10 that stays current within seconds and has the lowest networ
- A colleague designed db1 with its primary on Server1 and a geo-secondary on Server2. Both logical servers are in Australia East. The colleague claims this design provides both zone isolation and regio
- You have an Azure SQL Database named db1 on a logical server in East US. A geo-secondary of db1 on another logical server in East US serves reporting queries. You need to ensure that db1 can be recove
- You have an Azure SQL Database named db1 in Central US. A geo-secondary on another logical server in Central US serves read-only reporting for App2. Management now requires a failover target for a reg
- You have a Business Critical Azure SQL Database named db9 in France Central with a geo-secondary on another logical server in France Central. db9 must keep running through an availability zone failure
- 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
- You are choosing a vCore service tier for an Azure SQL Database named db11 that runs an order-entry workload with a very high transaction rate. During failovers and maintenance, the database must suff
- You have a Hyperscale Azure SQL Database named db12 that has zero high-availability replicas. After a hardware failure on the primary compute replica, recovery took longer than expected. The same data
- You have a Business Critical Azure SQL Managed Instance named SQLMI1. During failover tests, the workload resumes quickly with little performance impact. To reduce cost, the team proposes moving SQLMI
- You have a General Purpose Azure SQL Database named db1. During an unplanned failover, the compute node that hosts db1 fails and the service starts a new database engine process on another node. You n
- 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
- You have a Hyperscale Azure SQL Database named db14 that was created without zone redundancy. It has one high-availability replica and uses geo-zone-redundant backup storage. You need db14's workload
- You have a Standard S6 Azure SQL Database named db1. To meet a requirement that db1 keeps serving App1 during a zone outage, an engineer changes db1's backup storage redundancy to zone-redundant stora
- You have a General Purpose Azure SQL Database named db1 on a logical server named SQL1 in a region that supports availability zones. db1 isn't zone-redundant. You need to make db1 zone-redundant by us
- You have a zone-redundant Hyperscale Azure SQL Database named db13 in a region with three availability zones. App13 commits orders to db13 around the clock. An auditor asks what happens to committed t
- 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
- An engineer changed the backup storage redundancy of SQLMI6, a General Purpose Azure SQL Managed Instance, to GZRS. The engineer now reports SQLMI6 as protected against a whole-zone outage. Zone redun
- You have a Business Critical Azure SQL Managed Instance named SQLMI1 that uses local redundancy. A colleague says SQLMI1 already survives a whole-zone outage because Business Critical keeps several da
- SQLMI4 is a General Purpose Azure SQL Managed Instance in resource group RG4. Its backup storage redundancy is already set to zone-redundant storage, and its region supports zone redundancy. In Azure
- You have a locally redundant Business Critical Azure SQL Managed Instance named SQLMI2. It must stay available through the loss of an entire availability zone in its region, without failing over to an
- You plan a Next-gen General Purpose Azure SQL Managed Instance named SQLMI5 that must be zone-redundant. The target region supports zone redundancy for SQL Managed Instance in general. You need to con
- You have a General Purpose Azure SQL Managed Instance named SQLMI3 in a region that supports zone redundancy. SQLMI3 uses locally redundant backup storage. You must enable zone redundancy for SQLMI3 s
- 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
- You have an Azure SQL Database named db1 that supports a payment application. The application must be back online within 30 minutes of a regional outage in West Europe, with predictable downtime. Back
- You have an Azure SQL Database named db1 that is 3 TB. The documentation lists a typical RTO for geo-restore. During a large regional outage, a restore of db1 takes much longer than that value. You ne
- You have an Azure SQL Database named pay1 for a payments service in East US 2. To save cost, the team proposes creating a geo-secondary of pay1 in West US only after an outage in East US 2 is declared
- Your team proposes meeting a one-hour regional RTO for an Azure SQL Database named db1 by scripting geo-restore with Bicep and Azure CLI, which avoids paying for a secondary database. db1 is 1.5 TB. Y
- You have an Azure SQL Database named arch2 for an internal archive app in East US 2. After a regional outage, the business accepts recovery from the most recent geo-replicated backups and a recovery t
- 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
- App6 runs on web servers in East US and uses db6, which is in a failover group with West US. An outage hit only SQL Database in East US, so db6 failed over and is now primary in West US. SQL Database
- You have App1 deployed in East US and West US. Its Azure SQL Database named db1 is in a failover group across the two regions. You need users to be routed automatically to the West US web front end wh
- App1 runs on web servers in East US and uses an Azure SQL Database named db1 in a failover group with West US. During a DR test, db1 fails over successfully, but users still can't use App1 because Eas
- App1 stores uploaded documents in a storage account in East US and their metadata in an Azure SQL Database named db1 that is in a failover group with West US. You need to ensure that App1 can serve do
- Your DR runbook states that App1's RTO is met because its failover group promotes db1 in under one minute. App1's web front end and its identity configuration are deployed only in the primary region.
- 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
- You have a zone-redundant General Purpose Azure SQL Database named db1. You run Invoke-AzSqlDatabaseFailover to test App1. Besides session reconnects, you need to check another effect that this test c
- Pool7 is an elastic pool that holds 15 databases for App8. Before release, the test plan requires one user-initiated local failover of the whole pool as a single operation, so that all 15 databases fa
- You have a single Azure SQL Database named db7 on a logical server named SQL7. Before App7 goes live, you need to test how App7's retry logic handles a user-initiated failover of db7 within its region
- A developer plans to test App1's handling of brief connection drops by running a forced failover of db1's failover group to the secondary region every week. App1 only needs to handle transient reconne
- You test App1's retry logic by running Invoke-AzSqlDatabaseFailover against db1, a General Purpose Azure SQL Database whose primary must stay in East US. Five minutes later, you run the same command a
- 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
- You have an Azure SQL Database named db1 that is zone-redundant and in a failover group with another region. To cut costs, a manager proposes reducing db1's short-term backup retention to one day beca
- You are designing protection for an Azure SQL Database named db1. The solution must survive a zone outage, recover from a regional outage, and recover the database to a point before an accidental bulk
- You have an Azure SQL Database named db12 with a geo-secondary in another region. Short-term retention is 35 days, and long-term retention keeps weekly full backups for 12 weeks. Auditors find that a
- You have an availability group named AG6 with a synchronous-commit secondary on SQLVM9 that is SYNCHRONIZED. A developer drops Table9 in db9 on the primary, and the transaction commits. A colleague su
- db14 runs in an availability group on SQL Server VMs. It uses the FULL recovery model, with log backups every 10 minutes. At 14:32, a developer ran an UPDATE without a WHERE clause on dbo.Rates, and t
- 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
- You have a failover group named FG1 for an Azure SQL Database named db1. Both the primary and secondary servers are healthy. You must run a production DR drill each quarter, and the business doesn't a
- During a DR drill, you start a planned failover of a failover group named FG1. Replication lag to the secondary is higher than usual because of a heavy batch load on the primary. The failover takes lo
- You plan a DR drill for a failover group named FG1 on the logical server Server1. Both servers in FG1 are reachable. You will run Switch-AzSqlDatabaseFailoverGroup against the secondary server, and th
- You have a failover group named FG1 for db1 and db2 on Azure SQL Database. The solution must meet the following requirements: - Run a quarterly DR drill in production. - Lose no committed data during
- Your team plans a DR drill for a failover group named FG1. A team member proposes a forced failover because it switches roles faster than a planned failover. Both regions are healthy. You need to expl
- You run a planned failover of a failover group named FG1 during a DR drill. The Azure portal shows that the operation completed and that the secondary server is now the primary. You need to confirm th
- 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
- You run a geo-restore drill against a test copy named sales1test on Server5. You need to simulate the outage so the test application gets connectivity failures. The simulation must be easy to reverse
- Your team proposes that the first geo-restore rehearsal for an Azure SQL Database named db1 should be a production cutover to the most recent geo-replicated backup, so the test is realistic. You need
- Your team schedules its first geo-restore drill for an Azure SQL Database named sales1 that App5 uses in production. The drill must avoid any data loss for production users and must follow the documen
- During a geo-restore drill, you restore a test copy of db1 to a server named Server3 in another region. The test instance of App1 still points to the original test server. You need to complete the nex
- You finish a geo-restore drill for a test copy of db1. The test application now connects to the restored database on another server. The business RPO for db1 is two hours. You need to decide whether t
- 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
- You have a failover group named FOG1 for Azure SQL Managed Instances named SQLMI1 and SQLMI2. You performed a failover from SQLMI1 to SQLMI2 one minute ago, and DNS already points to SQLMI2. You plan
- Your failover group FG1 failed over to West US during an outage. East US has just come back online, and its database is still catching up to the West US primary. A manager asks you to fail back right
- After a regional outage, you forced FG1 to fail over from East US to West US. East US is healthy again, and its database is now a synchronized secondary. You need to move the primary role back to East
- You have a failover group named FG1 for db1. During a regional outage in East US, you performed a forced failover to the secondary in West US. The East US region has now recovered. You need to identif
- After a regional outage, you forced a failover group named FG1 from East US to West US, and App1 has written to West US for six hours. East US recovers ten minutes later. A colleague proposes an immed
- 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
- A nightly job on SQLVM3 runs RESTORE VERIFYONLY ... WITH CHECKSUM against the latest full backup of db3, and it always succeeds. An auditor asks you to prove that the data in that backup has no struct
- You are writing a quarterly recovery rehearsal plan for db1 on SQL Server. The plan must prove that the database can be recovered and used within the RTO. Recovery uses full, differential, and log bac
- An auditor reviews your backup evidence for db1. Each night, a job runs RESTORE VERIFYONLY WITH CHECKSUM on the latest full backup and records success. The auditor accepts this as proof that db1 is re
- A database administrator runs RESTORE VERIFYONLY FROM DISK against a full backup of db1 that was taken WITH CHECKSUM. The command succeeds. The backup is stored on a network share. You need to describ
- Your recovery runbook for db1 on SQLVM1 uses one full backup, one differential backup, and 24 log backups stored in Azure Blob Storage. The RTO is two hours. Nightly RESTORE VERIFYONLY checks of each
- 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
- During a DR load test of an Azure SQL Database named db17, write throughput on the primary drops. Queries show the HADR_THROTTLE_LOG_RATE_MISMATCHED_SLO wait type in sys.dm_exec_requests. You need to
- A DR test for an Azure SQL Database named db1 opened one idle connection to its geo-secondary after failover, and the team approved the design. The secondary has half the compute of the primary. You n
- In production, App15 writes orders to an Azure SQL Database named db15 while its reports run on db15's readable geo-secondary. The draft DR test replays only the order writes against the primary. The
- You fail over a failover group named FG1 during a DR test. App1 connects successfully to the new primary in West US. The test plan must prove that the recovered environment can run the production work
- Order processing on SQLVM1 commits to an availability group named AG5. You plan to move its synchronous-commit secondary from the same zone as the primary to another availability zone. The order team
- You are writing an HA/DR test procedure for db16 with a replica at a remote site. The procedure must meet the following requirements: - Measure how the replica placement affects commit performance. -
References
- https://learn.microsoft.com/en-us/azure/reliability/disaster-recovery-overview
- https://learn.microsoft.com/en-us/azure/azure-sql/database/business-continuity-high-availability-disaster-recover-hadr-overview
- https://learn.microsoft.com/en-us/credentials/certifications/resources/study-guides/dp-300
- https://learn.microsoft.com/en-us/azure/reliability/availability-zones-overview
- https://learn.microsoft.com/en-us/azure/azure-sql/managed-instance/business-continuity-high-availability-disaster-recover-hadr-overview
- https://learn.microsoft.com/en-us/sql/database-engine/sql-server-business-continuity-dr
- https://learn.microsoft.com/en-us/sql/database-engine/availability-groups/windows/availability-modes-always-on-availability-groups
- https://learn.microsoft.com/en-us/sql/t-sql/statements/alter-availability-group-transact-sql
- https://learn.microsoft.com/en-us/azure/azure-sql/virtual-machines/windows/business-continuity-high-availability-disaster-recovery-hadr-overview
- https://learn.microsoft.com/en-us/sql/database-engine/availability-groups/windows/contained-availability-groups-overview
- https://learn.microsoft.com/en-us/sql/database-engine/availability-groups/windows/active-secondaries-readable-secondary-replicas-always-on-availability-groups
- https://learn.microsoft.com/en-us/training/modules/describe-high-availability-disaster-recovery-strategies/4-describe-azure-high-availability-disaster-recovery-features
- https://learn.microsoft.com/en-us/azure/virtual-machines/availability-set-overview
- https://learn.microsoft.com/en-us/sql/relational-databases/sql-server-transaction-log-architecture-and-management-guide
- https://learn.microsoft.com/en-us/azure/site-recovery/site-recovery-overview
- https://learn.microsoft.com/en-us/sql/database-engine/availability-groups/windows/distributed-availability-groups
- https://learn.microsoft.com/en-us/azure/azure-sql/managed-instance/managed-instance-link-feature-overview
- https://learn.microsoft.com/en-us/azure/azure-sql/managed-instance/update-policy
- https://learn.microsoft.com/en-us/sql/relational-databases/replication/transactional/transactional-replication
- https://learn.microsoft.com/en-us/azure/azure-sql/managed-instance/replication-transactional-overview
- https://learn.microsoft.com/en-us/sql/relational-databases/replication/publish/filter-published-data
- https://learn.microsoft.com/en-us/azure/azure-sql/database/high-availability-sla-local-zone-redundancy
- https://learn.microsoft.com/en-us/azure/azure-sql/managed-instance/high-availability-sla-local-zone-redundancy
- https://learn.microsoft.com/en-us/azure/azure-sql/managed-instance/instance-zone-redundancy-configure
- https://learn.microsoft.com/en-us/azure/azure-sql/database/automated-backups-overview
- https://learn.microsoft.com/en-us/azure/azure-sql/database/active-geo-replication-overview
- https://learn.microsoft.com/en-us/azure/azure-sql/database/failover-group-sql-db
- https://learn.microsoft.com/en-us/azure/azure-sql/managed-instance/failover-group-sql-mi
- https://learn.microsoft.com/en-us/azure/azure-sql/database/recovery-using-backups
- https://learn.microsoft.com/en-us/azure/azure-sql/managed-instance/point-in-time-restore
- https://learn.microsoft.com/en-us/sql/relational-databases/accelerated-database-recovery-concepts
- https://learn.microsoft.com/en-us/azure/azure-sql/managed-instance/failover-group-standby-replica-how-to-configure
- https://learn.microsoft.com/en-us/azure/azure-sql/database/disaster-recovery-drills
- https://learn.microsoft.com/en-us/sql/t-sql/statements/restore-statements-verifyonly-transact-sql
- https://learn.microsoft.com/en-us/sql/relational-databases/backup-restore/back-up-and-restore-of-sql-server-databases
- https://learn.microsoft.com/en-us/sql/t-sql/statements/restore-statements-arguments-transact-sql
- https://learn.microsoft.com/en-us/sql/t-sql/database-console-commands/dbcc-checkdb-transact-sql