Log Shipping to a Readable Standby: Restores vs Report Users

Log shipping to a readable standby lets users run reports on the secondary database between transaction-log restores. The tradeoff is that each restore needs the database free of readers. A readable standby works well only when restore timing, user disconnect behavior, and acceptable reporting delay are planned together.

Understand the Restore Cycle

The primary takes transaction-log backups. A copy job moves them to the secondary, and a restore job applies them in order. WITH STANDBY leaves the secondary readable after each restore by using an undo file for uncommitted transactions. The database cannot accept writes in this mode. The next restore needs exclusive access and can disconnect or wait for report sessions.

I ask report owners how long queries run and how stale the data can be. A thirty-minute report window cannot coexist with a five-minute restore cadence unless interruptions are acceptable. Which requirement wins during the morning reporting period?

Initialize the Readable Standby

In the log shipping configuration wizard, initialize the secondary from a matching full backup and required log chain. On the Restore tab, choose Standby mode and supply an undo-file location with enough protected storage. Choose whether the restore job disconnects users. Test a read-only query after the first restore, then confirm the next log backup applies successfully.

-- Standalone restore concept for a lab secondary; use the actual chain.
RESTORE LOG ReportingCopy
FROM DISK = 'D:\LogShipCopy\ReportingCopy_001.trn'
WITH STANDBY = 'D:\LogShipUndo\ReportingCopy_undo.tuf',
     STATS = 5;

The actual log shipping jobs are configured by the wizard or system procedures, not by this single RESTORE statement. Keep the undo file with the secondary and protect it from cleanup jobs. A missing or damaged undo file can prevent the next restore. Do not let report users mistake the secondary for a writable production database.

Set Disconnect Users Deliberately

The secondary setting disconnect_users = 1 lets the restore job remove readers when it needs exclusive access. That keeps the log chain moving but can terminate a long report. With disconnect_users = 0, reports can finish, but restores can wait or fail and lag grows. Neither setting is universally correct; it encodes a service-level choice.

USE master;
GO
EXEC sys.sp_change_log_shipping_secondary_database
    @secondary_database = N'ReportingCopy',
    @restore_mode = 1,
    @disconnect_users = 1;
EXEC sys.sp_help_log_shipping_secondary_database
    @secondary_database = N'ReportingCopy';

Run this only after the log shipping configuration exists and the owner approves the behavior. Both procedures must run in master; from msdb they stop with Msg 5001. The help procedure confirms the effective settings. In a lab, start a long read while the restore job runs and observe whether the reader is disconnected or the restore waits. That test is clearer than a checkbox description.

Measure Copy and Restore Delay

msdb.dbo.log_shipping_monitor_secondary records last copied and restored files and times, plus last restored latency in minutes. Read it on the secondary or monitor server and capture samples through the day. Compare file names so a copied backup that has not been restored is visible.

SELECT secondary_database, last_copied_file,
       last_copied_date_utc,
       last_restored_file, last_restored_date_utc,
       last_restored_latency,
       restore_threshold, threshold_alert_enabled
FROM msdb.dbo.log_shipping_monitor_secondary
WHERE secondary_database = N'ReportingCopy';

The last restored date is when the operation occurred, not necessarily the data's transaction cutoff. Last restored latency reflects backup creation to restore. Add the primary backup schedule and current time when reporting freshness to users. A single green status can hide a widening queue if copy files arrive faster than restores can apply them.

Fit a Daily Reporting Window

One workable plan is frequent backups and copies all day, restores every ten minutes outside reporting hours, then a scheduled quiet reporting window when restores pause. After reports finish, resume the restore job and let it catch up in order. Another plan keeps regular restores and tells users that sessions can be disconnected. Choose based on freshness and interruption tolerance.

I put the window in an operations calendar: 07:00 catch up, 08:00 to 09:00 reports, 09:05 restores resume and process backlog. Those are example times, not defaults. Before pausing restores, calculate the maximum lag and log retention needed on the primary and copy location. A longer reporting window makes the secondary less current and can increase recovery time.

Monitor the Backlog and Alerts

Set restore thresholds that match the chosen schedule. A paused restore job during an approved window should have a documented exception, not a permanently disabled alert. Watch backup, copy, and restore jobs separately. A missing primary backup cannot be fixed by changing the secondary's disconnect option. A copy failure and a restore blocked by readers have different remedies.

SELECT secondary_database, last_copied_date,
       last_restored_date, last_restored_latency,
       restore_threshold
FROM msdb.dbo.log_shipping_monitor_secondary;

I keep a daily chart of restored latency and count of report disconnects. If reports are repeatedly cut off, shorten them, change the window, or move reporting to a technology better matched to continuous reads. STANDBY is useful precisely because its tradeoff is explicit and measurable.

A Readable Standby Is Not a Failover Replica

Log shipping recovery requires applying remaining logs where possible and bringing the database online with RECOVERY, which ends the log shipping restore chain in that form. Keep a separate failover runbook. Report users should know that the standby can be unavailable during restores and that data freshness follows the last applied log.

I validate one full backup-copy-restore cycle after any schedule change. The acceptance checks are straightforward: reports can connect in the promised window, restores resume afterward, and measured lag stays within the agreed limit. A green job history without those user-facing checks is incomplete evidence.

Show Readable Standby Freshness to Users

A report user needs to know the latest data time, not only whether the secondary is readable. Show the last applied backup time or a data watermark in the report header. If restores pause during a reporting window, the secondary becomes progressively older. A dashboard that looks live while showing data from 07:00 can lead to bad decisions at 09:00. Make the delay visible and within the agreed tolerance.

Test Both Disconnect Settings

In a lab, run a long report while the restore job starts with disconnect users off, then repeat with it on. Record report outcome, restore duration, and resulting lag. This gives the owner a concrete choice rather than a theoretical checkbox. A report can be written to finish quickly, use snapshot-style logic where supported, or run in an agreed window; each change should be measured with the same workload.

Related reading on this blog: Log Shipping Monitor Not Getting Updated and What is WRK file in Log Shipping?.

A readable standby is not always available, it is a shared schedule for restore and reporting.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.