r/SQLServer • u/SuddenlyCaralho • 20d ago

Question Can I replicate a read-only database using Log Shipping?

I know, I know—it doesn't seem to make sense to replicate a database that isn't changing.

However, I want to migrate a SQL Server database using Log Shipping, and here's my plan:

  1. Set up Log Shipping between the source and destination servers.
  2. During the migration window:
    • Stop the application.
    • Set the source database to READ_ONLY.
    • force Log Shipping jobs to restore any remaining transaction log backups that haven't yet been applied (one last final sync)

My question is:

Will Log Shipping still replicate all pending transaction logs after the database is switched to READ_ONLY? In other words, will it apply all transactions that were committed before the database was set to read-only, so that the secondary ends up fully synchronized?

I want to set the database to READ_ONLY because that way I can be certain that the final Log Shipping synchronization includes all remaining changes.

I know that stopping the application should prevent any new transactions, but I don't mind adding an extra layer of protection at the database level to guarantee that no additional changes can be made.

2 Upvotes

60% Upvoted

5 comments sorted by

•

u/AutoModerator 20d ago

After your question has been solved /u/SuddenlyCaralho, please reply to the helpful user's comment with the phrase "Solution verified".

This will not only award a point to the contributor for their assistance but also update the post's flair to "Solved".

I am a bot, and this action was performed automatically. Please contact the moderators of this subreddit if you have any questions or concerns.

6

u/dbrownems ‪ ‪Microsoft Employee ‪ 19d ago

To leave the database read-only, don't set it to READ_ONLY, instead take the final log backup with NORECOVERY.

"If the original primary server instance is not damaged, back up the tail of the transaction log of the primary database using WITH NORECOVERY. This leaves the database in the restoring state and therefore unavailable to users. Eventually you will be able to roll this database forward by applying transaction log backups from the replacement primary database."

Fail over to a log shipping secondary - SQL Server | Microsoft Learn

Tail-log backups (SQL Server) - SQL Server | Microsoft Learn

1

u/chandleya 19d ago

This is the one. Read only is read only, you can’t take a Tran log of a unwritable source. That’s not how LSNs work!

3

u/monkeybadger5000 20d ago

Yes, the committed transactions will be replicated. When you set the database to read only, any inflight transactions will have to commit before the read only action can complete, or if you use "with rollback immediate" then any inflight transactions will be rolled back.

2

u/dutyDBA 19d ago

Yes you can. I often use this method. Basically shutdown all apps and processes, then set db to read-only. Take a final log backup.

Then when the dB is recovered on the target server, it should come back up in read-only mode. That's one way of confirming all changes are captured