SQL Server Performance Forum – Threads Archive

Testing a 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 <img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ /> 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 <img src=’/community/emoticons/emotion-5.gif’ alt=’;-)’ /> * 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.

In your case test the Disaster Recovery solution as per the setup, in any case restoring the database backup is the first and best solution to ensure your DR solution is working and in place. If any issues with the primary server immediately switch to secondary server and then assess the issues on the primary server by reviewing event viewer log, sQL error logs and etc. Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided â€œAS ISâ€ with no rights for the sake of knowledge sharing.

Thanks for the advise Satya, can i confirm what you think is best is to switch to the secondary server using appropriate scripts for bringing secondary server online.
Once we’ve tested that and happy, back it up and restore that to the live primary server and setup log shipping again. I believe i can make my current primary server a secondary standby once i’ve switched over but can i not reverse that back yet again after testing successfully? Also do you have any experience of what to do for jobs and DTS packages as we have a load of them currently on the primary server which we would need to get over to the secondary. One idea we’ve had which we are testing is to set up a cname in DNS and put that in the global variables of each DTS package and if we needed to switch for whatever reason we only need to change one setting within DNS.

The total time of switch over depends on the database size and its usage, and if you have any jobs such as DTS too then refer to this linkhttp://vyaskn.tripod.com/moving_sql_server.htm which guides you the process. Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided â€œAS ISâ€ with no rights for the sake of knowledge sharing.

]]>