2

With regards to backward compatibility, what is the maximum compatibility mode I can set on some of the older databases which may be hosted within a newer version of SQL Server (SQL Server 2019 to be specific) ?

In one MS Workshop I was advised SQL 2012 is usually a safe bet, but I would like to hear if there are any differing opinions on this.

7

This will entirely depend on your database schema and code plus your application code. Your best bet is to run the Database Migration Assistant which can help identify incompatibilities within the database.

Best practice is to turn on QueryStore migrate the database and use a low compatibility level, then raise the compatibility level and use QueryStore to help you identify any plan regressions. This is especially important if you are crossing from the old to the new cardinality estimator (introduced with SQL Server 2014) - this is perhaps the source of the advice to use SQL 2012.

Microsoft® Data Migration Assistant v5.4

3
  • If we exclude plan regressions and focus only on application code compatibility, would there be any functions/features in SQL 2005/2008 which would cease to work in for example SQL 2012? – Asher Jun 24 at 7:12
  • 1
    Yes - these are known as discontinued features and you would need to get a list for all intermediate versions (ie 2008, 2008 R2 and 2012) a quick bingle found me this list for 2012 wikidba.net/2012/12/23/… – Stephen Morris - Mo64 Jun 24 at 7:27
  • Since level 130 converting datetime to datetime2 has more precision this can cause problems (especially when using old third party drivers like jTDS) – eckes Jun 25 at 1:05

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.