Update the compatibility level set to150 from 140 for SQL Database.

Question

Currently in our production environment we could see the compatibility level set to140 and 150 for certain databases. we would like to know a few things :

1. How can we update the compatibility level of these database.? Is it done at the time of database upgradation or handled via maintenance?
2. What are the implications of have 2 different set of compatibility level set for database within a sql server?
3. Before proceeding with the upgradation, is there anything specific that we might need to check or be aware of to avoid failures or any future impacts?

Answer 1

Hi @Sachin Vettiyattil ,

Welcome to Microsoft Q&A platform and thanks for using Azure services.

As I understand from the question, you want to know the compatibility level upgradation from 140 to 150 in Azure SQL Database.

Default compatibility level designation of Azure SQL Database(Database Engine Version 12) is 150 and Supported compatibility level values are 160, 150, 140, 130, 120, 110, 100.
The compatibility levels of the tempdb, model, msdb, and Resource databases are set to the default compatibility level for a given Database Engine version.
The master system database retains the compatibility level it had before upgrade. This won't affect user database behavior.

Please find below inline reply:

1). How can we update the compatibility level of these database.? Is it done at the time of database upgradation or handled via maintenance?

Use ALTER DATABASE to change the compatibility level of the database. The new compatibility level setting for a database takes effect when a USE <database> command is issued, or a new login is processed with that database as the default database context. To view the current compatibility level of a database, query the compatibility_level column in the sys.databases catalog view.

As of November 2019, in Azure SQL Database, the default compatibility level is 150 for newly created databases. Microsoft doesn't update database compatibility level for existing databases. It is up to customers to do at their own discretion. Microsoft highly recommends that customers plan to upgrade to the latest compatibility level in order to use the latest query optimization improvements.

2). What are the implications of have 2 different set of compatibility level set for database within a sql server?

The master system database retains the compatibility level it had before upgrade. This won't affect user database behavior.
Compatibility level affects behaviors only for the specified database, not for the entire server, so master compatibility level is not going to affect the rest of the databases,F urther in Azure SQL,master databases just holds logins and metadata related info.

3). Before proceeding with the upgradation, is there anything specific that we might need to check or be aware of to avoid failures or any future impacts?

Please go through the official documentation by Microsoft alter-database-transact-sql-compatibility-level to understand the differences between compatibility levels, backward compatibility, syntax etc.

Hope this helps. Please let us know if further queries.

Answer 2

You can change the comp level of a database on any time and this up & down, see ALTER DATABASE (Transact-SQL) compatibility level