For
those that may not already know, I'm a bit of a gear head and love motorsports
of all kinds. Of all the races around the world and from all the different
categories, my favorite has to be The Isle of Man TT.
The
Isle of Man TT (Tourist Trophy) is an incredible motorcycle race conducted in a
time-trial format on the closed public roads of Isle of Man (between Ireland
and Britain). The 37.75 miles of road consist of narrow, twisting streets and
lanes all flanked by stone walls and buildings and an average course speed exceeding 120 mph and top speeds breaking
200 mph. The top speed record is 206 mph and for my money, this is by far the
most exciting motorsports race there is.
So
what would happen if a person entered with a Vespa? They have a tool with the
means to let them ride and finish the course. But the problem is obvious. With
the standard Vespa topping out at 45 mph, it’s clearly not the right tool for
task at hand.
I
found myself thinking about this when I was tasked with finding a SQL Server performance
monitoring solution. Thus ensuring that I use the right tool for the task (and
preventing me from riding a Vespa in the Isle of Mann TT). Below are the tools
I decided to investigate.
- Idera SQL Diagnostic
Manager
(DM)
- Quest Spotlight on SQL
Server
- Quest Foglight for SQL
Server
- SQL Sentry Performance
Advisor for SQL Server (PA)
I
quickly eliminated Quest Spotlight based on my Quest sales rep’s
recommendation,
“Foglight for SQL Server is very
similar to Spotlight, however it is a web-based tool, offers a cross platform
single pane of glass view if you are looking to manage other servers outside of
just SQL. It also offers customized alerting, customized reports, customized
dashboards and alerting based on deviations from typical server performance.
So, if the above items are important to you, Foglight for SQL Server is the way
to go.”
Aside
from Foglight’s enhanced features, since the rep’s mentioned that Foglight is
more comparable to both Idera DM and SQL Sentry PA. I crossed off Quest
Spotlight.
I
read the Quest Foglight installation requirements and found Foglight requires
an agent be installed on each server being monitored. While this may not be a
big deal for shops with a few servers, my organization has over 100 servers and
a 100 agent solution is not a logistical footprint I want to manage. Quest
Foglight gets the same red ink as Quest Spotlight.
That left Idera DM and SQL Sentry PA.
I used four areas of focus to compare the two: Cost, Footprint, Monitoring, and
Alerting.
Cost
It is important to note that all prices came directly from the company’s website. Final prices may include days of emails, hours of phone calls and my potential bartering skills (or lack thereof).
It is important to note that all prices came directly from the company’s website. Final prices may include days of emails, hours of phone calls and my potential bartering skills (or lack thereof).
At the Idera website, I am presented with a straight purchase option per
license. While there is an option to talk with a sales rep to "ask about
volume pricing," no volume prices were listed.
Idera SQL DM
|
|||
# of Licenses
|
Price
|
Maintenance
|
Total
|
1
|
$2,049.00
|
$409.80
|
$2,458.80
|
5
|
$10,245.00
|
$2,049.00
|
$12,294.00
|
10
|
$20,490.00
|
$4,098.00
|
$24,588.00
|
From the SQL Sentry website, I found options for single licensing as well as Quick
Start 5-packs. Again I was prompted to contact a sales rep for additional
volume pricing.
SQL Sentry PA for
SQL Server
|
|||
# of Licenses
|
Price
|
Maintenance
|
Total
|
1
|
$1,495.00
|
$299.00
|
$1,794.00
|
5
|
$4,495.00
|
included
|
$4,495.00
|
10
|
$11,970.00
|
$1,495.00
|
$13,465.00
|
**Note that the cost for 10 licenses
consists of (1) Quick Start 5-pack and (5) individual licenses with (5)
maintenance fees.
With a 55% of the cost; SQL Sentry
has the substantial cost savings
Footprint
Comparing Idera DM and SQL Sentry PA
shows they both have the same basic elements: repository database, client interface;
and collection service. And neither requires an agent be installed on any
monitored servers. Both services will also require local admin and sysadmin access
to monitored servers.
But there are distinctive
differences.
When reading about the features, Idera
DM identified 3 separate services are needed for full functionality. Those
services are 1) collection service, 2) management service, and 3) predictive
service. Idera also relies on Windows/SQL Server clustering as a means of fault
tolerance.
SQL Sentry PA, on the other hand
offers full functionality with a single monitoring service. If desired,
however, it also offers the able to install multiple services introducing the
functionality of automatic load balancing for the monitored servers. This can help
reduce the impact of each service on the host server’s resources. As well as
load balancing, should any one service be stopped for any reason, the other
will automatically "pick-up" the servers it was responsible for and
continue monitoring them.
Multiple services can also prove
beneficial when working with domains without trusts; DMZ environments; or any firewalls that have limiting
access. By installing a separate service within the protected environment, the
SQL Server port (1433 by default) is the only access needed.
In spite of a similar structure, SQL
Sentry’s ability to scale while maintaining integrated fault tolerance, gives
it the advantage once again.
Monitoring
Because both of these tools monitor
much of the same performance and SQL Server information, I'm going to focus on
what they do differently.
When monitoring a server with Idera
DM, there
are two different ways performance data is collected. The first is by the
monitoring service, and the second is directly via the GUI. The monitoring
service collects performance data at 6 minute intervals all the time. While
this can be adjusted, Idera DM will offer a warning that it may negatively
impact the server’s performance. Then when the client GUI is open, it will
directly connect to the monitored server and collect performance counter data
at 10 second intervals. This may cause problems should the client not have
permissions to the monitored server for any reason. It is also important to
note that T-SQL statement collection is not enabled by default. Idera DM does,
however, offer the ability to monitor VM host stats (ESX metrics) should you be
working with VM's.
When working with SQL Sentry PA, the
monitoring service is the only point of data collection and it collects performance
counter at 10 second intervals at all times. Also, while both products show
completed queries and query related stats, SQL Sentry PA collects actively
running queries as well as the associated query plans. It is also worth noting that
this functionality is enabled by default. Finally, SQL Sentry PA monitors disk
configuration and space allocation, as well as recently introduced monitoring for
Processor Groups; NUMA configuration; and table and index size and
fragmentation.
When looking at the major
differences, there are a few key concepts that make SQL Sentry PA the winner.
If I get a call from a user/developer that a server was previously slow, I
cannot rely on data with six minute intervals to best represent the server’s
activity. Plus, if someone is reporting that the server is currently slow, I want
to see what is actively running to have the complete picture. Though monitoring
VM metrics is convenient, I'd rather have a monitoring tool specific for OS
level monitoring (like SQL Sentry PA for Windows).
Conditional alerting/responding
The alerting and responding options
for both products are again similar. Both can send email/execute T-SQL/launch
jobs and both have the ability to integrate with SNMP trapping.
So how are they different?
The biggest difference between these
products is the ability to be alerted on performance counter threshold. This is
something that Idera DM currently offers while SQL Sentry requires a license for
their Event Manager product for this functionality.
If I were able to watch the
dashboard for all servers throughout the day; I would be able to know when
specs are beginning to get out of whack - rendering the need for threshold
alerts useless. But unless you are among the lucky few to have that luxury, we
rely on alerts to warn us when a server needs attention before causing large impacts
to users.
While SQL Sentry is currently
developing an alerting solution within the PA products, the advantage for
alerting goes to Idera.
Conclusion
I
am reminded that while there are many ways to accomplish a task, I’m sure we
all come to the same conclusion that using the right tool can be the difference
between finishing the race and winning the race. Just as the high performance motorcycle will
leave a Vespa in a trail of dust and win the Isle of Mann TT, SQL Sentry’s
Performance Advisor is the clear winner for monitoring SQL Server.
Additional resources:
No comments:
Post a Comment