SQL

How to Upgrade SQL Server 2014 to SQL Server 2016

SQL Server 2016 is maturing and many are looking to upgrade. Let's take a look at how to upgrade SQL Server 2014 to SQL Server 2016

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

Next, specify your product key or choose Evaluation mode.

sql16up02

Accept the license agreement.

sql16up03

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

sql16up04

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

sql16up05

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

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

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

sql16up08

Here you can specify the service accounts and collation configuration.

sql16up09

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

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

sql16up20

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

sql16up21a

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

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.

Subscribe to VirtualizationHowto via Email 🔔

Enter your email address to subscribe to this blog and receive notifications of new posts by email.



Brandon Lee

Brandon Lee is the Senior Writer, Engineer and owner at Virtualizationhowto.com, and a 7-time VMware vExpert, with over two decades of experience in Information Technology. Having worked for numerous Fortune 500 companies as well as in various industries, He has extensive experience in various IT segments and is a strong advocate for open source technologies. Brandon holds many industry certifications, loves the outdoors and spending time with family. Also, he goes through the effort of testing and troubleshooting issues, so you don't have to.

Related Articles

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.