Home About Resume Services Articles Client Results Schedule Contact
Michael Paycer — SQL Server Disaster Recovery: Numbers First, Then Technology
SQL Server Guide

SQL Server Disaster Recovery: Numbers First, Then Technology

SQL Server disaster recovery that survives a real outage. Set RPO and RTO with the business first, then pick between AGs, FCIs, and log shipping to meet them.

SQL Server Services All Articles Contact

A manufacturer I worked with had synchronous availability groups, Enterprise licensing on six cores per node, and a SAN replication contract. Their SQL Server disaster recovery design protected an order entry database the business would have been content to lose four hours of. Nobody had asked. The architecture came first and the numbers were reverse-engineered to fit it, which is the normal order and the wrong one.

Every technology below is mature and has been for a decade. What it costs, what it protects against, and whether it is the right answer all depend on two numbers that come from the business rather than from a product page.

RPO and RTO before anything else

Two numbers drive every technology decision. Pick the technology first and you will spend the project justifying whatever you already bought.

Recovery Point Objective is how much data the business accepts losing, measured in time. An RPO of 15 minutes means transactions committed in the final 15 minutes before the failure may be gone.

Recovery Time Objective is how long the business tolerates the database being unavailable, counted from the start of the outage rather than from the moment someone hands you the go-ahead.

Asking a business owner "what is your RTO" produces the answer "zero" every time. Ask three different questions:

  1. If this database stops, what stops with it? Name the process, not the system. "Warehouse pickers cannot scan" beats "the WMS is down."
  2. What does an hour of that cost in dollars, and who signs that number? A finance person, not an architect.
  3. If we lose the last N minutes of transactions, how do we reconstruct them?

I have run this exercise more than once and the answers keep surprising the people who commissioned it. A distribution client reversed its own priorities (composite, details changed). The order entry database, which IT treated as tier one, could lose four hours without harm: every order arrived by EDI and the trading partner held 72 hours of resends. The label printing database, which IT treated as tier three, could not lose four minutes, because a reprinted label with a duplicate serial number gets a truckload rejected at the customer dock. The DR spend moved.

Write the agreed numbers down per database, with a name next to each one.

Backups are the floor, and the log chain sets the RPO

Every technology below sits on top of backups and none of them replace backups.

In the full recovery model, your RPO is bounded by the interval between log backups plus whatever tail of the log you rescue after the failure. Log backups every hour means an RPO of an hour, no matter what you spent on the SAN. The interval is also what keeps the log file from growing without limit, which is the other half of the same decision: when the log fills before the backup runs, you find out how wide the gap had grown.

The chain matters more than the individual files. A full backup, the differentials taken since, and an unbroken sequence of log backups from the full to the point of failure: break it anywhere and everything after the break is unrestorable. Things that break it: a log backup taken to somebody's laptop without COPY_ONLY, a switch to simple recovery and back, a restore over the top of the original.

This query walks the log backup history in msdb and reports gaps. In a healthy chain each log backup's first_lsn matches the previous backup's last_lsn.

WITH log_chain AS
(
    SELECT
        bs.database_name,
        bs.backup_set_id,
        bs.backup_start_date,
        bs.backup_finish_date,
        bs.first_lsn,
        bs.last_lsn,
        bs.database_backup_lsn,
        bs.begins_log_chain,
        bs.is_copy_only,
        bs.has_backup_checksums,
        bs.is_damaged,
        LAG(bs.last_lsn) OVER (PARTITION BY bs.database_name, bs.family_guid
                               ORDER BY bs.backup_start_date, bs.backup_set_id) AS prior_last_lsn
    FROM msdb.dbo.backupset AS bs
    WHERE bs.type = 'L'
      AND bs.is_copy_only = 0      -- copy-only log backups do not move the archive point
      AND bs.backup_start_date >= DATEADD(day, -14, GETDATE())
)
SELECT
    database_name,
    backup_start_date,
    prior_last_lsn,
    first_lsn,
    CASE
        WHEN begins_log_chain = 1 THEN 'New chain started here'
        WHEN first_lsn <> prior_last_lsn THEN 'GAP: log backup taken elsewhere'
    END AS finding
