2

We are currently upgrading few SQL Server instances from SQL Server 2008 R2 to SQL Server 2012.

This is a temporary solution (because Microsoft EOL support for 2008 R2 is coming soon).

I noticed that after the upgrade all system database EXCEPT master are set for the new compatibility_level (110).

Does somebody know why [master] is not getting the new compatibility_level as part of the upgrade?

I know I can set it manually, but wondering why it is not set be default as the other system database.

Thanks,

enter image description here

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Yossi M
  • 61
  • 10

1 Answers1

1

From the documentation of SQL Server 2017, however the same behavior can be observed also in SQL Server 2012: ALTER DATABASE (Transact-SQL) Compatibility Level

The below behaviors are expected for SQL Server 2017 (14.x) when a database is attached or restored, and after an in-place upgrade:

  • If the compatibility level of a user database was 100 or higher before the upgrade, it remains the same after upgrade.
  • If the compatibility level of a user database was 90 before upgrade, in the upgraded database, the compatibility level is set to 100, which is the lowest supported compatibility level in SQL Server 2017 (14.x).
  • The compatibility levels of the tempdb, model, msdb and Resource databases are set to the current compatibility level after upgrade.
  • The master system database retains the compatibility level it had before upgrade.

So, this is behavior by design.

Alexander Volok
  • 5,630
  • 3
  • 17
  • 33
  • Thank you Alex. I'm glad to see that its be design. – Yossi M Mar 12 '19 at 19:58
  • As part of our efforts to minimal the risks , we decided to keep the user databases on the old compatibility level (100). But tin the case of [master], is there any risk of changing it to the new compatibility level? – Yossi M Mar 12 '19 at 20:01
  • This is a normal practice to keep databases after the upgrade in the original compatibility level and then eventually switch them all to the current one. When it comes to system databases, including master, personally I see no risk with setting them to a current comp level directly. – Alexander Volok Mar 12 '19 at 20:03