Auditing SQL Server data changes

Question

I'm looking at changing our Auditing process for our SQL Server 2005 databases and I came across Change Data Capture in SQL Server 2008.

This looks like a good idea, and I'm tempted to try it, but before I do has anyone used it in a commercial environment and what are your thoughts?

I noticed when I was reading about CDC in the MS help, it said that audit data would usually be kept for a couple of days. That's not possible here, I'd like to keep the data indefinitely, does anyone know of problems with this approach?

If this isn't a good solution for reasons I'm unaware of, have you any other solutions for auditing of data changes. I'm keen to use a system that can be implemented across the board on any tables I desire.

I'm after the basic: "Before, After, Who By, When" information for any changes.

Answer 1

Quite late but hopefully it will be useful for other readers…

Below are several different techniques for auditing with its pros and cons. There is no “right” solution that would fit all. It depends on the requirements and the system being audited.

Triggers

• Advantages: relatively easy to implement, a lot of flexibility on what is audited and how is audit data stored because you have full control
• Disadvantages: It gets messy when you have a lot of tables and even more triggers. Maintenance can get heavy unless there is some third party tool to help. Also, depending on the database it can cause a performance impact.

CDC

• Advantages: Very easy to implement, natively supported
• Disadvantages: Only available in enterprise edition, not very robust – if you change the schema your data will be lost. I wouldn’t recommend this for keeping a long term audit trail

Traces

• Advantages: a lot of flexibility on what is being audited. Even select statements can be audited.
• Disadvantages: You would need to create a separate application in order to parse trace files and gather useful information from these.

Reading transaction log

• Advantages: all you need to do is to put the database in full recovery mode and all info will be stored in transaction log
• Disadvantages: You need a third party log reader in order to read this effectively

I’ve worked with several auditing tools from ApexSQL but there are also good tools from Idera (compliance manager) and Krell software (omni audit)

ApexSQL Audit – Trigger based auditing tool. Generated and manages auditing triggers

ApexSQL Log – Allows auditing by reading transaction log