FROM log_chain
WHERE prior_last_lsn IS NOT NULL
  AND (first_lsn <> prior_last_lsn OR begins_log_chain = 1)
ORDER BY database_name, backup_start_date;

family_guid stays constant across restores of the same original database, so partitioning by it keeps a restored copy's history out of the comparison. begins_log_chain flags a backup that starts a fresh chain, which tells you the break was deliberate. The is_copy_only = 0 filter matters: a copy-only log backup preserves the existing log archive point and does not affect the sequencing of regular log backups, so leaving them in reports a gap that is not there.

The companion check is coverage: which databases in full or bulk-logged recovery have gone too long without a log backup.

SELECT
    d.name,
    d.recovery_model_desc,
    ls.log_backup_time,
    DATEDIFF(minute, ls.log_backup_time, GETDATE()) AS minutes_since_log_backup,
    CAST(ls.log_since_last_log_backup_mb AS decimal(18,1)) AS log_since_last_backup_mb,
    CAST(ls.total_log_size_mb AS decimal(18,1))           AS total_log_mb,
    ls.log_truncation_holdup_reason
FROM sys.databases AS d
CROSS APPLY sys.dm_db_log_stats(d.database_id) AS ls
WHERE d.recovery_model_desc IN ('FULL', 'BULK_LOGGED')
  AND d.state_desc = 'ONLINE'
  AND d.database_id <> 2
ORDER BY minutes_since_log_backup DESC;

sys.dm_db_log_stats arrived in SQL Server 2016 SP2. On older instances, fall back to log_reuse_wait_desc in sys.databases and the backupset query above.

A backup you have never restored is not a backup

Take every backup WITH CHECKSUM. It is not the default. SQL Server then verifies existing page-level checksums or torn-page detection as it reads each page, generates a checksum over the whole stream, and fails the backup on a page error. Restore verifies those checksums whenever they are present on the media. Add CONTINUE_AFTER_ERROR only for a database you already know is damaged and want salvaged; it flags the backup set as containing errors and records the bad pages in suspect_pages.

BACKUP DATABASE [Sales]
TO DISK = N'\\backup01\sql\Sales_full.bak'
WITH CHECKSUM, STATS = 5, COMPRESSION;

RESTORE VERIFYONLY FROM DISK = N'\\backup01\sql\Sales_full.bak' WITH CHECKSUM;

BACKUP and RESTORE work in every edition. COMPRESSION and encrypted backup are Enterprise and Standard only, and Express has no SQL Server Agent to schedule any of it.

Microsoft's list of what VERIFYONLY does is short: confirms the backup set is complete and all volumes readable, checks some header fields of database pages such as the page ID, validates the checksum if one is on the media, and confirms there is room on the destination. It does not verify the structure of the data inside the backup. Passing means the file is readable, not that the database inside it will come up.

The real test restores to a different instance and runs DBCC CHECKDB on the result. Automate it against a rotating subset of databases and alert on failure. A restore test someone has to remember to run stops happening in week three.

RESTORE DATABASE [Sales_RestoreTest]
FROM DISK = N'\\backup01\sql\Sales_full.bak'
WITH  MOVE N'Sales'     TO N'E:\RestoreTest\Sales.mdf',
      MOVE N'Sales_log' TO N'F:\RestoreTest\Sales_log.ldf',
      RECOVERY, CHECKSUM, STATS = 5;

DBCC CHECKDB ([Sales_RestoreTest]) WITH NO_INFOMSGS, ALL_ERRORMSGS, DATA_PURITY;

Time that restore and record the number. It is the only honest input you have for the RTO of a restore-based recovery, and it grows with the database.

Always On availability groups

An availability group streams log records from the primary to each secondary, which hardens and redoes them into its own copy of the files. Every replica has its own storage.

Synchronous commit holds the transaction open on the primary until the secondary has hardened the log record to disk and acknowledged it. The primary writes the log record, sends it concurrently, waits for the acknowledgment, and then confirms the commit to the client. This is the only mode that supports automatic failover, and it needs the secondary in the SYNCHRONIZED state with the WSFC holding quorum.

