How to Upgrade to SQL Server 2016 from Earlier and Older Versions

SQL Server 2016 comes with multiple components. Upgrade considerations for each one of them is different. Before upgrading, ensure that the features are still supported and not deprecated. In this post, I am consolidating a list and talk about how to upgrade to SQL Server 2016 from earlier and older versions.

Note: If you buy something from our links, we might earn a commission. See our disclosure statement.

The first steps before upgrading is to check for the various factors that could affect your upgrade plans. Once that is validated, run the “Upgrade Advisor” to check for any existing issues in the code that might cause compatibility issues. You can download the “Upgrade Advisor” and run a set of upgrade rules against your existing installation.

The SQL Server 2016 Upgrade Advisor is not a part of the SQL Server Setup.

The Upgrade advisor checks the following:
Existing code that might break in SQL 2016.
Any deprecated features that is still in use on the older environment.
Help adopt newer features like stretch databases, In-memory OLTP, Azure SQL migration etc. by giving recommendations.

How to Upgrade to SQL Server 2016 from Earlier and Older Versions

Once you run the tests on the target servers, you will be able to see and resolve existing compatibility issues with your databases.

Check and Compare SQL Server Version Comparison from the Table Below

[table id=13 /]

Upgrade Considerations for SQL Server Analysis Services

In-place upgrade will replace your older folder path with new binaries of SQL Server 2016. No manual backup and restoring of data is necessary.

Side-by-side migration will allow you to migrate your SQL Server to a different environment. Advantage of Side-by-Side migration over in-place method is that you can fall back to your previous environment easily, whereas in case of in-place it is much difficult. You can simply backup and restore your database on-to the new environment and test it before moving into production.

This process is applicable to move Tabular and Multi-dimensional data models. You can run post installation steps and run DBCC commands to check the database consistency and orphaned objects.

Tabular Models will get the maximum performance benefits when moved from 2012 to 2016 models.

In-place upgrade for Tabular Models running in direct-query models is not supported.

More upgrade details are available on the MSDN website.

Affiliate Disclosure: Faceofit.com is a participant in the Amazon Services LLC Associates Program. As an Amazon Associate we earn from qualifying purchases.