We did an upgrade of SQL Server 2014 to 2016. In the development environment we did an in-place upgrade, which left the master database at compatibility level 120. But in production we did migration to new server so there master is compatibility level 130.
I would like to have both environments as similar as possible. Additionally we have one application which is running its queries from the master database, so theoretically they can behave differently between environments.
Is it safe to change compatibility level of the master database from 120 to 130?
Best Answer
I'm not certain what you mean by one application "running its queries from the master database." As you likely know, this would fall into the category of "worst practices."
The upgrade worked as expected in regard to the master database compatibility mode. See Change the Database Compatibility Level and use the Query Store. Unfortunately, this documentation doesn't give any guidance on whether it should be changed after the upgrade.
So if everything is working fine in production with the higher compatibility mode, then it should be safe to change the compatibility mode in development. The only reason I say should be safe is that I've been in IT way to long to ever say anything will be safe. If you aren't sure of why this change should be safe, then I would recommend that you do a bit of reading on what the compatibility mode actually does, and then it should be clear that it is OK to change it--particularly since you've already tested the configuration in production.
Additionally, I have not been able to find anything to suggest that it's not OK to change it, and I have found a couple of references that suggest it should be done:
Lastly--this question is a day old and nobody has commented or answered with any warnings, and I'm quite certain someone would have already commented if there was any known significant risk.
In your case, since it is a development system and there is a production server partner with the upgraded compatibility mode, I would certainly get it upgraded. If the compatibility mode has a negative affect on any queries, you will want to find that in development, not in production. For most systems this wouldn't matter as applications aren't using code in the master database, but it sounds like you have some stored procedures there or something that may be impacted by the master database compatibility mode.