Event Manager for SQL Server

If you're a pressed-for-time DBA managing multiple servers in multiple locations, then SQL Sentry's Event Manager for SQL Server (formerly InterCerve sqlSentry) could be the tool you need to get a handle on managing all the jobs running on your various database servers. You could think of Event Manager for SQL Server as SQL Agent on steroids, but its functionally actually goes far beyond SQL Agent. Like SQL Agent, Event Manager for SQL Server lets you create and schedule jobs to run on SQL Server. But Event Manager for SQL Server surpasses SQL Agent by offering visual schedule management, performance monitoring, cross-server job scheduling, event notification, and cross-platform support. Event Manager for SQL Server is supported on SQL Server 2005 and 2000, as well as on Oracle 8i, 9i, and 10g releases. In addition to scheduling SQL Server jobs, it can also schedule and monitor Windows tasks. Event Manager for SQL Server is a 100 percent .NET application and requires the .NET Framework 2.0.

Installing the SQL Server Enterprise Edition of Event Manager for SQL Server proved to be somewhat difficult because it hung up on a password prompt after my installation attempt failed. However, SQL Sentry support personnel quickly identified the problem and provided a fix that enabled me to successfully complete the installation. The installation process creates a database named SQLSentry20. All of my SQL Servers previously registered in SQL Server Enterprise Manager were automatically registered in the SQL Sentry Console by the SQL Sentry installation process.

Event Manager for SQL Server doesn't use agents on remote servers, so getting up and running is quick and easy. Smaller shops with a dozen or so servers can be running in just a few minutes as you add each new server to Event Manager for SQL Server's management console.

You manage Event Manager for SQL Server by using the SQL Sentry Console, which Figure 1, shows. The SQL Sentry Console supports a surprising number of tasks, and mastering it takes some effort. Fortunately, the SQL Sentry Web site provides a variety of brief training videos to help you quickly get up to speed. Event Manager for SQL Server can track SQL Agent Jobs, Alerts, DTS package execution, maintenance plans, and Reporting Services reports. In addition, Event Manager for SQL Server can monitor Windows Task Scheduler jobs.

One of SQL Sentry's most valuable features for the DBA is the all-devices global calendar view, which gives you a big picture of your enterprise job execution schedule. All events appear on the global calendar, and you can filter the view to show only failed and long-running jobs so that you can quickly see which server and jobs might need immediate attention. The SQL Sentry Navigator pane (the leftmost pane in Figure 1) lists all the servers that Event Manager for SQL Server is monitoring. Each job is represented by a rectangular box on the

visual schedule window (the rightmost pane in Figure 1). A green or red status bar in the job box signals the job's success or failure. A blue duration bar indicates how long the job ran. Clicking any of the event boxes displays a pop-up window that lists the job's run details, including error-message text for failed jobs. One limitation of the product was its inability to create new jobs and alerts on SQL Server 2005.

The SQL Server Enterprise Edition of Event Manager for SQL Server supports a feature called event chaining that lets you create a set of dependencies between events on one or more servers. Event chaining supports a basic workflow concept wherein one set of tasks can be executed when an event succeeds and a different set of tasks can be run if the event fails. Event Manager for SQL Server's SMTPbased notification system sends job completion notifications. (The notification system doesn't require the use of SQL Mail or MAPI.) The tool also supports setting job execution thresholds to automatically terminate long-running events. In addition to job scheduling, Event Manager for SQL Server can monitor for and report on a variety of predefined performance thresholds. The tool utilizes Reporting Services to deliver a set of management reports. Out of the box, you'll find seven different preconfigured reports, including Current Failures, Management Summary, Performance Counters List, Configured Actions Lists, and Notification Lists.

If you need to manage a complex job execution environment that's spread across multiple servers, or if you need to manage jobs on both the SQL Server and Oracle platforms, then Event Manager for SQL Server belongs on your shortlist. Its unique capabilities for cross-system job scheduling and monitoring enable DBAs to manage multiple servers more effectively.

Processing Formatting Codes

Now that you've created the table to store the formatting codes, you need to write the stored procedure that performs the conversion. Web Listing 1 (http://www.sqlmag.com, InstantDoc ID 94954) shows the sp_format_dt stored procedure, which accepts three parameters: a date, a dateinput format, and a desired output format. To simplify input, all three parameters are initially defined as the varchar data type. The following statement shows how you might execute the stored procedure:

exec sp_format_dt ‘05/10/2008',  ‘mm/dd/yyyy','yyyy-mm-dd'

Executed with these parameters, the stored procedure returns the date formatted as 2008-05-10T00:00:00.

Notice that the output—which contains unwanted hours, minutes, and seconds— doesn't exactly match the requested format of yyyy-mm-dd. This result is an international ISO8601 format (style code 126), which represents the closest match to the desired output. To get the result you want, you need to eliminate the time portion by replacing the CONVERT() function in the stored procedure with a custom expression.

Extending and Customizing Date/Time Formats

In the preceding example, you couldn't exactly match the requested output format. To solve the problem, you need to modify the dt_codes table to contain the values that Table 3 shows.

First, change row 7 in the dt_codes table so that the style_text column includes hours, minutes, and seconds. Then, add a new row, row 8, that contains the date-only output yyyy-mm-dd and a dt_function value of CUSTOM. The CUSTOM value specifies that the date/time conversion or formatting code that the stored procedure should call is in a custom expression. Row 8 also sets the mssql flag to False, indicating you don't use a native T-SQL function to format the output.

The dt_codes table's dt_function column is for reference only; the stored procedure actually uses the style_code column to determine the output format. In row 8, the style_code is 200, which is outside the range of style codes available to the CONVERT() function.

Now, rerun the stored procedure using the same parameters as before:

exec sp_format_dt ‘05/10/2008', ‘mm/dd/yyyy','yyyy-mm-dd'

This time, instead of invoking the CONVERT() function, the stored procedure passes the parameters to a user-defined expression, which produces the desired yyyy-mm-dd, date-only format: 2008-05-10. (Note that T-SQL's DATEFORMAT setting determines how SQL Server interprets character strings as it converts them to date values. This article's examples assume the DATEFORMAT setting is the English default, mdy. A different DATEFORMAT setting will yield different results.)

The user-defined expression simply trims the CONVERT() function's output (style code 126) to a shortened, date-only version, as callout A in Web Listing 1 shows. You can easily add your own custom expressions to the stored procedure to produce virtually any date/time format you need. Just remember that every row in the dt_codes table needs a corresponding expression in the sp_format_dt stored procedure. For custom expressions, use stylecode numbers outside the range of the built-in CONVERT codes (100-131). As you add expressions, you might want to use the SELECT CASE construct instead of a series of IF … ELSE IF statements. Either construct works, but SELECT CASE more efficiently handles multiple options of the same type. In your production implementation, also remember to add some error trapping in the stored procedure to improve fault tolerance.

A Worthwhile Investment

After you incorporate your own custom touches, you'll have a production-ready utility to add to your T-SQL toolbox. The stored procedure's input parameters are simple and more intuitive than a numeric style code. And the extensible, table-driven design lets you add new date/time formats as you need them. In a production environment, user-friendly formatting of data elements isn't just desirable; it's typically required for the UI and printed reports. Your one-time investment in writing the code to produce various output formats will pay off in increased efficiency as you continue to find uses for this little utility.