Question :
One of my clients would like to have the OS upgraded to Windows 2012 enterprise and then the plan is to upgrade the SQL Server to 2016. I would like to know what would be the steps in doing so and how to revert the same if something bad happens in between and the upgrade fails. I have not really done this in the past so I do not have knowledge on the same. Below are the details of my client system
- SQL Server 2008 R2 standard
- OS: Windows 2008 R2 standard
- RAM: 48 GB (32 GB usable)
- Server: Vmware (Virtual platform)
Answer :
OS changes, in my opinion, should always be done side-by-side, never in-place. In this scenario, SQL 2016 requires Windows Server 2012R2 or better, so you’ll need to view it as 2 separate, yet equally important migrations. My recommendation would be to do this in 2 phases:
1- Stand up a new server with Windows 2012R2. Odds are there will be hardware upgrades on the new server as the old server was probably built years ago. install the current version of SQL that you are using then migrate a copy of production over to it. Bang away at it and make sure the OS upgrade isn’t causing any undesirable behavior. After testing is complete, migrate production to the new server (SQL 2008R2, Windows 2012R2) and let it soak in for a few weeks. If everything is good and you’ve addressed any issues, proceed to step 2.
2- Stand up a new server with Windows Server 2012R2 and SQL 2016, copy production DBs and test again. Be careful with compatibility mode of the DBs as anything greater than 2014 (120) will have the new cardinality estimator which can effect query performance. Once testing on 2016 is complete, then migrate production again to complete the upgrade.
This is by no means a comprehensive list of things that you need to accomplish but an abridged version that just hits the overall flow of doing an upgrade like this.
Can you do it all in one step? Sure, but then if you hit an issue, the line between the SQL upgrade (which is a pretty major version jump) and the OS upgrade may become blurred and you won’t know which upgrade is giving you a headache.
You are using virtualization and you can use the advantages it provides. It comes with a powerful set of functionality which will make work much more easier.
You may use a copy of the virtual machine and test an upgrade in an separated environment while the origin system remains online.
Another option is to work on the original system and create snapshots before each major step of you upgrade process. This allows you to revert changes easily if it provides not the desired results.
Working on the origin system will also mean down time. Especially if your upgrade path is not tested and a longer downtime is a problem, the first option may be the recommend way. Also there, you may use the advantage of snapshots.
A third option may be a migration to a new clean environment. Virtualization allows you to run the new system parallel and when all services are ready and stable, you can take over the latest data and you are done.