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.
Once you have created the UCP, you can start enrolling instances of SQL Server you wish to monitor and manage. This is done in the Enroll Instance Wizard, launch it by expanding the Utility Explorer tree, right-clicking on Managed Instances and selecting Enroll Instance. You will see the Introduction screen (as in the below two screenshots)
This wizard is also a series of five steps that will collect information regarding the SQL Server instance to be enrolled and register it with the UCP. On the first screen (below) click Next to proceed with the wizard.
Next, as in the UCP wizard, click on the Connect button to bring up the Connect to Server dialog box (see below) where you select the Instance to be added, and then click Next to proceed.
Next, either specify a Windows Domain Account or a SQL Server Agent service account for the utility collection set. Microsoft recommends using a Windows domain account. Even if a SQL Server Agent account must be used, it should not be a built-in account such as LocalService, NetworkService or LocalSystem, but a windows domain account. Click Next to continue (see below)
The Instance Validation screen (see below) shown above is similar to the one you see in the UCP creation wizard. Click Next to proceed after confirming all Action Results are a Success.
The Summary shows the settings of the SQL Server Instance to be added. When you click Next, you will come to the final step of the wizard – Enrollment of SQL Server, showing the status of actions taken to enroll the instance in the UCP. Once you confirm the Success of all actions, click Finish to close the wizard (see below two screenshots)
In the Utility Explorer Content page, there are several sections showing a graphical representation of the resources utilization. The Utility Summary box shows how many instances are managed. The Managed Instance Health and Data-tier Application Health indicates the overall health of instances. The Managed Instance with Overutilized Resources shows how many instances have overutilized the available resources. Likewise, the Managed Instance with Underutilized Resources shows how many instances are underutilizing the available resources. A similar explanation holds for the Data-tier Application boxes on the right. The two boxes in the bottom shows the utilization history of the utility control point managed instance.
A breakdown of utilization of each resource by individual managed instances is also available. In the Utility Explorer page, you click on the Managed Instances node, and on the right-hand side, in the Utility Explorer Content Page select the individual instances and look through the utilization of each resource in detail.
No comments:
Post a Comment