Auditing SQL Server data changes

views:

771

answers:

+4 Q:

Auditing SQL Server data changes

I'm looking at changing our Auditing process for our SQL Server 2005 databases and I came across Change Data Capture in SQL Server 2008.

This looks like a good idea, and I'm tempted to try it, but before I do has anyone used it in a commercial environment and what are your thoughts?

I noticed when I was reading about CDC in the MS help, it said that audit data would usually be kept for a couple of days. That's not possible here, I'd like to keep the data indefinitely, does anyone know of problems with this approach?

If this isn't a good solution for reasons I'm unaware of, have you any other solutions for auditing of data changes. I'm keen to use a system that can be implemented across the board on any tables I desire.

I'm after the basic: "Before, After, Who By, When" information for any changes.

+4 A:

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

Jose Basilio 2009-04-27 16:16:14

I see, I've just read some more on CDC and understand what you're saying. It seems CDC benifits from being asynchronous. I quite like your idea of the single table to track all changes, however it seems to me that with "highly transactional databases" this table could get very big, very quickly.

MrEdmundo 2009-04-27 19:29:59

Hey, I've finally got round to having a look at what you were saying. I was wondering how do you keep track of who made the changes in your system? In principle CDC seems to be fairly easy to set-up and provides some nice functionality.

MrEdmundo 2009-05-15 14:59:10

In my setup, each table has 4 additional columns: CreatedDate,CreatedBy,UpdatedDate,UpdatedBy. I fill the last 2 columns with the username/date in my sql update statements. I use that determine who changed what.

Jose Basilio 2009-05-15 16:50:07

Thanks for your help. I've ended up using triggers for now. Anything I've read for CDC has said it could be used for Auditing, although that's not necessarily what it is designed for. I was worried about using something it was indented for. In the end I've implemented a system that creates Audit data similar to CDC albeit by using Triggers. I've followed a similar system of 4 types of operation and it seems to be OK.

MrEdmundo 2009-05-18 13:29:56

Would you please approve my answer?

Jose Basilio 2009-05-18 14:29:50

In SQL Server 2008, you can use the "Audit" feature and stock data on a file, app log or system log. Find more infos at : http://msdn.microsoft.com/en-us/library/cc280386.aspx

Christopher 2009-09-03 11:53:12

ansaurus

tags:

views:

answers:

Auditing SQL Server data changes

related questions