Sql Server 2008 Change Data Capture Vs Triggers in Audit Trail

SQL Server 2008 change data capture vs triggers in audit trail

I use CDC in my WPF app. Works very well but I've discovered three problems:

• You need to back-up change tables quite often (I use MERGE statement to add records to historical tables). Because record stays in change table only for about 2-3 days as I found out. Don't forget to backup cdc.lsn_time_mapping table.
• You can't truncate tables with CDC enabled.
• There is a problem with disabling cdc and reenabling (should be solved in new service pack as MS said). I've got this problem only once so it's not so annoying.
  
  

  http://blogs.technet.com/b/claudia_silva/archive/2010/06/04/cdc-cdc-hangs-when-i-try-to-disable-it.aspx

Anyway, CDC is very useful mechanism which helps me track all changes on database.

Change Data Capture or Change Tracking - Same as Traditional Audit Trail Table?

I think you still need audit tables in your circumstances. Looking in BOL it appears that a cleanup job is automatically created and ascheduled that runs every day at 2 am. From BOL:

The cleanup job runs daily at 2 A.M.
It retains change table entries for
4320 minutes or 3 days, removing a
maximum of 5000 entries with a single
delete statement.

That sounds like it definetely doesn't do what you want. I can't think that would do what anyone who audits tables woudl want. It also appears that it would be difficult if not impossible to add any fields not in the data table to the audit log other than it's own five default fields (I couldn't find what they were.) It also appears that the data would not be very useful to query or to use to rollback a specific bad change. OR maybe I just don;t understand the process because BOL is pretty poorly written on this subject, it certainly didn't answer any of the concerns I would have in replacing my auditing with this apparently poorly thought out process.

For Auditing Triggers or CDC

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.

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

Auditing data changes in SQL Server 2008

A correctly written trigger should be fast enough.

You could also look at Change Data Capture

• Auditing in SQL Server 2008

I quite often use AutoAudit:

AutoAudit is a SQL Server (2005, 2008, 2012) Code-Gen utility that creates
Audit Trail Triggers with:

Created, CreatedBy, Modified, ModifiedBy, and RowVersion (incrementing
INT) columns to table

Insert event logged to Audit table

Updates old and new values logged to Audit table Delete logs all
final values to the Audit table

view to reconstruct deleted rows

UDF to reconstruct Row History

Schema Audit Trigger to track schema changes

Re-code-gens triggers when Alter Table changes the table

Update: (Original edit was rejected, but I'm re-adding it):

A major upgrade to version 3.20 was released in November 2013 with these added features:

• Handles tables with up to 5 PK columns

• Performance improvements up to 90% faster than version 2.00

• Improved historical data retrieval UDF

• Handles column/table names that need quotename [ ]

• Archival process to keep the live Audit tables smaller/faster but retain the older data in archive AutoAudit tables

Auditing SQL Server data changes

The CDC should is just a means to an end in my opinion. I have implemented audit trail solutions in the past and they have involved the use of Triggers. This got to be very messy and performance intensive for highly transactional databases.

What the CDC gives you is the ability to log the audit data without the use of triggers, but you still need a means to take that data into a permanent table. This can be done with a mirror table for each table to be audited or a single table that tracks all the changes to all the tables (I have done the latter).

Here are some links with additional information on how it was done using triggers:

SQL Audit Trail

sql-server-history-table-populate-through-sp-or-trigger

Here's an open source audit tracking solution that uses LINQ: DoddleAudit

---

Related Topics