Simple-Talk.com has published my very first article on their site. This article is introducing Change Data Capture – the new concept introduced in SQL Server 2008. Change Data Capture records INSERTs, UPDATEs, and DELETEs applied to SQL Server tables, and makes a record available of what changed, where, and when, in simple relational ‘change tables’ rather than in an esoteric chopped salad of XML. These change tables contain columns that reflect the column structure of the source table you have chosen to track, along with the metadata needed to understand the changes that have been made.
Introduction to Change Data Capture (CDC) in SQL Server 2008
SQL Server 2005 introduced the new features of ‘after update’, ‘after insert’ and ‘after delete’ triggers that almost solved the problem of tracking changes in data. A better solution was introduced in SQL Server 2008 and is called Change Data Capture (CDC). CDC has allowed SQL Server developers to deliver SQL Server data archiving and capturing without any additional programming.
CDC is one of the new data tracking and capturing features of SQL Server 2008. It only tracks changes in user-created tables. Because captured data is then stored in relational tables, it can be easily accessed and retrieved subsequently, using regular T-SQL.
When you apply Change Data Capture features on a database table, a mirror of the tracked table is created with the same column structure of the original table, but with additional columns that include the metadata used to summarize the nature of the change in the database table row. The SQL Server DBA can then easily monitor the activity for the logged table using these new audit tables .
Please read my article and leave your valuable comments here. If you like the article do not forget to rate it.
Reference : Pinal Dave (https://blog.sqlauthority.com)
66 Comments. Leave new
hi Pinal,
it’s very helpful article. but still i have small doubt, what is the Architecture of SQL server change data capture(CDC)? is sql server using trigger to capture change data in CDC?
Thanks
Tharindu Dhaneenja
Hi Pinal,
I have some issue with CDC. My table has one varchar(max) column and one xml column. Whenever any update happens in the table where above two columns are not involved the before update data shows some strange values for those two columns.
My xml column has value “”. In the CDC table before update it shows null. Same thing is happenning for varchar(max) column. Any idea on this?
Hi
data capturing time can get for each lsn by below sql
SELECT *
FROM cdc.lsn_time_mapping
This is cool feature and very well explained.
do you have similar explanation for Change Tracking features, it’s pros and cons?
Is it a good practice to use Change Tracking when polling is done with very high frequency say in every 2-3 minutes?
Hi Pinal,
I am looking for the opportunities to use this feature in my current DW implementation however, our source systems are in SQL 2005 and Sybase. Can CDC support other data sources somehow?
Thanks
Ashish
Hi Pinal,
great article, i love your way to explain.
i’m not clearing that when i change views,stored proc, function, then cdc can work?
and if any dba open table in edit mode & perform any insert/update/delete operation then in that case it also works?
Hi Pinal,
CDC was enabled on the existing database table where it had few records in it. CDC is not capcturing changes for old data which is there prior to the CDC, working great for new records that got inserted. What is the work around for this problem ?
Hi Pinal,
I have applied CDC on child tables, now the requirement has changed and i need to capture changes in parent tables. I can apply CDC on parent tables, but while fetching these change records in parent table, i want the records from its child tables as well which are associated with it at that point of time. Hope you get the point here.
Hi Pinal
I have applied CDC on child tables, now the requirement has changed and i need to capture changes in parent tables. I can apply CDC on parent tables, but while fetching these change records in parent table, i want the records from its child tables as well which are associated with it at that point of time.
Thanks for u..
Is there any way to insert into CDC table manually?
We have history tables already existing, we are trying to replace them with CDC tables, but we need to migrate data from old history tables into CDC table.
Hi Pinal,
Is there any way to keep my Change Data tables in another database instead of other schema?
Your kind help is highly appreciated.
Regards,
Atiq Rahman
One of the best articles on CDC in SQL Server. Thanks Pinal.
great article on cdc. thank you pinal dave sir.
How can I use CDC for bulk load when I have one database on a SQL Server 2005 server and the staging database on SQL Server 2008 with CDC? I don’t want to use lookups or joins or exist statements but I need to get the initial bulk load from the 2005 server and all incremental loads from the 2005 server. Can you help?
How to push CDC data in change tables to destination
The requirement is to license my DB data to others.
After I give initial dump of data I am ware of only 2 options to keep the data in sync between source & destination
1. Steup replication or
2. Enable CDC .
I have very limited knowledge of both.
Can you please help me with which of the 2 mentioned above will be an efficient approach ?
Also in case of replication the changes made to publisher are pushed to subscriber but in case of CDC i am not aware how to push the captured changes from CDC tables to the subscriber/destination server/DB
Please guide me on this
really nice one
Hello Pinal,
I just want to THANK YOU for every of your article. Your articles are so easy to understand. Really appreciate your help.
Nick
Really nicely presented article on CDC.
It is clear and easy to understand.
Thanks!
using CDC can a table data be restored to pre-change event?
Wish I had read the article today before fumbling through BOL to gather an understanding :) Reading through your article confirms most of what I comprehended by reading BOL, but it also explained a few additional items. Excellent job with the article.