0

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.

7
  • Have you done some research on the topic? What have you found so far?
    – jpnadas
    Jun 23, 2020 at 11:17
  • My findings are what I put in the original post based on what I've read online, I think upgrading the OS to 2012 then upgrading SQL to 2017 will work, but I'm asking if this is the best way to go about it. E.g. if someone has experience of doing this then they might be able to tell me if that is a bad idea or suggest a better solution. Also any pitfalls that might occur.
    – LamNam
    Jun 23, 2020 at 12:03
  • I am sorry, but that is really not apparent in your question. Consider adding at least the sources of where you read about it.
    – jpnadas
    Jun 23, 2020 at 12:06
  • Ok, point taken, but have you done this task before ie can you actually help or provide advice? Or are you just here to point out flaws in my post?
    – LamNam
    Jun 23, 2020 at 12:34
  • SO is moderated by the community. Don't take this as a personal attack, this is all done to improve the level of the forum. I haven't done that task before, so I am sorry if I cannot help you with the answer you need.
    – jpnadas
    Jun 23, 2020 at 12:37

1 Answer 1

3

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.