0

I have a database with hundreds of stored procedure, views, triggers, function, ... The database Compatibility Level is 2008 (100) and we would like to move it to 2016 (130).

The SQL Server version is 2016. We read that migrating the Compatibility Level could/would improve performance.

What is the best way to verify each object of the database to ensure working compatibility of the database? I already checked the "Upgrade Advisor" and it doesn't support the version of the database.

3

For this number of jumps in compatibility level (or if you are being appropriately paranoid with a production application, for any jumps in compatibility level) a full application regression test is the only way to be absolutely sure.

Even if you have ensured that all the matters listed in the release notes either don't affect you or have been mitigated, you might find instances where code is relying on officially undefined behaviour which has changed (changes like this may not appear in release notes). For instance, when moving some of our work from 2005 to 2008r2 we experienced problems with a change in how some (admitedly odd) queries using FOR XML generated their output.

  • Is there a tool that could help me for the migration (at least the obsolete types, function, ...). – Lenny32 Feb 21 at 13:53
  • @Lenny32 - Probably not all the way from 2008 to 2016. If the DB were still on an older DB instance you could perhaps use the upgrade advisers in a stepwise fashion, upgrading one or two versions at a time. – David Spillett Feb 21 at 15:55

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.