Why is the compatibility level of the database that supports Data Collection set to 100 even in SQL Server 2012 or SQL Server 2014?

 

A question posted by Greg Low to the MCMs distribution group recently:

I was surprised that the data collection system creates a database with a dbcompat level of 100 on both 2012 and 2014.

Wouldn’t it make more sense for it to create a database with the same level as the server instance that the database is being created on?

 

And this were my findings:

That happens when you instantiate the “New Database” dialog from the wizard, instead of choosing a database from the list.

When the wizard loads the list of databases you can use as your Data Collection Warehouse, it filters out those whose compatibility level is lower than 100. A database whose compatibility level was 90 wasn’t supported, so this check is understandable.

Now, if you click “New” to create a new database based on the “New Database” UI dialog, when the wizard initializes the prototype database it intentionally sets the compatibility level to 100 (and that hasn’t changed ever since Katmai, when this was introduced.) It was probably coded that way to not rely on model’s compatibility level, which in an instance of 2008, 2008R2 or 2012, could have been lowered to 90.

And in case you have changed it from the Options pane of that dialog, and exception is thrown:

Whose text is this ”The compatibility level of the new database does not support a management data warehouse. Select a different database, or change the compatibility level to the current version of the server.\r\n\r\nWould you like the wizard to change the compatibility level of the new database to a supported value? ”

So, I filed a defect against SQL15 iteration and the fix for it has already been checked-in. Next major release of SQL Server will set the compatibility level of that database to 130 instead.