High Availability and Disaster Recovery Options for SQL Server and Azure SQL in 2025

---

High availability and disaster recovery are no longer optional layers added after a system is built. In 2025, with hybrid architectures spanning on-premises SQL Server and Azure, always-on business operations, and RTO and RPO targets measured in seconds rather than hours, HA and DR must be part of the initial design. SQL Server offers a broad range of options from the simple and low-cost to the sophisticated and enterprise-grade. Understanding where each fits is the starting point for every architecture decision.

1 SQL Server Replication Intermediate

Replication copies data from a Publisher to one or more Subscribers. It has been part of SQL Server since version 6.5 and in 2025 still covers specific use cases that other features do not: offloading reporting to a secondary server, distributing data to partners, and synchronizing subsets of data between distributed applications.

Topologies

• Snapshot: Full copy of a dataset at a point in time. Suitable for reference data that changes infrequently.
• Transactional: Near real-time change delivery after an initial snapshot. The most commonly used topology for reporting secondaries.
• Merge: Bidirectional sync between multiple servers with conflict resolution. Complex to maintain.
• Peer-to-Peer: Multi-master replication for active-active write scenarios. Advanced and requires careful conflict management.

When it fits

• Sending data to a reporting server in another region without AG read-only replica licensing
• Replicating a filtered subset of rows or columns to partners or downstream systems
• Synchronizing lookup or reference data between applications
• Environments requiring replication between SQL Server and Oracle

Limitations

• No automatic failover
• Schema changes must be coordinated between publisher and subscribers
• Conflict management in merge and peer-to-peer topologies adds significant operational complexity
• Initial configuration is more involved than other options

2 Log Shipping Beginner

Log shipping backs up transaction logs on a primary database and restores them on one or more secondary servers at scheduled intervals. Failover is manual. It remains a relevant and cost-effective DR option in 2025, particularly for Standard Edition environments where Always On AG is not available in its full form.

Why it is still relevant

• Included with all SQL Server editions including Standard
• Simple to configure, maintain, and monitor
• Can target multiple secondary servers simultaneously
• Works well in hybrid on-premises and VM architectures
• The secondary database can be kept in standby mode for read-only access between restores

Limitations

• No automatic failover: failover requires manual intervention and application reconfiguration
• RPO is determined by log backup frequency, not transaction-level synchronization
• Requires SQL Server Agent and a shared file path accessible to both servers
• No native support for Azure SQL Database or Azure SQL Managed Instance as a target

3 Database Mirroring (Removed in SQL Server 2022) Beginner

Database Mirroring introduced concepts that directly shaped Always On Availability Groups: synchronous and asynchronous commit modes, automatic failover with a witness server, and database-level protection. Understanding mirroring helps explain some AG architecture decisions, but it should not be used in any new or ongoing implementation on a currently supported SQL Server version.

Migration path

• SQL Server 2019 or earlier with mirroring: Migrate to Always On Availability Groups. Basic AG is available on Standard Edition as a two-database, two-replica replacement for mirroring.
• SQL Server 2022 or 2025: Mirroring is not available. AG is the only database-level HA/DR option.

4 Failover Cluster Instances Intermediate

Failover Cluster Instances provide instance-level HA using Windows Server Failover Clustering with shared storage. When a node fails, another node takes ownership of the shared storage and brings the SQL Server instance online. The virtual network name used by applications does not change, so no connection string changes are required at failover.

What FCI protects that AG does not

FCI is an instance-level solution. It protects everything that belongs to the SQL Server instance: system databases, all user databases, SQL Server Agent jobs, linked server definitions, logins at the instance level, and configuration settings. Always On AG protects individual databases but not the instance-level objects. In environments where instance-level protection matters, FCI and AG are often combined.

Key characteristics

• Zero data loss at failover because all nodes share the same storage
• Supported on Standard Edition with two nodes and Enterprise Edition with more nodes
• Storage options include SAN, Storage Spaces Direct (S2D), and SMB file shares
• No readable secondaries: the passive node serves no queries until failover
• Geographic redundancy requires combining FCI with AG in a distributed or stretched cluster configuration

Limitations

