ballardchalmers - home

Contact us 01342 410223

Blog

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 enquiries@ballardchalmers.com

Comment on this article

About

Ballard Chalmers is one of the UK’s leading enterprise software engineering companies, specialising in development for the Microsoft Enterprise Application Platform. We are principally dedicated to software development and integration for the Microsoft Cloud, as well as on-premises and hybrid systems. Our expertise is in Microsoft .NET, SQL Server, BizTalk Server and SharePoint Server.

Let's talk

  01342 410223

Find out more

Thank you for contacting us.

We will be in touch with you shortly.