Testing Log shipping DR Solution
-
Hello
I am currently running SQL 2000 Enterprise Ed. on Windows 2000 Advanced Server on a cluster, disk storage provided by a SAN.
Yes, I'm v. lucky 🙂
The only downside is that if we lose our site, we lose our data, so I have setup log shipping to a different server on a different site.
I have successfully got my two databases across, but now I have two questions.
1. Is there a best practise for getting all the jobs & DTS packages across?
I can script out both, but I would need to change all references from primary to the secondary.
2. I need to test this, but is it an easy thing to flip between the Primary & Secondary, as it is on a Cluster? It is a more manual process, but I would like someone to confirm that switching the 'instance' back to the Cluster box is as simple as running the jobs that I need to run to make the Secondary the Primary, but on the other box.
Gosh, that's hard to read. Let me list the steps I would make in an ideal world.
* Setup log shipping - done
* Backup, backup, backup 😉
* Failover SQL 'instance' to Seconday Server
* Point clients at Secondary Server
* Test, including updates to DB
* Bring Primary back on line
* Log ship from Secondary to Primary to bring DB up to date? Or do I backup Secondary & restore to Primary ?
* Flip 'instance' back to Primary
* Point clients back to Primary Server
* Test, including looking for updated record(s)
Has anyone gone through this cycle, in a test scenario or, god forbid, for real?
If so, how was it for you? Did things work as advertised, or did you find gotcha's that you had to work through.
My biggest fear is flipping my instance to the secondary, but then being unable to flip it back.
any thoughts welcome.
Dave Jackson
-
This was removed by the editor as SPAM
-
Any one there? I can't believe no one has done this! Am I posting in the right forum?
Dave J
-
David,
I'm kind of surprised you haven't gotten any responses also! To be honest, this is the first time that I have seen this thread, but it was certainly a wake up call for me! I am shipping logs for one database currently, and I didn't consider jobs or dts. After thinking about it for a couple of seconds I realized that as of now, I'm ok, but its something that definitely needs to be addressed!
Good question! Lets see if we can spark some interest? I would think its in the right topic. Maybe everyone else is scrambling to ship their jobs and dts packages???
Steve
-
Thanks for the response, at least I know I'm not alone 🙂
On the issue of testing DR failure, what is your experience with Testing it? Specifically, flipping from ServerA to ServerB, by running the jobs that make it available, synchronising the logins etc., and the reverting back to ServerA. This is the critical bit. How easy is it to promote ServerB & then demote it back to secondary?
On the DTS & jobs question, I am intending to set a CNAME Alias on our DNS Server, and point all DTS packages at that. I then only need to change the DNS entry, & it *should* all work. Added bonus that none of the settings on the clients will need changing, as they all will be pointing at the CNAME entry, so only one place to change. 🙂
Jobs I'm less clear on, but still thinking about it.
Dave J
-
I just finished typing a long reply, clicked post, and it went away. To where, I don't know.
Anyway, the one database that we're shipping is our backup tape library, and its being shipped to the DR site. In testing, the link is broken, the database recovered, and the DR backup server is pointed to it. Its only been in place since our last DR test, so demoting hasn't been tested yet. Of course it would depend on the nature of the demotion; DR test (re-establish log shipping), production server crash (restore to production server and re-establish log shipping), company wide disaster (hopefully same as production server crash)
As for jobs and DTS, they're both stored in msdb. Would there be any issues with shipping a periodic full backup to the standby server and restoring it in the case of a disaster?
Steve
-
Same thing just happened to me, I pressed preview & it vanished!
So I'm typing this into an editor & I'll paste it in when done.
Here goes again...
Shipping the MSDB is a good idea if all the DTS packages are pointing at (local) ?
We use a Dynamic properties task & set the connection to a Global variable at the start of each package. This helps migrate from QA to UAT through to Live.
Setting that GV to a CNAME entry (posts passim) should sort that out.
Job are interesting in that the last step is:
-- Add the Target Servers
EXECUTE @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @JobID, @server_name = N'(local)'
IF (@@ERROR 0 OR @ReturnCode 0) GOTO QuitWithRollback
This uses (local), so that should be OK
I log to a UNC that is ServerA, so changing that to C:\LogFolder should work as long as C:\LogFolder exists on ServerB
Lots of DTS Reports write to a UNC on a FilePrint server in the same site as ServerA. In the scenario of losing a site this FP server would *not* be available, so we need a DR FP server & point DTS packages on serverB to write output to the DR FP. That could be sorted out with another GV? Not sure.
This means we need to ship msdb & then process it via a script?
My main concern is testing. I've said it before, but it's worth repeating
I want to fail over to ServerB, & point clients at it & do selects, updates etc. I then need to flip back to serverA. How do I do that without a full backup of serverB & restore onto ServerA? can I simply run the process again but in reverse?
Ether MS documnetation on getting back to where you were is lacking, or I cant find it.
An untested DR solution is not a complete one. How do you know it will work?
Someone must have done this. How easy was it to get back?
Dave J
-
Interesting thoughts... I (We) will be setting up a disaster recovery site a few states away next year and I have no experience in Log shipping... You have brought up questions I never thought of. I have got some serious planning to do. Jobs... DTS packages... hum..
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply