How to succesfully set up sp_AllNightLog to create a Reporting Server?

Question

I would like to set up a secondary, mirrored SQL Server for reporting purposes.

The reporting database doesn't have to be up to date with the primary and can generally lag behind the production database by as much as one day, maybe more.

I've been trying to achieve something like this for a few days now with little to no success, using Brent Ozar's SQL-Server-First-Responder-Kit and sp_AllNightLog.

Generally, the first FULL backup gets restored, and sometimes even a few LOG backups, but then it gets stuck, it stops seeing newer backups and just goes in a loop, doing nothing.

Troubleshooting seems to indicate to me that sp_AllNightLog @Restore = 1 runs into an error when restoring a database, or maybe a LOG file, records that in the restore_worker table, which stops all further restores from being issued. If I clear the error_number (-1) and last_error_date from the table, it starts restoring again but then hits an error again because it starts restoring the FULL backup instead of continuing with LOG restores. Then it logs that error date and error number and stops again.

Another issue is that sp_DatabaseRestore (from the Kit) leaves the restored databases, either from a FULL restore or LOG restore, in an unrecovered state (always uses NORECOVERY and never runs RESTORE WITH RECOVERY). That makes the databases unreadable on the secondary unless I change sp_AllNightLog from using @RunRecovery = 0 to @RunRecovery = 1:

-- Line 1302
IF @restore_full = 0
    BEGIN

        IF @Debug = 1 RAISERROR ('Starting Log only restores', 0, 1) WITH NOWAIT;

        EXEC master.dbo.sp_DatabaseRestore @Database = @database,
             @BackupPathFull = @restore_path_full,
             @BackupPathLog = @restore_path_log,
             @ContinueLogs = 1,
             @RunRecovery = 1,
             @OnlyLogsAfter = @only_logs_after,
             @Debug = @Debug

    END

IF @restore_full = 1
    BEGIN

        IF @Debug = 1 RAISERROR ('Starting first Full restore from: ', 0, 1) WITH NOWAIT;
        IF @Debug = 1 RAISERROR (@restore_path_full, 0, 1) WITH NOWAIT;

        EXEC master.dbo.sp_DatabaseRestore @Database = @database,
             @BackupPathFull = @restore_path_full,
             @BackupPathLog = @restore_path_log,
             @ContinueLogs = 0,
             @RunRecovery = 1,
             @Debug = @Debug

    END

Do I have to delete backup history before I set this up? Has anyone written a complete guide to setting something like this up? I genuinely can't seem to find one even while googling.

Any help is greatly appreciated!

Answer 1

Like any transaction log restore, sp_AllNightLog needs exclusive access to a database in order to do the restore.

This means you'll run into trouble when:

1. A user starts a very long-running query
2. sp_AllNightLog tries to start a log restore, and is blocked
3. Other users try to start queries, but are blocked by sp_AllNightLog, leading to timeouts and angry users

I wouldn't use sp_AllNightLog for this purpose (and that's not what it's designed for, so that's why you're not finding tutorials on how to do it.) If you DID insist on doing it, you would need to write code to:

1. Check to see if there are any user queries running
2. If yes, then bail out gracefully without trying to start a restore (but probably check to see if it's been a long time since a restore has been done - because you might be in a position where you need to kill the reporting query to get the data caught up)
3. If no, then start the restore process

That is left as an exercise for the reader.