The question:
We are planning to upgrade our existing SQL Server 2016 instances to SQL Server 2019. Our application is in some other environment already running on SQL Server 2019 so we should not have any compatibility issues. We are running on Azure VMs that are only a coupple of years old and and there are no need for upgrading the OS. We are currently deciding between doing an in place upgrade or migrating to a new server.
On one hand we have the benefits of a migration with testability, easy rollback and short cut over time but require changes in our customers evironments. These changed in the customer environments are somwehat work intence and will also need action from our customers.On the other hand we have the risks with in place upgrade bit instead minimal changes on the customer environments.
I have only been able to find some vague mentioning of issues wiht in place upgrade that have taken days to fix but no specifics about what kind of issues. My question is what are these potentially risks and how likely are they to happen?
The Solutions:
Below are the methods you can try. The first solution is probably the best. Try others if the first one doesn’t work. Senior developers aren’t just copying/pasting – they read the methods carefully & apply them wisely to each case.
Method 1
FWIW, I just recently did a “semi in-place” upgrade from SQL Server 2016 CU2 to SQL Server 2019 RTM, without any actual issues. It was in-place of a new and less used server that we then migrated the databases from the old server over to after the upgrade. So we had the original old server still available in case something did go wrong during upgrade but once the upgrade and migration was done and we turned the new server on in production, we could no longer easily access the old server for production purposes, hence me calling this “semi in-place”.
One minor issue we did run into after the upgrade was the RTM version of SQL Server 2019 had a few bugs, and we needed to patch it to the latest CU. This was an easy fix, and would’ve occurred regardless if we did an in-place upgrade or a side-by-side one, and we realized it proactively because we did our upgrades in our DEV environment first.
In-place upgrades aren’t necessarily problematic, and in fact Microsoft even offers information and steps on how to proceed with an in-place upgrade. Rather the issue with in-place upgrades is they overwrite the existing SQL instance on that server, and there’s no recourse for rolling back if an issue occurred. So you should ensure to have proper backups (in my opinion at least a server level snapshot of the box) and recovery / rollback plans in place, should in the unfortunate case something did go wrong and you needed to rollback, as cautioned in the aforementioned Microsoft docs:
When you upgrade SQL Server, the previous SQL Server instance will be overwritten and will no longer exist on your computer. Before upgrading, back up SQL Server databases and other objects associated with the previous SQL Server instance.
That all being said, of course there are benefits with a side-by-side upgrade that the in-place upgrade doesn’t have, such as the ability to benchmark performance changes in the new version and being able to cut back over to the old version / server easily, among other reasons. So a side-by-side upgrade is usually the preferred and recommended approach, but there are some cases where an in-place upgrade makes more sense too.
All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0