SQL Server 2008 R2 : More peeking under the hood

AaronBertrand — Mon, 31 Aug 2009 17:00:00 GMT

A little over a week ago, I performed some initial investigation into what has changed between SQL Server 2008 and 2008 R2. Steve Cooney quickly asked, "Where is all the schema for UCP and DAC?" It was a great question, and my initial response was that, most likely, the schema to support UCPs was not actually created until you create one. This turned out to be true. Comparing 2008 to 2008 R2, as I showed last week, did not yield anything extremely interesting, including nothing regarding UCP. However, once I ran the UCP wizard and created a Control Point, my comparison of the msdb databases on the two systems yielded the following new synonyms on the UCP instance:

Maybe I haven't been paying close attention, but this is the first time I can remember seeing Microsoft use a synonym in the product. These are synonyms to views in a new user database that was created on the UCP instance, sysutility_mdw. (At first I thought some of them were procedures, because the naming scheme is inconsistent. Nope, just poor adherence to any kind of logical standard, aside from the syn_ prefix, which doesn't make sense to me... one of my favorite uses for a synonym is to abstract the fact that the targeted object is somewhere else in the first place. I also noticed that they have at least one redundant synonym there (syn_sysutility_utility_storage_utilization_view and syn_sysutility_volume_object_view both point to the same inline function).)

So, the UCP wizard actually creates a database, a handful of objects, and then adds several synonyms to the msdb database, which explains why my initial comparisons did not find anything.

As it turns out, creating a Utility Control Point (and then registering an instance to manage) only changes the Control Point instance. The managed instances don't change in terms of schema, but they do receive updates to data -- for example, jobs are created on each managed instance ("MONITOR" is the name of my UCP instance, and "SQL2008R2" is the name of the first instance I've added):

I did not check whether the wizard makes changes to the resource database. Even if it does, at this point, it seems kind of irrelevant.

Search results matching tags 'SQL Server 2008 R2', 'Utility Control Point', and 'UCP'

SQL Server 2008 R2 : More peeking under the hood