2

I'm planning to do SQL Server migration of SQL Server 2012 to a new hardware and a new version of SQL Server (2016). I'm trying to find the best solution with minimal downtime. I found some suggestions on the internet that in theory sounds great. I'm talking about DB Mirroring and Rolling Upgrade. But I also have read that MS does not recommend DB Mirroring between different SQL Server versions. So now I'm confused should I or shouldn't use DB Mirroring for this. Any suggestions? Have anybody tried that method before? I'm also thinking about Log Shipping.

  • 1
    Mirroring may be possible but since you are doing this for migration the support does not comes into picture and I believe this would not exist post migration. I would suggest to use logshipping just because of different version – Shanky Jul 4 '18 at 7:19
  • DB Mirror would not exist post-migration, only during migration. Thnx for a suggestion, I think I'll go with Log Shipping. – IgorG Jul 5 '18 at 9:15
5

We used successfully mirroring to migrate databases from SQL 2005 to SQL 2016 (on new hardware). All worked flawlessly. On hardware that was 13-14 years old and using Windows Server 2003.

Can't say anything about MS support, as since we had such an old version, we didn't have any type of support from MS (not even Extended).

If you're curious, here's a short list of steps we used:

Prerequisites

  • logins
  • linked servers
  • jobs
  • SSIS packages
  • export sys.messages

Mirroring preparation

  • setup endpoints and permissions
  • disable log backups
  • restore dbs on mirror
  • mirror setup on both the principal and partner
  • re-enable backups

Actual migration - the only moment of downtime

  • manual fail-over, make sure you do it step by step, as it will take a while to get in sync
  • break mirror and set dbs online
  • set the databases on the old principal as OFFLINE, so the apps won't connect to it
  • UPDATE STATS (fully if possible) - can't stress how important this step is
  • then enable the application logins and you can get on with it

Everything gets a tad more complicated with replications, but you get the point. It works very well. Log shipping will work too, no worries. But since it involves a bit more manual steps, we preferred to reduce the complexity if possible.

  • Thnx for a detailed answer and step by step procedure, I appreciate your effort. I might give a try :) – IgorG Jul 5 '18 at 9:19
  • It is working, trust me :). Did it for 6-7 instances last year. It takes some preparation to make it work well, but so is any other method. Don't fear mirroring if you've worked with it before. – Marian Jul 5 '18 at 12:24
  • One quick question, what's the next step for "export sys.messages"? How will you use them? – Just a learner Jul 15 '18 at 15:04
  • Ha, that's useful only if you use custom error messages in your stored procs. If not, you can ignore this. – Marian Jul 23 '18 at 9:34

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.