Stewart McKie     

Easier Database Monitoring

WorkWise DAS leaves business users less reliant on IT workers for some monitoring and workflow setup

More proactive information delivery and tighter business-process integration are now management imperatives. Whether you manage operations or IT services, you face these demands — products of today’s fast-paced e-business environment. Unfortunately, many line-of-business applications do not offer sophisticated functions to satisfy either demand, which opens a niche for best-of-breed products such as WorkWise Data Agent Server (DAS).

DAS is a database-monitoring tool that responds to database events by carrying out a sequence of actions that could include sending an email message, executing a program, or printing a file. That means DAS could be used as the basis for a business alert system, an information distribution system, or as a process integration enabler, among other things. (See sidebar, “Ten Business Uses for DAS,”)

Concepts

DAS monitors data sources, database columns, or files for events such as insert, update, and delete. It uses software agents to monitor specified data source types and carry out specified actions in response to the events it traps. (See Table 1)

Name	Description
Monitoring Agents Database Monitor File Monitor	Monitors OLE DB- or ODBC-compliant database for events Monitors file, group of files, or file folder
Actioning Agents Database Update SQL Execution Scripting Run Program Printing SMTP Email MAPI Email	Updates any OLE DB- or ODBC-compliant database Creates, schedules, and executes SQL statements Schedules and runs a script (VBScript, JavaScript, Perl, and so on) Schedules and runs any executable file (supports parameter passing) Schedules a file print (RTF and text files can merge data) Schedules and sends SMTP mail (includes field embedding and attachments) Schedules and sends MAPI mail (includes field embedding and attachments)
Administrative Log	For archive, backup, and purging data from Log Database
TABLE 1 Software agents monitor data sources and execute responses.

DAS calls this sequence of steps in the event-action workflow a “process.” You can create as many processes as you want, with as many steps and substeps as you want, and run them on demand or unattended via a scheduler that operates as an NT service. WorkWise claims that if a process crashes, it will not affect other processes running or about to run.

A Typical DAS Process

I followed the DAS tutorial example to get a feel for what the product could do, and how easily. The tutorial uses two tables (source and target) in a small Microsoft Access 97 database. The same principles of process definition would apply to any database, such as Microsoft SQL Server or Oracle, for example.

You begin by starting the DAS NT service and viewing the process manager window. From there, you can maintain processes, view the log file, and start and stop services and processes. I started by creating a new process, using a popup tab folder window to describe the process and define the schedule and steps.

You can describe the process with a name, a text comment, a process category, and a flag that sets the process as offline or in use. The range of scheduling options is comprehensive: once, hourly, daily, weekly, monthly, quarterly, yearly, and continuously. You can also set some advanced options such as limiting the process execution time and determining what to do in case of a timeout or aborted process.

The bulk of setting up a DAS process, as with any workflow, is in the step definition. I opted to monitor a Microsoft Access 97 inventory table via the Jet OLE DB driver. (See Figure 1) You use a popup window to select a data source, build a query to define the data you want monitored, and select the primary key of the result set and the specific column to monitor for changes. Query building was easy using the wizard to select tables and columns, specify join conditions, add WHERE and GROUP BY clauses, and so forth. The UI provides helpful buttons to preview the result set or see the SQL the query generates. Columns selected for the query can be subject to additional logic, or new custom columns (such as computed columns) can be defined and added to the result set.

FIGURE 1 Defining a DAS process step.

My test monitored a product’s unit price column to detect price changes that it should communicate to a separate database that a Web storefront used, in order to keep the two systems synchronized. It detects changes by taking a snapshot of the data when it first runs. This initial snapshot serves as a baseline for comparing future data snapshots taken when the process is executed either manually or by the scheduler.

With the monitoring step set up, I created some additional process substeps, one responding to price changes by updating the target database table, and the other sending an email alert and attached, updated price list to specific salespeople.

You update the target table much the same way as you define the data source to monitor. Once the target criteria are defined, you can update, insert, or delete rows in the target table. (You could always create it first if you wanted, using the SQL Agent.) For each column in the target table, you can assign a value based on a source, destination, or lookup table column or insert a specific value. You can have the email message alert created and sent via the MAPI or SMTP agents and you can embed columns from the target table in the message and attach files if you wish.