Asynchronous commit sends the log record and confirms the commit without waiting. The secondary never reaches SYNCHRONIZED. The only failover it supports is FORCE_FAILOVER_ALLOW_DATA_LOSS.

Across any real distance, asynchronous is the answer, and the reason is latency arithmetic. Every write on a synchronous replica pays a round trip. At 12ms to a site 400 miles away, a single-threaded batch doing 5,000 commits takes an extra minute. Concurrent work absorbs some of that, and a nightly serial job absorbs none of it. Teams discover this after they cut over.

Synchronous commit is also not a guarantee of zero data loss. When a synchronous secondary stops responding for longer than the primary's session timeout, which defaults to 10 seconds, the primary marks the databases on that replica NOT SYNCHRONIZING and keeps committing without it. Brent Ozar wrote the piece most people cite on this, "Synchronous Always On Availability Groups Is Not Zero Data Loss" (September 2015). Nobody gets paged, and if the primary then fails, the data written during that window is gone. SQL Server 2017 added REQUIRED_SYNCHRONIZED_SECONDARIES_TO_COMMIT to close the hole: raise it above its default of 0 and commits on the primary fail when the required number of synchronized secondaries is missing. That trades availability for durability, so make the trade on purpose.

The maximums moved too. SQL Server 2019 raised the number of synchronous-commit replicas to five including the primary, up from three in SQL Server 2017, and automatic failover works within that group. Every version from SQL Server 2014 forward supports one primary and up to eight secondaries; SQL Server 2012 was limited to four secondaries and three synchronous-commit replicas.

Microsoft's own pages disagree about that five. The 2022 editions matrix footnote reads "up to 8 secondary replicas, including 5 synchronous secondary replicas," which would make six. The ALTER AVAILABILITY GROUP reference still carries the pre-2019 limit of three, including the primary, for both SYNCHRONOUS_COMMIT and FAILOVER_MODE = AUTOMATIC. The availability modes page is the one that is version-attributed and spells out the breakdown as one primary plus four synchronous secondaries, so that is the number I design to. Confirm it against your build before you commit a topology to it.

Measure the RPO against the async secondary rather than assuming it:

SELECT
    ar.replica_server_name,
    DB_NAME(drs.database_id)        AS database_name,
    drs.synchronization_state_desc,
    drs.synchronization_health_desc,
    drs.last_commit_time,
    drs.secondary_lag_seconds,
    drs.log_send_queue_size         AS log_send_queue_kb,
    drs.redo_queue_size             AS redo_queue_kb,
    drs.redo_rate                   AS redo_rate_kb_per_sec,
    CASE WHEN drs.redo_rate > 0
         THEN drs.redo_queue_size / (drs.redo_rate * 1.0)
    END                             AS estimated_redo_seconds
FROM sys.dm_hadr_database_replica_states AS drs
JOIN sys.availability_replicas AS ar
  ON ar.replica_id = drs.replica_id
ORDER BY ar.replica_server_name, database_name;

log_send_queue_size is your data loss exposure if the primary dies right now. redo_queue_size divided by redo_rate is the extra time the secondary needs after a failover before it serves queries, and it belongs inside your RTO. Both are KB. secondary_lag_seconds has been available since SQL Server 2016.

The secondary after a failover, and read-only routing

After a forced failover with data loss, the former primary databases and any remaining secondaries sit suspended until you resume each one by hand. Put that in the runbook with the divergence it implies: transactions that existed on the old primary and never reached the new one are gone unless you pull them out of the suspended copy first.

Read-only routing sends read-intent connections to a readable secondary, and only when the client uses TCP, sets ApplicationIntent=ReadOnly, connects through the listener, and names a database in the group. Set the URL under each replica's secondary role, then the list under its primary role:

ALTER AVAILABILITY GROUP [AG1]
MODIFY REPLICA ON N'SQLDR01'
WITH (SECONDARY_ROLE (ALLOW_CONNECTIONS = READ_ONLY,
                      READ_ONLY_ROUTING_URL = N'TCP://SQLDR01.contoso.com:1433'));

-- SQLDR02 needs its own SECONDARY_ROLE block, same shape as SQLDR01,
-- or it sits in the list below and never receives a routed connection.

