Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Testing Secondary database in Log Shipping Expand / Collapse
Author
Message
Posted Monday, March 26, 2012 6:01 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, October 15, 2014 10:25 AM
Points: 42, Visits: 257
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?



Post #1273113
Posted Tuesday, March 27, 2012 1:39 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Wednesday, September 17, 2014 9:19 AM
Points: 1,307, Visits: 4,508
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).



Post #1273247
Posted Tuesday, March 27, 2012 2:15 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 2:09 PM
Points: 40,193, Visits: 36,597
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 2008, MVP
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

Post #1273271
Posted Tuesday, March 27, 2012 2:26 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Wednesday, September 17, 2014 9:19 AM
Points: 1,307, Visits: 4,508
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.



Post #1273281
Posted Tuesday, March 27, 2012 2:45 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 2:09 PM
Points: 40,193, Visits: 36,597
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 2008, MVP
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

Post #1273288
Posted Tuesday, March 27, 2012 2:57 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Wednesday, September 17, 2014 9:19 AM
Points: 1,307, Visits: 4,508
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.



Post #1273296
Posted Tuesday, March 27, 2012 8:46 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, October 15, 2014 10:25 AM
Points: 42, Visits: 257
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?


Post #1273551
Posted Tuesday, March 27, 2012 8:47 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 2:09 PM
Points: 40,193, Visits: 36,597
I suspect the latter, but please do test that out on a dev/test machine first.


Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
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

Post #1273553
Posted Tuesday, March 27, 2012 10:05 PM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Today @ 2:09 PM
Points: 4,400, Visits: 6,261
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 at GMail
Post #1274039
Posted Friday, March 30, 2012 1:48 PM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Today @ 7:37 PM
Points: 568, Visits: 73,854
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.
Post #1276007
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse