One somewhat obscure issue you might encounter when upgrading from a legacy version of SQL Server to SQL Server 2016 or later is a change to how some data type conversions are handled. Microsoft describes it like this:
“Microsoft SQL Server 2016 and Microsoft Azure SQL Database include improvements in data type conversions and in several other operations. Most of these improvements offer increased precision when you deal with floating-point types, as well as with classic datetime types.”
This changed behavior only comes into play when you are running on database compatibility level 130 or later, and it only affects persisted objects (such as persisted computed columns, filtered indexes, indexed views, and indexes that use computed columns).
Because of this potential issue, Microsoft recommends that you do some additional validation checking on your legacy databases before you change their compatibility level to 130 or higher to find out if you might be affected by these changes.
Here are the steps for the validation and correction process:
- Restore the legacy database to SQL Server 2016 or later instance in your test environment
- Enable global trace flag 139
- Run DBCC CHECKDB WITH EXTENDED_LOGICAL_CHECKS, NO_INFOMSGS, TABLERESULTS
- Disable global trace flag 139
- Change the compatibility level of the database to 130 or later
- If Step 3 reveals any issues, then rebuild the objects that it identified
Microsoft has more detail about this change here. Personally, I think this just another step that you should add to your testing plan during an upgrade project.
Additional Resources
My new Pluralsight course, SQL Server: Upgrading and Migrating to SQL Server 2016 has just been published. This is my eleventh course for Pluralsight, but the complete list of my courses is here.
Building on this online course is a new three day class, IEUpgrade: Immersion Event on Upgrading SQL Server, taught by myself and Tim Radney. The first round of this course will be taught in Chicago from October 11-13, 2017.
Finally, I will be presenting a half-day session called Migrating to SQL Server 2017 at the PASS Summit 2017 in Seattle, WA from October 31- November 3, 2017. You can use this code: BL150GG to register for the PASS 2017 Summit to get a $150 discount.
Here is a link to the complete series about upgrading SQL Server.