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.

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.

Vendor Information

sqlSentry

Web: www.sqlSentry.net

Email: sales@sqlSentry.net

Tel: +1 704-895-6241

Fax: +1 704-895-8771