SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Moving SQL DB - Perfect Storm


Moving SQL DB - Perfect Storm

Author
Message
Mark Jackson-443782
Mark Jackson-443782
Forum Newbie
Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)

Group: General Forum Members
Points: 3 Visits: 447
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 !
adb2303
adb2303
Ten Centuries
Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)

Group: General Forum Members
Points: 1294 Visits: 3150
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
dan-572483
dan-572483
Ten Centuries
Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)

Group: General Forum Members
Points: 1349 Visits: 1999
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.
george sibbald
george sibbald
SSChampion
SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)

Group: General Forum Members
Points: 10554 Visits: 13687
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.

---------------------------------------------------------------------
Mark Jackson-443782
Mark Jackson-443782
Forum Newbie
Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)

Group: General Forum Members
Points: 3 Visits: 447
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.
SQLSACT
SQLSACT
SSCrazy
SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)

Group: General Forum Members
Points: 2108 Visits: 2931
I would implement a "Log Shipping" scenario between the servers and then just cut-over when the time comes
george sibbald
george sibbald
SSChampion
SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)

Group: General Forum Members
Points: 10554 Visits: 13687
A free tool such as 7-zip may be able to compress a file that size, not sure.

---------------------------------------------------------------------
Joie Andrew
Joie Andrew
SSCrazy
SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)

Group: General Forum Members
Points: 2371 Visits: 2032
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"
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search