ALTER AVAILABILITY GROUP [AG1]
MODIFY REPLICA ON N'SQLPRD01'
WITH (PRIMARY_ROLE (READ_ONLY_ROUTING_LIST = (('SQLDR01','SQLDR02'), 'SQLPRD01')));

The nested parentheses load-balance across the replicas in the inner set, which SQL Server 2016 added. Set the list on every replica that can become primary, or read-only routing dies at the first failover.

An availability group is not a backup

Someone runs DELETE FROM dbo.Orders without a WHERE clause. The log records ship to every secondary within milliseconds and every replica loses the rows. Point-in-time restore from backups is the only recovery.

Physical corruption behaves the opposite way, which is its own problem. Each replica writes its own pages, so a failing HBA on the primary corrupts pages there and not on the secondary. Automatic page repair covers that: a replica that cannot read a page requests a fresh copy from a partner. Check whether it has been firing, because a busy one means hardware is dying somewhere:

SELECT
    DB_NAME(database_id) AS database_name,
    file_id,
    page_id,
    error_type,      -- 2 = bad checksum, 3 = torn page
    page_status,     -- 4 = repaired, 5 = repair failed
    modification_time
FROM sys.dm_hadr_auto_page_repair
ORDER BY modification_time DESC;

Because each replica carries independent physical pages, DBCC CHECKDB on the primary tells you nothing about the secondary. Run it on both.

The redo queue is the other thing an AG makes you own. A nightly index rebuild generates log in proportion to the index, ships all of it, and a secondary that cannot redo fast enough turns your RPO into whatever the backlog says. Maintenance that floods the redo queue is worth sizing before you schedule it.

Backups in an AG have their own rules. The automated backup preference defaults to Secondary and SQL Server does not enforce it: your job calls sys.fn_hadr_backup_is_preferred_replica and exits when it returns 0. Through SQL Server 2022 a secondary supports copy-only full backups and regular log backups and nothing else, so differentials and non-copy-only fulls run on the primary. Log backups taken on any replica form one consistent chain. SQL Server 2025 added full and differential backups on secondaries.

DECLARE @DatabaseName sysname       = N'Sales';
DECLARE @BackupPath   nvarchar(400) = N'\\backup01\sql\Sales_'
    + CONVERT(nvarchar(8), GETDATE(), 112)
    + REPLACE(CONVERT(nvarchar(8), GETDATE(), 108), ':', '') + N'.trn';

IF sys.fn_hadr_backup_is_preferred_replica(@DatabaseName) = 1
BEGIN
    BACKUP LOG @DatabaseName
    TO DISK = @BackupPath
    WITH CHECKSUM, COMPRESSION;
END

Basic availability groups on Standard Edition

Standard Edition does not get availability groups. It gets basic availability groups, introduced in SQL Server 2016, and the restrictions decide whether they fit:

The full edition ledger runs wider than this one feature, and what two replicas and one database cover is the question to settle before the licensing conversation starts.

Multiple basic availability groups can coexist on one instance, which is how people cover several databases. That holds until an application needs two of them consistent with each other after a failover, because each group fails over on its own: an order system with orders and inventory in separate groups can end up with the halves on different servers at different points in time. For an application that spans databases, log shipping is the better answer.

Failover cluster instances

An FCI protects the instance. Two or more nodes share storage, the instance runs on one at a time, and the WSFC moves it to a surviving node on hardware, OS, or service failure. Clients reconnect to the same virtual network name. It covers a dead node, a failed NIC, a Windows patch, an instance that will not start on one machine. Enterprise supports up to 16 nodes, Standard supports two.

It does not cover the storage. Microsoft states it plainly: shared storage "has the potential of being the single point of failure," and the FCI depends on the storage solution for data protection. One SAN, one LUN, one corruption event, and both nodes see the same broken database. An FCI is a high availability tool and on its own not a disaster recovery tool.

The combination that works pairs an FCI for local availability with an AG replica at the DR site for the database-level copy. A replica hosted on an FCI supports manual failover only, so plan for the hand-off.

Log shipping

Log shipping is three SQL Agent jobs, a backup on the primary, a copy that moves the files, a restore on the secondary, plus an optional monitor server that records history and raises alerts when a step misses its schedule. Failover is manual. It stays the right answer in several situations, and I have deployed it inside the last year:

