Looking for a way to monitor resources health and usage from SQL Server instances and databases without expending more money and much time?
Then this article is for you.
You only need to have an Enterprise Edition of SQL Server 2008R2 or superior so you can use the SQL Server Utility
Utility Explorer is a component of SQL Server Management Studio that provides information about the health state of managed instances of SQL Server 2008R2 or superior, providing a user interface to view and manage policy definitions.
A Windows Domain Account is also necessary to configure the Utility Control Point (UCP).
NOTE: Monitoring of Data-Tier Applications is not covered by this article.
Utility Explorer is not visible in the SQL Server Management Studio by default. To view the Utility Explorer content pane, open the View menu and click on Utility Explorer.
At the first time you need to follow the Utility Configuration Steps, starting with the creation of an Utility Control Point (UCP).
Just click on "Create Utility Control Point (UCP)" link and follow the respective wizard.
I won't provide the screenshots for the wizard because it's very trivial and there's a link for an help video in case you need it.
Just remember that SQL Server Agent Service need to be running (set it to start automatically to avoid future issues) and that you need to provide a Windows Domain User Account for this component.
NOTE: The SQL Server Agent service account must have read permission on Active Directory users and must be a member of the local group Performance Monitor User.
What the creation of a UCP does is to create and configure the Utility Management DataWarehouse (UMDW). After that you will see the dashboard in Utility Explorer Content:
The dashboard it still blank because the datawarehouse has no data but at least you can see already what kind of information and how it will be provided.
Going back to ObjectExplorer we can confirm that the datawarehouse (sysutility_mdw) has been created:
Now it's time to configure the Policies, Security and the Datawarehouse. For that, in the Utility Explorer pane click on Utility Administration option. The Utility Explorer Content will change to something similar to the below screen:
Here you can set the Health Policies (thresholds), Security (permissions) and the Datawarehouse data retention period (by default it's one year) and collection upload frequency (by default every 15 minutes). I recommend you to leave the defaults values and later if necessary tweak as your need.
After configuration you can start to enroll your SQL Server instances one by one
, by right-clicking on Managed Instances and chose "Enroll Instances" and will appear a wizard very similar with the wizard for create an UCP. Once more it's a very trivial wizard and that's why and won't provide screenshots for that.
Just remember that each enrolled instance must have it owns SQL Server Agent Service running and that you need to provide the Windows Domain User that is used for the SQL Agent account of the UCP server. Also you can't enroll the SQL Server instance where the UCP's data warehouse is running and can only enrolling SQL Server instances from another domain if there is a 2 way trusts between the domains.
You can see the existence of 2 SQL Agent jobs in each MSSQL instance that has been enrolled in UCP:
If those jobs aren't running then it's mostly possible that the security policy doesn't let you run PowerShell scripts. This can be confirmed by verifying in the Job history if the error returned is:
"Executed as user: Domain\UserAccount. A job step received an error at line 1 in a PowerShell script. The corresponding line is 'set-executionpolicy RemoteSigned -scope process -Force'. Correct the script and reschedule the job. The error information returned by PowerShell is: 'Security error. '. Process Exit Code -1. The step failed."
If so check what's the Execution Policy configured by running the following PowerShell command: Get-ExecutionPolicy
You'll need to set it to Unrestricted
and if you don't have the necessary privileges to change it then contact your system administrator to change this policy for you. The PowerShell command is:
Set-ExecutionPolicy -Scope LocalMachine -ExecutionPolicy Unrestricted
Others kind of errors can occur during the enroll phase. For more information on those errors please read Troubleshoot the SQL Server Utility
By default the above jobs are running every 15 minutes and collecting data into the datawarehouse (sysutility_mdw
). Let it pass some time (one hour or more) to have some data to be analyzed. Below is an example of how the dashboard in UCP Explorer can looks like:
Go to Manage Instances
to check the available data.
Here you'll find information about CPU and Storage health and usage. Below are two sample images of what kind of information you can get from UCP and I really challenge you to dig around to know more about the UCP capabilities.
As you can see there's no code to run and it's very easy to set a monitoring solution only with SQL Server resources. Unfortunately UCP monitors only CPU and Storage. I would like that in a future version Microsoft will also add Memory monitoring.
- The average disk space used by the datawarehouse (sysutility_mdw) is approximately 2 GB per managed instance of SQL Server per year.
- Removing a managed instance from the UCP will not reduce the disk space used by UCP databases until expiration of data retention periods for the managed instance.
- Each managed instance of SQL Server and every data-tier application is a member of one and only one SQL Server Utility and managed by a single UCP only.
- When enrolling a SQL Server instance that has Data-tier applications, they are automatically enrolled as well.