SQL Server 2008 Auditing, Change Data Capture and Tracking

When I started working on SQL Server 2008, I was truly amazed with the new features of SQL Server 2008, one of that cool features is auditing in SQL Server 2008. First I thought to give here step by step Walkthrough, but I thought instead of reinventing the wheel, why don't I just give you some really good links only, which will guide you in much better way and also save some of my time :)

Please find below the links which I followed and found very useful.

Introduction and Step by Step Walkthrough
http://blogs.msdn.com/manisblog/archive/2008/07/21/sql-server-2008-auditing.aspx

MSDN Link
http://msdn.microsoft.com/en-us/library/dd392015.aspx

But again I would like to mention that Auditing is the feature which will give you the Audit in Object, Schema and Database Level So my next challenge was to Keep track of the Database at the Field/Column level which Auditing does not provide (Correct me if I am wrong),

So I found one new features of SQL Server 2008 known as Change Data Capture/ Change Data Tracking, which is again a very cool feature,

Change data capture enables SQL Server administrators and developers to capture insert, update and delete events in a sql server table as well as the details of the event which caused data change on the relevant database table.

When you apply Change Data Capture feature on a database table, a mirror of the tracked table is createad which reflects the same column structure of the original table and additional columns that include metadata which is used to summarize what is the change in the database table row.

So enabling the Change Data Capture feature on a database table, you can track the activity on modified rows or records in the related table.

Change Data Capture (CDC) can be considered as Microsoft solution for data capture systems in SQL Server 2008 and next versions.

There were samples of data capture solutions implemented for Microsoft SQL Server 2000 and SQL Server 2005 by using after update/insert or
after delete triggers. But CDC enables SQL Server developers to build sql server data archiving without a necessity to create triggers on
tables for logging. SQL Server database administrators or programmers can also easily monitor the activity for the logged tabled.

You can find more details of how to configure and enable the Change Data Source on Tables
http://www.databasejournal.com/features/mssql/article.php/3720361/Microsoft-SQL-Server-2008----Change-Data-Capture--Part-I.htm

Difference Between Change Data Capture and Change Data Tracking
http://msdn.microsoft.com/en-us/library/cc280519.aspx

MSDN Link
http://msdn.microsoft.com/en-us/library/bb522489.aspx

I hope you will find these useful.

~Brij