0

We are about to upgrade our SQL Servers from 2017 standard to 2019 enterprise. Does anyone have any real world experience on the best way to do this? We do not use HA, so our one prod server needs to be upgraded. We have a "Staging" server that is a day old restore of the prod database so my first thought is to try to upgrade staging to 2019 and make sure everything works, then move Production to 2019 if there are no concerns. Is the only "Backout" plan if we find something does not work that is a show stopper completely remove SQL on the prod server, reinstall 2017 and restore all system databases and user databases? Is there a "Rollback" if there is an issue? How have you done this? upgrading to standard -> enterprise should not break anything since it just adds features, correct?

1
2

you are asking two things.

  • edition upgrade
  • version upgrade

edition upgrade is when you want to change from standard to enterprise.

https://docs.microsoft.com/en-us/sql/database-engine/install-windows/upgrade-to-a-different-edition-of-sql-server-setup?view=sql-server-ver15

It is quite easy, mount the sql image and in the menu select maintenance then edition upgrade. Try it on your staging server and go easely.

Version upgrade is when you change sql server version from 2017 to 2019. This is something you have to test carefully because can generate performance regression in query plans or other issues in appplication activities. MIcrosoft suggest to evaluate it using this model:

https://docs.microsoft.com/it-it/sql/database-engine/install-windows/choose-a-database-engine-upgrade-method?view=sql-server-ver15

I suggest you to upgrade on a new server as a side-to-side migration, starting from DMA:

https://docs.microsoft.com/it-it/sql/database-engine/install-windows/prepare-for-upgrade-by-running-data-migration-assistant?view=sql-server-ver15

Then do some test migration to a new sql server 2019 env end test deeply applications.

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

1
  • 1
    I second side-by-side. In-place upgrades are time bombs. – Aaron Bertrand Jan 20 at 19:27

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