Azure SQL Alter compatibility level Strategy

Question

I have a Azure SQL database serverless.

I want to change compatibility level from 120 to 130 :

ALTER DATABASE DP300 SET COMPATIBILITY_LEVEL = 130;

What strategy previous to execute ALTER should do?

Answer 1

Hi,

Thanks for your question.
Before updating the compatibility level in SQL Server, it is important to consider the following precautions:

1. Test the update in a non-production environment first to ensure that there are no compatibility issues with your existing applications and scripts.
2. Make sure to take a full backup of your databases before making any changes to the compatibility level.
3. Review the Microsoft documentation for the specific version of SQL Server you are upgrading to, to ensure that there are no known compatibility issues with your current configuration.
4. Consider the impact of the update on any third-party tools or applications that may be connected to your SQL Server instance.
5. Have a rollback plan in place in case the update causes any issues.
6. Monitor the performance of your SQL Server instance after the update to ensure that there are no unexpected issues

Hope this helps!

Answer 2

But 120 and 130 are pretty old. 160 is the latest and greatest.

What strategy you should apply depends a little on what your concerns are. Functionally, there are not that many breaking changes, although there are a few marginal ones you could run into.

More likely is that run into performance issues, because you get a new version of the optimizer and there are queries for which the intended improvement backlashes. Query Store is your friend here. If this happens, you can find the query in Query Store and force the old plan, until you have analysed the query in more detail.