Compatibility Level of the Master DB Remains Same After Upgrades

Scenario

During an upgrade from SQL Server 2008 R2 to SQL Server 2016 SP2, we have noticed that all system databases, except for the master database, are automatically assigned the new compatibility level (130). In this article, we will explore the reasons behind this behavior and explain why the compatibility level of the master database is not automatically updated.

TSQL to check ststem DB compatibility level

select name, database_id, compatibility_level
from sys.databases where database_id > 5

All system databases, except for the master database, are automatically assigned the new compatibility level (130)

Introduction

It’s crucial to think about the system databases’ compatibility levels when updating Microsoft SQL Server. The master system database preserves its pre-upgrade compatibility level while the tempdb, model, msdb, and resource databases adopt the compatibility level of the upgraded version.

Understanding Compatibility Levels

To ensure backward compatibility with prior versions, SQL Server uses compatibility levels to control how the database engine handles specific behaviours and features. It’s critical to balance adding new features with keeping existing databases compatible while performing an upgrade.

System Database Behavior

The tempdb, model, msdb, and resource databases’ compatibility levels are automatically adjusted upon an upgrade to correspond to the improved version. This alignment makes sure that these system databases can utilise all of the brand-new features and functionality added to the most recent edition of SQL Server.

Master Database Exception

The master system database, on the other hand, keeps the compatibility level from before the update and contains crucial data about all other databases on the SQL Server instance. The functionality of the current user databases is preserved, and stability is guaranteed.

Preserving Database Stability

Microsoft guarantees that the user databases built before the update will continue to run without a hitch by maintaining the compatibility level of the master database. These databases may have been built and optimised based on a particular SQL Server version’s level of compatibility. Altering the master database’s compatibility level could potentially cause compatibility problems and interfere with the operation of the current user databases.

Customizing Compatibility Levels

You have the freedom to check and see if your user databases work with the new version after the upgrade. Afterward, you can manually alter the compatibility levels of specific databases as necessary, making use of the new capabilities and enhancements offered.

Conclusion

A successful update depends on knowing the SQL Server system databases’ compatibility levels. The master database retains its pre-upgrade compatibility level, but tempdb, model, msdb, and resource databases align with the updated version’s compatibility level. This strategy maintains stability and permits current user databases to continue operating normally. You can take use of the new features and optimisations of the upgraded SQL Server version while retaining compatibility with your current environment by reviewing and modifying compatibility settings on a per-database basis.

Special thanks to Ayyappa Addala