mssqltips logo

Introduction to Utility Control Points in SQL Server 2008 R2

By:   |   Updated: 2010-09-15   |   Comments (6)   |   Related: More > Monitoring

Problem

I'm looking at the new features in SQL Server 2008 R2 and I see one called a Utility Control Point. Can you provide an overview of this feature? Does this only work with SQL Server 2008 R2? Check out this tip to answer these questions and see how to use this new feature in your SQL Server environment.

Solution

SQL Server 2008 R2 has a set of new features called Application and Multi-Server Management that focus on reducing the complexity of managing databases and applications. The Utility Control Point is one of these features. At a high level the Utility Control Point provides a dashboard view of the overall health and resource utilization of the SQL Server instances and Data-tier applications in your organization. The dashboard is available from the Utility Explorer in SQL Server Management Studio 2008 R2 and is shown below:

SQL Server 2008 R2 has a set of new features called Application and Multi-Server Management

The following are the main points about the dashboard:

  • SQL Server instances and Data-tier applications are classified as well utilized, over utilized or under utilized
  • Resources reported on are per SQL Server instance: CPU usage, database files, storage volumes, and server CPU usage
  • Storage utilization history can be shown over a day, week, month or year
  • The criteria for determining whether a resource is well utilized, over utilized or underutilized are configurable via policies in the Utility Explorer
  • The dashboard is populated from data collected by SQL Agent jobs which are added when you create the Utility Control Point
  • You have to enroll each SQL Server instance in order to collect its data and include it on the dashboard; SQL Agent jobs are added to the instance to collect data and forward it to the Utility Control Point

The Utility Explorer also provides a dashboard view for a single SQL Server instance that includes CPU and storage utilization as shown below:

The Utility Explorer also provides a dashboard view for a single SQL Server instance
Resources reported on are per SQL Server instance: CPU usage, database files, storage volumes, and server CPU usage

In this tip I will cover the following steps involved in setting up the Utility Control Point:

  • General Requirements
  • Creating a Utility Control Point
  • Enrolling a SQL Server instance in a Utility Control Point
  • Configuring policies in the Utility Explorer

I covered Data-tier applications in the tip Introduction to Data-Tier Applications in SQL Server 2008 R2. When you enroll a SQL Server instance in the Utility Control Point, its Data-tier applications are automatically enrolled as well.

General Requirements

The most important requirement is that the Utility Control Point is only available with SQL Server 2008 R2 instances; i.e. the Utility Control Point itself and every SQL Server instance that is enrolled must be SQL Server 2008 R2. The Utility Explorer (which displays the dashboard and includes the policy configuration) is only available in the version of SQL Server Management Studio that comes with SQL Server 2008 R2. Only the Database Engine is monitored in this initial release. FILESTREAM data is not monitored.

Additional requirements include:

  • All SQL Server instances must be in a single domain or you must have 2 way trusts
  • The server configuration Agent XPs (SQL Server Agent Extended Stored Procedures) must be enabled; you can use sp_configure to turn on Agent XPs; for more information see Agent XPs option in Books on Line. When you specify SQL Agent to start automatically during installation this will be done automatically.
  • SQL Agent should be set to start automatically
  • The SQL Agent service account must have read permission on Active Directory users
  • The SQL Agent service account must be a member of the local group Performance Monitor User
  • The edition of SQL Server for a Utility Control Point must be Data Center or Enterprise
  • Each enrolled instance will accumulate as much as 2GB of data per year
  • The MSDB database will use about 20MB of storage for each enrolled instance

Creating a Utility Control Point

Open SQL Server Management Studio 2008 R2, click the View menu then select Utility Explorer from the context menu as shown below:

Open SQL Server Management Studio 2008 R2

The Utility Configuration Steps are displayed as shown below:

The Utility Configuration Steps

Click Create a Utility Control Point (UCP); you will be guided through a wizard to perform the operation. You will need to specify an instance of SQL Server 2008 R2 that will contain the Utility Control Point and a name for the UCP:

specify an instance of SQL Server 2008 R2 that will contain the Utility Control Point and a name for the UCP

Next you need to specify a Windows domain account to perform the data collection; you can specify an account or use the SQL Server Agent account as shown below:

 you can specify an account or use the SQL Server Agent account as shown

