locked
Using Mirroring to migrate 2005 to 2016 RRS feed

  • Question

  • Hi.  I need to migrate a fairly large database (525 GB) from SQL Server 2005 to 2016.  My goal is to compress the time to migrate the database during the outage window.  Full backups of the source database is taking between 2 and 3 hours.  I also do hourly tran log backups.  I'm aware of a few methods to get the database from source to target server: detach/copy/attach, or backup to a intermediate server (or directly to the target server) via UNC path and restore from there.  You can see that those two methods could take at least a few hours, and hold up the migration.   Another DBA recommended using combination of full and incremental backup/restore to compress the migration time.  Then I recalled that I had used mirroring in the past migrate a somewhat large (350 GB) from 2005 to 2008 R2.  I recalled it was fairly straightforward to set up.  I have in the past used incrementals to back up windows servers, but never SQL Server.  Have always just done fulls and tran log backups.  So not familiar with incrementals.  Finally to my question:  is mirroring a sensible solution the pre-stage the database on the target server, and just failover to complete the migration?  On the other hand, it seems that full/incremental backup/restore would be safer.  Thanks!
    Wednesday, October 3, 2018 2:50 AM

All replies

  • I would go with Log Shipping (the procedure the other DBA suggested you). The reason is, there is a risk of a break in the Mirroring if the upgrade goes out of whack. If that happens then your primary becomes useless. Also, one of the prereqs to set up mirroring is for both instances to be of same versions but lower to higher may be possible for upgrades. In any case, I would prefer Log Shipping over mirroring.  

    Check out the following post by Robert in which he talks about why Mirroring is not a good idea for migrations. 

    http://sqlsoldier.net/wp/sqlserver/whylogshippingisbetterthandatabasemirroringformigrations

    Log shipping is pretty easy to implement and if you want to lessen the migration time duration, run log backups frequently so there are not many log records to back up. The target DB on the new server would have been initialized with a full backup much ahead of the cut-over and keep it up-to-date with the log backups. Once the migration window starts, simply do the last log backup(tail-log) and take the DB offline. Restore it on to the target DB and recover it. That's pretty much all there to it. 

    I have also listed out steps in this thread, in case you want to skim through.

    Lastly, Mirroring is a deprecated feature.


    Please remember to click "Mark as Answer" if my response answered your question or click "Vote as helpful" if it helped you in any way.

    • Proposed as answer by Puzzle_Chen Thursday, October 4, 2018 9:53 AM
    Wednesday, October 3, 2018 3:55 AM
  • Hi there,

    "Another DBA recommended using combination of full and incremental backup/restore to compress the migration time." This idea seems to be OK. you can also automate this process to minimize manual process.

    good luck

    kumar

    Wednesday, October 3, 2018 5:49 AM
  • Both Mirroring and Log shopping are viable options here, since you said you need to minimize time going with backup restore would take time. 2-3 hours to backup and then almost double to copy. Then again take transaction log backup stop application move the backup and restore it and then bring database online. LS is easy to implement here and gives greater control mirroring is also easy but the "control" is less.

    Cheers,

    Shashank

    Please mark this reply as answer if it solved your issue or vote as helpful if it helped so that other forum members can benefit from it

    My TechNet Wiki Articles

    MVP

    Wednesday, October 3, 2018 6:14 AM