Introduction to Utility Control Points in SQL Server 2008 R2

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:

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:

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:

The Utility Configuration Steps are displayed as shown below:

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:

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:

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

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:

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:

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:

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:

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: