Suggestions for Implementing Audit Tables in SQL Server

Suggestions for implementing audit tables in SQL Server?

How much writing vs. reading of this table(s) do you expect?

I've used a single audit table, with columns for Table, Column, OldValue, NewValue, User, and ChangeDateTime - generic enough to work with any other changes in the DB, and while a LOT of data got written to that table, reports on that data were sparse enough that they could be run at low-use periods of the day.

Added:
If the amount of data vs. reporting is a concern, the audit table could be replicated to a read-only database server, allowing you to run reports whenever necessary without bogging down the master server from doing their work.

Audit tables: Each field for table or one table

Which is a better design, one table that keep the history of
transactions or one field for each table? (Pro and cons)

Rather than focus on the 2 choices here's a answer on the 4 approaches I've worked with over the years. Each with its pros and cons.

1. Just three fields

Just add three fields (last action, time_stamp, update_user) to every table and call it a day.

Pros Super easy. Performs well

Cons You can't report on data you don't have, so this structure tells you almost nothing (except for deletes)

2. Clone table

Each table has a copy plus the three audit fields and every time a user changes a record the audit table gets inserted into.

Pros Performs pretty well. Easy to create a row by row history that the user can dig through.

Cons

• Every change to the base table needs a corresponding change to the audit table.
• If the users don't want a row by row history to dig through and they want a report of what exactly changed it can get nasty in a hury. See the answers to How can I write a query to extract individual changes from snapshots of data?

3. History Table only

There's no base table only a history table.
This is basically the same as Clone Table except now you have to always get the current record.

Pros Pros of 2 but everything's an insert. Less maintenance then the option 2.

Cons You'll end up losing the maintenance gain because you'll end up maintaining views or you'll be sprinkling get-the-current-record logic all over the place

4. Generic audit table

This table has four columns ( Table*, Column_name, old_value, new_value ) and the three audit fields.

Pros Easy to set up and maintain.

Cons

• Its unintuitive but it takes up a lot of space because your old_value and new_value fields have to be nvarchar(max) or equivalent so it can accept anything that's in your base table.

• Performs poorly on reads and writes.

• Its a pain to set up a row by row history report

• If there's any kind of workflow in the records audit reporting can become non-trivial. For example you get a requirement that users only want to see changes that occur after the status on the records becomes 'approved'. That's hard even in options 2 and 3 but becomes a disaster in the Generic audit approach.

Summary

I prefer #2 the Clone table approach as it seems to work best for me. I've had issues with #1 being insufficient and #4 can be a serious perf nightmare that requires a lot of work to undo.

Best way to implement an audit trail in SQL Server?

There are many ways to do that; it depends which version of SQL Server you are using.

Here are few

• Audit trail with shadow table and trigger Here is the link

• Also you can consider to use SQL Server 2008 Audit feature Here is the link

Suggestions on Adding Transaction Auditing In a SQL Server 2008 Database For a Financial System

Take a look at Change Data Capture if you are running Enterprise edition. It provides the DML audit trail you're looking for without the overhead associated with triggers or custom user/timestamp logging.

Global Audit Table in SQL Server

Logging DML with database level trigger on big data and bulk insert/update/delete have a performance issue, so there is three other option :

1. SQL server Change Tracking : more info
2. Build-in data changes function (CDC) : more info
3. Database level Audit Log : more info

And i strongly preferred option no 1.

Simple sql data audit log tables

If we are talking about only one table then yes. If there are several tables then maybe. If there are a lot of tables you need to audit then no.

Reason is that the more duplicate tables you have to maintain the more complex it becomes. Also, reporting from many tables may turn out to be a difficult task.

Here are couple other ideas:

Two table design for storage: Idea is to keep details about transaction in one table (user, host machine, transaction time, table name, database name, etc) and data changes in second table (old values, keys and such)

Third party tools: There are several tools that can provide auditing at a different level . ApexSQL Audit is a trigger based auditing tool, ApexSQL Log is more advanced and can audit permission and schema changes, Idera’s Compliance manager is the most advanced and can even capture select statements.

Change Data Capture: This is built into SQL Server 2008+ but only in enterprise version.

---

Related Topics