Compatibility level in AlwaysUpToDate

Question

Compatibility level in AlwaysUpToDate

Julie 200

In Azure SQL Managed Instance configured with the Always-up-to-date update policy, can databases run with compatibility level 160 (SQL Server 2022)? If supported, are there any differences in behavior compared to Managed Instances using the SQL Server 2022 update policy?

Answer accepted by question author

2 additional answers

Your answer

Answer 1

Manoj Kumar Boyini 9,180 Microsoft External Staff Moderator

Hi Julie

you can absolutely run your databases at compatibility level 160 on an Azure SQL Managed Instance that’s using the Always-up-to-date update policy. Managed Instance always hosts the latest stable SQL engine bits, so as soon as SQL Server 2022 (v16.x) bits are live, you can use all of the SQL 2022 compatibility-level features—even under the Always-up-to-date policy.

As for differences versus a Managed Instance using the “SQL Server 2022” update policy:

• Compatibility behavior (T-SQL syntax, query optimizer features, etc.) is driven solely by the database’s compatibility level, so setting it to 160 behaves the same in both environments. • The only real distinction is in how and when engine patches and hotfixes are applied: – Always-up-to-date rolls engine and OS fixes out continuously (you get bug-fixes and performance improvements as soon as they’re ready). – The SQL Server 2022 policy groups updates into scheduled CUs aligned with SQL Server on-premises releases.

Everything you rely on from compatibility level 160—new optimizer enhancements, T-SQL capabilities, metadata features—works identically in either policy.

References

Resolve SQL version issues (“Azure SQL Database always on latest engine; use compatibility_level to gauge feature support”) https://docs.microsoft.com/azure/azure-sql/database/sql-database-paas-overview
View or change database compatibility level https://docs.microsoft.com/sql/relational-databases/databases/view-or-change-the-compatibility-level-of-a-database?view=sql-server-ver15
Understanding Azure SQL Database version numbers (“version 12.x on Azure != on-prem; compatibility level is what matters”) https://docs.microsoft.com/azure/azure-sql/database/sql-database-paas-overview
Compatibility certification for Azure SQL Managed Instance https://docs.microsoft.com/sql/database-engine/install-windows/compatibility-certification?view=sql-server-ver17#compatibility-levels-and-database-engine-upgrades

Julie 200 Reputation points

2026-03-06T08:33:29.42+00:00

Thank you :) Is it possible to set compatibility level to 2022 for a database after it has been alwaysuptodate?
Manoj Kumar Boyini 9,180 Reputation points Microsoft External Staff Moderator

2026-03-06T12:04:23.13+00:00

Hi Julie

Yes, You can set a database to compatibility level 160 (SQL Server 2022) even when the Managed Instance is using the Always-up-to-date update policy. Compatibility level is a database‑level setting, while the instance update policy determines the SQL engine version the MI runs.

ALTER DATABASE YourDatabase SET COMPATIBILITY_LEVEL = 160;

Compatibility level controls query optimizer behavior and T‑SQL semantics. The Always‑up‑to‑date policy means the instance runs the latest available engine build, so underlying engine improvements may still affect execution plans and performance even when the database is set to compatibility level 160.

One additional note: Always‑up‑to‑date instances may upgrade the internal database file format as newer engine versions are released. Because of this, backups taken from an Always‑up‑to‑date instance may not restore to another instance running an older update policy, even if the compatibility level is set to 160.

Hope this helps, Please let us know if you have any questions and concerns.
Julie 200 Reputation points

2026-03-06T14:36:05.98+00:00
When I run this on my databases on AlwaysUpToDate I get 170 as result. Is it correct that database compatibility level is 2025 by default even if instance is alwaysuptodate? Will this change automatically with updates?

SELECT name AS database_name, compatibility_level FROM sys.databases WHERE name = 'databasename';
Manoj Kumar Boyini 9,180 Reputation points Microsoft External Staff Moderator

2026-03-06T14:45:50.62+00:00

Hi Julie

Yes, on Always‑up‑to‑date Managed Instances, new databases default to compatibility level 170 (SQL 2025). Existing databases don’t auto‑upgrade; their compatibility level only changes if you manually set it. This behavior is expected and by design.
Manoj Kumar Boyini 9,180 Reputation points Microsoft External Staff Moderator

