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
- 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
- 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
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.
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
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.
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
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
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.
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.
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
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.
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.
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.
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
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 and quick start was complete
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.
Easy installation and quick start made
configuration a snap.
sqlSentry is a fantastic tool that is one
of my "must haves".
Tel: +1 704-895-6241
Fax: +1 704-895-8771