Product Reviews

Know What's Happening

Keep tabs on your SQL Server jobs with SQL Sentry Event Manager.

• By Josh Jones
• 09/01/2007

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.