compatibility level SQL Server 2019

Question

Hello,

I am migrating SQL2012/SQL2016 to SQL2019.

I want to know which compatibility level I should set in SQL Server 2019 after the upgrade?

Pls suggest

Answer 1

I want to know which compatibility level I should set in SQL Server 2019 after the upgrade?

Ideally it should be be 150 which is SQL Server 2019 compatibility level. And if some query, may be due to regression are not performing poor you can use query hint QUERY_OPTIMIZER_COMPATIBILITY_LEVEL_n to 110 or 130. The issue should ideally come when moving from SQL Server 2012 to SQL Server 219 due to cardinality estimator change.

When moving from SQL Server 2016 to SQL Server 2019 you should ideally not face regression issue. where query running fine in SQL Server 2016 starts taking long time in SQL Server 2019 without any proper reason.

Answer 2

Hi @BilalBhat-0178,

In many cases, most user databases never had their compatibility levels changed after a migration to a new version of SQL Server. This usually didn’t cause any issues unless you actually needed a new feature that was enabled by the latest database compatibility level. Please refer to Differences between Compatibility Levels.

Before upgrade SQL server instance, suggest you using Microsoft Data Migration Assistant tool. Please refer to Compatibility levels and Database Engine upgrades.

By the way, using ALTER DATABASE statement to change database Compatibility level. Please refer to ALTER DATABASE (Transact-SQL) Compatibility Level.

Best regards,
Cathy

---

If the response is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 3

Setting the compatibility level down does NOT make the server 100% compatible with previous versions. It only changes certain functionality.

Normally you do not need or want to set the compatibility level to previous version.

See:
https://learn.microsoft.com/en-us/sql/t-sql/statements/alter-database-transact-sql-compatibility-level?view=sql-server-ver15

Answer 4

I need to use some of the Sql Server JSON functionality that requires setting the compatibility level to 130. I am using Sql Server 2019. Will setting the level to 130 affect performance negativity?