Author Bio

Steve Jones Editor at SQLServerCentral.com You can follow Steve on Twitter as way0utwest (www.twitter.com/way0utwest)

Change Data Capture

By Steve Jones in SQL Musings | 02-12-2008 1:51 PM | Categories:

Rating: (not yet rated) |

Discuss | 1,257 Reads | 71 Reads in Last 30 Days |2 comment(s)

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?

Comments

sush_nmv said:

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.

January 14, 2009 2:24 PM

Jody Claggett said:

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.

February 2, 2009 11:18 AM

SQL Musings

Change Data Capture

sush_nmv said:

Jody Claggett said:

Blog Roll

Rockstart Blogger