Tuesday, June 28, 2011

Microsoft SQL Server's UCP (Utility Control Point)

I truly wanted to love this feature http://www.microsoft.com/sqlserver/2008/en/us/r2-multi-server.aspx but, alas, it was not meant to be.  I've been sorely jilted but maybe you might find it more useful so let's start the story of this tragic love affair from the beginning.

Setting up UCP is straight-forward. You will need a copy of SQL Server 2008 R2 Enterprise Edition to run the application. Once you have the correct version its all a matter of opening up SSMS and clicking on the Utility Explorer:

From there you'll get your standard "do not show this page again" splash screen:

First thing you need to do is create a Utility Control Point. This is where you'll need to spend the money on a Enterprise Edition. Most likely the server you choose will be your primary Central Management Server. In our case it was and it was also our MDW server.

From here you will specify an account. We used our SQL Server agent account.  You will then run a validation. The utility requires a number of different validations. Some highlights include version 10.5 or higher. SQL Server edition, existence of a database named sysutility_mdw and collections sets must be stopped.

Since the UCP server and any subsequent servers we enrolled were already used in an MDW architecture the  collections set validation caused us some real headaches. Simply stopping the collection set jobs and\or disabling the collection service was not enough to cause the validation to succeed. You have to manually stop each collection set through SSMS as seen in this screenshot:

Once everything validated we could move on and enroll a server.  You return to the original screen and select the option to enroll a server. The process is basically the same so I won't bore you with the details and the excessive screen shots

So now we were ready to hit-the-ground-running and see what we can do with the tool. This is where we ran into the limitations. It's not a long list but for us they were show stoppers.

You can only manage a maximum of 25 servers
For us this ended it. 25 maximum instances for a multi-instance management application! 25 wouldn't even cover 1/3 of the number of servers we would like to manage.  I think that's a malevolent limitation considering you need to purchase Enterprise Edition to even use the tool.

The database name sysutility_mdw is non-configurable. 
As I mentioned before we implement MDW on a grand scale. Our MDW database is called..well...MDW. If we happened to call it sysutility_mdw then installing the UCP would have required us to wipe it out. When enrolling an instances in UCP that is already using MDW, the UCP enrollment routine will change all the collections to go to the sysutility_mdw.  We could change the collection set to send data, including UCP data to our MDW database but the UCP utility on the manager would not see the MDW database.  It's basically all or nothing. Everything must go to a database called sysutility_mdw.  This is frustrating because the database created for MDW and the database created for UCP are basically the exact same database. Why Microsoft couldn't allow you to call the database whatever you want is beyond me.  My guess is one Microsoft team worked on MDW and another one worked on UCP and they didn't like each other.

You cannot configure the 15 minute upload interval
This wasn't too big of a deal for us but it is frustrating. You can schedule the data collection on enrolled instances but you cannot change the 15 minute upload interval on the UCP server. Why? Only the boys in blue know for sure.

Default policies are limited and there is no integration with Policy Manager
UCP policies are basically CPU usage and database file storage. And, from what I can tell, there is no means to add policies to the UCP dashboard. This is what you are stuck with out-of-the-box:

This might be a cool dashboard to impress a manager but, as a useful tool to managing your critical systems, it's worthless.

One other thing to keep in mind before I end.  When you designate the account running the UCP collection set, by default all the cache files will go into that account's temporary user directory on the C drive. I suggest you change this. Also, do not manually run and upload the UCP collection set job. This throws it out of whack and you'll get primary key violations.  To resolve the error you'll need to delete all the cache files in the cache directory.

Links to all these problems are here:

On a positive note I think UCP has potential and I'll keep it in mind as versions progress. For now I cannot recommend it as a serious tool for multi-instance management and I would suggest your time is better spent on other technologies.

No comments:

Post a Comment