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.