skip to Main Content

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:

Server Management With Utility Control Points 1

Then select the Enroll Instance menu, as shown below, to register each server that you wish to monitor:

Server Management With Utility Control Points 2

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):

Server Management With Utility Control Points 3

The top level UCP dashboard then gives an overview for the state of health of all servers:

Server Management With Utility Control Points 4

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:

Server Management With Utility Control Points 5

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.

By Geoff Ballard, Chief Technical Officer

Have a comment? Let us know below, or send an email to [email protected]

About the Author

As Co-Founder of Ballard Chalmers, Geoff is the company’s CTO, directing technical strategy, overseeing technical consultants, managing larger development projects and ensuring technical delivery quality standards.

Education, Membership & Awards

Geoff graduated from the University of London with a BSc (Hons) in Mathematics and computing and a Masters degree in Database Management Systems. Additionally, he is a certified SQL Server Development, Microsoft Certified IT Professional (MCITP) and Microsoft Certified Technical Specialist (MCTS).

His professional memberships include Charted Engineer (Software), Member of the British Computer Society (MBCS), Member of European Federation of Engineers (FEANI) and Fellow of the Institute of Analysts and Programmers (FIAP).


Back To Top
Contact us for a chat