Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. Join them; it only takes a minute:

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?

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?

1 Answer
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.

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.