• Shared storage is a single point of failure for the entire cluster: if storage fails, all nodes lose access
• No read scale-out: passive nodes are completely idle until failover
• No geographic DR without additional architecture components

5 Always On Availability Groups Intermediate

Always On Availability Groups are the modern HA and DR standard for SQL Server Enterprise Edition and the recommended primary HA/DR solution for any environment that can use them. Since their introduction in SQL Server 2012, AGs have matured into a comprehensive platform with capabilities that continue to expand in each release.

SQL Server 2025 AG capabilities

• Up to 8 secondary replicas with up to 5 in synchronous commit mode
• Contained AGs replicate logins, SQL Server Agent jobs, and other instance-level objects as part of the AG, eliminating a traditional gap versus FCI
• Full, differential, and log backups can execute on secondary replicas, offloading backup I/O from the primary
• Readable secondaries support reporting and analytical workloads without additional licensing for those reads
• Distributed AGs allow groups spanning multiple WSFC clusters for cross-data-center topologies
• Clusterless AGs support Linux and container deployments
• TLS 1.3 support for encrypted AG endpoints

Standard Edition: Basic AG

Standard Edition supports Basic Availability Groups: one database per AG, one secondary replica, no readable secondary. This provides automatic failover protection for a single database as a replacement for Database Mirroring.

Considerations

• Synchronous replicas add latency to every commit because the primary waits for secondary acknowledgment
• Setup and ongoing maintenance is more complex than log shipping or FCI alone
• Troubleshooting failovers and synchronization issues requires understanding of the full AG commit pipeline
• AG does not protect instance-level objects unless Contained AG is used

6 Managed Instance Link Intermediate

Managed Instance Link connects an on-premises or VM-hosted SQL Server instance to Azure SQL Managed Instance for hybrid DR, reporting offload, or phased migration. It uses a Distributed Availability Group under the hood, leveraging the AG replication engine without requiring a full AG deployment on-premises.

Failover capabilities by SQL Server version

• SQL Server 2016, 2017, 2019: One-way failover from SQL Server to Managed Instance only. Failback requires manual steps.
• SQL Server 2022: Bidirectional failover supported. Failover from SQL Server to MI and failback from MI to SQL Server are both supported as planned operations.

When it fits

• Hybrid DR strategy where Azure is the DR target and on-premises is primary
• Phased migration to Azure: run the link for months while validating MI, then fail over when ready
• Reporting workloads offloaded to MI without standing up full AG infrastructure

Current limitations (as of mid-2026)

• One database per link: multi-database configurations require multiple links
• MI-to-MI link not supported
• Not integrated with Failover Groups for automatic failover

7 Microsoft Fabric Mirrored Databases Beginner

Microsoft Fabric Mirrored Databases continuously mirrors data from SQL Server or Azure SQL into OneLake for real-time analytics in the Fabric platform. This is a data pipeline and analytics integration feature, not a high availability or disaster recovery mechanism. There is no failover, no data protection at the transaction level, and no recovery capability in the event of a source database failure.

Including Fabric Mirroring in an HA/DR discussion is a category error. It is listed here only to clarify that it does not belong in an HA/DR architecture. Use it for analytics integration, not for protection against data loss or downtime.

8 Feature Comparison Table Beginner

Feature	HA	DR	Data Movement	Auto Failover	Readable Secondary	Azure Support	Edition
Replication	Limited	Yes	Async	No	Yes	MI only	All
Log Shipping	No	Yes	Async	No	Standby only	VMs only	All
DB Mirroring	Removed	Removed	N/A	Removed	No	No	SQL 2019 and earlier only
FCI	Yes	Limited	Shared storage	Yes	No	No	Std (2-node), Ent
Availability Groups	Yes	Yes	Sync/Async	Yes (sync)	Yes	Hybrid	Ent (Basic AG on Std)
MI Link	No	Yes	Async	SQL 2022+ only	MI replica	Yes	Ent / MI
Fabric Mirroring	No	No	Continuous	No	Analytics only	Yes	Fabric

9 Decision Matrix: Which Option Fits Your Scenario Intermediate

Use this matrix to identify the right starting point for common deployment scenarios. Most production environments combine two or more options.

