LearnItFirst User Forum

Welcome Guest

Search | New Posts | Members | Log In | Register

SQL Server Forum - LearnItFirst.com » SQL Server Database Administration » General SQL Database Question & Answer » SQL Server 2008 functionality for Audit Log

SQL Server 2008 functionality for Audit Log

Options

Previous Topic · Next Topic

totaltech

Posted: Wednesday, November 18, 2009 12:01:05 AM

Rank: Newbie

Joined: 11/17/2009
Posts: 1
Points: 3

Hello

I have to implement a functionality for audit ( insert/update/delete ) log of the each table.
There are two option
1) Through SQL Server 2008 Feature : I heard that in the SQL Server 2008 we have CDC(Change Data Capture) features which can be enable on each table and track each data changes. It insert new row into a special system table which we need audit log.
2) Create a unnormalized table for each table and handle the audit (insert/update/delete) log using the trigger.

Can anybody help to me to choose which options is best and easy to manage in the future?

Thanks,
Craig
Database Development Company San Diego

Scott Whigham

Posted: Monday, November 23, 2009 12:43:22 PM

Rank: Super Mod

Joined: 3/20/2006
Posts: 476
Points: 1,053
Where do you live?: Dallas, TX

Hi Craig -

Many years ago when SQL 2005 first came out some non-SQL folks came up with a SQL CLR solution to this: http://learnitfirst.com/go.aspx?id=C22G. It uses a key-value store (a.k.a. EAV, or Entity Attribute Value) that allows a single table to store all auditing information. Seems crazy to me though.

You can use CDC for sure however I would just suggest purchasing a 3rd party tool to do this stuff. Just search for "sql server audit software" or some such. Really - it's just easier IMO to pay $1000 for the software to do this stuff instead of creating lots of homegrown solutions. That's just my opinion though; there are tons of folks, I'm sure, who think otherwise.

Users browsing this topic

Guest

Forum Jump

You cannot post new topics in this forum.
You cannot reply to topics in this forum.
You cannot delete your posts in this forum.
You cannot edit your posts in this forum.
You cannot create polls in this forum.
You cannot vote in polls in this forum.