Log Shipping Recovery Procedure

Network_Padawan
Network_Padawan used Ask the Experts™
on
Hi,

I have just configured log shipping from UK to Syd. Appears to be working fine. Logs are being sent across to secondary server share and applying them to the db.

My question now comes in regards to recovery test scenario. If I pull the plug on the primary server, I understand that I need to run the following command to get the secondary server online


restore database {db name}
with recovery

The problem is we now have all users  (once we manually direct all traffic to UK) connecting to the UK, we would obviously want to bring up the primary and have that as the source server again and the UK db as the secondary.

What are the procedures to do this with NO disruption? The problem I foresee is that copying the backup of the dabatase across to Syd will take at least a day, in which time the db will become obselete as records would have been updated on the UK server by then.

What are your opinions? Or should I disregard Log-shipping altogether and look towards mirroring?
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Vitor MontalvãoIT Engineer
Distinguished Expert 2017

Commented:
A Log Shipped database can't come online without stopping the process so you'll have always disruption.

Commented:
In the DR situation, you would enable 'reverse-shipping' i.e. you would end up witht eh same situation , with Syd as the primary site, shipping logs to UK as DR. Once you are ready to revert back to the UK site you can simply stop the shipping and change it to go the other way again.

Author

Commented:
hi eabeukes,

I guess my question is...if we want to revert to the Syd db the primary again once a failover as has taken place...how do i do this exactly?

The log-shipping configuration specifies a primary and secondary, if the primary goes down then the secondary is taking all the requests, if i restore the primary db back from a restore, it is an outdated copy as the secondary has since had new entries.

How do I update the newly restored db to have all those latest entries that have taken place in the meantime to the secondary.

I hope Ive made myself clear.
Acronis in Gartner 2019 MQ for datacenter backup

It is an honor to be featured in Gartner 2019 Magic Quadrant for Datacenter Backup and Recovery Solutions. Gartner’s MQ sets a high standard and earning a place on their grid is a great affirmation that Acronis is delivering on our mission to protect all data, apps, and systems.

Commented:
OK I see what you mean.

If you still had the UK DB in place, all you would need are the t-logs from the Syd DB and then restore those to the UK DB, and set it up as the primary again.

SO::

UK DB ---log ship t-logs---> Syd
UK DB breaks
Syd DB takes over
UK log shipping paused
Syd DB --> t-log backups to disk
To 'recover' UK DB::

Restore last full backup
Restore t-logs from Syd DB
Start UK DB and check

If all OK, you would want to do a full log-ship backup/transfer/restore operation to the Syd DB to ensure integrity.

Are you using any compression by any chance? SQL2008 has it built in, but the lower ones can be setup to call a zip programm as in this example:
http://knol.google.com/k/sql-server-2005-low-bandwidth-log-shipping-configuration#

Author

Commented:
Ok so your saying, as part of the log shipping process....

1. Primary stores tlogs in a share on the secondary server that as part of "normal" log shipping, it used to collate the latest updates and restores them into its DB.

2. When Primary goes down, I must go to the secondary and do the following:

restore database {db name}
with recovery

3. Now do a restore on the "downed" primary from the latest backup, and restore the the tlogs from the secondary server (which is now live) to the primary.

Is this it in a nutshell?

If so, don't I need to schedule an outage when doing step "3" cause if I dont, there will be added entries to the now live databases that are not during the restore process? I guess what I am asking is, when restoring the downed db, don't i need to do a maintenance outage on the live db for 10 - 15 min to ensure the primary has the latest entries?
Commented:
You will always have to take an outage to swap over, unless you used database mirroring. This will enable you to supply both databases in your connection strings so in the event of a failover everything is seamless including the fail-back.

The process above should work, however I would be inclined to alter it to the following:


1. Primary stores tlogs in a share on the secondary server that as part of "normal" log shipping, it used to collate the latest updates and restores them into its DB.

2. When Primary goes down, I must go to the secondary and do the following:

restore database {db name}
with recovery

3. Now do a restore on the "downed" primary from the last backup of secondary, and setup log shipping from the secondary server (which is now live) to the primary. Once you have confirmed that the primary is OK, restart primary as per step #2.

It does mean shipping the whole DB backup over the line, and having users work against the remote DB but this is during DR invocation, so speed will be the last thing on users minds.

Author

Commented:
Fantastic! Thanks

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial