0

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!

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.

3
  • Wow! Thanks Brent! I'm humbled. Didn't expect the first answer to be from you. Couldn't I run restores at night? I don't need RTO/RPOs here. I thought I'd get away with something like 1h intervals. Are there any other solutions though? Maybe from MS or commercial? Jul 25 '19 at 10:47
  • Hahaha, you're welcome. The problem with restores at night is that you just can't guarantee that no other queries will be running - scheduled report jobs, CHECKDB, people who left SSMS queries open in tabs, etc. There are plenty of other solutions available - check for how to use regular log shipping as a reporting server, and you'll find lots of guidance out there for that. Just not for sp_AllNightLog, since that's not what it's designed to do.
    – Brent Ozar
    Jul 25 '19 at 11:05
  • Thanks a lot Brent! It's good to know that all is not lost, meaning that log shipping was not entirely the wrong way to go. Believe it or not, it didn't actually occur to me that there might be other log shipping solutions for building a reporting server. Jul 25 '19 at 12:01

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.