After installing the three components of SQLdm, I used the primary GUI to begin
monitoring several servers. Using either the wizard or the direct-entry methods
was quick and easy, and in each case SQLdm immediately displayed graphical evidence
of monitoring. Since SQLdm installs nothing on monitored servers, there was
no delay in displaying results.
As I exercised the product further, I discovered that I needed to enable OLE
Automation on monitored SQL Server 2005 instances in order to collect the metrics
displayed on the OS Performance tab of the Server Overview screen. Although
OLE Automation is something one normally enables by using the Surface Area Configuration
for Features tool in SQL Server 2005, Idera made enabling OLE Automation a one-click
procedure from the OS Performance tab.
Monitoring queries and stored procedures were the only other functions that
I needed to enable for each monitored server. SQLdm reports the worst performing
and the most frequently run stored procedures, triggers, single statement SQL
queries, and SQL batch queries filtered by the query types and performance thresholds
you configure.
An Idera representative told me that with full monitoring enabled, server overhead
typically doesn't exceed the 2 to 4 percent range. Although I wasn't able to
validate this claim, I saw nothing that would lead me to doubt it.
SQLdm will also collect table growth and fragmentation statistics during a
daily two-hour quiet time window that you specify on selected days of the week,
for selected databases.
The drill-down tools are a powerful way to not only see real-time metrics but
also to select (by day and date range) and view historical raw metric data (with
a default of one week) for after-the-fact analysis. SQLdm lets you export displayed
graphs or data to the Clipboard or Excel with a mouse click.
SQLdm offers a flexible alerting system that is configurable at a granular
level. For monitored metrics, you can set warning and error threshold levels
for each monitored SQL instance. Notification destinations are configurable
by SQL Server instance, metric, and severity level. SQLdm supports several notification
destinations: to a list of email addresses, Windows application event logs,
Windows messages, any ODBC data source, and an external script. For jobs, SQLdm
lets you filter alerts by SQL job category and lets you configure alerts on
abnormally ending jobs and on jobs that run a specified percentage of time longer
than the average run time.
Web Console
The Web Console takes some configuring in the beginning: You must configure
servers and server groups, and the accounts that will authenticate access to
them. One limitation is that all users are equal. Although you can create server
groups to make it easy to navigate to the servers you manage, all users have
the same access to all servers. The interface is reminiscent of Outlook 2003,
with three primary sections: Server Status, Agent Status, and Performance Analysis.
The information presented is a small subset of what's available by using the
GUI, and you are limited to viewing one server at a time—there is no
"all servers" consolidation that the console GUI includes.
The lack of a way to install multiple consoles that connect to and share a
single data collection service is a limitation that Idera plans to eliminate
in the next SQLdm release, due out later this year.
| SQL
DIAGNOSTIC MANAGER
PROS: Strong drill-down capabilities; effective console display
CONS: Weak Web console for remote access; weak multiple-console support
RATING: 4 1/2 out of 5
PRICE: $1295 per monitored SQL Server instance
RECOMMENDATION: Product of choice for shops without significant need
for multiple-console or remote access.
CONTACT: Idera • http://www.idera.com |
SPOTLIGHT ON SQL SERVER ENTERPRISE
Quest Software's Spotlight on SQL Server Enterprise is another easy-to-install,
quickly usable product for monitoring instances of SQL Server 2005 and 2000.
Michael Campbell reviewed Spotlight on SQL Server 5.7 (not the Enterprise version)
in February 2007 (InstantDoc ID 94548). I'll highlight the additional features
of the Enterprise version and expand on the two products' common features.
Architecture
Key differences between the standard and Enterprise versions of Spotlight on
SQL Server are architectural. In the standard edition, each Spotlight installation
connects directly to the monitored SQL servers. When monitoring a SQL Server
instance from several locations (i.e., from several installations of Spotlight),
each location will connect to the monitored server independently. The Enterprise
edition introduces a Diagnostic Server and an Enterprise Viewer. The Diagnostic
Server connects to each monitored server and manages data collection. Multiple
GUI consoles can connect to the Diagnostic Server, ensuring that each monitoring
location has the same view of monitored SQL Server instances while eliminating
the load that additional, redundant connections would impose on the monitored
server. The Diagnostic Server runs as a service and incorporates its own database
system for short-term data storage. When you create a connection to a SQL Server
instance, Spotlight installs a database and stored procedures on that monitored
instance that it uses for data collection. Spotlight on Windows Enterprise is
included in both versions of Spotlight on SQL Server and adds the ability to
monitor and archive Windows OS metrics. In the GUI you'll see two connection
icons for each server when Spotlight is monitoring both SQL Server and Windows
metrics.