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

Published Friday, April 24, 2009 12:58 PM by bmdayal

Comments

# Valuable Internet Information » SQL Server 2008 Auditing, Change Data Capture and Tracking - bmdayal

Pingback from  Valuable Internet Information » SQL Server 2008 Auditing, Change Data Capture and Tracking - bmdayal

# re: SQL Server 2008 Auditing, Change Data Capture and Tracking

Hi, Wasup.

I i saw yer thread

Very well constructed

In fact I have been searching for this for yonks

dotnetslackers.com  is just what I was looking for.

Great effort congrats !

John

Wednesday, October 14, 2009 7:06 PM by JoJo Johnathen

# re: SQL Server 2008 Auditing, Change Data Capture and Tracking

Hiya  Wasup.

I saw your post

Very absorbing

In fact I have been looking for this for ages

dotnetslackers.com  is definitely on my bookmarksnow.

Great effort keep up the good work !

John

<a href=www.accountant1.org it audit training </a>

Wednesday, October 21, 2009 3:55 AM by JoJo Jonboy Jnr

# re: SQL Server 2008 Auditing, Change Data Capture and Tracking

Amiable position keep up your passable work.

Thursday, November 05, 2009 1:52 AM by Nighimabime

# re: SQL Server 2008 Auditing, Change Data Capture and Tracking

Hiya  Wasup  

I saw yer blog on  dotnetslackers.com

Very absorbing

In fact I have been Googling for this for yonks

dotnetslackers.com  will see me back again

Great effort congrats !

John

<a href=www.adstractor.com equipment locator services knoxville tn </a>

Thursday, November 05, 2009 7:30 AM by JoJo Jonboy Jnr

# re: SQL Server 2008 Auditing, Change Data Capture and Tracking

I deliberate on the dispatch is good and on the point. This enter extraordinarily helped me in my assignment.

Monday, December 28, 2009 4:48 AM by WP Themes