Wednesday, August 17, 2016

Extremely slow log shipping restore in standby mode ...

Recently I was working on an environment where the requirement was to use a log shipped secondary database for reporting purposes. The catch here was that the restore of all the transaction log backups needed to complete within a fixed time.
I understand that the restore of transaction logs are dependent on a number of parameters like disk performance, number of transactions present in the log backup, resource availability on the server where the restore is occurring, But considering that all the factors remain constant, there is a basic difference between a secondary log shipped database in No Recovery and in Standby mode. In Standby mode, a partial recovery is done with the help of a transaction undo file to get the database into a read-only state. This partial recovery operation and undo that occurs while performing the next log restore (with the use of the transaction undo file) requires time and resources and can slow down the time of restore for each transaction log backup that is restored on the secondary database, especially if there are resource contention issues on the server.
There can be a significant amount of time saved when operating usingnorecovery as compared to standby mode for log shipping. Since it is not supported to directly modify the log shipping jobs, you can create your own job which executes the necessary T-SQL commands and invokes the necessary log shipping jobs in the correct order.
The correct flow of events would be:
1. Change log shipping restore mode to norecovery using the stored procedure sp_change_log_shipping_secondary_database
2. Start the log shipping restore job
3. Change the log shipping restore mode to standby using the stored procedure sp_change_log_shipping_secondary_database
4. Initiate the log backup job on the primary server
5. Initiate the log copy job
6. Initiate the restore job in the same order to ensure that the new restore mode is affected as an operating mode changes only after a new transaction log backup is restored.

1 comment:

Jones Sathya said...

Dot Net Framework is the software framework developed by Microsoft Corporation. A programming infrastructure created by Microsoft for building, deploying, running applications and services use of Learn .net technologies such as desktop applications and web services.