The RPO is the log backup interval plus copy and restore latency. The RTO covers restoring whatever the secondary has not applied plus recovery of the database. Measure it rather than estimate it.

Quorum, witnesses, and the split-brain problem

Automatic failover in an AG and failover in an FCI both depend on Windows Server Failover Clustering, which decides who survives by counting votes. Each node gets one, a witness can hold one, and more than half must be active for the cluster to run.

Split brain is what quorum stops: two halves of the cluster, separated by a network failure, each concluding it is the survivor and each bringing the database online. Microsoft calls it a scenario "where separate sections of the cluster each believe they're functioning independently, which can result in data inconsistencies or corruption." When the active vote count drops below the majority the cluster shuts down rather than risk it, so a cluster that refuses to start after a site loss is doing its job.

Configure an odd number of voting elements, and with an even number of nodes add a witness. Three types:

The failure I see most: a two-node, two-site cluster with the file share witness in the primary data center. Lose that data center and the DR node holds one vote out of three. No quorum, no automatic failover, and an engineer forcing quorum by hand at 3am. Put the witness in a third location.

Windows Server 2012 R2 added dynamic witness, which grants the witness a vote when the node count is even and withdraws it when odd. Check what the cluster believes:

Get-Cluster | Select-Object Name, DynamicQuorum, WitnessDynamicWeight
Get-ClusterNode | Select-Object Name, State, NodeWeight, DynamicWeight
Get-ClusterQuorum

Run this before an outage, not during one.

What each technology delivers

These assume a healthy network and a database you have measured. They are starting points for your own testing, not a specification.

Technology Typical RPO Typical RTO Automatic failover Edition Protects against
Full plus log backups to local disk Log backup interval (5 to 60 min) Hours, scaling with database size No All Corruption, human error, total loss
Log shipping Log backup interval plus copy plus restore latency (15 to 60 min) 15 min to hours No Enterprise, Standard, Web Site loss, human error (with delay)
Basic availability group, synchronous Near zero within the sync window 30 to 120 sec Yes Standard, one DB per group Node loss, storage loss
Availability group, synchronous commit Near zero within the sync window 30 to 120 sec Yes Enterprise Node loss, storage loss
Availability group, asynchronous commit Seconds to minutes, measured by log_send_queue_size Minutes, plus redo queue drain No, forced failover only Enterprise Site loss
Distributed availability group Same as the underlying async link Minutes, manual No, manual only Enterprise, 2016 and later (Standard for the Azure SQL Managed Instance link) Loss of an entire cluster
Failover cluster instance Zero, same storage 30 sec to several min Yes Enterprise (16 nodes), Standard (2 nodes) Node loss only

Two rows deserve emphasis. An FCI's RPO is zero because both nodes read the same files, which is also why its protection against a storage disaster is zero. And an asynchronous availability group's RTO covers more than the failover command: the redo queue draining, DNS or connection strings pointing somewhere new, and the application restarting its pools.

The order to work in when a DR plan is missing

  1. Write down RPO and RTO per database, with a business owner's name on each number.
  2. Fix backups. Full, differential, and log backups WITH CHECKSUM, a log backup interval that meets the RPO, and copies off the primary storage.
  3. Automate a restore test to a separate instance with DBCC CHECKDB on the result. Alert on failure, and record how long it takes. That number is the RTO of a restore-based recovery and it grows with the database.
  4. Choose the technology against the numbers from step 1 and the edition you own. Do not buy Enterprise before you have proved Standard cannot meet the number.
  5. Configure quorum with the witness outside both data centers. Verify with Get-ClusterQuorum and record what you expect to see.

Steps 1 and 3 are the ones teams skip. Step 1 decides whether the rest of the spend is aimed at anything, and step 3 is the only evidence that the floor under all of it holds.

A topology that meets the numbers still fails if nobody has rehearsed it, and the failure mode is a four-hour argument about who gets to say the word "disaster." Writing the runbook and testing it is the other half of this work.

If you would rather hand this off, it is the kind of work I do. See SQL Server DBA services, review client results, or get in touch.