2026-03-09T11:24:37.3466667+00:00

Hi Julie

Just checking in to see if the information provided earlier was helpful. Please let us know if you need any additional details or assistance, and we’ll gladly continue supporting you.

Answer 2

Yes, you can run compat level 160 or lower in SQL Server MI with always up-to-date policy.

With compat level, you still have access to many functions added in SQL 2025, for instance some of the REGEXP functions (but not all). With compat level 160 you are also missing out on new optimization feature as for instance Optional Parameter Plan Optimisation. New optimiser features are normally only available under the most recent compat level.

The most important thing with always-up-to-date is the version number of the database format. I believe this is currently 998, which is the same as SQL 2025, but that could change if Microsoft adds a new feature which requires changes to the system catalog. It is not possible to restore a database with a higher format to an engine that only supports a lower format. Thus, you cannot restore your always-up-to-dat1e to SQL 2022, even if the compat level is 160.

Answer 3

Yes. In Azure SQL Managed Instance configured with the Always-up-to-date update policy, databases can run at compatibility level 160, which corresponds to SQL Server 2022 behavior for the query processor and database features.

However, there are important behavioral and capability differences between:

An instance using the SQL Server 2022 update policy, and
An instance using the Always-up-to-date update policy with a database at compatibility level 160.

Key differences based on the update policy:

Engine version and feature set
- Always-up-to-date instances run on “the latest stable SQL Server Database Engine” for Azure SQL Managed Instance, not fixed to SQL Server 2022 or 2025.
- Instances with the SQL Server 2022 update policy are explicitly aligned with “the latest stable SQL Server 2022 Database Engine.”
- This means that even at the same database compatibility level (160), an Always-up-to-date instance can receive newer engine-level improvements, performance, security, and reliability updates beyond what is in the SQL Server 2022 branch.
Feature availability tied to update policy
- Some features are explicitly gated by the update policy, not just compatibility level. For example, Degree of parallelism (DOP) feedback is:
  - Supported in Azure SQL Managed Instance with the SQL Server 2025 or Always-up-to-date update policy, starting at compatibility level 160.
  - Not supported for instances using the SQL Server 2022 update policy, even if the database is at compatibility level 160.
- This shows that a database at compat level 160 on an Always-up-to-date instance can have different optimizer behavior and IQP capabilities than the same compat level 160 database on a SQL Server 2022–policy instance.
Reverse migration / restore back to SQL Server
- For SQL Server 2022 update policy: databases can be restored back to SQL Server 2022 instances.
- For Always-up-to-date update policy: databases cannot be restored back to SQL Server at all.
- So, even if the database runs at compatibility level 160, choosing Always-up-to-date removes the ability to reverse-migrate via backup/restore to SQL Server.
Managed Instance link and failback behavior
- Certain Managed Instance link capabilities (establishing a link from Managed Instance to SQL Server and failing over from Managed Instance to SQL Server 2022) are only supported when the Managed Instance uses the SQL Server 2022 update policy.
- With Always-up-to-date, a link can be established from SQL Server 2022 to the Managed Instance, but after failover to Managed Instance, it is no longer possible to replicate data or fail back to SQL Server 2022.
- These behaviors depend on the update policy, not the database compatibility level.
General recommendation and behavior
- Documentation recommends Always-up-to-date unless a specific requirement mandates SQL Server 2022/2025 alignment (for example, restore back to SQL Server or specific failover/link scenarios).
- Even when running compat level 160, Always-up-to-date instances can gain incremental improvements over time that SQL Server 2022–policy instances will not, so behavior (especially performance and optimizer decisions) may diverge as the platform evolves.

Summary:

Databases on an Always-up-to-date Azure SQL Managed Instance can run at compatibility level 160.
Behavior is not identical to a SQL Server 2022 update policy instance at compat 160, because:
- Engine branch and feature set differ (e.g., DOP feedback support).
- Reverse migration and Managed Instance link/failback capabilities differ.
- Always-up-to-date continues to evolve beyond SQL Server 2022.

References:

Share via

Compatibility level in AlwaysUpToDate

2 additional answers

Your answer