For Auditing Triggers or CDC

Question

Hi All,

I wanted to see if others are using SQL Server 2008 Change Data Capture and if so how do you like it? We currently use APEXSQL Audit Triggers for our auditing purposes which seems to work pretty well, but means we have to add triggers to all of our "audited" tables.

Some of the articles I have read have pointed out things like having to create a new capture table when you change a schema then drop the old one, but as far as the general maintenance is concerned it seems to be fairly straight forward.

Any comments /input is greatly appreciated.

--S

Answer 1

How busy is the system and what is the end goal for the Auditing; tracking changes in a short period of time, or auditing changes for a long time? One of the biggest problems I have with CDC is that it utilizes the log reader and SQL Agent jobs to capture changes, so a busy system can get behind to the point that it will never catch up unless you turn off CDC, leading to at worst a full transaction log, or at best delayed truncation causing the log to grow in size. If your intent is to do real auditing CDC is not built for that, its more for synchronizing changes than it is for auditing for a long term, unless you setup jobs to pull the data over into audit tables like you would with a triggered solution.

You don't mention the new Server Audit Specifications here, which would be another option to look at, but keep in mind that Server Audit Specifications are used for auditing by inclusion. This is one of the reasons that I still use the old tried and true triggers and audit tables method in my SQL Server 2008 Ent databases, its still the easiest solution until the newer features get past being v1.0 features in the product.

Answer 2

If you have a working auditing solution, I wouldn't even try it.

Another problem I noted when looking into this was that you can't add things like the user who made the change to the tables (or at least I couldn't figure out how), so your audit tables may be more flexible than CDC allows.

Finally CDC tables expire in 3 days (although I think you can change the expiration but still have to set a specific time frame.) We keep our audit records for longer than that so you still need to copy them out of the CDc tables to an audit table.