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:
http://support.microsoft.com/kb/980497
http://technet.microsoft.com/en-us/library/ee210570.aspx

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.

Useful Features in SSMS for Managing a Large SQL Server Environment

I really enjoyed presenting at SQL Rally about challenges DBAs face in large SQL environments.  My evaluations were not as stellar as I had hoped (I actually ended up pretty much setting the average bar) but all the comments against my presentation were spot-on.  Mostly they were that the presentation covered too much and was too general.  I had actually said this prior to my presentation so maybe I'm partly to blame for planting the seed. The past is the past and that presentation really was designed as an outline to provide fuel for thought - and future blog posts and articles!  This is where this post comes in.  

Countless articles have already been written about SSMS features. SSMS has certainly come a long way since the days of Enterprise Manager.  One way Microsoft has advanced the tool is in the realm of managing a large number of instances. I think people overlook some of the features developed in SSMS 2005 and continued into SSMS 2008. I've been managing large environments for years and I'm even still discovering new tips and tricks that make my job more efficient. Here are few notable points.  Again, the details of these can be gathered in other technical postings and some I may elaborate on in future posts, but here's the gist:

1) Use CMS (Central Management Server)
This is a no-brainer for a large environment. Out of all the changes to SSMS I truly believe this is the single greatest change.  Simply put CMS allows you to designate a SQL Server as repository for all your registered servers.  Why is this important?  Because of CMS all DBAs on a team now have a single, standard list of all the registered SQL Servers in the enterprise. Additionally, in the age of VM we add a server almost every week (I built 3 last week) and now all I have to do is register it in the CMS and everyone sees it. I use to have to send out a notification telling everyone to register the server and I would have to assume they did. Many times people forgot and I now have a DBA who doesn't realize we manage a server. This can prove a bit awkward for after-hours support calls.   There are other advantages to using CMS that I'll get into in later posts.

2) Using Groups to Add Servers to Multiple Categories
In a large database environment information comes to you in many forms depending on the where the information originates. For example, a non-technical user may inquire about a database problem but have no idea of anything beyond the application name listed as in icon on his desktop.  You also may get requests from internal IT folks like the server or storage teams who know nothing about the database but do know the server name.  The solution for this in SSMS is to create multiple groups.  You cannot put the same server name multiple times in one group but you can add the same name to different groups.  Here are my groupings - your mileage may vary:

Here's my reasoning. The "ALL SERVERS" is my list of all the servers in the environment. If someone comes to me with a server name,  I look here to see if we support it. If it's not listed then we don't support it.

Under "APPLICATIONS" is a list of more groups with names like "SHAREPOINT", "XEROX", and "LENEL".  Each group is the general name of the application and under that group is the registered server.  This allows me to quickly find the server if a user refers to the system by the application name.  the "DEPARTMENT" group I don't use too much but it allows me to separate out internal systems from external systems. In this group there are other groups listed such as "CITRIX TEAM", "MESSAGING TEAM", and "SERVER TEAM".  Each group has their own list of registered servers.

"LOCATION" is helpful if you have a geographically distributed environment. If  a user knows no other information except for where their server is physically located then this group allows me to have a fighting chance of tracking it down.

"NONPROD" and "PRODUCTION" help me separate our servers for monitoring and support. The screenshot doesn't show it but under "PRODUCTION" I have groups named "SQL SERVER 2000", "SQL SERVER 2005" and "SQL SERVER 2008" which is used for Policy Management.

Finally, there is "POC" which stands for "proof of concept". These are servers created by various teams for testing or product selection.  I keep these separate because they tend to (hopefully) only stick around for a short amount of time but I want to keep track of them so I can remind the users that they eventually need to go away to free up licenses.

3) Use Templates (and Soon-to-Arrive Snippets)
Templates I've found are a rarely used feature and I've had difficulty getting my team to use them.  Templates in SSMS lack a lot of functionality but I think it's a good start.  Here is a link to a Tim Ford article at MSSQLTips thoroughly explaining templates in SSMS: http://www.mssqltips.com/tip.asp?tip=1465.  Right now templates aren't great for collaboration but they do allow you to somewhat standardize your scripts and easily access them. I keep a list of a few key scripts and pass them out to my team when I update any of them.  I know Microsoft has some plans to expand the idea using snippets.  Snippets will be similar to templates but allow for a centralized source for code.  Aaron has a good summary on his blog here: http://sqlblog.com/blogs/aaron_bertrand/archive/2010/11/23/sql-server-11-denali-the-new-vs-shell.aspx

These 3 simple items will do wonders to help you manage your large environment.  They also help keep your team in sync.  The Central Management Server is the groundwork for implementing additional services. In another blog post I'll talk about more using services like Policy Management, MDW (Management Data Warehouse), and Multi-Instance Management to help monitor large, complex environments.