Server Management with Utility Control Points
Utility Control Points (UCPs) were first introduced in SQL Server 2008 R2 and are designed for managing servers of SQL Server 2008 R2 or later.
The bad news is that if you have older versions of SQL Server then you will have to upgrade to take advantage of this facility for those servers.
The rest is pretty much all good news because UCPs are a very easy way to get an overall overview of the state of all servers and to drill into view the state of specific servers.
To create a UCP in Management Studio first display the Utility Explorer using menu View > Utility Explorer menu and then select the third icon in the tool bar and run through the wizard to create the UCP:
Then select the Enroll Instance menu, as shown below, to register each server that you wish to monitor:
Then wait 15 minutes for the data collectors to update the database and the system is operational (as shown by the green ticks in the screen below):
The top level UCP dashboard then gives an overview for the state of health of all servers:
Information on individual servers can be investigated by selecting the individual server.
Polices on what constitutes over utilization and underutilisation and so on, can be set globally or per server.
The only part that I found marginally confusing during the configuration was when I received error message “The collection sets on the specified instance of SQL Server must be stopped”. This error concerned me because initially I thought it meant that it was not possible to use the Management Data Warehouse facilities for monitoring query performance at the same time as using UCPs. However it turns out that UCPs just use the same Management Data Warehouse infrastructure, and so require that any Management Data Warehouse data collectors on any of the servers involved must use the same database. So you just have to turn off any data collectors you have running and then restart them once the UCP is in place and all will work together nicely. To stop and start data collectors just right click on each one and use the Stop or Start Data Collection Set menu as shown below:
Overall the Utility Explorer and UCPs represent a simple to use and efficient way of viewing overall health for a number of SQL Server instances.
