Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

A Review of sqlSentry 2.5

By Brian Knight,

Review of sqlSentry 2.5

In early December, InterCerve released sqlSentry 2.5. Amongst other things, the tool is primarily known for giving the DBA an easy way to manage his or her production jobs. It also has a very robust job alerting system. sqlSentry 2.5 is unlike any tool on the market for the SQL Server DBA and I could not find a competitor. I can not think of any way for a DBA to save more time than with a job management system like sqlSentry. Some of my favorite features in the tool are:

  • A global event management system that looks like Outlook that lets you visualize job conflicts.
  • The ability for you to kick off performance monitors before a job starts and automatically end after it completes.
  • A more robust paging system than the one that ships with SQL Server.
  • A method to provide DBAs a consolidated job view across the entire SQL Server environment. In other words, see all the jobs that failed in a single glance when you get into work in the morning.
  • A method to close "tickets" for jobs that failed with notes.
  • Hooks into DTS and Reporting Services to transparently see what packages and subscriptions have failed.
  • Queuing of jobs to execute only after the first job completes.

These are a few of the features that I'll be talking about in this review. The product has a rather large surface area now in this release so let me caveat by saying that I can only give a high level view of the product in this review.

Installation

Before you install sqlSentry, you must make some core architecture decisions. sqlSentry does not install any agent on the servers you wish to monitor but there is a monitoring service that must be installed somewhere in the environment. The service has a light footprint and does not require a lot of horsepower but as you monitor more and more servers, it will eat more of the CPU of that machine. It is recommend that you have a 2-way machine to support your first 50 monitored SQL Servers.

You'll also have to decide where you're going to store the sqlSentry database. This database stores history about the jobs that are being monitored and configuration information. Essentially, it allows every DBA to see the same console by connecting to this database. This database needs to be on the side of the firewall that allows your DBAs to connect to it as well as the sqlSentry service that we just mentioned. From there, you can install as many client machines pointing to that repository as you'd like. You're licensed on how many SQL Servers you monitor, not how many DBAs use the tool.

The installation of the tool was very easy. sqlSentry requires the .Net Framework 2.0 and it's included in the main setup. This makes the setup file large. The installation without the framework (assumes you already have it installed) is about 10 MB. You are first prompted whether you'd like to install the client, server or both. The client is just the admin console and you'll want to install this on each of your DBAs machines and anyone in your operations group. The server is the service that will be monitoring your SQL Server instances and is only run once per environment typically. If you selected to install the server, you will be prompted for a service account. The screen states the account that starts sqlSentry needs to have sysadmin rights to each instance that you wish to monitor and local Windows admin rights, which could be an issue for some environments. This is because the tool accesses some system tables that require elevated permissions and uses performance counters that require local admin rights. For more information on this, see http://www.sqlsentry.net/forum/Default.asp?sub=show&action=posts&fid=9&tid=145. This is not an issue on the client installations though. You'll also be prompted for where you want to install the database.

Once you open the application for the first time, you're prompted to walk through a series of Quick Start screens. I loved this new feature in 2.x because it cut down the configuration time by a factor of 5. Literally in minutes, you have your first operator configured with mail and your first SQL Server alerts. This was a huge usability plus.

Using sqlSentry

The first time you click on a SQL Server to monitor, you will be prompted for sqlSentry to begin "watching" the objects on that instance. By selecting yes, you have decremented your licenses by one. This will load a few jobs onto that instance and will begin to load the sqlSentry repository with historical data that you can later view or slice and dice in some of the sqlSentry canned list views. After you begin watching the jobs, it may take some time for it to gather the information it needs to fully show you the screens. It does this asynchronously so you won't have to wait but it took me in some cases 5 or 10 minutes.

Primarily, I imagine people use sqlSentry as a job report and monitoring tool. The tool quickly gives you a view of your jobs that resembles Outlook to help you visually see how jobs may be conflicting. If you see a conflict, you simply must drag the job like an Outlook calendar entry to the next available spot and the SQL Server Agent schedule is updated. You can click on one of the entries to see a history of why the job failed. In the below screenshot of the tool, you can see a small example of how jobs are color coded. Jobs with a green bar to the side have executed successfully. Items in brown have conflicts and jobs with a red bar to the side have failed.

If you were to left-click on any of the jobs that have run you would see the detailed history of that job instance. If the job was a job that called a DTS package, it would show you each DTS step that executed. These hooks into DTS saved me a considerable amount of time. Typically, this is the second step in troubleshooting process of a DTS package and sqlSentry cut that step out altogether with this single view of problems.

The sqlSentry interface looks and feels like Outlook, which is a plus for usability. The only thing is that to achieve this look eats up a lot of real estate but I see no way around that. To efficiently use the tool, I'd recommend having your resolution set to something greater than 800x600. Another usability item worth mentioning is that I noticed a huge slowdown on my workstation if I accidentally leave the client tool running all night. This was in the 2.0 release of the product and never experienced in 2.5. Frankly, I just became used to closing the admin console down at night and never noticed an issue again. When opening Task Manager during this time, it looked to be RAM related the few times I experienced it. My machine at the time though had a whopping 512 MB of RAM. There was never a slowdown in the actual monitoring service and to resolve the issue, I simply closed and reopened the administration console.

On the left pan you see a list of your SQL Server devices, contacts (users and groups that can receive alerts), custom views and configuration sections. On the right you can see a MDI interface for whatever is in scope. If you're viewing jobs, then you'll see the calendar on the right. If you're configuring an alert, you'll see that configuration dialog box there. The benefit of the tabbed MDI type environment as you may know is screens stack up and you can easily flip from one to another without having to close each screen. The only bad thing about this in the default configuration is as you go from screen to screen, you leave a large trail behind unless you close each of the tabs frequently. I only wish they had a feature like Visual Studio did where I could select Close All But this Window. You can configure the interface to only open new tabs when you double-click on a new node though in the navigation tree on the left.

Vendor Update: The close all request was implemented in the latest build released on 1/9/06.


Tile sqlSentry Screen

The Devices section shows you all the physical machines that you're monitoring (Windows Task Scheduler instances and SQL Server instances consolidated). You can monitor Windows Scheduler and SQL Server jobs. The SQL Server node in the tree shows you all the SQL Servers that are in your Enterprise Manager installation. At a quick glance in the tree, any server that has a job that has failed will be in red. As you drill down, any node that has an issue will be red. So a DBA arriving first thing in the morning could pull open the tool and quickly see that he has a job that failed on ServerB.

Drilling into an instance, you can see maintenance plans, alerts, Reporting Services reports, DTS packages, and of course jobs. With maintenance plans, reports and DTS packages, you can see when they are scheduled to run. For example, you can click on the Reporting Services node to see when the subscriptions are executing. You can even tie performance counters to an individual job at this level to see how much CPU (or whatever counter you'd like) the server is consuming for your ETL process. Again, I'm only scratching the surface here!

For jobs, you can do the same. You can tie a performance counter to kick off each time the job starts and automatically stop after it completes. I used this to find serious contention on one of my network drives at a certain time. We'd been debugging the problem for some time and within the next night, sqlSentry found the contention was in the network card by using this. Much to the dismay of the sysadmin who swore it was a network issue.

What's especially nice about the event view is you can filter this view to only show me the jobs that have failed or only jobs that have a certain text in the title. This works at any level so I can click All SQL Servers and I will see any job that failed across all SQL Servers that I'm monitoring. If you don't like the Outlook style view you can also see the jobs in a list view. After a while, I preferred to see the jobs in this manner personally.

Alerting is especially strong in sqlSentry. It can send out messages using SMTP to a list of users, groups, or a single person. The alert text can be very verbose if you'd like it to be (you can exclude user notes if you’d like less-verbose alerts). Unlike your normal job failure message in SQL Server that is very obscure, this can send you the step information of DTS step information about the job so you can debug it on your Blackberry before dialing in to fix it. After you fix the job, you can right-click on the job to add notes on the job to describe what you did to fix it. The next time an operator is looking at the job, your notes show up. Also, you can choose to have the notes emailed out during alerts so if you an environment where you have a rotating pager, the person who's on call that week will see how you fixed the issue last week on his Blackberry or email.

Alerting works at many levels. Of course you would assume that you would be alerts on jobs failing or succeeding but you can also be alerted if the job "hangs". A job hanging is a condition that you setup that states that if it takes more than XX% above the average run time, let me know. You can also be alerted if SQL Server Agent, SQL Server, or Task Scheduler is stopped or if someone changes a configuration setting. These are just a few of the conditions that you can set.

Shared views is a huge feature in 2.x of sqlSentry. This feature allows you to create a view of certain types of jobs across a defined list of servers or all of the servers. This can be shared out with anyone else that's using the sqlSentry client application. The way I used this was to create a shared schedule for each of the high profile products that I supported. I then updated each job for the product to have a category of "Product Name Support" and filtered the shared view to only see these jobs. It can also be used to see all failed jobs across all servers that meet a given optional criteria. In seconds you see all the important jobs that may have failed and you're off to the races to save your job.

The last thing to note in this huge feature list are the concepts of job chaining and queuing. These features are not ones that exist in the native SQL Server toolset. Chaining is used for cases where you need to define explicit dependency relationships between multiple jobs or tasks using conditional logic like success, failure, or completed. Queing works with jobs running on the same server, and enables you to have one job push other scheduled jobs out while running to avoid resource contention. For example, if you have a reindex job scheduled with a backup job scheduled an hour later, you can prevent them from ever running at the same time if the reindex happens to run long by setting it to queue other jobs for a specified period of time. With this configuration, the backup job won't start until the reindex completes or its "queue until" time expires, whichever comes first. This will eliminate any resource contention and ensure both jobs run as quickly as possible. Lastly, you can "pin" specific jobs to the schedule which states that they can't be a victim of being queued.

Chaining works a bit differently where you can configure a job on InstanceA to execute as soon as a different job on InstanceB is complete. This is a very simple scenario, as you can have multiple chain levels and multiple jobs per level, all running on different servers. You can also chain SQL Server Agent jobs together with Windows tasks.

SQL Server 2005 notes: sqlSentry is not 100% 2005 compatible. For example, it does not work with SSIS packages and maintenance plans like it does in SQL Server 2000. It does work with DTS packages and maintenance plans that you ported over from 2000 however. A release of sqlSentry that supports this will be delivered in Q1 of 2006. sqlSentry now also has 64 bit support which gives you the ability to monitor 64 bit servers or install the service on a 64 bit server.

Conclusions

sqlSentry 2.5 is the single most important tool I carry with me in my DBA toolbox. I've become so spoiled by it that I can't imagine going back to the way it was before. When I was a production DBA, I easily saved an hour a day across 12 servers by using the tool and I would highly recommend it.

Ratings

I will rate each of the following using a scale from 1 to 5. 5 being the best and 1 being the worst. Comments are in the last column.

Ease of Use

4.5

The quick start goes a long way to fixing the usability issues when you were configuring the product initially in past releases. Still a small learning curve with amount of features.

Feature Set

5

I sure had a hard time thinking of new features to add. The SQL Server 2005 features were not complete yet but they're coming. For example, you can't see SSIS packages yet but you can see their legacy counterparts when connecting to a 2005 server.

Value

5

If you add up a DBA's salary, this can easily save hours of time a day for that DBA. The average contract DBA costs $45 an hour in Jacksonville. I found the tool saving about an hour a day in a 10 server environment adding up to $16,425 in contractor money saved. This was time that contactor could spend working on development. Additionally the quality of the alerts were far superior and collaboration became easier. I think it saved far more money than that in an environment where the DBA was on rotation weekly and needed to quickly determine how to fix job.

Technical Support

5

In anonymous tech support requests, I received immediate responses in under an hour in email support. When I did receive an error that I choose to report to sqlSentry, I received a personalized email from the tech support asking if they could assist.

Lack of Bugs

4

I only encountered one "soft error" the entire review. During this error, a dialog box requested that the program could send the error to the vendor and the program did not crash afterwards.

Documentation

5

Documentation and quick start was complete and easy.

Performance

4

The nature of aggregating dozens of servers together is not a speedy one. All screens came back in under 5 seconds. Also there seemed to be some type of RAM leak that was only detected if the client was running all night in 2.0 that was not experienced in 2.5. The performance of the server was fine. As a general guideline you can minimally support 50 monitored servers on one sqlSentry server.

Installation

5

Easy installation and quick start made configuration a snap.

Overall

4.7

sqlSentry is a fantastic tool that is one of my "must haves".

 

Vendor Information

sqlSentry

Web: www.sqlSentry.net
Email: sales@sqlSentry.net

Tel: +1 704-895-6241
Fax: +1 704-895-8771

 

Total article views: 6633 | Views in the last 30 days: 3
 
Related Articles
ARTICLE

New 64-bit sqlSentry Enhances Performance and Scalability

sqlSentry, a leading developer of management solutions for Microsoft SQL Server, today announced tha...

ARTICLE

Monitoring Longest Running Transaction using SQL Server Agent Alerts

Step by Step guide to setup SQL Server Agent Alert for the proactive monitoring of Monitoring of Lon...

FORUM

SQLSentry on Clustered Servers

Connection issue on clustered servers

ARTICLE

Brooke Philpott of sqlSentry

The guys that build software rarely get exposure of credit. Maybe that's why so many of them turn to...

FORUM

Alert to monitor DBCC CheckDB

Configure an Alert to monitor when DBCC CheckDb finds an error

Tags
product reviews    
reviews    
 
Contribute

Join the most active online SQL Server Community

SQL knowledge, delivered daily, free:

Email address:  

You make SSC a better place

As a member of SQLServerCentral, you get free access to loads of fresh content: thousands of articles and SQL scripts, a library of free eBooks, a weekly database news roundup, a great Q & A platform… And it’s our huge, buzzing community of SQL Server Professionals that makes it such a success.

Join us!

Steve Jones
Editor, SQLServerCentral.com

Already a member? Jump in:

Email address:   Password:   Remember me: Forgotten your password?
Steve Jones