What's So Cool About the SQL Server Utility?

Well, in version R2, the tool offers a new way of looking at managing your server databases. Here's what you'll drool over.

I've mentioned the SQL Server Utility briefly in past posts, but I want to dedicate this week's post entirely to this new piece of functionality coming to us in SQL Server 2008 R2.

The SQL Server Utility is an entirely new way of managing all SQL Servers in a given environment. It is designed to help administrators get a holistic picture of the health of their SQL Servers, from database- and query-related information to CPU use and storage.

In order to use the SQL Server Utility, administrators must configure a Utility Control Point, or UCP, that will be the central management point for the Utility. Then, servers that are being monitored are "enrolled" in the utility. The UCP must be hosted on a SQL Server 2008 R2 server, and any managed instances must be at least SQL Server 2008 R2; hopefully this will change once SQL Server 2008 R2 is released. Additionally, the edition of the server hosting the UCP must be either Data Center, Developer, or Enterprise.

Setup of a UCP is wizard-based and is launched directly from SQL Server Management Studio (see Fig. 1). When in SSMS, click the View menu, and then choose the Utility Explorer. You'll then get a new "Getting Started" window which helps you through the rest of the process.

UCP Setup in SSMS
Figure 1. Setting up a Utility Control Point in SQL Server Management Studio. (Click image to view larger version.)

You can then click on the "Create a Utility Control Point (UCP)" link, or optionally, click the Video link to get a nice walkthrough video from Microsoft. From here the wizard simply asks for an instance of SQL Server to create the utility in, as well as an account to use as the proxy for data collection. Obviously, a Windows Domain account is recommended; this account needs to have access to both the UCP server and all monitored SQL Servers. You can also use the SQL Server Agent Service account, provided it is a domain account with the necessary privileges. Once you provide the account info, the wizard validates your settings, and creates the UCP (see Fig. 2).

Utility Control Point
Figure 2. Now you have a UCP; next is enrolling instances... (Click image to view larger version.)

At this point, we have a UCP, but no enrolled instances. When we go back to the Utility Explorer, we can see the newly created UCP in the left-hand tree navigation pane. When selected, we end up with a nice dashboard that can tell us what the health is of various instances (see Fig. 3). By default, the instance that is hosting the UCP is enrolled in that UCP. Enrolling other servers simply entails running the "Enroll instances of SQL Server with a UCP" from the Getting Started window. As with the UCP, you have to provide a domain account for the data collection process; I suggest using the same account across the board.

A view from the R2 dashboard
Figure 3. In this instance of the dashboard, you can see the various categories of information being gathered. (Click image to view larger version.)

Any data-tier applications that exist on a SQL Server when it is enrolled will automatically be monitored by the UCP. In addition, you can see that CPU, memory, database storage and disk space are all being monitored. Additionally, underutilization is reported so that administrators can make decisions around reallocating resources to other servers/processes.

While the SQL Server Utility's major limitation is the version (you have to have an environment entirely consisting of SQL Server 2008 R2 servers at the time of this writing), the Utility is extremely useful, and can help replace third-party monitoring and in-house built applications that do the same work.

About the Author

Joshua Jones is co-author of A Developer's Guide to Data Modeling for SQL Server: Covering SQL Server 2005 and 2008 (Addison-Wesley Professional, 2008) and is a principal with Denver-based Consortio Services LLC.

Featured

  • Office Mobile Apps To End as Microsoft Highlights New Office App

    Microsoft plans to end support for Windows 10 Mobile applications on Jan. 12, 2021, according to a Friday announcement.

  • Is Microsoft Finally Reinventing Office?

    Microsoft is testing out a new technology called "Fluid Framework." It could mean that Brien's dream of one Office app to rule them all might soon become reality.

  • Azure Active Directory Connect Preview Adds Support for Disconnected AD Forests

    Microsoft on Thursday announced a preview of a new "Cloud Provisioning" feature for the Azure Active Directory Connect service that promises to bring together scattered Active Directory "forests."

  • Microsoft Defender ATP Gets macOS Investigation Support

    The endpoint and detection response (EDR) feature in Microsoft Defender Advanced Threat Protection (ATP) has reached the "general availability" stage for macOS devices.

comments powered by Disqus

Office 365 Watch

Sign up for our newsletter.

Terms and Privacy Policy consent

I agree to this site's Privacy Policy.