Subscribe to this blog
Briefcase
Print
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?
Posted by sush_nmv on 14 January 2009
Is there any way to set up the change tracking tables - the ones that are created with a suffix "-CT" in a separate database?
We have a high transaction environment and the _CT tables can grow to enormously large tables and if they are in the same database, the backup sizes become huge.
Any solution that we can think of?
We can have these tables in a separate file groups, but we take transaction log backups for log shipping and we also need to take full database backups.
Posted by Jody Claggett on 2 February 2009
I am working on a similar situation myself. Although, we have a relatively low volumn of DML hitting our sources tables, we still need to capture more then the default 3 days of data that CDC captures.
I am working on an SSIS solution that will push these the CDC table data from our staging db to our datawarehouse.
Seems to me that since CDC is so new, there are not a lot of solutions out there.
Posted by vinothr 33504 on 19 October 2010
what technology they used prior to CDC in 2005??
Posted by Steve Jones on 19 October 2010
There was no technology prior to 2008 used. You had to manually implement tracking of changes.
Posted by Steve Jones on 19 October 2010
sys.sp_cdc_change_job will help you change the retention schedule (msdn.microsoft.com/.../bb510748.aspx)