Possible ways to generate audit trail for every save of business entities

Question

We currently have a requirement to implement audit trail generation for some of our main business entities, as usual in these cases we need to persist old and new value of every field which was changed and also some header data like time stamp, entity ID and user who did the save.

I understand there are different ways to do this, such as:

1. .NET code side, using Reflection
2. SQL Server side triggers
3. SQL Server CDC (Change Data Capture)

A .NET Reflection based method could possibly take a bit longer to be written but if properly done will be smart enough to include new properties added in the future without any code change, and it can also expand and compare all children entities (like collections of other sub-entities added to our main .NET entity).

We actually have a legacy application which uses such .NET based audit trail generation, we save the whole audit trail as XML field in a SQL database and over the years that audit table is now something like 35GB of data.

I am thinking how easy it could be a trigger based solution in terms of:

• first implementation
• every change required in future modification of the entity to audit (add/change/remove a field etc...)
• how readable is the audit data? can we simply have a query which is showing old and new values for a specific saving operation?

... and how about performances?

Does anybody have experience with both approaches and can suggest or point out some pros and cons?

Answer 1

In the past, for similar requirements, I have turned to domain events and messaging. It does bring in some complexity, but can be worth it. I would suggest at least considering it.

Essentially, you would make the change a first class citizen of the model by defining an event that fires when changes are made to the business objects. These events can also be a good way to capture business intent instead of just changes at the field level. For example, a business event named OrderRefunded is usually a better audit point than OrderTotal field changed from 45.00 to 0.00.

Firing these domain events with messaging using publish/subscribe allows many subscribers to handle the event. One of these subscribers could be an Audit subscriber. This takes all the performance impact (of rebuilding indexes etc) away from the domain that is processing the original request and places the burden on the Audit subscriber. this also means that you would never get a problem where a bug in your audit code takes down the processing of business transactions.

Another benefit is about how much data that needs to be saved. This approach gives you the advantage that the Audit subscriber will only have to save the amount of data that it intends to use. Rules about how much data to save or archive are also localized to the service that is handling audit. Therefore, you can be sure that you are not storing any data without a need.

Tools I've used in the past to do this include NServiceBus and RabbitMQ. Each had its benefits and liabilities depending on the problem.

Answer 2

Requirements for audit are very specific to each every organisation. In my last project, we were required to keep an audit trail on messages sent to Real time sysems.

The volume was substantial.. some days over 50GB text files and on others average 10-15GB.

The 1st solution we used was to persist it in SQL

• Performance slow
• Queries slow
• Archiving solution equally slow
• Support for querying db records only

About 2 years back

• We moved to logged directly to text file. Open and append
• Daily basis we gzip the text file to reduce space consumed.
• Fast writes
• slow reads (read the gzipped stream and query records)

Last year

• Limit file size to 4Gb and roll over to use a new file (improved gzip performance, reduced OOM)
• gzip any files every morning
• fast write
• can execute parallel reads so better reads (read the gzipped stream and query records)

What you choose is based on your needs.