As your business grows, the number of applications grows as well, as do the SQL Server instances to support these applications. As a SQL DBA, you need to have a multi-server management dashboard that proactively tells you about the resource utilization on each SQL Server instance.
SQL Server 2008 R2 introduced the SQL Server Utility and Utility Control Point, which lets you have a consolidated dashboard-type view of resource utilization on all the servers in your multi-server environments. It helps SQL DBAs to proactively monitor their SQL Server instances’ resource utilization. In this article I will talk about SQL Server Utility and Utility Control Point in detail.
Understanding SQL Server Utility and Utility Control Point (UCP)
SQL Server Utility and Utility Control Point (UCP) allows SQL DBAs to centrally monitor and manage SQL Server instances (which are enrolled) and data tier applications on those instances.
The processing unit of SQL Server Utility is called Utility Control Point (UCP), which collects resource utilization (Instance CPU utilization, Computer CPU utilization, both data and log file space utilization, and storage volume utilization) data using the defined policy evaluation from all the enrolled SQL Server instances on the interval and stores it in Utility Management Data Warehouse (UMDW) database (called sysutility_mdw and created when you create UCP) in consolidated form. Data gets collected from all the enrolled instances every 15 minutes and you can set the retention policy for the collected data. By default, collected data is retained for one year, though you can change it to 1 month, 3 months, 6 months or 2 years per your need and requirement.
SQL Server Utility has Utility Explorer, which is very much like Object Explorer, which shows a hierarchical tree view of SQL Server Utility component. It also allows you to connect to an already created UCP, create a new UCP, enroll an instance to a UCP, and so on.
SQL Server Utility has Utility dashboard, which shows a graphical representation of collected data and policy violations, if there are any.
Note that you can create a UCP on SQL Server 2008 R2 or higher versions only. Also it’s available with Datacenter, Enterprise, Developer or Evaluation editions only. For other requirements for creating UCP, refer here.
Creating Utility Control Point (UCP)
When you create a Utility Control Point (UCP), SQL Server creates Utility Management Data Warehouse (UMDW) database, provisions UCP schema, jobs and policies on the specified instance. It also enables the collection set to periodically collect data from managed instances and stores it into UMDW database.
There are basically two ways to create a UCP (though you can also use PowerShell cmdlets for UCP creation). First you can go to View menu in SSMS and click on Utility Explorer menu item as shown below. It will launch the Utility Explorer, which is very much like Object Explorer.
Figure 1 – Launch Utility Explorer
You can either click on “Create Utility Control Point” icon as shown below in the Utility Explorer to create a new UCP.
Figure 2 – Creating UCP from Utility Explorer
Or you can you can either click on “Create a Utility Control Point” link on the Getting Started page as shown below to create a new UCP. To learn more about creating UCP, you can click on the “Video” link before the “Create a Utility Control Point” link. You need to have an Internet connection to be able to view the video.
Figure 3 – Creating UCP from Getting Started Page
After clicking on the “Create Utility Control Point” icon or on the “Create a Utility Control Point” link, the Utility Control Point creation wizard gets started as shown below. By default the first screen is a welcome screen (which you can set to not show up next time) which tells about the steps of creating Utility Control Point.
Figure 4 – Utility Control Point Creation – Introduction
On the next screen you need to specify the name of the instance you intend to create Utility Control Point on and name of your Utility Control Point. You can have multiple UCPs created for managing and monitoring servers of different environments. As you can see I have created one for managing and monitoring my development servers; likewise you can have for test, QA and production as well. To specify the instance click on the Connect button to connect on to that instance.
Figure 5 – Utility Control Point Creation – 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 6 – Utility Control Point Creation – Collection Set Account
On the next screen the wizard validates the instance for pre-requisite for Utility Control Point. There is a set of rules that gets executed and the result of this validation is 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 7 – Utility Control Point Creation – Validation
You can see the result of each validation check by clicking on the link in the Result column, for example in my case as in above image I had a warning, clicking on the link displayed the reason for warning as you can see below:
Figure 8 – Utility Control Point Creation – Result
Next screen is a Summary page which lists all the selections you have made so far on the wizard. If you need to change anything you can go back or else click on Next button to start creating Utility Control Point as shown below:
Figure 9 – Utility Control Point Creation – Summary
The last screen of the wizard actually shows the progress and status of Utility Control Point creation. By default the instance on which you created UCP gets enrolled with it as you can see in the image below. You can click on Save Report button to save the progress/status in HTML format for later usage if you want to do so:
Figure 10 – Utility Control Point Creation – Progress/Status
Once Utility Control Point is created, the Utility Explorer connects to the created UCP and displays the information in hierarchical tree view as you can see below:
Figure 11 – Utility Explorer
Once you click on the root of the tree view, the Utility Explorer Content dashboard is displayed as shown. For some time after the UCP creation, you will not be able to see any data on the dashboard as data is collected on some interval. You need to wait for some time for the dashboard to display the collected data. You can notice the managed instance health-related information shown on the dashboard along with the health of all the data-tier applications on those managed instance. To learn more about Data-tier applications click here.
Figure 12 – Utility Explorer Dashboard
Now to verify that if the Utility Management Data Warehouse database is created, connect to the SQL Server instance using SSMS and check for sysutility_mdw database. In this database you will notice a couple of partitioned tables and views built on top of these partitions.
Typically the sysutility_mdw database takes 2 GB of space for each managed instance, whereas msdb takes 20 MB for each managed instance. These are just an estimate and might vary depending on your configuration. Also note that you can remove an instance from UCP at any time, but data related to that specific instance will not be removed from UCP until expiration of retention periods.
Figure 13 – UMDW database
Apart form the above you will also notice a couple of SQL Server Agent jobs being created as part of Utility Control Point creation. These jobs are basically responsible for running collection sets to gather data from the managed instances; also it purges the data which goes out of the specified retention period:
Figure 14 – SQL Server Utility Jobs
Please note, UCP doesn’t capture data related to FILESTREAM for monitoring.
In this article I talked about SQL Server Utility, Utility Control Point, what they are, how they help SQL DBAs in multi-server administration and finally a step by step demonstration creating Utility Control Point.
In my next article in this series I will be talking 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.