Know What's Happening
Keep tabs on your SQL Server jobs with SQL Sentry Event Manager.
Sentry Event Manager 3.1
1: Virtually inoperable or nonexistent
5: Average, performs adequately
We've all been in situations where we spend a great deal of time navigating
the complicated Web of inter-server SQL Agent jobs to find resource contention
and scheduling problems. When you add the layer of complexity that many SQL
Agent jobs simply run DTS or SSIS packages, the problem becomes even greater.
As if that weren't enough, adding Windows Scheduled Tasks further complicates
the troubleshooting process, because most DBAs have limited visibility into
the scheduling and history of these tasks. While troubleshooting the actual
code for each job can help uncover performance problems, it's often an iterative
development process. Many times the problem lies in the order of the work being
done. The timing of jobs on different servers is very delicate.
Track Those Events
One of the few third-party devices available to help with this task is SQL Sentry
Event Manager from InterCerve Inc. Originally developed as an in-house tool
to support InterCerve's hosting centers, the company has been using it since
2002. SQL Sentry Event Manager supports graphical management of SQL Agent jobs
across an entire environment, providing drag and drop scheduling, job chaining,
alerting and reporting.
SQL Sentry provides an Outlook-style calendar that displays all jobs on any
given server. This helps you quickly discover problem areas and resolve them
from within the tool itself. You can click and drag jobs to different time slots
in SQL Sentry to reschedule the job on the destination SQL Server.
[Click on image for larger view.]
|Figure 1. The
SQL Sentry Event Manager calendar lets you view and investigate all jobs
scheduled, including dependencies between jobs.
You can also define multiple views to arrange and display servers and jobs.
For example, you can create different views to display servers and their jobs
by environment, by data center or by application supported. Using these views
can help you avoid loading too many servers and jobs in the generic calendar
display, which can become too cumbersome to effectively interpret.
You can also set precedence constraints, known as "Event Chains,"
between jobs on a given server, as well as between jobs on different servers.
You can configure these constraints to control the work order of multiple jobs
and account for job failures by canceling the downstream jobs. You can even
run different jobs based on the outcome of earlier jobs.
There are basic performance thresholds you can configure on jobs themselves.
For example, if your backup job runs more than 25 percent longer than its average
run time, you can have an alert sent to the DBA on duty to troubleshoot the
problem. You can also monitor and configure the DTS packages, Maintenance Plans
and SQL Agent Alerts in the same manner.
To Serve and To Protect
On top of its visual scheduling and job management capabilities, SQL Sentry
provides notifications for a number of server conditions. SQL Sentry can monitor
a long list of server performance thresholds for any given object (such as SQL
jobs or Task Scheduler tasks). You can configure most of these to fit any environment.
This can help you determine the resource impact any job is having on a server,
which greatly reduces troubleshooting time.
Through SQL Sentry, you can send alerts for a variety of server-wide events
via SMTP mail. In fact, you can eliminate reliance on SQL Server mail or Database
Mail for most server level related alerts using SQL Sentry's built in mail functionality
for all SQL Agent alerts. You can even view SQL Server Agent event logs from
within SQL Sentry. This will save you the hassle of using multiple tools to
correlate alerts, job failures and log entries.
SQL Sentry installs a central service on your chosen management server. This
service will need credentials that can log it on to that machine as a service,
as well as "sysadmin" rights on all monitored servers. There's also
a repository database that the tool will install to the SQL Server of your choice
(more than likely to an instance on the management server).
For monitoring tasks, SQL Sentry deploys in an agent-less method, but it does
install a number of objects in the "msdb" database of each monitored
server. During testing, there was no additional performance overhead or ill
effects due to these objects. As with any product that installs anything on
your SQL Servers, be sure to test them thoroughly before full deployment.
There are currently Standard and Enterprise Editions of SQL Sentry. The Enterprise
Edition provides more of the cross-server, large enterprise-type functionality,
as well as enhanced reporting. One notable feature available only in the Enterprise
Edition is the "Shared Resource Calendar." This view lets you see
enterprise-wide status reports, like a list of all backup jobs that write to
the same shared storage device, such as a SAN or NAS.
Joshua Jones, MCTS: SQL Server 2005, MCITP: Database Administrator, is co-owner and operating systems/database technologies consultant with Consortio Services in Colorado Springs, Colo. He provides training, administration, analysis and design support for SQL Server 2000 and 2005, in addition to training and support for all flavors of Windows.