The Art of SQL Server Database Administration, Development, and Career Skills for the Technically Minded
Yesterday I said true monitoring and troubleshooting didn’t need much more than a good understanding of DMV, extended events, and the query window. You could make the same argument for monitoring multiple servers at once using a central management server and registered server groups. With that said, however – there is good baseline info to be collected from many servers in one collection using two enterprise level data collecting tools offered with SQL Server and SSMS. Today we will talk about SQL Utility and then tomorrow MDW.
SQL Utility is a technology provided by Microsoft to set up an instance of SQL Server – designated as the Utility Control Point – which collects server health information from all the other server instances connected to the utility and stores this data locally in the utility management data warehouse, UMDW. Don’t confuse the UMDW with the Management Data Warehouse (MDW). While similar – they remain different solutions. We’ll talk about MDW tomorrow.
The data points collected by SQL Utility are defined by policies managed by the administrator. These policies focus on CPU, Disk, and Data/Log file utilization. The policy sets acceptable limits and durations giving you as the administration a way to monitor servers that are outside of pre-defined utilization ranges.
So, as a quick example. You could set the CPU utilization policy so that every time an enrolled database server’s CPU usage is above 80% for more than 5 minutes it is identified as ‘Overutilized’. These exceptions are stored centrally in the data warehouse (the UMDW). Management of SQL Utility and the UMDW is most easily accomplished with SSMS.
Creating a Utility Control Point
There is a ton of documentation about SQL Utility on books online. As this series is on SSMS and what you can do with it we’ll just do a quick setup and look at working with your UCP via SSMS to get your feet wet – I’ll return you to MSDN for a deeper dive.
You create a utility control point by bringing up the Utility Explorer and clicking the add new icon. You will be presented with a straightforward GUI that walks you through setting up a new server to act as the control point. A database (the UMDW) is created on this instance called sysutility_mdw.
With this instance now acting as the UCP you can enroll new instances by right clicking the ‘Managed Instances’ now within your Utility Explorer. The GUI for enrolling new instances is very similar to the one for setting up the initial UCP (which by default is a member of the same collection).
You can enroll both Database Instances and Data Tier Applications – yes there are some subtle differences between the two.
You can manage policies on a global basis or an instance by instance basis. Your options with regards to setting policies are pretty much limited to the following. 1. CPU Utilization (system), 2. CPU Utilization (instance), 3. File Utilization (instance), and 4. Storage Volume Utilization (system).
From there you specify definitions of underutilization and overutilization:
You can also define ‘Volatility’:
SSMS will also allow you to graphically manage security and retention in your UMDW.
Now that you have your UCP and some enrolled instances you can keep an eye on all your servers via the Utility Explorer. There are some decent infographics as well as hot-linked bar graphs.
Each link takes you to an additional report that gives deeper information on utilization. This is what the volume based page under storage utilization looks like for example: