A common problem with data platform modernisation is gaining application vendor support for newer SQL Server versions, but that has been made easier since Microsoft’s change in position on the long-term use of database compatibility mode announced in 2018.
Microsoft recognised that the cost of testing against a SQL Server version was a blocker for many upgrades and instead advocates the use of database compatibility mode to ensure backwards compatibility for an application as a long-term, supported solution. This feature affects only the specific database on which it is enabled and will allow you to upgrade the SQL Server Engine to the latest version while keeping the application unaffected.
For example, SQL Server 2014 has a database compatibility level of 120 so if your application supports SQL Server 2014 then it will work exactly the same on SQL Server 2019 when you keep your database at compatibility level 120.
It’s not entirely bullet-proof as some breaking changes can’t be protected and discontinued features aren’t protected, but this won’t affect the majority of databases and will be picked up by a scan using the Data Migration Assistant (DMA) which replaces the SQL Server Upgrade Advisor.
The two main areas for concern with an upgrade are changes in T-SQL default behaviour and query plan stability. Database compatibility level was originally created to ensure that T-SQL didn’t change in behaviour between versions of SQL Server, but is now also used to disable newer optimizer changes that could cause unexpected query plan behaviour.
If you are an Independent Software Vendor (ISV) it would be helpful to your customers, and to the systems integrators that support them, if you certify your applications for a specific compatibility mode rather than SQL Server version so they can upgrade to later versions of SQL Server without chasing you to certify the version they’re deploying to.
It’s also a useful tip for Software-as-a-Service vendors as it gives you the opportunity to modernise your database infrastructure without introducing risk to your customer.
Upgrading to the latest compatibility level should ultimately be the end-goal as it opens up new features and optimizations for you to take advantage of, but it’s not always possible in a timeframe that works with your or your customer’s plans. Maintaining an older compatibility level gives you and them the opportunity to decouple application testing from platform upgrades and maybe spread some joy along the way 😊
Read more about compatibility levels here: https://docs.microsoft.com/en-us/sql/t-sql/statements/alter-database-transact-sql-compatibility-level?view=sql-server-2017#compatibility-levels-and-sql-server-upgrades
