Recently I had the pleasure of diving into audit logging. I’m working on a government project which involves the law making process, so it is imperative that all database changes are completely traceable. That means that we need to be able to trace who made which changes and when. We’re working with latest and greatest version of SQL Server (i.e. SQL Server 2008 R2), which has a feature called SQL Audit. Reading the documentation SQL Audit seemed to do everything we need, except that it doesn’t know which application user is making the changes. This is logical since it is a web application and we’re using delegation. For this reason we were already planning to have the application send along the user id when it does an insert, update or delete, and we decided to only logically delete a record. So far so good.

When it came to testing, we quickly found that SQL Audit logs the SQL statement making the change. Sounds right doesn’t it? Well actually it isn’t. LINQ-to-SQL, LINQ-to-Entities and other O/R Mappers use parameter queries, and in fact if you edit records in the SQL Management Studio UI, the same is true. The problem is that the parameters are not part of the SQL statement being logged! So we can see which database user made what kind of change, but not which data was changed, and hence not which application user made the change either. Back to the drawing board :(.

In reviewing our options, we looked at:

• All logging in the O/R Mapper: Not an option, because we need to know what DBA’s do too.
• SQL Trace: not recommended by Redmond, and it takes a huge performance hit.
• Triggers: in transaction, taking enormous performance hit.
• C2 auditing: tracks all changes, so it gathers huge amounts of data, not easily searchable.
• Change Data Capture: really for BI purposes, deleted after three days, no indication of the user making the change.

All of the above options have some sort of problem associated with it. The conclusion is that there is no single solution, unless Microsoft fixes the SQL Audit issue (you can vote on it here: https://connect.microsoft.com/SQLServer/feedback/details/624935/sql-server-2008-database-audit-on-insert-update-and-delete-actual-sql-and-not-parameter-values).

We now do the following:

1. In the Data Access Layer add the application user that did the insert or update in an extra field on the table.
2. Only do logical deletes (i.e. add a “Deleted” flag to a table).
3. Track all changes using Change Data Capture (which uses the transaction log and therefore has less impact on performance).
4. Export CDC data to an “Audit Database” periodically (like using a data warehouse).
5. Use SQL Audit for all changes done by a database user other than the DB account used by the application.
6. Export SQL Audit logs to the Audit Database periodically.

By cross referencing SQL Audit and CDC data, we can figure out who changed what if the change was made outside the application.