Description

Topicus wants their current auditing system revised because this
solution’s impact on the performance is big and requires much disk space. They
also want to be capable of reverting entities in a database to previous
versions, for which a complete and correct audit trail is required. Because of
this, an investigation is executed in order to find an alternative method for
auditing, which better matches Topicus’ needs and which supports reverting
entities to earlier versions.

We investigated existing audit solutions and compared them by a list of criteria,
extracted from the ISO 25010 software quality standard. We then created
prototypes of the most promising solutions. Both prototypes were put through
several tests to validate their performance. The best solution was the Service
Broker, in combination with Triggers. Triggers automatically run after an
insert, update or delete action and gather information about the action. The
Service Broker allows for reliable messaging between databases, making it
possible to store the audit information in another database, which can be
located on a different system. After this the prototypes were adapted to the
following setups; in one test the audit and audited databases were present on
the same system, and in the other these two databases were present on different
systems. This allowed us to determine if running the audit database on a
second, potentially dedicated, system would reduce the overhead of the audit
solution on the audited database by a significant amount. It was found that running
the audit database on another system lowered the performance overhead of the
Service Broker audit solution by more than 50%.

After we finished our investigation into suitable audit solutions, we investigated if, and how,
we can use an audit trail to revert entities to previous versions. We examined
several issues and discussed possible approaches for each issue. Through this
investigation it became clear that an audit trail can indeed be used to revert
entities to previous versions, as long as a complete and correct audit trail
exists. There are many different strategies which can be used to revert an
entity to a previous version. Which strategy you want to use depends on your
goals and is very much project dependent.

Finally we investigated which audit table designs exist and are compatible with Topicus’
needs. The performance of reverting entities and auditing is affected by the
used audit table design and the amount of disk space required to store the
audit trail also depends on the used design. Topicus currently uses full copies
of entities in their audit tables, thus their audit trail also contains
information on columns which aren’t modified by an action. This is redundant
information, taking up unnecessary disk space. To resolve this problem, two
designs were found which reduce the disk space consumed by the audit trail, as
these designs only store data about changed columns. The first design stores
all these changes in one column, using an XML format. The other design uses two
tables, one table contains all the metadata about the action, while the other
table contains one row for each changed column. The exact performance effects
of these two designs are still unknown.

Because of these findings we advise Topicus to use the following setup as an auditing
system:

• 
  Install
  the audit database on a separate system from the audited database.
• 
  Setup
  communication between databases based on Microsoft’s Service Broker principle
• 
  Use
  triggers to gather audit information.
• 
  Save
  the information in audit tables which make it possible to revert entities to a
  previous version.
• 
  Use
  one of the discussed audit table designs to reduce disk space usage.

Additional Resources

1. The paper