Sql-server – Need to upgrade SQL Server from 2012 to 2017 on a Server 2008 box

sql serversql-server-2012sql-server-2017upgradewindows

I have been asked to upgrade SQL Server from 2012 to 2017, it is currently installed on a server with Windows 2008 OS.

My thinking from what I've researched is this:

  1. Take a snapshot of the server
  2. Upgrade OS first from 2008 > 2012R2
  3. Run SQL Server data migration assistant to make sure nothing is incompatible with new SQL version
  4. Take a snapshot of the server
  5. Backup all DBS, script out all jobs/logins/linked servers, etc using PowerShell
  6. Upgrade SQL Server in place to 2017

From this thread I have found that 2017 should be compatible with 2012 OS.

And here it says I should be able to upgrade SQL Server directly from 2008 SP4 to 2017.

The only thing I'm not sure about is the initial OS upgrade, will this cause any issues with the current SQL install? Am I doing this in the correct order?

If anyone has done this kind of upgrade I would be grateful if you could advise me if I'm going about this the correct way or if there's a better solution I'm not seeing.

Best Answer

This will be somewhat an opinion answer, so likely to be downvoted.

First up, in-place upgrades are fraught with issues. So many things can go wrong along the way and you have no fallback position except a restore. This means you must first ensure that at every step you have a restore option, for the databases and the OS. However, I recognize that not everyone can do a side-by-side upgrade.

Yes, you're doing this in the correct order. Get the OS upgraded first and ensure it's stable. Then do the SQL Server upgrade. Both these are fairly standard upgrades and under normal circumstances should go off without a hitch. However, you're missing steps on the upgrade.

While I understand that you can't do a side-by-side upgrade, I'd still do side-by-side testing. Get a second server, even if it's a VM on a dev laptop, and run through this whole process. Validate that it works. Don't trust some idiot on the internet. Most of your application code should work just fine. Some may not. Find that early, before you've committed to a difficult to undo upgrade.

I'd also add the step of recording standard behavior of the system now using Extended Events and then again after the upgrade so you have data to understand how the upgrade affected performance and behavior.

Because you're crossing the 2014 threshold, moving from 2012 to 2017, you have to deal with the new cardinality estimation engine introduced in 2014. For most queries, no difference will be apparent. For a few queries, you may see a performance enhancement. For a minority of queries, you may see horrific degradation of performance.

After you restore your databases to 2017, leave the compatibility mode in place, at 2012. Enable Query Store on your databases. Run your normal load for a period of time. It's on you to figure out how long. Depends on the system. Some could be a day, some a month, somewhere in between. I don't know. After running the load for this period, change the compatibility level to the new version. When you see regressed queries, use Plan Forcing to make them run the old way (the old plans will be in Query Store).

That should cover you better.

Related Question