How many SQL Server upgrades have you been a part of? Was there much discussion about whether or not to do an in-place upgrade versus a side-by-side upgrade? I’ve been part of both and to me there is only one safe way to perform upgrades. Let’s go over some pros and cons of each and see if you come to the same conclusion.
When you’re performing an in-place upgrade, there’s no need to spec out new servers or check that all of your application connections point to new servers. From an application standpoint, it’s pointing to the same location before and after the upgrade. This means less required prep work outside of SQL Server.
When it’s time to upgrade you can open the SQL Server setup file, follow the installation center instructions, and relax when you get the “Your SQL Server upgrade completed successfully” message.
What’s that? You got an error when you were installing? Or your install completed successfully but now your application isn’t working as expected?
If you need to rollback, you better have packed a lunch because it’s going to take some time and effort.
What options do you have? There’s not really a good way to rollback and in-place upgrade. You’re going to be looking at uninstalling your newly installed SQL version, reinstalling the previous SQL version, and then restoring every database on the server. Does that sound like a quick process? Imagine if you get the point of restoring and start having issues restoring backups. I wouldn’t wish that pain on my worst enemies.
For a side-by-side upgrade, more preparation is needed but that should not be looked at as a negative. Along with determining specs with SQL Server in mind, ask yourself what the rest of your environment looks like. For example, if you’re upgrading from SQL Server 2012 to SQL Server 2016, is it now also time to upgrade the OS from Windows Server 2012 R2 to Windows Server 2016?
With a side-by-side integration, you have a clear back out plan. If there are problems on the new server and version, you can switch back to the old version right away. And assuming you’re keeping both versions in sync up until cutover, you can plan for a much smaller outage window while switching to the new version.
Do You Feel Lucky?
In-place is “easier” if it goes right the same way it’s “easier” to get rich in Vegas when you go all in on your first hand at the Blackjack table. If you are dealt 21 then you’re set for years to come. If you lose it all, good luck finding a way home. Do you really want to take that gamble?
Thanks for reading!