Moving SQL DB - Perfect Storm

  • 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 !

  • 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

  • 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.

  • 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.

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

  • 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.

  • I would implement a "Log Shipping" scenario between the servers and then just cut-over when the time comes

  • A free tool such as 7-zip may be able to compress a file that size, not sure.

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

  • 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"

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply