Behavior Differences Running SQL 2017 in 130 Compat Mode vs. Running SQL 2016 in 130 Compat Mode?

All we need is an easy explanation of the problem, so here it is.

We have a new application which includes ETL scripts, R, and .NET code all actively being developed on SQL 2016 architecture.

I just recently received approval to start setting up new environments with SQL 2017.

I would like to understand if there are any code migration related changes that might need to be made for a SQL 2017 installation or would running on SQL 2017 with SQL 2016 DB compatibility mode behave the same as if it was just a SQL 2016 installation running with the full SQL 2016 (130) compatibility mode?

Reading through this link I found the following

To upgrade the SQL Server Database Engine to the latest version, while maintaining the database compatibility level that existed before the upgrade and its supportability status, it is recommended to perform static functional surface area validation of the application code in the database, by using the Microsoft Data Migration Assistant tool (DMA). The absence of errors in the DMA tool output, about missing or incompatible functionality, protects application from any functional regressions on the new target version.

Assuming this check passes, is there anything else that I should be doing or looking at?

How to solve :

I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.

Method 1

It will not behave exactly the same. Compatibility mode works at the database level, not the Instance level, which is still 2017. But it will mostly work the same.

I would check the breaking feature list for 2017 as some Instance level changes may still affect your code, despite it being in 2016 compatibility mode. However, the breaking changes from 2017 are relatively minor so it is probably unlikely you’d be affected. In general, some version breaking changes are covered under compatibility mode and some are not.

The documentation gives good examples of this,

For example, the FASTFIRSTROW hint was discontinued in SQL Server 2012
(11.x) and replaced with the OPTION (FAST n ) hint. Setting the
database compatibility level to 110 will not restore the discontinued
hint.

and

An example of a breaking change protected by compatibility level is an
implicit conversion from datetime to datetime2 data types. Under
database compatibility level 130, these show improved accuracy by
accounting for the fractional milliseconds, resulting in different
converted values. To restore previous conversion behavior, set the
database compatibility level to 120 or lower.

You should also look at the deprecated feature list for 2017 and try to remove any of those areas from future development in order to future-proof your applications. And of course, any new features from 2017 may require updates to take advantage of them as well, but I get the impression you’re more concerned about breaking changes.


With all that said, you’re probably fine, but should still carefully and thoroughly test upgrading the compatibility mode before moving it to production.

Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

Leave a Reply