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

Moving SQL DB - Perfect Storm Expand / Collapse
Author
Message
Posted Wednesday, June 5, 2013 8:05 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, July 18, 2014 2:53 AM
Points: 3, Visits: 372
Am struggling to arrive at a solution for a requirement that has come up,

We need to move a live Business process management system from our UK data centre to our US one, the database is around 300 GB.

They want zero downtime, or as close to as possible.

Problem is, tripple whammy of large database, long distance and a change of version from 2005 to 2012. To copy the backup for the database I have estimated nine hours.

We cant mirror due to the versions being different, not recommended.

Thinking of looking at moving a copy then getting up to current by automating log recovery at the other end, perhaps using DFSR to replicate the logfiles.

Open to any suggestions !
Post #1460244
Posted Wednesday, June 5, 2013 9:02 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Today @ 8:30 AM
Points: 834, Visits: 2,440
there's an MSDN link for mirroring from 2005 to 2012, so it must be supported...

http://msdn.microsoft.com/en-us/library/bb677181.aspx
Post #1460265
Posted Wednesday, June 5, 2013 10:03 AM
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: Tuesday, August 19, 2014 9:08 AM
Points: 550, Visits: 1,610
Copy a full backup to the new server, restore with no recovery. Then do a differential backup, copy to the new server can restore with recovery. Take notes on how long each step takes so when you're ready for the actual cutover you time time the beginning of the backup (or the transcontinental file copy) so that the differenital backup can correspond to the start of the scheduled downtime.

Or if the other suggestion of mrroring 2005 to 2012 won't work, but you have a suitable 2005 server & storage available at the US server, you can set up 2005 mirror at the US data center and make that the primary for the last day or so, and shorten the transfer time & uncertainty of a long-distance large file copy.
Post #1460317
Posted Wednesday, June 5, 2013 11:16 AM
SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: 2 days ago @ 3:52 PM
Points: 5,991, Visits: 12,923
Unless you can do SAN replication between the data centres and then attach the database files at cutover time you are looking at copying over the full backup and restoring, and then doing a final differential or log copy\restore at cutover time.

there will have to be some downtime when they stop the app and repoint it so the question is how much database activity will there be during the day or so spent copying and restoring the full backup. If the amount is small you can get away with manually doing a differential or the last log file (do a few during the day if you have to), if its large you will have to look at automating the log transfers (log shipping?).

Check you can go from your SP level of 2005 to 2012 in one go and ensure you have set up the server level objects up front (logins, agent jobs, whatever else you have)

A dry run would be nice, and presuming your new server is more powerful its usually faster to 'pull' the files over from the destination server than push from the source.

Overall elapsed time for the full backup may be reduced if you can compress, copy, decompress.


---------------------------------------------------------------------

Post #1460341
Posted Thursday, June 6, 2013 1:36 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, July 18, 2014 2:53 AM
Points: 3, Visits: 372
Cheers guys, some good suggestions there, I appreciate you takign time out of your busy days to assist.

Goign to present a few scenarios with the pros and cons to my management.

Current favourite is to do a two phase move to 2012 locally in the UK data centre, potentially by detaching the database and the san luns from the current 2005 server and presenting them to a SQL 2012 server, hopefully this should present very little downtime, risk here is if the data files get screwed, would have to go to backup.

Then the second phase would be to mirror from the UK to the US, that gets us on the same version globally plus for moving the initial copy we can utilise compressed backups, which is only a possibility with 2005 if we get SQL safe or something, not a huge benefit but copying 350 Gb or so across the WAN would not go down with our network guys.
Post #1460555
Posted Thursday, June 6, 2013 2:00 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Wednesday, August 20, 2014 5:58 AM
Points: 1,375, Visits: 2,663
I would implement a "Log Shipping" scenario between the servers and then just cut-over when the time comes

Post #1460565
Posted Thursday, June 6, 2013 3:04 AM
SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: 2 days ago @ 3:52 PM
Points: 5,991, Visits: 12,923
A free tool such as 7-zip may be able to compress a file that size, not sure.

---------------------------------------------------------------------

Post #1460589
Posted Thursday, June 6, 2013 3:38 AM
Say Hey Kid

Say Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey Kid

Group: General Forum Members
Last Login: Wednesday, August 20, 2014 7:28 AM
Points: 709, Visits: 1,417
I second the log shipping solution.

Have you tested how long it takes for a full backup of the database to copy over? If you have backup compression enabled that could reduce the copy time significantly.


Joie Andrew
"Since 1982"
Post #1460597
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse