How to use Database Compatibility to increase your migration success

Microsoft's recommended SQL Server upgrade  workflow is to upgrade to the latest SQL Server but keep the source DB compatibility level, assess the workload impact via establishing a baseline and based on testing move the compatibility level  to the latest. 

Upon creation of a new user database - the create  database sets the compatibility level at the default compatibility level of the SQL Server. Keep in mind - if the model database level is set lower than the create database will set the compatibility level based on the model db

You can also change the database compatibility level at any time

When you restore a database and the compatibility level is lower than the default SQL Server instance value - the restored database will keep its original level. Unless its original level is lower than the minimum allowed for the SQL Server - which in that case - will set the compatibility level at the lowest minimum allowed

--view the database compatibility levels
SELECT name,compatibility_level  FROM sys.databases   

--server compatibility level : multiply first two digits by 10 
SELECT SERVERPROPERTY('productversion')

ALTER DATABASE database_name
SET COMPATIBILITY_LEVEL = { 150 | 140 | 130 | 120 | 110 | 100 | 90 }

Source:https://www.sqlskills.com/blogs/glenn/database-compatibility-level-in-sql-server/

An important point and one which is raised when assessing  risk when discussing database upgrades with application owners . From Microsoft docs  "For pre-existing databases running at lower compatibility levels, as long as the application does not need to leverage enhancements that are only available in a higher database compatibility level, it is a valid approach to maintain the previous database compatibility level"

Microsoft has worked on getting this right and upgrading SQL Server , has become a much less onerous task from an application certification perspective. It is in Microsoft's interest to decouple the application code . This makes it easier to justify migrating from SQL Server to Azure SQL Database aka managed SQL Server Instance. 

Traditionally any SQL Server upgrade required a recertification for T-SQL behaviour. But the database compatibility level settings and maintaining the same compatibility level supports existing app t-sql code to behave as expected. But remember we are discussing database level , not the whole server.

The upgrade risk is more on the impact of Query Optimizer changes. It is reasonable to expect Optimizer changes between versions. There is a risk as a database is migrated to a higher SQL Server version , there is negative SQL Server impact. This can lead to delays in currency upgrades. When a Query Optimizer enhancement is introduced , they are  mapped to the default highest compatibility level of the particular SQL Server version.

Microsoft uses a concept called Query Plan Shape Protection. The Query Plan Shape is the visual representation of the execution plan operators , and every effort is made to maintain the same query plan shape.  Obviously - if environment changes are made - than this could have a negative impact. 

Read more on SQL Server Migration topics 

Data Migration Assistant for SQL Server 2016 Upgrade

Checking compatibility for a SQL Server database migration to Azure with sqlpackage.exe