July 12, 2005 at 2:34 pm
The proc sp_change_secondary_role looks useful for setting your standby log shipping server as the primary ( ie, the primary has failed ). Mainly the part about getting it out of standby to recovery state and multi_user access.
The rest of the BOL information raises questions for me. We run a two-node sql cluster, with SAN storage. If any of that fails completely, we failover to the standby server. If both nodes of the sql cluster fail you can't reverse the log shipping flow and no real need to change the monitor role. If the SAN fails you can't access your last trans log backup, so the standby is as current as it's going to get. With the system databases on the SAN, either of those scenarios puts our prod SQL server offline.
With no full-time DBA here, my thought is to bring up the standby in an outage situation, and then on the weekend, do a full backup of the standby ( with all users locked out ) and restore it to the now fixed production hardware. No reversal of log shipping roles/flows.
Only question is how hard it will be to restore and get the "primary to standby " log shipping going again and in sync after the fact.
July 15, 2005 at 8:00 am
This was removed by the editor as SPAM
July 15, 2005 at 8:48 am
Since you are using the "out-of-the-box" LogShipping facility, check out this whitepaper:
http://support.microsoft.com/default.aspx?scid=kb;en-us;323135&sd=tech
But to answer your question, it is not really difficult to restore the "primary to standby" log shipping, because the easiest (and in my opinion most reliable) way to accomplish this is by dropping the logshipping relationship and recreating it (without initializing the "target" database).
Hope that this helps.
July 20, 2005 at 10:20 am
Have you thought about simply disabling the log-shipping copy & restore jobs (at the primary and secondary sites, respectively) + the monitoring for those jobs, then bringing the databases online at the secondary?
This is "good enough" for DR testing, and does not require a teardown of the log-shipping setup. If the primary has failed for whatever reason, then that saves you having to disable anything at that location.
Using the standard GUI for log-shipping setup will also create (as disabled) jobs at the secondary site for transaction log dumping. Enable those and you're done.
Following the DR drill, sync the secondary to the primary and re-enable/disable the jobs that were touched.
Regards, Melissa
July 20, 2005 at 8:01 pm
Yes I'm all for simplicity here. If I can't get to the primary hardware during an outage I don't have to worry about restoring a final trans log to the standby.
I can disable the restore job at the secondary and do:
restore database <myDBname> with recovery
I'm not sure I would care if the trans log continue shipping ( copying ) from primary to standby server, but in any outage I can imagine there wouldn't be any to copy. Might also disable some alerts at the secondary during the outage so as not go be nagged.
I think what you meant by the following was the jobs created to ship logs from standby to primary? I didn't select that option, sounded too messy. For us, if primary goes down it stays down until the weekend when I backup the standby and restore onto primary ( ie no reverse log shipping flow ever )
Using the standard GUI for log-shipping setup will also create (as disabled) jobs at the secondary site for transaction log dumping. Enable those and you're done.
thx,Randy
July 21, 2005 at 9:41 am
One more thing. In either a real outage of the primary, or in DR testing I want the best tsql commands to get the standby out of standby for use, and then to put it back in standby to resume log shipping from the primary after the outage. Does the following look about right ( again, I'm not reversing the log shipping flow ever )
drop database collateralmanager
go
restore database collateralmanager from disk='c:\database\data\CollateralManager.bak' with standby='c:\undo.ldf'
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply