I started working through some labs for my SQL Server 2008 class and one of the labs was on the new Change Data Capture, essentially an auditing action that allows you to tell what changes have been made to data in a table, including insertions and deletions. You have to enable the option for each table, but it proves to be very interesting.
It uses LSN from the log, which there are functions to allow you to grab based on times, and so I wondered if it was a view into the log. This makes some sense, but since you backup and truncate the log, your auditing would be compromised. Or your log could grow out of control.
Essentially you set upa retention schedule and this allows you to manage how long you keep data around. I have to dig in more, but this looks like a very interesting feature. What I'm not sure is how well it will be used and if it will become a management headache for administrators. And you can backup and restore audit data?
