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.
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:
- linked servers
- SSIS packages
- export sys.messages
- 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.