SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Testing Secondary database in Log Shipping


Testing Secondary database in Log Shipping

Author
Message
alark
alark
SSC Journeyman
SSC Journeyman (86 reputation)SSC Journeyman (86 reputation)SSC Journeyman (86 reputation)SSC Journeyman (86 reputation)SSC Journeyman (86 reputation)SSC Journeyman (86 reputation)SSC Journeyman (86 reputation)SSC Journeyman (86 reputation)

Group: General Forum Members
Points: 86 Visits: 300
Our customer wants to test all secondary databases in our Log Shipping environment. Since the test require writes/updates, I will need to change databases from StandBy to Online mode. As I understand correctly, it will break the log shipping.
Is there a nice way to conduct the test without breaking log shipping? If not, then how should I proceed to have the least amount of work to do later? Or there is no way and I will need to build log shipping from scratch again?



Ian Scarlett
Ian Scarlett
SSCrazy
SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)

Group: General Forum Members
Points: 2219 Visits: 6875
You won't break log shipping... have a look in BOL at Log Shipping Role Changes.

Essentially, you do your final transaction log backup at the primary using "WITH NORECOVERY", then
restore this backup at the secondary using "WITH RECOVERY"

Your secondary is now acting as the primary, so all the backing up and restoring of logs is reversed. When you are ready for the primary to assume it's original role just do the reverse (backing up the secondary WITH NORECOVERY etc).



GilaMonster
GilaMonster
SSC Guru
SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)

Group: General Forum Members
Points: 87521 Visits: 45272
Since you're bringing the secondary DB online and making changes, there's no 'go back' after that. Take a full backup from primary, restore on the secondary WITH NORECOVERY and the log shipping should continue.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass


Ian Scarlett
Ian Scarlett
SSCrazy
SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)

Group: General Forum Members
Points: 2219 Visits: 6875
GilaMonster (3/27/2012)
Since you're bringing the secondary DB online and making changes, there's no 'go back' after that.


Are you sure? This was something I had done previously (SQL2000 admittedly) with a customised log shipping solution.

Backing up the last transaction log at the primary using WITH NORECOVERY and then restoring that on the secondary keeps the LSNs in sync. That allows you to you to start backing up at the secondary and restoring them at the primary.



GilaMonster
GilaMonster
SSC Guru
SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)

Group: General Forum Members
Points: 87521 Visits: 45272
He's making changes to the secondary. Hence the LSNs won't be in sync after that. I don't read that as asking how to switch log shipping around, just how to test the secondary without breaking the log shipping to it (and that can't be done)

Restoring a log WITH RECOVERY will bring the DB online all right, nothing wrong there. If changes are made to the secondary, it can't be just backed up WITH NORECOVERY afterwards and have the log backups from the primary continue to be restored.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass


Ian Scarlett
Ian Scarlett
SSCrazy
SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)

Group: General Forum Members
Points: 2219 Visits: 6875
Agreed... it looks like our reading of the original requirement is different.

I assumed the test would involve a role switch and back again, but with some updates done at the secondary in between.

Doing a test to simply bring the secondary databases online doesn't prove much.



alark
alark
SSC Journeyman
SSC Journeyman (86 reputation)SSC Journeyman (86 reputation)SSC Journeyman (86 reputation)SSC Journeyman (86 reputation)SSC Journeyman (86 reputation)SSC Journeyman (86 reputation)SSC Journeyman (86 reputation)SSC Journeyman (86 reputation)

Group: General Forum Members
Points: 86 Visits: 300
GilaMonster, You heard me right. I will bring the secondary databases for testing the apps in DR environment, meanwhile primary databases will continue running in real production. Then after the testing completes, I need to restore log shipping. So my question is about the steps: Will I have to rebuild the log shipping for all DR databases from the scratch or I can just disable the existing LS jobs, restore full backups, then enable LS jobs?



GilaMonster
GilaMonster
SSC Guru
SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)

Group: General Forum Members
Points: 87521 Visits: 45272
I suspect the latter, but please do test that out on a dev/test machine first.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass


TheSQLGuru
TheSQLGuru
SSChampion
SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)

Group: General Forum Members
Points: 12487 Visits: 8553
alark (3/27/2012)
GilaMonster, You heard me right. I will bring the secondary databases for testing the apps in DR environment, meanwhile primary databases will continue running in real production. Then after the testing completes, I need to restore log shipping. So my question is about the steps: Will I have to rebuild the log shipping for all DR databases from the scratch or I can just disable the existing LS jobs, restore full backups, then enable LS jobs?


You will not be able to restore any additional tlogs from the still-operational primary after you open up the secondary databases for writes (i.e. restore a tlog with recovery). Time to reinitialize I am afraid.

Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
DBA_Dom
DBA_Dom
Say Hey Kid
Say Hey Kid (678 reputation)Say Hey Kid (678 reputation)Say Hey Kid (678 reputation)Say Hey Kid (678 reputation)Say Hey Kid (678 reputation)Say Hey Kid (678 reputation)Say Hey Kid (678 reputation)Say Hey Kid (678 reputation)

Group: General Forum Members
Points: 678 Visits: 81110
You will need to rebuild log shipping from scratch. This is because of the .tuf file needs to be recreated and can only be done by sql server itself. Believe me I have tried to recreate this file after accidentally deleting it and the only solution is to rebuild log shipping from scratch.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search