Problem
SQL Server and Azure SQL offer many options for high availability and disaster recovery. In this article, we look at various options for SQL Server high availability and SQL Server disaster recovery.
Solution
There are many disaster recovery solutions (DR) and a few high availability (HA) solutions at our disposal. Some date back to 1990s, while some are specific to Azure SQL only and most are available on SQL Server. We will review them one by one, including a bit of history, review general architectural designs, list compatibility with different editions of SQL Server and Azure SQL offerings, and describe pros and cons of each approach.
High Availability and Disaster Recovery Features in SQL Server
In SQL Server, whether it is on-premises or in a cloud (Azure, AWS, etc.), the following features are available:
- SQL Server Replication
- Log Shipping
- Failover Cluster Instances
- Database Mirroring
- Always On Availability Groups
- Managed Instance Link
SQL Server Replication
At the most basic level, replication is a means to copy data from a Publisher to a Subscriber. The technology was originally introduced in SQL Server 6.5 (1996). The technology has evolved with merge replication and GUI wizards in SQL Server 7.0 (1998), further improvements in SQL Server 2000, peer-to-peer in 2005, and continued refinements, but no fundamental redesign after that point.
The big difference of this feature is that it allows for a very granular level of selection of data for replication (down to specified tables and columns, including filtering by WHERE predicates).
SQL Server Replication is neither high availability (HA) nor a pure disaster recovery (DR) technology — but it can play a supporting role in both, depending on how it’s architected. Not ideal forHA — replication doesn’t offer automatic failover or synchronous data copying. The Publisher doesn’t wait for the Subscriber to send an acknowledgement before committing on the Publisher itself. It has a use for DR – replication can distribute data to remote sites for reporting or recovery.
The Replication topology typically consists of a Publisher, Distributor and Subscriber. All these can be a single server (when replicating from one instance to another, for example), or more servers. Especially if you have many subscribers, or a merge or peer-to-peer type where roles are mixed.
All work is done by Replication Agents, which are standalone programs, that are generating snapshots, reading transaction logs, distributing data to subscribers and reconciling changes. It supports one-way (transactional, snapshot), bidirectional (transactional), and multi-master setups (merge and peer-to-peer).
SQL Server Replication Options
- Snapshot – The entire dataset is copied over to subscriber
- Transactional – After copying initial dataset (i.e. snapshot), all subsequent changes are also streamed on a per transaction level
- Merge – Used when multiple servers are exchanging information and synchronizing on regular intervals
- Bi-Directional – Specific transactional replication topology that allows two servers to exchange data with each other.
- Peer-to-peer – Enables multi-master topology, where all servers act as both publishers/subscribers and exchange data in near real time.
SQL Server Replication Analysis
For support by different editions, please see the screenshot below:
| Replication Type | Express, Web | Standard | Enterprise | Azure SQL Database | Azure SQL Managed Instance |
|---|---|---|---|---|---|
| Transactional | Subscriber Only | Yes | Yes | Subscriber Only | Yes |
| Snapshot | Subscriber Only | Yes | Yes | Subscriber Only | Yes |
| Merge | no | Yes | Yes | no | no |
| Bi-Directional | no | Yes | Yes | no | Yes |
| Peer-to-Peer | no | no | Yes | no | no |
How to configure: right click the Replication folder in SSMS Object Explorer -> New Publication. See the URLs below for additional details.
Pros:
- Ability to replicate to multiple servers
- Allows for read data on Subscribers
- Multi-master setup (master and peer-to-peer)
- Customization of which data to replicate (tables, columns, predicates)
- Replication between SQL Server (Enterprise) and Oracle
Cons:
- Initial configuration and topology design may be complex
- Schema changes may need to be coordinated between servers
- Possible conflicts in merge and peer-to-peer replication
Additional Information:
- Official Documentation
- Types of Replication
- Prepare for Replication
- Configure Transaction Replication
- SQL Server Replication Configuration Scripts
- Overview of Peer-to-Peer Transactional Replication in SQL Server 2008
- Configure One Way Merge Replication for SQL Server
SQL Server Log Shipping
The first native implementation of Log Shipping was in SQL Server 2000, complete with built-in support and a graphical user interface (GUI) for configuration and monitoring. In SQL Server 2005, Log Shipping became available in Web, Standard and Enterprise editions.
Log Shipping in SQL Server is a warm standby disaster recovery solution that involves automatically backing up transaction logs from a primary database, copying them to one or more secondary servers, and restoring them there on a scheduled basis.
Log Shipping is configured and operates on a per database level. It requires SQL Server Agent to be running (relies on jobs) and a shared path for primary and secondary servers.
Like Replication, Log Shipping is not ideal for HA, since there is no automatic failover – manual intervention is required. There’s also a delay between log backups and restores (default is 15 minutes). Designed for DR, to recover from primary server failure. Secondary server can be brought online manually with possible data loss.
It’s best suited for scenarios where:
- You need a cost-effective DR strategy
- You can tolerate some delay in data synchronization
- You’re okay with manual failover in case of disaster
SQL Server Log Shipping Analysis
Of course, fully supported on SQL Server on Azure VMs, but not supported on Azure SQL Database and Azure SQL Managed Instance.
| Log Shipping | Express | Standard, Web, Enterprise | Azure SQL Database | Azure SQL Managed Instance |
|---|---|---|---|---|
| Log Shipping | no | Yes | no | no |
How to configure: right click the database in Object Explorer, click Tasks -> Ship Transaction Log.
Pros:
- Simplicity and reliability
- Low cost
- Multiple secondary targets
- Customizable delay
- Reporting capabilities
Cons:
- No automatic failover
- Per-database configuration
- Synchronization delay
- RPO and RTO limitations
- SQL Server Agent dependencies
Additional Information:
- Official Documentation
- How to Configure
- LinkedIn Article
- Step By Step SQL Server Log Shipping
- Configure Log Shipping for SQL Server on Linux
- SQL Server Log Shipping to a Different Domain or Workgroup
SQL Server Database Mirroring
SQL Server Database Mirroring was introduced in SQL Server 2005 and saw improvements in 2008 (log steam compression). In SQL Server 2012, Database Mirroring was marked as deprecated, but it is still available in SQL Server 2025 release candidate.
For the first time with Database Mirroring, you could choose between asynchronous commit vs synchronous commit modes. Database mirroring can be used as both HA and DR solution. This architecture consists of principal server, mirror server and witness server (optional). It has three operating modes:
- High Safety (synchronous commit) with optional automatic failover using a witness server.
- High Performance (asynchronous commit) for lower latency, but no automatic failover.
- High Safety without automatic failover – synchronous commit but manual failover only.
Database Mirroring is configured on a per-database level.
Since this feature is deprecated, Microsoft recommends migrating to Always On Availability Groups for modern HA/DR scenarios.
SQL Server Database Mirroring Analysis
| Database Mirroring | Web, Express | Standard | Enterprise | Azure SQL Database | Azure SQL Managed Instance |
|---|---|---|---|---|---|
| Database Mirroring | No | Sync mode only | Yes | No | No |
How to configure: Right click Database -> Tasks -> Mirror…
Pros:
- Fast automatic failover (in high safety mode with witness only)
- Zero data loss (in high safety mode only)
- Automatic page repair (get copy from partner server)
- Rolling upgrades with minimal downtime
- Encryption for data transmission
Cons:
- Per database configuration and failover
- No readable mirror (no read-only replicas)
- Possible inconsistent failover (each database fails over independently)
- Deprecated feature (may be removed in any future version of SQL Server)
- Need to update connection string after failover (if connection driver doesn’t support “Failover Partner” part)
Additional Information:
- Official Documentation
- Configure SQL Server Database Mirroring with Management Studio
- Configure Database Mirroring with T-SQL Code
- SQL Server Database Mirroring Inventory and Monitoring Scripts
- Change operating modes for SQL Server Database Mirroring
Failover Cluster Instance
SQL Server 7.0 (1998) was the first version to officially support Failover Cluster Instances (FCI), leveraging MSCS (Microsoft Cluster Service) for instance-level failover. In SQL Server 2000, integration into Windows Server Failover Clustering (WSFC) was included. The FCI technology has seen improvements in each version of SQL Server afterwards, including SQL Server 2025.
FCI is a single SQL Server instance installed across multiple nodes in a Windows Server Failover Cluster (WSFC). It provides instance-level high availability — if one node fails, another takes over with minimal downtime.
Since FCI is instance-level HA solution, all databases on an instance are included, you can’t pick individual databases or group of databases. Logins, jobs and other server-level objects are “replicated” to other nodes automatically, so you don’t have to worry about synchronizing them between nodes.
FCI does not replicate data between nodes at the SQL Server level. Instead, it relies on shared storage — typically SAN, SMB, or Storage Spaces Direct (S2D) — that is accessible by all nodes in the cluster. So, unlike Availability Groups (which use synchronous/asynchronous commit), FCI simply moves the SQL Server instance to another node, which then mounts the same data volumes. There’s no data copy or sync — just a switch in ownership.
With third party tools, you can setup FCI to not need shared storage and data replication between the nodes is handled by the third party tool.
If you’re architecting HA, FCI is excellent for local high availability with zero data loss, but it’s not ideal for geo-redundancy or read-scale.
Enterprise edition has full support for FCI, including multi-node clusters, multi-subnet failover, and advanced features like Storage Spaces Direct (S2D). Standard edition is limited to 2 nodes.
SQL Server FCI Analysis
| Failover Cluster Instance | Web, Express | Standard | Enterprise | Azure SQL Database | Azure SQL Managed Instance |
|---|---|---|---|---|---|
| Failover Cluster Instance | No | 2 nodes max | Yes | No | No |
How to configure: install SQL Server on 2 or more nodes as Failover Cluster Instance through the installation wizard.
Pros:
- Instance-level protection and failover (including system databases, logins, linked servers, jobs, etc.)
- Single copy of data (shared storage)
- Zero data loss and automatic failover (HA)
- Supports all recovery models (simple, bulk-logged, full)
- No need to update connection strings after failover (apps connect to VNN)
Cons:
- No read-only replicas
- Requires shared storage (can be costly)
- No data redundancy (storage failure = total failure, unless no external replication)
- Can’t scale out reads or writes (only one active node at a time)
Additional Information:
- Official Documentation
- Install a SQL Server FCI
- Windows Server 2022 Failover Cluster Step by Step for SQL Server 2022
SQL Server Always On Availability Groups
SQL Server 2012 introduced Availability Groups (AG), a powerful and flexible HA/DR solution. AGs have seen major improvements in all consequent versions of SQL Server, including 2025.
Full support of Availability Groups is an Enterprise Edition feature. Since 2016, Standard Edition has Basic Availability Groups, which are a very limited version of AG.
The topology consists of a primary server, secondary server(s), and a listener to which clients can connect and be redirected to primary server (or secondary, depending on the connection string settings).
AG Supports both synchronous and asynchronous commit modes. AGs can have many secondary replicas in either sync or async modes, all of them can be used to offload read-only queries. Load balancing (read-only routing) is supported. There is no need to update application connection stings after failover, since the listener’s address doesn’t change. AG works on a “group of databases” and you pick which databases to add in a group. FCI instance can serve as a replica in AG, so complex topologies could be built. Also, log backups and copy-only full backups can be offloaded to secondary servers. Since 2025, there is full support for all kinds of backups (full, diff, log) on secondary servers.
SQL Server Availability Group Analysis
Key features added in each version of SQL Server:
| SQL Server Version | Key Improvement or Added Features | Notes |
|---|---|---|
| 2012 | Initial release | Enterprise Edition only |
| 2014 | Several improvements | Readable secondary performance, other |
| 2016 | Basic AG | In Standard Edition (1 database only) |
| 2016 | Distributed AG | Cross-cluster replication (AG of AGs) |
| 2017 | Linux and Containers | On Linux using Pacemaker |
| 2019 | Clusterless AG | No WSFC required |
| 2022 | Contained AG | System databases, logins, jobs are synced |
| 2025 | Backups on Secondary | Full, Differential and Log |
Distributed AGs (DAG) can have two separate Availability Groups in one DAG. One will serve as a primary, and second as secondary.
On Linux, Pacemaker is used to orchestrate replicas and failovers.
A clusterless AG allows you to build an AG without WSFC or Pacemaker. In this case, only manual DR is available.
Number of Replicas
Maximum number of secondary replicas is 8, out of which 5 can be in synchronous commit mode.
| SQL Server Version | Max Secondary Replicas | Max Readable Replicas | Max Synchronous Replicas |
|---|---|---|---|
| 2012 | 4 | 1 | 2 |
| 2014 | 8 | 8 | 2 |
| 2016 | 8 | 8 | 2 |
| 2017 | 8 | 8 | 2 |
| 2019 | 8 | 8 | 5 |
| 2022 | 8 | 8 | 5 |
| 2025 | 8 | 8 | 5 |
Availability on different editions on SQL Server and Azure SQL:
| Availability Groups | Web, Express | Standard | Enterprise | Azure SQL Database | Azure SQL Managed Instance |
|---|---|---|---|---|---|
| Availability Groups | No | Basic AG only | Yes | No | No |
How to Configure: First, enable the Windows Server Failover Cluster feature in Windows Server. Create a cluster in Failover Cluster Manager in Windows, add nodes. Second, SSMS -> Object Explorer -> right click Always On High Availability -> New Availability Group Wizard.
Pros:
- Works on “group of databases”
- Automatic failover
- No need to update connection strings after failover
- Multiple replicas
- Readable secondaries (offload reporting)
- Load balancing (read-only routing)
- Encrypted endpoints and TLS 1.3 support
- Ability to build complex architecture (including on-prem to cloud hybrids)
- A lot of configuration options (DAG, BAG, CAG, clusterless)
- Ability to run backups on secondaries
Cons:
- Enterprise edition requirement
- Complex setup and configuration
- Sync replicas can introduce latency
- Troubleshooting of failovers or replica timeouts can be hard
Additional Information:
- Official Documentation
- Configuration
- What is SQL Server AlwaysOn?
- Create an Always On Availability Groups using TSQL
- Setup SQL Server Always On Basic Availability Groups
- Distributed Availability Groups for SQL Server Disaster Recovery
- Multi-Site SQL Server Always On Availability Groups
- SQL Server Contained Availability Groups Configuration
- SQL Server Read-Scale Always On Availability Groups
Managed Instance Link
Managed Instance link is disaster recovery and migration feature that is available in SQL Server versions starting 2016. It allows replicating data from SQL Server to Azure SQL Managed Instance, which can also be used to offload reporting.
The minimum required edition for SQL Server is Standard.
SQL Server 2016, 2017 and 2019 only support one-way replication and failover to Azure SQL MI. Once failover has been performed, you can’t fail back, and the link is broken.
SQL Server 2022 allows for bi-directional failover, changing roles on demand, and establishing initial link from Azure SQL MI as a primary to SQL Server 2022.
MI Link is using Distributed Availability Groups under the hood to make things work. MI is configured on a per-database level.
Managed Instance Link Analysis
| Managed Instance Link | Web, Express | 2016-2019 Standard, Enterprise | 2022+ Standard, Enterprise | Azure SQL Database | Azure SQL Managed Instance |
|---|---|---|---|---|---|
| Managed Instance Link | no | yes (one-way repl/failover) | Yes | No | Yes |
How to configure: SSMS Object Explorer -> Right click DB -> Azure SQL Managed Instance Link…
Pros:
- Replicate data from SQL Server to SQL MI (and from SQL MI to SQL Server 2022+)
- Disaster recovery
- Offload reporting
- Migration capabilities
Cons:
- Single database per link
- Limited number of links
- No MI-to-MI support (as of 2025/09)
- Can’t establish a link on MI that is part of failover group (as of 2025/09)
Additional Information:
- Official Documentation
- Prepare the Environment
- Configure the Link
- The flexibility of hybrid options with Azure SQL Managed Instance
Microsoft Fabric Mirrored Databases
You might have heard about a new feature for replicating data that is in preview in SQL Server 2025 and Azure SQL MI, and live for Azure SQL DB. The new Microsoft Fabric mirrored databases feature is NOT designed for high availability (HA) or disaster recovery (DR) in the traditional sense.
Instead, it’s built to support analytics, data integration, and real-time insights by continuously replicating data from SQL Server, Azure SQL Database, or Azure SQL Managed Instance into Microsoft Fabric’s OneLake. I included this just to make it clear and avoid confusion, because some people tend to think that it is another HA or DR solution.
Feature Summary
For features that we discussed, please see table below with a classification by HA and DR categories:
| Feature | High Availability | Disaster Recovery | Data Movement |
|---|---|---|---|
| SQL Server Replication | Limited | Yes | async |
| Transaction Log Shipping | no | Yes | async |
| Failover Cluster Instances | Yes | Limited | – |
| Database Mirroring | Yes | Yes | sync / async |
| Availability Groups | Yes | Yes | sync / async |
| Link to Managed Instance | no | Yes | async |
For SQL Server, if you have Enterprise edition and enough budget, the best option can be Availability Groups as it covers both HA and DR and offers variety of advantages. Otherwise, other features have their own use cases, for cost effective and specific use-case scenarios.
Conclusion
In this article, we reviewed and compared available solutions for HA and DR in SQL Server and in some of the Azure SQL offerings, providing high level details, use cases and pros/cons for each solution.
Next Steps