Scenario	Primary Recommendation	Secondary Option	Notes
Best overall on-premises HA and DR, Enterprise Edition	Always On AG (synchronous + asynchronous replicas)	FCI + AG combined for instance-level protection	Synchronous replica for HA, asynchronous for DR site
Standard Edition HA (budget constraint)	Basic Availability Group	Log Shipping for additional DR copy	Basic AG replaces Database Mirroring on Standard
Low-cost DR only, RPO of minutes acceptable	Log Shipping	Replication for read offload	Simple and reliable; manual failover required
Instance-level protection (jobs, logins, system DBs)	Failover Cluster Instance	AG added for readable secondary and geographic DR	FCI alone has no geographic DR
Hybrid DR to Azure, on-premises primary	Managed Instance Link (SQL 2022 for bidirectional)	AG with asynchronous replica to Azure VM	MI Link is simpler; AG to Azure VM is more flexible
Read scale-out without additional licensing	AG readable secondary with read-only routing	Replication for subset or filtered data	AG readable secondary queries the secondary at no extra license cost
Report server in another region	Transactional Replication or AG asynchronous readable secondary	MI Link for Azure-hosted reporting	Replication for filtered subsets; AG secondary for full read scale
Migration from on-premises to Azure SQL MI	Managed Instance Link (online migration)	Backup/restore for offline migration	MI Link allows zero-downtime cutover on SQL 2022
Analytics and reporting in Microsoft Fabric	Fabric Mirrored Databases	Combine with AG for source database HA	Fabric Mirroring is analytics only, not DR
Inheriting Database Mirroring from a legacy system	Migrate to AG immediately	Basic AG on Standard, full AG on Enterprise	Mirroring removed in SQL Server 2022. No new implementations.

10 Workshop: Recommended Architecture for a 2025 Enterprise Deployment Advanced

This scenario covers a mid-sized enterprise running SQL Server 2022 Enterprise on-premises, requiring local HA, DR to Azure, and reporting workload offload.

Requirements

• RTO of 30 to 60 seconds for local HA failover
• RPO of zero for local HA (no data loss)
• RTO of 15 to 20 minutes for DR failover to Azure
• RPO of seconds to low minutes for Azure DR
• Reporting queries offloaded without impacting the primary
• Backups not running on the primary server

Recommended architecture

• Primary replica: SQL Server 2022 Enterprise, on-premises Node 1
• Synchronous secondary replica: SQL Server 2022 Enterprise, on-premises Node 2, same data center. Synchronous commit enables automatic failover with zero data loss. Serves as the local HA target.
• Asynchronous secondary replica: SQL Server 2022 Enterprise, on-premises Node 3 or a second data center. Read-only routing sends reporting queries here. Scheduled backups run on this replica.
• Azure DR: Managed Instance Link from the primary to Azure SQL Managed Instance. Asynchronous replication to Azure. Bidirectional failover available with SQL Server 2022.
• Listener: AG Listener provides a single connection endpoint. Read-only routing rules direct reporting connections to the asynchronous replica automatically.

Key implementation steps

1. Configure Windows Server Failover Clustering on all on-premises nodes
2. Create the Availability Group with synchronous commit on Node 2 and asynchronous commit on Node 3
3. Configure the AG Listener and set up read-only routing rules
4. Schedule full, differential, and log backups to run on the asynchronous replica
5. Create the Managed Instance Link to Azure SQL MI for cloud DR
6. Set up monitoring covering AG health, log send queue, redo queue, and failover history
7. Document and test failover procedures quarterly for both local and Azure DR scenarios

References

• Microsoft Docs: Always On Availability Groups Overview
• Microsoft Docs: Database Mirroring (deprecated, removed in SQL Server 2022)
• Microsoft Docs: Always On Failover Cluster Instances
• Microsoft Docs: About Log Shipping
• Microsoft Docs: SQL Server Replication
• Microsoft Docs: Managed Instance Link Overview
• Microsoft Docs: Discontinued Database Engine Functionality in SQL Server
• SQLYARD: Always On Availability Groups Complete Guide
• SQLYARD: SQL Server Error 833 and Long Sync IO in Always On
• SQLYARD: DBATools Migration Workflow Guide