Menu
When configuring LogShipping for SQL Server, you can choose for the secondary database to be in No Recovery mode or Standby mode. No Recovery means you have no access to the database while log shipping is going on. Standby gives you read-only access, and if you select the option to disconnect users whenever a restore is about to happen, would appear not to interfere with the log shipping process. This looks to me like an extra benefit of standby mode, but as far as I can see the documention mentions no adverse affects.
I'm therefore wondering why anyone would choose to use No Recovery mode? The only plausible reasons I can think of are if Standby mode caused a significant performance degredation (but there's no mention of anything like that in the docs), or if there is some security requirement to actively prevent anyone seeing the contents of the secondary database (which would seem rare/unlikely).
Can anyone enlighten me what the advantage of choosing No Recovery mode is supposed to be?
847
Secondary log shipped databases can be kept in one of two modes: Restoring - This mode is also known as NORECOVERY mode and cannot be used for read operations. Standby / Read-Only - This mode is also known as STANDBY mode and can be used for read operations.
The primary database must use the full or bulk-logged recovery model; switching the database to simple recovery will cause log shipping to stop functioning. Before you configure log shipping, you must create a share to make the transaction log backups available to the secondary server.
You will need to initialize log shipping by restoring the full database backup from the primary to the secondary. Once you re-initialize log shipping by performing a full backup of the primary database, copying the backup to the secondary and then restoring the secondary database, log shipping will get re-synced again.
When you use NORECOVERY mode, no access will be given to the target database, so the database does not have to care about uncommitted transactions. The log can just be restored "as is" and left in that state.
When you use STANDBY mode, the database restores as NORECOVERY, then analyzes and rolls back all uncommitted transactions in the log. It can then give read only access to users. When the next log is restored, the database disconnects all users and rolls the uncommitted transactions from the last log forward again before restoring.
As you can see, STANDBY has potentially large extra overhead at restore, depending on your transaction volume.
More details at this article at My World of SQL.
97
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