My process executed the first time using the SMTP agent, and the log file entries the process created were easy to follow. (See Figure 2). Naturally, the process will fail if the email connection is not up when the mail agent attempts to start. I found defining a DAS process very straightforward and self-documenting. However, some obvious improvements would be:

FIGURE 2 Viewing the DAS log file and file detail.

TEN BUSINESS USES FOR DAS

1. Synchronizing e-commerce systems with back-office systems 2. Synchronizing CRM systems with back-office systems 3. Creating automatic audit trails of specific business activity 4. Creating and refreshing data marts used for analysis 5. Generating business alerts distributed by email 6. Electronic distribution of report files originating from legacy systems 7. Batch printing files using scheduled execution 8. Running programs using scheduled execution 9. Managing content editing workflows 10. Compiling business statistics on database, table, and column usage

•To provide a means to visualize a process and to create and edit steps from an on-screen process flowchart

•To provide some process reporting, for example to document the logic of the process from both a business and IT perspective

•To provide more Web-savvy options such as to generate an HTML or XML document as output or to trap changes in data linked to an XML tag found within a document data source located on a Web site.

Other DAS Processes

Triggering changes in a target database and sending email alerts are not DAS’s only capabilities. I also tested the file change monitoring and file printing functions. The file monitoring traps file add, change (to a wide range of file attributes), delete, and nonchange events. The file printing function triggers the printing of RTF and TXT file formats and also allows fields to merge into the document prior to printing. This process was even simpler to set up and worked the first time.

Other agents can trigger an executable file, a script file (using Windows Scripting Host), or a SQL statement — for example, to perform a bulk insert, update, or delete. There is also an agent for managing the DAS Log file.

Do You Need DAS?

Of course you can achieve much of what DAS does using database triggers and stored procedures or any of a number of scheduling products. But DAS just makes it easier by bundling everything together, eliminating the need for any real coding and providing just enough ease of use so that business users can quickly get to grips with the tool and wean themselves off the IT department. An IT department would also find DAS a very useful tool to have in its kit and one that does not demand expensively acquired and scarce SQL or database skills to yield value.

Unless you execute a DAS process continuously, DAS does not operate in real time the same way as a trigger. This could be beneficial, because it reduces the load on the database server on an event-by-event basis. It could also be a drawback, in that the generation and comparison of a scheduled data snapshot could sap resources if the database and snapshot are both large. As ever, you have to decide which approach best suits the business need and your technology infrastructure.

Yet, it would be difficult, if not impossible, to create and maintain a trigger or stored procedure, or find a scheduling product or embedded alerting function, that carried out all the steps and substeps you can include within a single DAS process. Used intelligently, DAS could perform much the same function as a workflow management engine and certainly could be used as the “glue” to manage myriad application integration tasks. WorkWise is working with application vendors, such as midtier ERP players including Best Software Inc. and SBT Accounting Systems Inc., to provide their HR and accounting applications with alerting and process integration functions.

Also available is an alerts-only version of DAS, Business Alerts, that includes just the database monitoring and email messaging capabilities. However, this functionality may already be available from within your business application, as many now include basic business alerting capabilities as part of their out-of-the-box functionality.

I found DAS easy to understand and reliable to use during my limited testing. It occupies a niche somewhere between basic alerting systems found in ERP software and much more sophisticated and expensive contextual monitoring tools such as Searchspace Ltd.’s ITM. Other products comparable to DAS, such as Categoric Alerts by Categoric for example, have a more sophisticated application program interface (API), better scalability, more output options such as short messaging service (SMS), and stronger support for Internet connectivity via XML. But they cost more and probably demand more IT resources to implement and run.

Once WorkWise adds some process visualization capabilities, better support for HTML and XML data sources and alert output, and more process reporting, DAS will be a very useful tool at its relatively low price point. It’s also a tool that many businesses could use today to add value to their midtier ERP systems and provide some much-needed integration across what can be a mess of back-office, front- office, and e-commerce applications.

Stewart McKie is an independent software analyst and technology writer who can be reached via his Web site www.cfoinfo.com.

RESOURCES Categoric: www.categoric.com Searchspace: www.searchspace.com