One of the most useful new features of SQL 2008 R2 for DBA’s is the ability to manage and administer multiple instances of SQL Server from a central location using the new SQL Server Utility tool.
SQL Server Utility provides a unified view of the managed instances of SQL Server providing details such as CPU utilization, database file space utilization and volume space utilization by each managed instance. It provides a Utility Control Point (UCP) as a central repository for configuration and management of performance data collected for managed instances, and a Utility Explorer as a dashboard to view detailed and summary reports of the utilization, resource health and configuration information of all instances enrolled in the SQL Server Utility.
To setup the SQL Server Utility you need to:
(1) Create a UCP from the SQL Server Utility.
(2) Enroll instances of SQL Server with the UCP.
(3) Define Global and Instance level policies, and manage and monitor the instances.
To create a UCP, start by launching the SQL Server Utility and then select View > Utility Explorer in SQL Server Management Studio (SSMS). This will launch the getting started page:
The first option on this page is Create a Utility Control Point (UCP). Clicking this will launch the first screen of the UCP wizard as shown below.
This wizard will take you through a series of 5 steps to create and set up the UCP. Click Next to begin and you will be prompted to specify the name for the UCP, and select the SQL Server Instance to act as the UCP.
Next, you will need to specify the SQL Server proxy account for the data collection set as shown below:
Next, the Instance Validation screen that will show the progress and results of the validation conditions for an instance.
Next, the wizard will confirm the settings to be applied upon installation:
Finally, click Finish to close the wizard.
Since the UCP itself becomes a managed instance automatically, once the wizard closes you will be directed to the Utility Explorer Content page displaying a graphical view of the various parameters like CPU utilization, database file space utilization and volume space utilization that are measured for the UCP instance.