Upgraded sql server from sql 2016 to sql 2022, but master database still at 130 level. Should i change it ?

Question

We upgraded sql server from sql 2016 to sql 2022, but master the database still at 130 compatibility level level. I get why the upgrade process needs to keep the master database at 130 level. Post upgrade, should we manually change the compatibility level of the master database to 160 ( ALTER DATABASE master SET COMPATIBILITY_LEVEL = 160;) ? i.e. is this best practice ? Are there any risks in doing so ?

Answer 1

When you restore a database to a higher version, the lower compatibility level is retained; you must always set it explicitly.

The same applies if you do an in-place upgrades, both to user databases and master. (But looking at some instances where I have a made an inplace upgrade, it does change the compat level for msdb, model and tempdb.)

I don't know why the raise the compatibility level for master, but presumably it is because they don't have any code that fails with a lower compat level. So they stick to the lower level, just in case you would have put something in master yourself that would break with the new compat level. Presuming that you have not done that, there is no reason not to set the compatibility level to 170.

Answer 2

Changing the master database compatibility level from 130 to 160 after upgrading the SQL Server instance is supported and aligns with the general goal of eventually moving all databases to the latest compatibility level, but it should be done in a controlled and tested way rather than as an automatic step.

Key points from the provided context:

1. Compatibility level controls T‑SQL and query optimization behavior per database, not per server. The recommendation is to eventually upgrade databases to the latest compatibility level to benefit from new features such as Intelligent Query Processing, but to do so in a controlled way and use lower levels as a safer migration path when needed.
2. When a database’s compatibility level is changed, all stored procedures in that database are automatically recompiled to use the new level. This applies to master as well, so any stored procedures defined there will be recompiled under level 160.
3. New query optimizer and cardinality estimator changes are enabled only at the default compatibility level of the new engine version. For SQL Server 2022 (16.x), that is level 160. Keeping master at 130 means it continues to use the older behaviors; moving it to 160 opts it into the newer CE and optimizer behaviors.
4. Best practices for upgrading compatibility level emphasize:
   • Using the recommended workflow for upgrading database compatibility level (for example, via Query Store and Query Tuning Assistant) to maintain performance stability.
   • Treating lower compatibility levels as a temporary safety measure and planning to move to the latest level once validated.

Practical guidance based on this:

• It is not mandatory to change master to 160 immediately after the engine upgrade; the upgrade process intentionally preserves the old level to avoid unexpected behavior changes.
• It is acceptable and ultimately desirable to move master to 160, provided that:
  • Any code that runs in master (logon triggers, utility procedures, jobs referencing master, etc.) is validated under level 160.
  • There is a rollback plan (for example, ability to change compatibility level back if a regression is found).
• Risks are the same as for any database compatibility level change: potential behavior or performance changes due to new optimizer and CE behavior, and any T‑SQL that relies on old behavior controlled by compatibility level could behave differently. These are mitigated by testing and by following the documented best‑practice workflow for compatibility upgrades.

In summary: changing master to 160 is supported and consistent with best practices for eventually adopting the latest compatibility level, but should be done after appropriate validation rather than as an automatic post‑upgrade step.

---

References:

• ALTER DATABASE (Transact-SQL) compatibility level
• Compatibility certification