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.
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.
2 comments:
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. http://www.bestdotnettraininginchennai.com/
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.
mtbuzzer
Post a Comment