Managing breaking changes between SQL Server versions

When you migrate a database to a newer version of SQL Server or to SQL Database, you may encounter breaking changes from the version of SQL Server on which the database was running. These changes might break applications, scripts, or functionalities that are based on earlier versions of SQL Server. There are two types of breaking changes: changes that will be detected by the migration tool and prevent the migration and changes that are not detected but affect functionality. With respect to changes that affect functionality, running a migrated database at an older compatibility level is a short-term fix, but you will need to run your database at the highest compatibility level possible to take advantage of the newest capabilities of SQL Server or SQL Database.

Before you begin a migration, you should review the Microsoft documentation with respect to any breaking changes if you are migrating from an older version of SQL Server to a newer version of SQL Server. See the following links for details regarding breaking changes:

 SQL Server 2016 Breaking Changes
 SQL Server 2014 Breaking Changes
 SQL Server 2012 Breaking Changes
 SQL Server 2008 R2 Breaking Changes
 SQL Server 2008 Breaking Changes
 SQL Server 2005 Breaking Changes

Microsoft provides an upgrade advisor to help you detect and prepare for a version upgrade. These advisors will examine scripts, stored procedures, triggers, and trace files, but they will not analyze desktop applications or encrypted stored procedures. See the following links for information about how to install and use the upgrade advisor for the version of SQL Server to which you are upgrading and for information about where to find or install the upgrade advisor.

 SQL Server 2016
 SQL Server 2014
 SQL Server 2012
 SQL Server 2008 R2
 SQL Server 2008
 SQL Server 2005

With respect to issues you need to fix due to breaking changes, when you fix them depends on the source and the destination.

If you are migrating from SQL Server on-premises to SQL Server in a virtual machine, you can fix them in the source before the migration—in the source database (perhaps affecting the production system), in an on-premises copy as part of the migration, or after the database is restored. A SQL Server database backup can be restored to a newer version of SQL Server, even with issues that must be changed before the database will function properly (discontinued syntax, for example).

 If you are migrating from a non–SQL Server source database or are migrating to SQL Database, you must fix the issues detected before the migration because the migration mechanisms do not permit you to fix them after the migration.

To fix issues detected, you can use either SQL Server Management Studio or SQL Server Data Tools for Visual Studio (SSDT). With SSDT, you create a database project from the database schema and then modify the schema within the project without affecting the source database. You then can merge the changes back into the source database or to a database copy and then synchronize the data.

The SQL Server 2016 Upgrade Advisor is currently in preview. It is a new and substantially enhanced version, with more features to come soon. In late April or early May 2016, a new version of the SQL Server 2016 Upgrade Advisor will be released and will be named the Data Migration Assistant (DMA), also as a preview. Among the enhancements is the ability not only to detect upgrade issues, but also to fix some issues and to perform the upgrade and migration for you using database backup functionality.

Source of Information : Migrating SQL Server Databases to Azure