HADR architecture for database platforms: RTO, RPO, SQL Server, Azure PaaS, and hybrid solutions
Translate business recovery objectives into high-availability and disaster-recovery designs for SQL Server on Azure Virtual Machines, Azure SQL services, and hybrid environments.
Suggested study time: 82 minutes • Intermediate • Complete original rewrite with a concise summary for every topic
By João Ricardo Dutra••Complete original content
1. HADR begins with requirements, not with a product
High availability and disaster recovery (HADR) for a database platform is an architectural strategy, not a checkbox. Before selecting SQL Server or Azure features, identify the interruption the business can tolerate, the data it can afford to lose, the scope of failure to survive, and who will operate the response.
A feature can be technically valid yet fail the business objective. The design must connect recovery targets, application dependencies, data protection, failover behavior, networking, and regular validation.
Topic summary
Start with business recovery requirements and failure scope; choose technology only after the target state is measurable.
2. Recovery Time Objective limits acceptable outage duration
Recovery Time Objective (RTO) is the maximum time allowed to restore a resource or solution after an outage. Exceeding it can stop work, breach a service commitment, create financial loss, or trigger penalties.
Record both component RTOs and an end-to-end RTO. If SQL Server returns in five minutes but application servers require 20 minutes, users still wait 20 minutes; the slowest critical dependency governs the effective recovery time.
Topic summary
RTO answers how long the service may remain unavailable and must be measured across the complete dependency chain.
3. Recovery Point Objective limits acceptable data loss
Recovery Point Objective (RPO) defines the latest acceptable recovery point and therefore the maximum age of data that the business can lose. If an outage occurs at 10:00 and the RPO is 15 minutes, recovery must reach 09:45 or later.
RPO is a data objective, not a promise made by a product name. Replication delay, transaction-log backup frequency, copy time, restore behavior, workload rate, and consistency of the chosen recovery point determine whether it is achievable.
RPO looks backward from the incident to the latest acceptable data point; RTO looks forward to the service restoration deadline.
Topic summary
RPO answers how far back data may be recovered; every protection and transfer interval must fit inside that loss budget.
4. Set objectives through a business and technology negotiation
Zero downtime and zero data loss are attractive but frequently expensive or technically unrealistic. Business owners, application teams, database specialists, infrastructure operators, security, and finance should agree on objectives with the same facts.
Quantify the cost of downtime and the impact of lost transactions.
Make HADR investment proportional to the loss it prevents.
Include administrator skill, automation maturity, support coverage, and dependency recovery time.
Document separate targets for each critical component and the entire solution.
Review the targets periodically as workloads and business consequences change.
Topic summary
RTO and RPO are negotiated service requirements backed by cost, risk, operational capability, and dependency evidence.
5. Protection schedules and recovery tests must support the targets
A policy can contradict its objective. A transaction-log backup every 30 minutes cannot reliably satisfy a 15-minute RPO, and a backup that takes three hours to copy cannot support a two-hour recovery path. The architecture must include enough bandwidth, frequency, automation, and capacity.
Backups and replicas are only useful when they can be recovered. Rehearse planned failover, unplanned failover, restore, failback, client reconnection, and dependency recovery; record observed RTO and RPO rather than relying solely on configuration.
Topic summary
Align schedules and transfer times with the targets, then prove them through recurring recovery exercises.
6. High availability and disaster recovery need separate objectives
Availability scopes and expected response.
Dimension
High availability
Disaster recovery
Typical failure
Localized node, process, host, rack, or zone event
Region, data center, site, or broad dependency loss
Recovery pattern
Fast failover to an already running local replica or node
Activate another location and restore complete service dependencies
Typical time scale
Seconds or minutes
Minutes, hours, or longer
Objective
Maintain local continuity
Re-establish service after a large-scale event
One design can contribute to both scopes, but local HA should not be mistaken for regional DR. Document independent RTO and RPO values for each.
Topic summary
HA handles localized interruption; DR restores service after a wider failure, so each requires its own targets and tests.
7. IaaS and PaaS divide control and responsibility differently
SQL Server on Azure is IaaS: the operating system and SQL Server instance are visible, and administrators can combine database-engine, cluster, storage, network, and Azure platform features. This flexibility also increases design and operational responsibility.
and are PaaS. Microsoft operates the underlying nodes and exposes a smaller set of service-level availability and disaster-recovery controls. The architecture focuses more on service configuration, client resilience, and regional topology.
Topic summary
IaaS offers maximum HADR control with more operational ownership; PaaS embeds infrastructure recovery and exposes governed service options.
8. Instance-level and database-level protection cover different objects
SQL Server protection scope.
Feature
Protected unit
Key consequence
Always On failover cluster instance (FCI)
Entire SQL Server instance
Databases and instance objects move together, but shared storage is required
Always On availability group (AG)
Selected user databases
Each replica holds a database copy; instance-level objects need separate synchronization
Log shipping
Selected user databases
Backup, copy, and restore protect data; server abstraction and outside objects remain manual
Logins, SQL Server Agent jobs, linked servers, and other objects outside a protected user database do not automatically follow database-level protection. Account for them in deployment and failover runbooks.
Topic summary
Know the protected unit: database-level technologies do not automatically reproduce every object needed by the SQL Server instance.
9. Availability groups and FCIs depend on an underlying cluster
On Windows Server, Always On AGs and FCIs use Windows Server Failover Cluster (WSFC); on Linux, the common cluster manager is Pacemaker. Quorum and witness placement are part of the availability design, especially when nodes span locations.
Active Directory Domain Services and DNS must be reachable wherever Windows-based cluster identities and name resolution depend on them. A cross-region or hybrid topology therefore needs more than SQL replicas.
Topic summary
Cluster health, quorum, witnesses, directory services, and DNS are first-class HADR dependencies.
10. A failover cluster instance protects the whole installation
An FCI is created during SQL Server installation; an existing standalone instance cannot simply be converted into one. It receives a network name and address distinct from its nodes and cluster. Clients connect to this stable identity. In a traditional single-subnet Azure design, an internal load balancer helps route connections to the active node.
During failover, the entire instance stops and starts on another node. Existing sessions disconnect, the databases run recovery, incomplete transactions are rolled back, and resilient clients reconnect after the instance is ready. Because one database copy is shared, committed data remains consistent through the failover.
Topic summary
FCI failover restarts the complete instance on another node behind a stable network identity, protecting instance-level state.
11. FCI shared storage is both an enabler and a risk
Every node must access the same database storage. Supported architectural choices can include Azure Premium file shares, iSCSI, Azure Shared Disk, Spaces Direct, or a supported third-party replication product such as SIOS DataKeeper. The exact choice changes latency, quorum, supportability, cost, and failure behavior.
One database copy reduces storage multiplication but creates a storage dependency. Standard Edition supports at most two FCI nodes. Windows Server Replica can extend some Windows FCI designs for disaster recovery, while log shipping or an AG can also protect an FCI at another location.
Topic summary
FCIs simplify instance protection but require a carefully supported shared-storage and cluster architecture.
12. Availability groups protect independent database copies
An AG has a read/write primary replica and one or more secondary replicas that receive transaction-log changes. Data movement can be synchronous for low data loss or asynchronous where latency and distance make synchronous commit unsuitable. A listener gives applications a stable connection target.
Standard Edition supports a basic AG with one database and up to two replicas. Enterprise Edition supports multiple databases and up to nine replicas, including readable secondaries that can offload reporting, backups, or consistency checks. Secondaries are initialized from backup or automatic seeding.
Topic summary
AGs replicate selected databases to independent replicas and use a listener to decouple clients from the active primary.
13. AGs trade storage cost for failover flexibility
Because every replica stores its own database copy, AGs avoid shared storage and often fail over faster than FCIs, but storage consumption grows with replica count. Five replicas of a 1-TB database require roughly 5 TB before other overhead.
The new primary still needs synchronized logins, jobs, linked servers, credentials, and other external objects. Windows authentication, contained databases, infrastructure as code, and controlled synchronization can reduce this operational gap.
Topic summary
AGs remove shared storage and add usable replicas, but multiply storage and require explicit handling of instance-level dependencies.
14. Log shipping is a simple warm-standby pattern
Log shipping begins with a full database backup restored to a secondary in STANDBY or NORECOVERY. Scheduled jobs then back up the primary transaction log, copy each file, and restore it on the secondary. Its backup-copy-restore sequence is easy to understand and tolerant of weaker networks.
It normally serves DR rather than instantaneous HA. Unplanned activation can lose transactions after the last applied log backup, and the product supplies no listener abstraction. DNS aliases or another network-layer technique can reduce the client name change.
Topic summary
Log shipping supplies economical database-level DR through recurring backup, copy, and restore, with a measurable data-loss interval and manual activation.
15. Availability sets reduce correlated host failures in one data center
An availability set applies anti-affinity so related VMs are distributed across fault domains and update domains. Fault domains separate shared power and network failure boundaries; update domains separate groups that Azure may reboot together during maintenance. The module models as many as three fault domains in a data center.
Availability sets separate VMs across fault and update domains inside one data center.
Topic summary
Availability sets protect VM replicas from correlated hardware and maintenance events within a data center.
16. Platform placement does not repair failures inside the guest
Availability sets and zones do not understand SQL Server transactions and do not recover an operating-system or database-engine crash. Combine Azure placement with AG, FCI, log shipping, backup, or another workload-aware design to meet data objectives.
A multitier application should isolate each tier appropriately—for example, separate placement strategies for web, database, and AD DS VMs. An individual VM cannot simultaneously belong to an availability set and an availability zone.
Topic summary
Azure placement limits infrastructure blast radius; workload-aware protection is still required for guest and data failures.
17. Availability zones protect against data-center failure
A zone is a physically separate location within a supported Azure region. Placing replicas in zones 1, 2, and 3 can survive a data-center-level loss. Zone numbers are logical per subscription, so two customers’ “zone 1” labels do not prove physical co-location.
Zonal distance can add network latency. Test the workload rather than assuming synchronous commit will meet performance goals; round-trip latency is often below one millisecond, but the actual path and workload determine suitability. can also use zone redundancy.
Topic summary
Zones widen fault isolation to the data-center level, but their latency must be validated against synchronous replication and workload targets.
18. replicates the VM, not the database protocol
can continuously replicate an Azure VM from one region to another and orchestrate failover and failback. It protects many workloads and is useful when a VM-centric recovery method satisfies the objectives.
The service does not interpret SQL Server transaction boundaries. A VM may recover within the desired RTO while its data point falls outside the database RPO. The supplied module cited a two-hour monthly RTO; current Azure documentation states a one-hour VM failover RTO SLA. Treat published objectives as time-sensitive inputs and validate the complete application and data recovery path.
Topic summary
Site Recovery provides platform-level regional VM recovery; database consistency and RPO still require workload-aware validation.
19. PaaS includes local high availability
provides a 99.99% availability SLA for supported configurations and uses built-in node failover. Committed transactions are synchronously persisted to storage. If a node fails, the service creates or activates another compute node and attaches the data storage.
Connections and in-flight transactions can be interrupted during that transition. Cloud applications therefore need bounded retry logic, idempotent operations where appropriate, and timeout handling for transient failures.
Topic summary
PaaS hides node replacement, but applications must still tolerate dropped connections and transient failures.
20. Geo-replication and failover groups address regional PaaS recovery
supports active geo-replication, while failover groups can coordinate databases for or . Readable replicas can offload reporting and provide a secondary regional copy.
A failover-group listener keeps read/write and read-only connection endpoints stable across a geo-failover. Region choice, replication lag, failover policy, application dependencies, and failback procedure still belong in the architecture.
Topic summary
Use active geo-replication or failover groups to extend recovery across regions while preserving a manageable client endpoint.
and do not expose states such as OFFLINE or EMERGENCY in the same way as self-managed SQL Server. Azure operates the service, while capabilities such as RESTRICTED_USER and a dedicated administrator connection remain available where supported.
Accelerated Database Recovery (ADR) uses a persisted version store and aggressive log truncation to make rollback of long transactions and database restart recovery more predictable. It is enabled by default in these services and contributes to availability.
Topic summary
ADR reduces the recovery penalty of long transactions and supports the managed service availability model.
Azure maintains multiple local and regional data copies and backups, performs backup/restore integrity validation, and detects stale reads or lost writes. CHECKSUM is enabled by default, DBCC CHECKDB can be run without repair, and automatic page repair is attempted when possible.
The platform can repair without notification when there is no customer impact and issue proactive notification when impact exists. These controls reduce risk but do not remove the need for application-level validation and recovery exercises.
Topic summary
Redundancy, integrity checks, page repair, and alerting work together to protect consistency in managed services.
23. Single-region AG is a common database-level HA design
A primary and secondary SQL Server VM can be placed on separate Azure failure boundaries, joined to a WSFC, and exposed through an AG listener. Separate database copies protect the data and allow planned patching with a controlled role change.
A single-region AG combines Azure VM placement with SQL Server database-level replication.
Multiple data copies reduce a single database-storage failure.
Synchronous commit can provide minimal or no committed-data loss.
The listener standardizes primary and secondary access.
No shared database storage is required.
Topic summary
A single-region AG is a strong HA pattern when the database needs independent copies, fast failover, and a stable listener.
An FCI across two SQL Server VMs can use Spaces Direct or another supported shared-storage pattern, a WSFC, a virtual network, and client-routing infrastructure. It remains useful when instance-level protection is more important than independent database copies.
The FCI keeps one shared database copy and moves the complete SQL Server instance between nodes.
Applications use one clustered instance identity.
Patching can move service between nodes.
Many HA targets are achievable, but this topology alone does not provide regional DR.
Shared storage and its support model remain critical design points.
Topic summary
Choose an FCI when instance-level continuity and a shared copy fit the workload, and add a separate mechanism for regional DR.
25. A multi-region or hybrid AG can provide HA and DR
A traditional AG can span Azure regions or connect an on-premises location to Azure while every replica remains in one WSFC. This resembles an AG stretched across two data centers and works with Standard or Enterprise Edition within their replica limits.
A stretched AG uses one cluster and one availability group across regional or hybrid locations.
Reliable connectivity, quorum, witness placement, AD DS, and DNS at every required location are essential. A network partition can turn the single stretched cluster into the central failure concern.
Topic summary
A stretched AG can cover HA and DR with one database feature, but the shared cluster and cross-site dependencies must remain healthy.
26. A distributed AG separates cluster failure domains
A distributed availability group, introduced in SQL Server 2016 Enterprise Edition, joins two independent AGs rather than placing all replicas in one WSFC. The global primary sends changes to a forwarder, which is also the primary of the second AG and synchronizes its local secondary.
A distributed AG is an AG of AGs, separating quorum and witness management by location.
Each cluster owns its quorum and witness. Synchronization work is distributed, the design supports Azure-only or hybrid placement, and failback between locations can be planned without one cross-site WSFC.
Topic summary
Distributed AGs isolate cluster and quorum domains while extending Enterprise Edition database replication across locations.
27. Log shipping remains a practical DR architecture
A primary server creates transaction-log backups, a copy job transfers them through a backup share or equivalent path, and restore jobs apply them to one or more warm standbys. A monitor server can track status and history.
Log shipping is a durable, loosely coupled DR pattern based on native backup and restore operations.
It has a long operational history and is straightforward to administer.
It tolerates networks that are not robust enough for synchronous replication.
Backup frequency and restore delay make its RPO explicit.
It can protect an FCI at another location.
Topic summary
Log shipping is suitable when simplicity, network tolerance, and a nonzero RPO are acceptable.
28. Site Recovery is a broad VM-level DR alternative
For teams that do not want a SQL Server-specific DR topology, Site Recovery can orchestrate replication for the complete VM and other supported workloads. It is part of the Azure platform and can satisfy an architecture whose measured RTO and RPO fit its behavior.
Site Recovery protects the virtual-machine layer across regions and complements workload-specific recovery planning.
Database professionals often prefer database-centric replication for a lower and more explicit RPO. Make the decision from tested objectives rather than organizational preference.
Topic summary
Site Recovery is attractive for broad VM recovery, while database-centric designs can offer tighter transaction-aware recovery points.
29. Hybrid HADR extends the architecture beyond one cloud boundary
A hybrid solution places resources in Azure and on-premises or in another public cloud. Traditional hybrid HADR is primarily IaaS-based because infrastructure-level SQL Server features can span those locations. PaaS normally exposes managed Azure recovery patterns instead.
One exception is transactional replication from an on-premises or external-cloud publisher to an subscriber; the reverse direction is not supported in that scenario. Hybrid designs can support migration, but their most common use is an Azure DR target for an existing on-premises system.
Topic summary
Hybrid HADR commonly uses Azure as an IaaS recovery location for an existing environment, with limited PaaS integration exceptions.
30. Networking determines whether hybrid objectives are credible
A secondary AG replica in Azure also needs directory, DNS, routing, security, and operational access. Insufficient bandwidth or unstable latency can expand replication lag and make both RTO and RPO unattainable.
provides private, predictable connectivity when justified. Otherwise, use a secured site-to-site VPN through so Azure networks extend the private environment. Do not expose database VMs directly to the public internet.
Azure can also hold database backups as a recovery or cold archival destination, even when the live topology is not otherwise hybrid.
Topic summary
Model bandwidth, latency, routing, identity, and security as part of the recovery path; a hybrid replica is only as reliable as its connectivity.
31. Knowledge check
Check your understanding before opening the answers.
Question
Options
What does RPO describe?
A. Cluster node count · B. Point to which data must be recovered · C. Partial database restore
What makes a solution hybrid?
A. Azure plus on-premises or another cloud · B. Two database engines · C. Two SQL Server versions
What is present after database-level failover?
A. All instance objects · B. Databases and jobs · C. What is in the protected databases; outside objects need separate handling
Answers and rationale
1 — B. RPO is the acceptable recovery point and therefore the data-loss window.
2 — A. Hybrid refers to resources distributed across Azure and another environment, not merely mixed software.
3 — C. Database-level features protect database contents and logged changes; instance-level objects require another process.
Topic summary
Remember the exam distinctions: RPO concerns data, hybrid concerns location boundaries, and database-level protection excludes outside objects.
32. Build a defensible HADR decision
Document independent HA and DR RTO/RPO targets for the complete application and every critical dependency.
Choose IaaS or PaaS with an explicit understanding of control and shared responsibility.
Match instance-level or database-level protection to the objects that must survive failover.
Combine workload-aware SQL protection with Azure fault isolation where necessary.
Select local, zonal, regional, or hybrid scope from the failure scenarios that must be survived.
Use current Microsoft Learn documentation to validate service support, limits, regional availability, and implementation details before finalizing a production design.
