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

Posted by HardCode on Stack Overflow See other posts from Stack Overflow or by HardCode
Published on 2010-04-21T15:29:14Z Indexed on 2010/04/21 17:23 UTC
Read the original article Hit count: 421

Before I delve into the abyss of Microsoft documentation any deeper, I'd like to know if someone experienced with Change Data Capture and Change Tracking know if one or both of these can be used to replace the traditional ...

"Audit trail table copy of the 'real table' (all of the fields of the original table, plus date/time, user ID, and DML action field) inserted into by Triggers"

... setup for a database table audit trail, where the trigger populates the audit trail table (which is all manual work).

The MSDN overview documentation explains at a high level what Change Data Capture and Change Tracking are, but it isn't clear enough to me, and doesn't state outright, that these tools can be used to replace the traditional audit trail tables we've made so often.

Can someone with any experience using Change Data Capture and Change Tracking save me a lot of time, or confirm that I am spending time looking at the right tool? The critical part of our audit trail is capturing all changes to a table's fields (on INSERT, UPDATE, DELETE), when it happened, and who did it. These changes are commonly provided to an end user chronologically via an audit trail report. Which is another question ... Change Data Capture or Change Tracking is the solution, I'd assume that this data can be queried just like data from a normal table?

EDIT: I need a permanent audit trail, irregardless of time. I see that Change Data Capture has to do with the transaction logs, so this sounds finite to me.

© Stack Overflow or respective owner

Related posts about sql-server-2008

Related posts about change-data-capture

  • Change Data Capture Webinar

    as seen on SQLIS - Search for 'SQLIS'
    I am going to be doing a webinar with our friends at Attunity on Change Data Capture.  Attunity have a good story around this technology and you can use it in your SSIS loads to great effect. Join Attunity and Konesans/SQLIS for a Webinar on 17 September Space is limited. Reserve your… >>> More

  • Change Data Capture Webinar

    as seen on SQLIS - Search for 'SQLIS'
    I am going to be doing a webinar with our friends at Attunity on Change Data Capture.  Attunity have a good story around this technology and you can use it in your SSIS loads to great effect. Join Attunity and Konesans/SQLIS for a Webinar on 17 September Space is limited. Reserve your… >>> More

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

    as seen on Stack Overflow - Search for 'Stack Overflow'
    Before I delve into the abyss of Microsoft documentation any deeper, I'd like to know if someone experienced with Change Data Capture and Change Tracking know if one or both of these can be used to replace the traditional ... "Audit trail table copy of the 'real table' (all of the fields of… >>> More

  • TechEd 2010 Thanks and Demos

    as seen on SQL Blog - Search for 'SQL Blog'
    Thank you to everyone who attended my three sessions at this year's TechEd show in New Orleans. I had a great time presenting and answering the really great questions posed by attendees. My sessions were: DAT317 T-SQL Power! The OVER Clause: Your Key to No-Sweat Problem Solving Have you ever stared… >>> More

  • TechEd 2010 Thanks and Demos

    as seen on SQL Blog - Search for 'SQL Blog'
    Thank you to everyone who attended my three sessions at this year's TechEd show in New Orleans. I had a great time presenting and answering the really great questions posed by attendees. My sessions were: DAT317 T-SQL Power! The OVER Clause: Your Key to No-Sweat Problem Solving Have you ever stared… >>> More