How to Upgrade SQL Server 2014 to SQL Server 2016

0

With SQL Server 2016 currently at SP1 as well as a CU1 under its belt, many may be thinking of upgrading existing SQL Server 2014 installations to SQL Server 2016.  The upgrade process is painless as you will see below.  Let’s take a look at how to upgrade SQL Server 2014 to SQL Server 2016.

Why upgrade?  There are a lot of exciting features found in SQL Server 2016.  The following are just a few of the enhancements found in SQL Server 2016.

  • Query Store – Allows analytics for queries so administrators can see query performance over time
  • Row level security – RLS allows SQL administrators to control which data in a table a user has access to
  • Always Encrypted – new functionality to encrypt data at rest and in motion
  • Stretch Database – this allows some part of your database to live in Azure SQL. The query processor knows which parts live on premise and which live in Azure and divides up the workloads. This helps to alleviate expensive enterprise storage by leveraging cheaper Azure blob storage
  • In-Memory Enhancements – limitations have been greatly lifted from SQL 2014

How to Upgrade SQL 2014 to SQL 2016

Mount the SQL 2016 ISO or media on your current SQL 2014 server.

sql16up01 How to Upgrade SQL Server 2014 to SQL Server 2016

Next, specify your product key or choose Evaluation mode.

sql16up02 How to Upgrade SQL Server 2014 to SQL Server 2016

Accept the license agreement.

sql16up03 How to Upgrade SQL Server 2014 to SQL Server 2016

Here you can choose to allow Microsoft updates to pull updates for SQL as well.

sql16up04 How to Upgrade SQL Server 2014 to SQL Server 2016

The Upgrade process will include SQL Server product updates by default.

sql16up05 How to Upgrade SQL Server 2014 to SQL Server 2016

In the specify the instance of SQL Server to modify you can choose a specific instance, or in the dropdown combo box, you can also choose to only upgrade shared components.

sql16up06 How to Upgrade SQL Server 2014 to SQL Server 2016

With the upgrade, it is not possible to change the configured features currently configured by SQL Server 2014.  By default it will upgrade all configured features.

sql16up07 How to Upgrade SQL Server 2014 to SQL Server 2016

Here you can specify the instance ID for the instance of SQL Server.

sql16up08 How to Upgrade SQL Server 2014 to SQL Server 2016

Here you can specify the service accounts and collation configuration.

sql16up09 How to Upgrade SQL Server 2014 to SQL Server 2016

As you can see there are three choices here – Import, Rebuild, and Reset.  Since this was a clean installation of SQL Server 2014 that was being upgraded to SQL Server 2016, I chose to Rebuild full-text catalogs using the new and enhanced word breakers.  As the installation notes below, rebuilding indexes can take awhile, and a significant amount of CPU and memory might be required after the upgrade.

sql16up10 How to Upgrade SQL Server 2014 to SQL Server 2016

Finally, we come to the Ready to Upgrade screen that displays our configuration choices.

sql16up20 How to Upgrade SQL Server 2014 to SQL Server 2016

After the upgrade process has completed, you are notified that you need to reboot the server to finish out the process.

sql16up21a How to Upgrade SQL Server 2014 to SQL Server 2016

You should see Succeeded on all the individual upgrade steps.  If not, take a look at the setup log for the upgrade process to begin troubleshooting.

sql16up21b How to Upgrade SQL Server 2014 to SQL Server 2016

Thoughts

The how to upgrade SQL Server 2014 to SQL Server 2016 process is very straightforward and most will not run into any issues taking their 2014 installation up to 2016.  However, as with any process, always run the process in a lab environment first to verify any gotchas ahead of time.

StarWind VSAN