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.


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.

meena resma said...

It was so nice article. I was really satisfied by seeing this article. SQL server dba Online Training Bangalore

Coepd BA Trainings said...

We at Coepd declared Data Science Internship Programs (Self sponsored) for professionals who want to have hands on experience. We are providing this program in alliance with IT Companies in COEPD Hyderabad premises. This program is dedicated to our unwavering participants predominantly acknowledging and appreciating the fact that they are on the path of making a career in Data Science discipline. This internship is designed to ensure that in addition to gaining the requisite theoretical knowledge, the readers gain sufficient hands-on practice and practical know-how to master the nitty-gritty of the Data Science profession. More than a training institute, COEPD today stands differentiated as a mission to help you "Build your dream career" - COEPD way.

Trendy Peaks said...

Thank you for some other informative website. The place else may just I get that kind of information written in such a perfect method? I have a venture that I am simply now running on, and I’ve been at the glance out for such info.