Getting Started with Utility Control Point in SQL Server 2008 R2

In my last article I talked about SQL Server Utility, Utility Control Point, what it is, how it helps SQL Server DBAs in multi-server administration, and finally I demonstrated creating Utility Control Point step-by-step.

In this article, I will be talking about enrolling multiple SQL Server instances in an already created UCP for health data collection and monitoring purposes and how to administer SQL Server Utility and Utility Control Point.

Enrolling SQL Server instance to Create Utility Control Point

You can enroll multiple instances of SQL Server to Utility Control Point for monitoring and managing the instance. Right click on the Managed Instances node in the Utility Explorer and click on Enroll Instance menu as shown below:

ucpenroll

Figure 1 – Enrolling an instance

After clicking Enroll Instance, the menu item launches Enroll Instance wizard, which asks you to specify the instance names which are to be managed. Once an instance is registered, there are some jobs created on that instance to periodically collect data and send to Utility Control Point. Click on Connect button to specify and connect to the SQL Server instance.

ucpwizard

Figure 2 – Enroll Instance Wizard – Specify instance

On the next screen you need to specify the credential which will be used to collect the data from the managed instance. If your SQL Server Agent service account is a domain account then you can use the same account for collecting the data but its recommended you specify a different low privileged domain account under which data will be collected.

Enroll Instance Wizard

Figure 3 – Enroll Instance Wizard – Specify Account

The next screen of the wizard validates the instance for enrolling it to Utility Control Point. There is a set of rules which gets executed and the result of this validation is shown as shown below in the Result column. If the result of the validation is “Failed” you will not be able to proceed further. You need to first fix the issue, click on Rerun Validation button again for revalidation and once validation passes you can move to the next step.

ucpvalidation

Figure 4 – Enroll Instance Wizard – Validation

The last screen of the wizard actually shows the progress and status of enrolling an instance to the Utility Control Point as you can see below. You can click on the Save Report button to save the progress/status in HTML format for later use if you want.

Enroll Instance Wizard status

Figure 5 – Enroll Instance Wizard – Progress and Status

When you connect to Utility Explore content (you can launch this page by clicking on the created/connected UCP in the Utility Explorer), you will notice all the enrolled instances listed there. For a newly enrolled instance, you might notice the row is grayed out, as you can see in the second row in the image below; this is because no information has been collected so far from that instance. Once the information gets collected from the instance it starts appearing here:

ucpinstance

Figure 6 – Enrolled instance in action

As I said before, when an instance is added to UCP, there are some SQL Agent jobs that get created on the instance that allows the collection set to collect data from that instance and send it to UCP. You can verify these jobs under the Jobs node of the SQL Server Agent of the instance as shown below:

ucpjobs

Figure 7 – SQL Server Agent jobs created on the instance

Administrating Utility Control Point

Administration of Utility Control Point (UCP) comes in two flavors. First, you can do the global administration of defining policies, which will impact all the managed instances on the UCP. If you want to have different policies for specific instances, you can override the default global settings and define settings which will be applicable for that instance only, which is the second way of administrating UCP. To change the global settings that will impact all the managed instances on the UCP (unless they are explicitly overridden), connect to the UCP in Utility Explorer and click on the Utility Administration node in the tree as shown below. On the right side (in Utility Explorer Content) you will notice 3 tabs called Policy, Security and Data Warehouse.

ucpadmin

Figure 8 – UCP Global Administration

The policy tab has policies which can be defined for Data-tier applications, managed instances and policy evaluation settings. If you expand “Global Policies for Data-tier applications,” you can changes these settings. The default percentage when underutilized will be reported as 0 (zero), which means it will not report underutilization. You can change these values to something higher than 0 as per your need to let UCP report underutilization.

ucpadmin2

Figure 9 – UCP Global Administration – Data-tier applications

Likewise if you expand “Global Policies for Managed instances,” you can change these settings. By default, here also percentage when underutilized will be reported as 0 (zero), which means it will not report underutilization. You can change these values to something higher than 0 as per your need to let UCP report underutilization.

ucpadmin3

Figure 10 – UCP Global Administration – Managed Instances

Next you can change how and when policy evaluation will take place.

ucpadmin4

Figure 11 – UCP Global Administration – Policy Evaluation

As I said before you can override these global policy definitions for a specific managed instance. To do that, select the instance from the list of managed instances and go to the Policy Details tab as shown below:

ucpadmin5

Figure 12 – Instance Specific Administration

Under Policy Details tab, you can expand the bar and check the radio button to indicate whether you want to use the global setting or you want to override and have different settings for this instance.

ucpadmin6

Figure 13 – Instance Specific Administration – Overriding global settings

Conclusion

In this article on this series I talked about enrolling multiple SQL Server instances in an already created UCP for health data collection and monitoring purposes and how you administer SQL Server Utility and Utility Control Point. I also talked about global administration, which impacts all instances of the UCP and how to override these global settings for a specific instance to have separate settings than global ones.

Resources

Creating Data-tier applications in SQL Server 2008 R2
MSDN Overview of SQL Server Utility

See all articles by Arshad Ali 

 

Arshad Ali
Arshad Ali
Arshad Ali works with Microsoft India R&D Pvt Ltd. He has 8+ years of experience, mostly on Microsoft Technologies. Most recently, as a SQL Developer and BI Developer he has been working on a Data Warehousing project. Arshad is an MCSD, MCITP: Business Intelligence, MCITP: Database Developer 2008 and MCITP: Database Administrator 2008 certified and has presented at several technical events including SQL-School. On an educational front, he has an MCA (Master in Computer Applications) and MBA-IT. Disclaimer : I work for Microsoft and help people and businesses make better use of technology to realize their full potential. The opinions mentioned herein are solely mine and do not reflect those of my current employer or previous employers.

Get the Free Newsletter!

Subscribe to Cloud Insider for top news, trends & analysis

Latest Articles