Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Links Database Forum

» Database Journal Home
» Database Articles
» Database Tutorials
MS SQL
Oracle
DB2
MS Access
MySQL
» RESOURCES
Database Tools
SQL Scripts & Samples
Links
» Database Forum
» Sitemap
Free Newsletters:
DatabaseDaily  
News Via RSS Feed


follow us on Twitter
Database Journal |DBA Support |SQLCourse |SQLCourse2
 

Featured Database Articles

MS SQL

Posted September 28, 2011

Getting Started with Utility Control Point in SQL Server 2008 R2

By Arshad Ali

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 

 



MS SQL Archives

Comment and Contribute

 


(Maximum characters: 1200). You have characters left.

 

 




Latest Forum Threads
MS SQL Forum
Topic By Replies Updated
SQL 2005: SSIS: Error using SQL Server credentials poverty 3 August 17th, 07:43 AM
Need help changing table contents nkawtg 1 August 17th, 03:02 AM
SQL Server Memory confifuration bhosalenarayan 2 August 14th, 05:33 AM
SQL Server Primary Key and a Unique Key katty.jonh 2 July 25th, 10:36 AM


















Thanks for your registration, follow us on our social networks to keep up-to-date