SQL Server High Availability Options: FCI, Availability Groups and Log Shipping Compared

SQL Server high availability isn’t a single feature you switch on. It’s a choice between several technologies: failover cluster instances, Always On availability groups, basic availability groups, log shipping and, for older systems, database mirroring. Each one protects against a different kind of failure, needs a different SQL Server edition and costs a different amount to run. Picking the wrong one usually doesn’t show up until the day you need it.

This guide compares the SQL Server high availability options side by side, explains what each one does and doesn’t protect against, and shows how to choose based on your edition, your recovery targets and the failures you actually expect.

High Availability, Disaster Recovery and Backups Are Different Jobs

Three terms get mixed up in almost every HA conversation:

• High availability (HA) keeps the database running through a local failure, such as a dead server, a crashed operating system or a stopped SQL Server service. Failover is usually automatic and takes seconds to minutes.
• Disaster recovery (DR) brings the service back after you lose a whole site or data center. Failover is usually manual, and some data loss is often accepted.
• Backups protect against everything HA and DR can’t, above all human error, corruption that has already spread and ransomware.

Two numbers drive the design: the RPO (recovery point objective, how much data you can afford to lose) and the RTO (recovery time objective, how long you can afford to be down). We cover how RPO and RTO shape an Always On design in detail in our SQL Server Always On guide.

Start With the Failure You’re Protecting Against

Before comparing features, list the failures you need to survive. Each option covers a different set:

SQL Server High Availability Options Compared

Replication is missing from this table on purpose. It moves data between databases, but it has no failover mechanism, so it’s a data-distribution tool rather than an HA solution.

Failover Cluster Instances (FCI)

An FCI is a single SQL Server instance installed across the nodes of a Windows Server Failover Cluster. Only one node runs SQL Server at a time. When it fails, the cluster starts the instance on another node, and clients reconnect to the same virtual network name.

Because the whole instance moves, everything inside it moves too: logins, SQL Server Agent jobs, linked servers and server-level settings. That’s the FCI’s biggest strength. Its biggest weakness is storage. All nodes share the same disks (SAN, Storage Spaces Direct or an SMB share), so the storage is a single point of failure, and the FCI depends on it for data protection.

A failover is effectively a service restart on another node. SQL Server runs crash recovery on every database, so open transactions roll back before the databases come online. A long-running transaction at the moment of failure means a longer failover.

Always On Availability Groups

An availability group keeps a copy of a set of user databases on each replica, and every replica has its own storage. The primary sends transaction log records to the secondaries. A synchronous-commit replica confirms each commit before the primary completes it, so it can take over without data loss. An asynchronous-commit replica doesn’t wait, which suits distant DR sites but allows some data loss.

On Enterprise edition, an availability group supports up to eight secondary replicas and up to five synchronous-commit replicas, including the primary. Secondaries can serve read-only queries and take log backups and copy-only full backups. Clients connect through a listener that follows the primary. On Windows, an HA availability group needs a Windows Server Failover Cluster; on Linux, Pacemaker plays that role.

For a deeper look at listeners, failover modes and quorum, see our SQL Server Always On guide.

Basic Availability Groups (Standard Edition)

Since SQL Server 2016, Standard edition supports basic availability groups as the replacement for database mirroring. They work like regular availability groups but with tight limits:

• Two replicas only: one primary and one secondary.
• One database per availability group. You can create several basic groups on one instance.
• No read access, no backups and no integrity checks on the secondary.
• They can’t be upgraded to a full availability group. Moving to Enterprise means dropping and recreating them.

Log Shipping

Log shipping is three SQL Server Agent jobs: back up the transaction log on the primary, copy the file to one or more secondaries, and restore it there. It’s simple, cheap, works across long distances and tolerates slow links. The RPO is roughly the backup interval plus the copy time.

Failover is always manual. The secondary can be kept in STANDBY mode for read-only reporting, but users are disconnected while each restore runs. One feature is unique to log shipping: you can delay the restores on purpose. If someone runs a destructive change, the secondary can still hold the unchanged data for a while.

Database Mirroring

Database mirroring has been deprecated since SQL Server 2012 and will be removed in a future version. It still works, but it shouldn’t be used for new systems. Microsoft’s guidance is to use basic availability groups on Standard edition and availability groups on Enterprise edition instead. If you still run mirroring, plan the move during your next upgrade; the details are in our comparison of asynchronous mirroring and asynchronous availability groups.

Which SQL Server HA Option Fits Your Situation?

• Standard edition, one or a few critical databases, two servers: basic availability groups, one per database. Add log shipping to a third server if you also need DR.
• Many Agent jobs, logins and linked servers, and reliable shared storage: an FCI, because the whole instance moves together. Add an availability group or log shipping to another site for DR.
• Enterprise edition and a reporting load to offload: an availability group with a synchronous replica for HA and a readable secondary for reports.
• A second site on a limited budget: log shipping or an asynchronous availability group replica.
• An RTO of several hours is acceptable: a well-designed backup plan with regular restore tests may be all you need. Don’t buy complexity you won’t operate.

How to Monitor an Availability Group

An availability group that nobody watches is a risk of its own. In our experience, unmonitored Always On and replication latency is one of the most common causes of trouble we find in new environments. A disconnected or lagging secondary also stops log truncation on the primary, so the transaction log keeps growing until the disk fills. This query shows the health and queue sizes for every database on every replica:

SELECT ag.name                        AS ag_name,
       ar.replica_server_name,
       DB_NAME(drs.database_id)       AS database_name,
       drs.synchronization_state_desc,
       drs.synchronization_health_desc,
       drs.log_send_queue_size        AS log_send_queue_kb,
       drs.redo_queue_size            AS redo_queue_kb,
       drs.last_commit_time
FROM sys.dm_hadr_database_replica_states AS drs
JOIN sys.availability_replicas AS ar ON ar.replica_id = drs.replica_id
JOIN sys.availability_groups   AS ag ON ag.group_id   = drs.group_id
ORDER BY ag.name, ar.replica_server_name, database_name;

A growing log_send_queue_kb means data that would be lost in a forced failover. A growing redo_queue_kb means a longer failover. If a database’s log_reuse_wait_desc in sys.databases shows AVAILABILITY_REPLICA, a replica is holding back log truncation. Set up SQL Server alerts so these conditions reach someone before users notice.

Common High Availability Mistakes

• Treating HA as a backup. Replicas copy mistakes and corruption-causing changes as faithfully as good data.
• Never testing failover. A failover that has never been tried tends to fail on missing logins, jobs or firewall rules.
• Assuming asynchronous replicas lose nothing. Anything still in the send queue is gone after a forced failover.
• Ignoring quorum design. When the cluster loses quorum, the FCI or availability group goes offline even if SQL Server is healthy.
• Restarting in a panic. After a failover, databases have to finish recovery. Restarting again only starts recovery over. Our outage runbook covers what to check first.
• Patching without a plan. Clusters need a rolling, node-by-node process; see patching SQL Server on Always On clusters.