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.
Next, specify your product key or choose Evaluation mode.
Accept the license agreement.
Here you can choose to allow Microsoft updates to pull updates for SQL as well.
The Upgrade process will include SQL Server product updates by default.
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.
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.
Here you can specify the instance ID for the instance of SQL Server.
Here you can specify the service accounts and collation configuration.
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.
Finally, we come to the Ready to Upgrade screen that displays our configuration choices.
After the upgrade process has completed, you are notified that you need to reboot the server to finish out the process.
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.
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.
