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.

• By Joshua Jones
• 02/22/2010

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.

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).

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.

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.