Upgrade SQL Server

There are a lot of exciting features 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

Following are some prerequisites before upgrade:

Type of SQL Server upgrade 

- in-place or 

- side-by-side

Choose a migration method: 

If it is an in-place upgrade this is moot, otherwise consider detach/attach, backup/restore, log shipping, database mirroring, or another method to move the data from the legacy server to the new instance. Define how you will redirect the application and/or user connections to the new server: change connection strings, DNS, etc. If the target is already up and running, this is a good time to test moving the data and perhaps begin testing the workload on the new server.

After the migration: 

Backup databases including system databases. Ensure server logins are mapped to database users (sp_change_users_login.) Run update usage and update statistic commands (DBCC UPDATEUSAGE, UPDATE STATISTICS.) Check database consistency (DBCC CHECKDB.) 

How to Upgrade SQL 2012 to SQL 2016

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

Next, specify your product key.

Accept the license agreement.

choose to allow Microsoft updates to pull updates for SQL as well.

In the specify the instance of SQL Server to modifyyou 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 MSSQL 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.

Finally the SQL Server 2014 Enterprise Edition was able to be upgraded to SQL Server 2016 Enterprise Edition.