When you ask Spotlight to monitor a SQL server, it installs a work database
with stored procedures to assist data collection on each monitored instance
and doesn't install an agent service to run on the system. The Enterprise edition
looks at lots of data on the server: Performance Monitor, system tables, Data
Management Views in SQL Server 2005 using native SQL Server, and OS instrumentation.
The Diagnostic Server that is part of Spotlight on SQL Server Enterprise is
responsible for data collection and for communication to the instances of the
console program that might be installed around the enterprise. The Diagnostic
Server keeps the data it retrieves for one week, allowing you that much time
to review detailed metrics in support of root cause analysis. Another component,
installed separately but included with the basic license, is the Spotlight Reporting
Option. In addition to reporting, the Reporting Option also lets Spotlight on
SQL Server Enterprise users archive data to a SQL Server database for long-term
trend analysis. Using the Reporting Option Configuration tool, you can easily
create the database and enable automatic data collection. As for producing reports,
Quest provides three sample reports for use with SSRS to get you started.
Installation
How many ways can you say easy? Spotlight on SQL Server Enterprise requires
Windows Server 2003, XP, or Win2K with MDAC 2.8 and SQL Server 2000 Client Tools.
Storage space on an instance of SQL Server is needed to use the Reporting Option.
The Spotlight on SQL Server Enterprise Getting Started Guide outlined everything
I needed to know. Upon starting up the console after running the installation
routine, I was prompted to either connect to an existing Diagnostic Server or
install a new one. I selected Install and the resulting wizard completed quickly.
Just as easily, I used Spotlight on SQL Server Enterprise's discovery process
to find and configure SQL servers for monitoring. For kicks, I reran the Diagnostic
Server installation wizard, directing it to install the service on a remote
server, and the process was just as clean. The documentation warns that error
log scanning can affect the performance of the monitored server, and Spotlight
on SQL Server lets you configure what to scan for in the error log.
The Reporting Option installs on the Diagnostic Server from a separate downloadable
file. Once the Reporting Option is installed, you use the Spotlight Reporting
Option Configuration tool to specify a target SQL Server instance for storage
and, if necessary, create the database. A Configuration tab lets you select
which classes of metrics will be archived and how often. A Connection tab lets
you override the global defaults of the Configuration tab for specific connections.
The Spotlight Console
The Spotlight console offers several views of the activity of your monitored
servers. The Connection view, which Figure
2 shows, displays the status of a single SQL server. The first thing you
notice is the bright, active presentation. Indicators spin and flow to represent
rates of activity and flash to draw your attention to metrics passing an alert
threshold. The Spotlight Enterprise Today perspective lists current alarm conditions
across all monitored connections.
Shops with a larger number of SQL servers will appreciate the Enterprise Viewer,
which is simply another selectable view within the console. Enterprise Viewer
presents you with a configurable high-level view of the status of your systems.
Monitored servers appear as simple labeled circles that are color coded for
the highest severity alarm currently raised on the server. You can create groups
and subgroups of servers that display as a single icon, allowing you to represent
the status of the largest enterprise on one screen. The Enterprise Viewer also
let me create and save multiple named views, each with a different subset and
grouping of servers.
Overall, I felt that the multiple entry points for the various views that Spotlight
on SQL Server Enterprise can present detracted from its overall usability. I
can see a need to hide inactive connections in a situation where you have a
large number of monitored servers, but why not provide a simple check box to
highlight open connections when all are displayed? The Enterprise Viewer could
also be integrated more tightly into the console. The Enterprise Today view,
which is really a filtered display of alarms, is a useful display, yet it's
hidden in the "Welcome" group with Web links that are essentially gratuitous
marketing, and displaying it hides your list of views or connections, adding
an extra step to switch back out of. Separating OS- and SQL-related displays
into two connections also clutters the selection column, especially for those
with lots of servers.
No matter which view you're looking at, Spotlight on SQL Server Enterprise
lets you know when a high-severity alarm is raised by flashing an iridescent
red icon that includes the SQL Server instance. When Spotlight on SQL Server
Enterprise is minimized or not the active focus of the monitor, a balloon pops
up from the Task Bar notification icon with the alarm summary. To drill down
into the alarm, simply expand the group until you get to the server raising
the alarm, then display the home page for the flashing connection. Positioning
the cursor over the server's primary status icon displays the alarm summary.
From there you can quickly drill down into the various detail status pages that
the alarm indicates to explore the root cause.
I asked Quest Software what overhead Spotlight imposes on a monitored server,
and the answer was, "It depends." I was told that when a Spotlight console is
connected to and actively monitoring a server, the overhead is in the range
of 4 to 4.5 percent. The console calls for more frequent sampling than does
the Diagnostic Server alone, which causes less than 1 percent CPU overhead.
A key benefit to Spotlight on SQL Server Enterprise is the ability to install
the console at additional locations, each connecting to the same Diagnostic
Server, so I installed a second Spotlight on SQL Server Enterprise console on
an XP system. The installation was easy, and I pointed it to the Diagnostic
Server I had set up previously.
Spotlight has a lot of flexibility as you define alarms. It allows you to define
as many as seven severity levels of alarms for monitored metrics. Alerting/alarm
reporting options are relatively limited: For each metric, email is the only
external reporting option, and you are allowed to enter a single email address
for notification. I liked Spotlight's ability to send an email message when
it detects a "server down" condition.
| SPOTLIGHT
ON SQL SERVER ENTERPRISE
PROS: Good multiple-console support; console easily configurable
whether you have few or many SQL servers
CONS: The console would benefit from some usability tweaks; reporting
is relatively weak, with few templates
RATING: 4 1/2 out of 5
PRICE: $1995 per monitored server
RECOMMENTATION: The product of choice for the largest shops.
CONTACT: Quest Software • http://www.quest.com |
Summary
Both of these products are usable and effective. Many people will like SQLdm's
price point and features, and I found it to be a bit easier to use than Spotlight
on SQL Server Enterprise. SQLdm does have its limitations, notably in the area
of remote monitoring and multiple-console support. Spotlight has the edge here
with its Console/Diagnostic Server architecture. Combined with the ability to
create very high-level summary views of the health of the largest database farm,
the largest installations may well look to Quest. It was a difficult decision,
but my Editor's Choice for this review goes to SQL diagnostic manager for its
usability features and likely appeal to a very broad audience.