After completing the above steps in the wizard, the validation step is performed as shown below:

 the validation step is performed

The validation step performs a number of checks to make sure that your SQL Server instance meets the requirements for creating the UCP. You will have to correct anything where the "Result" column isn't "Success" (there may be some warnings that you can ignore). As you can see there are quite a few requirements; one that I had to correct was to add the SQL Agent service account to the local group Performance Monitor Users.

The wizard will create a database named sysutility_mdw to hold the data collected from your SQL Server instances. In addition the following SQL Agent jobs will be created to perform the data collection:

the following SQL Agent jobs will be created to perform the data collection

Please refer to Managing the SQL Server Utility in Books on Line for details on the configuration options available for the sysutility_mdw database and the SQL Agent jobs.

Enrolling a SQL Server Instance

A SQL Server instance must be enrolled with the Utility Control Point in order to collect its data and include it on the dashboard. You can enroll instances using the Utility Explorer in SQL Server Management Studio. Click on Enroll Instances of SQL Server with a UCP (see Utility Configuration Steps in the previous section). A wizard is displayed to guide you through the steps.

The first step is to identify the SQL Server instance to enroll in the UCP:

identify the SQL Server instance to enroll in the UCP

Note that you can only enroll an instance in one UCP and that a UCP can have a maximum of 25 enrolled instances.

The second step is the same as when you created the UCP; you have to specify the account to use for data collection (you can specify the SQL Agent service account).

The last step in the wizard is to perform a validation to make sure all of the requirements for enrollment are met:

 perform a validation to make sure all of the requirements for enrollment are met

Just like with creating the UCP, there are a number of requirements and the "Result" column must be "Success" in order to continue (there are warnings that you may be able to ignore).

Configuring Policies

The classifications of well utilized, under utilized and over utilized (as shown in the Utility Explorer dashboard) are determined based on global policies and instance-specific policies that you can configure. Click on Utility Administration in the Utility Explorer to view and edit the policy. The global policies for managed instances are shown below:

Click on Utility Administration in the Utility Explorer to view and edit the policy

You can change the global policies to suit your needs and you can also specify policies at the instance level. Click on Managed Instances in the Utility Explorer, select a SQL Server instance, then click the Policy Details tab to view and edit the policies for a particular instance as shown below:

select a SQL Server instance, then click the Policy Details tab
Next Steps


Last Updated: 2010-09-15


get scripts

next tip button



About the author
MSSQLTips author Ray Barley Ray Barley is a Principal Architect at IT Resource Partners and a MSSQLTips.com BI Expert.

View all my tips
Related Resources




Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name
*Email
Email me updates

Signup for our newsletter

I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.





Thursday, April 03, 2014 - 9:35:55 AM - Raymond Barley Back To Top

I'm not sure how popular it is; it's in SQL2012; I don't know about SQL2014


Thursday, April 03, 2014 - 2:55:04 AM - Greg Back To Top

Hi Ray.

I like this article- it is quite clear and precise. 

So is Utility Control Point a big item yet? I don't see or hear much written about it.

The potential is huge but has it been taken over by SCOM?

Further, is this utility included in SQL 2014 do you know?

 

Kind regards,

Greg.


Monday, October 22, 2012 - 6:44:37 AM - Toto Back To Top
hi sir will you please tell me if in fetruae selection page i uncheck the management tools-basic and other 3 checkboxes i check but not the management tools-basic then is it effect or me sql server 2008 r2 worked properly?plzzz reply i am waiting for ur answer thanks

Tuesday, September 13, 2011 - 9:37:33 AM - GMAN DBA Back To Top

If you are using UCP the you *really* want to apply SP1 to your 2008R2 server.  It reduces the amount of data stored in the sysutility database by an order of magnitude.


Tuesday, September 13, 2011 - 6:47:48 AM - Tike Back To Top
Mighty usfuel. Make no mistake, I appreciate it.

Thursday, June 23, 2011 - 12:46:03 PM - GMAN DBA Back To Top

Right lick Data Collection -> Properties.

Then set the Cache Directory to a valid dir that that the SQL Agent and SQL Server service accounts have permission to. Now all my SQL2008R2 servers are sending data to the UCP server, which is awesome.

 

http://support.microsoft.com/default.aspx?scid=kb;en-us;980497



download

























get free sql tips

I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



Learn more about SQL Server tools