Upgrading from SQL Server 2012 to 2014

Question

Hello.

One of our servers is currently running the SQL Server 2012 R2 Standard database. This version, while quite old, has been vetted to work with the application that utilizes it. A newer version of the application is now being deployed, and as such, the version of SQL server that needs to be in place is the 2014 Service Pack 2 release.

I have been doing some research, and there are two upgrade possibilities. The first is an in-place upgrade that should retain all settings without issue. The second is to uninstall the existing SQL server, and do a subsequent installation of the newer version. Either procedure will involve a full backup of all databases prior to being set in motion.

I wanted to get feedback from the community as to which approach would prove to be the better of the two.

Answer 1

It really boils down to time and space. Firstly I'm assuming that the server hosting SQL 2012 also supports installing SQL 2014. If that isn't true then the rest of the discussion is mute.

If you do an inplace upgrade then the server will go down while the upgrade is running. You'll likely need to reboot and then you are back up and running. However if anything goes wrong then you'll need to either restore the machine itself or uninstall SQL 2014, reinstall SQL 2012 and restore your DB. An inplace upgrade is designed for cases where you want to replace the existing database server AND you don't want to have to worry about migrating custom settings and whatnot from one instance to another. Of course the instance name is probably not going to change so hopefully you're not using an instance name of SQL2012 or something.

A side by side upgrade works well if you need to have the DB online while you are doing the upgrade and you have the resources to run both. In this case you would install SQL 2014 (you don't need to uninstall SQL 2012 first), migrate the DB and do your testing. If something goes wrong you still have the current SQL 2012 instance available. But any changes that might have been written to the old DB would be lost after you migrate the DB. Of course you'll need to set up the new instance with all the same configuration your old DB has such as security, and runtime configurations (if any). You could always restore the DB a final time to get any changes made while you were doing your testing. Once you are on the new DB then you can remove the old instance. This might require a reboot however.

All things being equal an inplace upgrade is probably the fastest route. However some old database settings may linger afterwards (such as the accounts being used and configured permissions) so you might run into issues in the future if you upgrade again. A clean install requires more effort but is more likely to be "pure" as it will be using the current SQL settings.

Answer 2

One of our servers is currently running the SQL Server 2012 R2 Standard database. This version, while quite old, has been vetted to work with the application that utilizes it. A newer version of the application is now being deployed, and as such, the version of SQL server that needs to be in place is the 2014 Service Pack 2 release.

Sorry, but this is just insane. SQL 2014 has been out of mainstream support for a few years by now. And if you move to SQL 2014, that should be SQL 2014 SP3 CU4, that is the last cumulative update of the last service pack. Then you will at least get security updates!

As for the upgrade path, I think that cooldadtc has a good discussion. What I like to add is that if you have this on a virtual machine, my suggestion is that you take a snapshot of the VM and do an in-place upgrade. That seems the easiest path to me.

...but most of all, the application should target SQL 2019, or maybe even better SQL 2022, which should be released before the end of the year.

Answer 3

Both methods are applicable. However, you will need to confirm that the upgraded version support your current SQL version. Check the following article to upgrade from SQL 2012 to 2019, all the prerequisites and steps are mentioned in simple manner, and these steps are applicable on other version of SQL as well. https://www.dbsection.com/how-to-upgrade-sqlserver-2012-to-2019/