1

Company may implement Log Shipping with Snapshot. This provides basic Disaster Recovery with Reporting area. Server A OLTP contains database which log ships to Server B (ReadOnly-Standby), Snapshot is taken on ServerB database. For updated data, we will create database snapshots every 30 min on ServerB.

(1) While restoring log every 30 min, I assume we cannot take snapshots on databases which are currently restoring, is that true?

(2) So plan is to take snapshots, when transaction log backups complete. If I have snapshot, and another transaction log restores occurs later on database, will be snapshot break or continue to operate normally?

0

(1) While restoring log every 30 min, I assume we cannot take snapshots on databases which are currently restoring, is that true?

Yes, all users has to be disconnected before the logs can be restored.

(2) So plan is to take snapshots, when transaction log backups complete. If I have snapshot, and another transaction log restores occurs later on database, will be snapshot break or continue to operate normally?

This depends on how you setup your Log shipping session. While setting up the Transaction log shipping, you have the option to Forcibly disconnect any users connected to the database or wait for users to disconnect, before the logs can be restored. But either way all users must be disconnected before the transaction logs can be restored.

enter image description here


Depending on your business needs (Preferably, get the business people involved in this decision) you can set the precedence for Log restore or Snapshot.

  • @BlueLamp82 if you are using SQL Server 2012 or later versions consider Always On Availability groups ( the best thing ever happened in SQL Server world) if you are using older version then Transactional Replication probably. – M.Ali Jun 17 '18 at 23:00
  • @BlueLamp82 Microsoft has announced,SQL Server 2008 R2 support is ending on 9th of July 2019, let them know, there will be no more bug fixing or patching for this version after this date, so to stay compliant they will have to upgrade to a later version. – M.Ali Jun 17 '18 at 23:07

Your Answer

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