You wouldn't ride a Vespa in the Isle of Man TT

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

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:

http://www.mssqltips.com/sqlservertip/1067/sql-server-performance-monitoring-tools/

http://www.idera.com/Support/Product-Documentation/

http://www.sqlsentry.net/help.asp

https://support.quest.com/searchdocumentation.aspx?pr=268444554&pv=4294952045

Change of Scenery

As I am going through a job change, I found myself thinking that this would be as good a time as any to begin blogging about my adventures (considering it might be something that others will relate to).

Let me start by rewinding to tell you a bit about myself. I started working at 14 and had a variety of jobs while trying to find my career path. My jobs ranged from grocery stores to movie rental stores; from ski shops to copy shops; from home loan processing to auto body repair estimates; all before ending up on a DBA team for a preventative health care company. This is where I was initially introduced to, and hooked on, databases and SQL Server. As you may have already guessed, I wasn't one of those people that knew what they wanted to do in high school, or college for that matter.

Once I got involved with SQL Server, I knew that I finally found something challenging and fun. I found every day brought a slightly different puzzle to solve and I loved it. It was then that I was able to turn all of this random job history and work experience into a successful Software Support Engineer position with SQL Sentry. (for those that don't know, SQL Sentry is the best SQL Server monitoring tool for DBA's to have - both in my biased opinion, as well as the un-biased opinion of most of the SQL Server community).

Fast forward three years to the present where I begin a new chapter in my career as a production DBA for a large financial company. I am no longer looking through the window to help DBA customers. Instead, I am the DBA.

While there were a lot of thoughts and emotions that came with this transition, excitement was by far the most prevalent. This excitement is also the reason that I have decided to start my blog. To share my experiences with the community; to show how reality is different than my expectations; and to show that change doesn’t have to be a bad thing.

I hope you enjoy the ride Into the mind of a DBA.