How to add self data change auditing capabilities to commercial, shrink-wrapped software with a SQL Server backend (4213395)

There are thousands of distributed. commercial software applications aka shrink-wrapped, from small shareware apps to large corporations, that use a SQL Server backend. These systems, although distributed, could often benefit from and sometimes require data change auditing at the client location.

Although ApexSQL sells a successful, enterprise auditing tool for SQL Server, ApexSQL Audit, this tool is often not appropriate for distributed, commercial software.

Challenges to auditing distributed software

• Configuration auditing at the SQL Server database level, at the client site, can be impractical due to cost, for example, the auditing solution may cost more than the software product itself or it might require a more expensive edition of SQL Server, the cost of which might be impractical
• Even if employed, the auditing system wouldn’t be tightly coupled with the data changes of the particular application out-of-the-box. This would mean it may audit more than what would be required to simply track the custom application and to narrow the focus to track application-specific operations and events, might require a significant amount of client-site customization. One could say this would be like shooting mosquitoes with a shotgun
• Finally, it is difficult to integrate dedicated auditing systems and technology, directly into the application, to address requirements for an auditing dashboard or reports that can be launched directly from the commercial software. Reporting, alerts etc. are mostly resident in the auditing application itself vs distributed as an API, which would be required to embed the functionality in a commercial application

Are DML triggers the solution?

This is where DML triggers can really come in handy. The advantages of DML triggers in this particular context include:

• Triggers can be built into the schema of the SQL Server database that ships with the product. And updates can easily be propagated to customers via software updates
• Triggers can be highly customized and coupled with the database architecture and business logic of the application, akin to an API, as triggers themselves are T-SQL code. This allows for a highly tailored auditing plan, tightly coupled with the design and business logic of the client software
• Triggers can be pre-configured with the commercial software, prior to shipping, include which tables to audit. This allows for a turn-key, out of the box, self-auditing application, as soon as the end user begins using it, with no customization required
• Archiving tables, that store auditing data, and the procedures and views, that query them, can be integrated directly into the client application to allow for dashboards, reports, alerts as well as direct integration into the business logic of the software. For example, if a manager permission is changed, an alert can be sent to the system administrator and require a manual override all through the client application, that is tightly integrated into the auditing layer
• Triggers can be added royalty-free, as they are part of SQL Server itself
• Triggers are available in all editions of SQL Server, including SQL Server express which is often used as a back-end to commercial software packages
• Triggers can easily be disabled and/or deleted with a few lines of T-SQL
• Triggers allow for some interesting and helpful valued added features like Lookups and Watches

The trouble with triggers

Although performance is often cited as a concern with triggers, unless triggers are put on tables that experience high throughput, like batch loads, triggers have a minimal or non-existent performance impact. The time to fire an individual trigger is normally measured in milliseconds. In a well-designed audit plan, triggers can actually offer performance and other advantages to alternatives like profiling. The biggest concern with triggers is generally initial setup and maintenance.

• Triggers have to be created initially, which can be time-consuming
• If created manually, there is a high potential for inconsistencies and bugs, in the created triggers, caused by human error
• Each time the database schema changes, triggers can be broken, for example, if an audited column is changed or removed
• If changes include new columns to audited tables, auditing can continue to work but not for the new columns that were added. Over-time, this would create “holes” in your auditing plan, where changes weren’t tracked. Worse, you wouldn’t necessarily know such holes existed until you went to review audit data for that column and noticed it was missing
• If the design of auditing triggers is changed, all existing triggers will need to be updated to ensure consistency or auditing behavior may vary/diverge between tables

Automation to the rescue

ApexSQL Trigger is a 3^rd party tool designed to automate the process of creating a trigger based auditing system. Once implemented, ApexSQL Trigger can allow for rapid refactoring of a trigger based auditing plan due to schema or trigger template changes.

At a high level, ApexSQL Trigger is a trigger-factory, that can automatically produce thousands of triggers, defect free, based on a trigger template and a Table schema.

How it works

ApexSQL Trigger is a software application that reads a table schema and processes against a template, to create a trigger for that table. To change the actual design and implementation of the trigger, simply change the template and re-generate.

A single trigger creation process looks as follows:

This transaction is simply repeated, for each table selected for auditing in a particular database.

Trigger template

A trigger template is a simple XML file that can be edited and customized within ApexSQL Trigger itself via built in editor that includes auto-complete and syntax highlighting:

A trigger template includes blocks of static code (generally T-SQL - mixed with actual programmatic code that will produce different results based on the nature of the particular table being processed at the time. Most users simply use the default template that ships with ApexSQL Trigger, but it can be fully customized and even replaced with a template designed from scratch:

By implementing a template based paradigm, ApexSQL Trigger can both rapidly produce a large number of templates quickly, but it also allows the templates to be easily redesigned and customized and then easily re-generated.

Audit plan

Once the trigger template has been approved for use, the focus switches to the user interface, where an audit plan can be created. This plan includes:

• Tables selected for auditing
• Columns, for those tables, selected for auditing
• Operations including Insert, Update and/or Delete for those tables

Once configured, the plan can be saved as a project. ApexSQL Trigger can even be configured to detect new tables or columns added since the last time the project was saved, and notify the user so that those additions can be considered for auditing:

Underlying architecture

This is great but where is the audit data written? On the first install of ApexSQL Trigger, the software will prompt for the addition of a variety of objects e.g. tables, procedures that form the architecture that allows auditing to work. This includes tables where the auditing data is written and procedures and views that supply the reports used to visualize the data.

When you ship your self-auditing, commercial application, all of these objects will need to be included as well. This way the triggers will be able to add data to the repository, and end users will be able to view and report on the audited data.

Reporting

Logging data changes is a key component but essentially useless without the ability to quickly and easily search and report on that data. The open architecture of ApexSQL Trigger allows for distributing reporting components along with the auditing trigger and repository objects that can function just like the built-in reports in ApexSQL Trigger itself:

By including ApexSQL Trigger stored procedures and views in the database backend of your commercial software, reporting can be easily integrated with the commercial tool. For example, a form that accepts user input and presents returned data can be added to the client application, and interface directly with the stored procedures included in ApexSQL Trigger:

Continuous integration and delivery

In addition to creating the audit plan initially, via the user interface, adding auditing triggers can be included as part of your Continuous integration and delivery process. This makes updating builds to add an auditing layer an easily automated process.

ApexSQL Trigger has a rich command line interface aka CLI that allows automation with PowerShell for example. Auditing is included as a step in the ApexSQL CI/CD toolkit and open source PowerShell scripts to automate this step can be downloaded from our GitHub repository

Putting it all together

Please see the following content, including articles on using ApexSQL Trigger, configuring audit plans and even automating the process via the CLI.

Key content

• How to create an auditing plan
• Using the template editor in ApexSQL Trigger
• How to automate adding SQL auditing triggers to a new, or changed database
• How to automate trigger-based SQL Server auditing
• Auditing triggers in SQL Server database

All articles

• ApexSQL Trigger KB
• ApexSQL Trigger solutions