SQLAudit 1.7

Track inserts, updates, and deletes

Red Matrix Technologies' SQLAudit auditing tool provides an easy-to-use trigger-based system for tracking inserts, updates, and deletes to your data. Whenever someone changes data in a table you've set up for auditing, SQLAudit logs the transaction type, the date and time of the transaction, the user who updated the record, the name of the updated table, the updated record's primary key, and the original value and the updated value for the fields you selected to audit.

SQLAudit has two components: a Script Wizard and a Log Viewer. The Script Wizard generates several server-side triggers and the tables that the product stores its audit logs in. The Log Viewer is a client application that lets you view the audit logs. SQLAudit doesn't analyze your SQL Server database log files but instead looks at its own tables, which hold the audit logs for the particular events that you've defined. This architecture has advantages and disadvantages. The primary advantage is that any application you write can read the tables that store the logs. The primary disadvantage is that the complex triggers that audit the changes can significantly slow down your data processing.

I reviewed SQLAudit 1.7, which works with SQL Server 7.0 and requires at least Service Pack 1 (SP1). I also used the program with SQL Server 2000 with no problems. (SQLAudit 1.7a, which includes a minor bug fix, is now available for download from the company's Web site.) The SQLAudit client installation procedure is simple; you need to answer just a few questions. The procedure installs the Microsoft XML Parser if you don't have Microsoft Internet Explorer (IE) 4.01 SP1 or later installed. After you complete the client installation, you need to run the SQLAudit Script Wizard to generate the T-SQL script that adds the server tables and the triggers that the Log Viewer uses. Note that SQLAudit lets you generate scripts only for tables that have primary keys. This limitation can be serious for some implementations.

The Script Wizard lets you select the tables and columns you want to audit, as Figure 1 shows. For example, you could set up SQLAudit to audit only updates, deletes, and inserts that users make to the Salary column in the Employees table. SQLAudit can audit every data type except image, binary, and varbinary. After the wizard is finished, it generates and saves to a file the script that creates the triggers for your tables. You then run the script in Query Analyzer to create the triggers and tables. Having to run the script in Query Analyzer complicates what could be a simple server installation process; Red Matrix should consider having the wizard run the script. Note that you must repeat this server-installation process for each database you want to audit.

The Script Wizard creates at least one trigger for each table you want to audit. But you can have up to three triggers per table: one for insert, one for update, and one for delete. SQLAudit also creates three tables in each database to hold the auditing and system information. One table holds text-field audit information, one table holds all other fields' information, and another table holds internal SQLAudit information. When a user updates a record in a table, the trigger executes a cursor that inserts the old and new values into the SQLAudit system table in simple XML Data Reduced (XDR) format.

A benefit of the XDR format that SQLAudit uses is that with just a bit of programming, you can create a way to roll back individual records that a user might have inadvertently deleted. Although this method might not be the most graceful way to restore an individual record, it might be easier than restoring a day's worth of transaction logs. In addition, XML is a flexible medium for transporting data through HTTP protocols, letting you use standard, firewall-friendly data streams to ship data across the Web. And variable-length data in XML format is self-defining, which lets you develop schema-driven GUIs for the data.

After running the script to create the triggers on the tables you want to audit, you use the SQLAudit Log Viewer to view the audit trails. Figure 2 shows the Log Viewer, which lets you find changes in the database by transaction type (insert, update, or delete), user, date range, or table name. A bug during my tests duplicated the users listed in the drop-down box based on the number of roles the users belonged to. Users who belonged to the db_datareader and db_datawriter roles, for example, appeared twice in the list. However, this glitch didn't keep the program from working.

To view the audit, you simply click the record you want to view, and the Log Viewer displays the record as it was before the update and after the update. The Log Viewer interface is easy to use. However, you have to perform an additional, unusual step: You have to click a Connect button to open a connection to the server before you can connect to the database you want. Most OLE DB applications connect to the database automatically when you select the database.

Note that because SQLAudit uses SQL Server triggers to capture the auditing information, the product can't audit some actions. For example, any nonlogged action, such as a bulk copy program (bcp), skips over a table's triggers, and SQLAudit can't audit it. SQLAudit also doesn't audit Data Transformation Services (DTS) Data Pumps in which fast data loading (the default) is enabled, the bulk insert task in DTS and T-SQL, or records deleted through table truncation (instead of through a delete statement).

SQLAudit 1.7 also has no mechanism for purging audit records from the system tables. The two SQLAudit audit tables can grow large over time, and you have to create a SQL Server job to clean up the tables. However, Red Matrix officials said they plan to add a table clean-up feature to the Log Viewer in a future release. Another Log Viewer feature that would add value to the product is the ability to generate complex reports that could show hot spots—rows of data that, because of frequent user updates, are often locked. You can see hot spots by generating a custom stored procedure to report on the sqlauditlog table. SQLAudit's online documentation shows you how to create customized applications and the system tables that you can call with stored procedures to generate such customized reports.

What effect do SQLAudit's triggers have on application performance? Any time a user inserts, updates, or deletes a record from any audited table, the triggers that SQLAudit installs run through complex logic to create an XML statement from the changed data. If you're performing an update, the trigger must also find which columns you're updating by issuing a series of IF... ELSE IF statements. To investigate whether these statements cause any performance problems, I ran several data-load tests. I created two generic customer tables, each with 12 columns, in the same database. I set up one table with SQLAudit auditing and one without. I then used the DTS Data Pump task to load each table. In the Data Pump task, I turned off the fast-load option so that SQLAudit could audit the actions. The table that I wasn't auditing loaded 20,000 records in 1 minute, 12 seconds. The table that I was auditing took 2 minutes, 9 seconds to load the same records.

If you've programmed auditing logic for your applications, you've probably found that auditing, in general, slows down performance. You have to use a cursor to parse each record through the auditing logic. I recommend that you be very selective about which columns and tables to audit with SQLAudit. Users probably won't notice the amount of time it takes to audit the update of a single record, but if you're auditing many operations, performance will be a problem.

How Valuable Are Your Time and Data?

SQLAudit can save you valuable time and frustration in creating complex auditing triggers and can help you safeguard your data. The product costs $1795 for the first workstation license, which can generate unlimited scripts on unlimited servers. Each additional Log Viewer license is $149 a seat. Although this price may seem hefty at first, you'd spend at least $3000 for a contractor working 40 hours at $75 per hour to create similar functionality. On a past project, I spent close to 3 weeks refining a customized auditing process for a 128-table database system—and that didn't include the GUI viewing tool.

The most frustrating problem I ran into when programming my own auditing triggers was having to recode all the necessary triggers whenever someone made a simple database model change. Even after I recompiled my triggers, I generally found that I made a typing error or two somewhere in the code. However, with SQLAudit, you can simply rerun the Script Wizard and apply the new scripts in Query Analyzer. Although you can implement similar auditing processes through SQL Profiler, SQLAudit's Log Viewer provides an easy way to search for an individual insert, update, or delete.

In addition, SQLAudit's method of storing auditing changes in tables lets you create stored procedures to roll back or recover records if needed. This storage method also gives you the flexibility to create custom reporting stored procedures to find hot spots in your data. Building such customizations into the SQLAudit Log Viewer, however, would make the product more useful.