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:
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.
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.
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.
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.
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:
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:
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.
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.
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.
Figure 10 – UCP Global Administration – Managed Instances
Next you can change how and when policy evaluation will take place.
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:
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.
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
