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
Generally while enable CDC default it created on same database. But now my requirement is enable the CDC on database1 and capture all the changes and logged into database2. That means all dml changes on database1 logged into database2 using CDC. If possible please assist on this.
Regards.
Sir i have question regarding that,
If we disable CDC and then re-enable, then we lose existing CDC tables that tracked changes up to the point of disabling CDC. How can we use both feature disable CDC but keep already tracked data?
Thx in advance …pls ans ASAP.
Hi,
I am using CDC to track my historical data.I have a doubt ,while clearing transaction log whether my CDC tables will clear its data?.
Thanks in advance.
Hi,
Very Nice Article.
Looks like what I was searching for.
But, I have a doubt over here,,
it says,
“By Default clean up occurs after 3 days.”
So how can I keep details(say in same db or another) which are being cleaned.
Great article as always…
Q: With CDC is it possible to set up a ‘number’ of previous tracking changes, rather than time based?
eg. Given a record in a database, track the last (say) 100 edits done to that row. Keep the newest, and purge the older ones.
Cheers!
Kon,
hi Pinal, thanks for the detailed article.
Few questions on CDC implementation:
1. What is the impact of CDC on DB server over classic Trigger on same set of tables; in terms of I/O, CPU, etc?
2. Can CDC tables be created onto a different database than the source tables?
Many thanks in advance.
How can We disable the CDC without removing the existing track record?
Hi Pinal,
For example I have a table employee having column empid,ename,lastupdate_date
Is their any sql server built in functionality which sets the last update datetime in each row , I don’t want to do it using trigger or adding code to set last updated datetime. But i want all the rows fetched which is last update between given datetime interval.
Please help me on this.
Thanks
Saurabh
Hi pinal,
I have a set of database in which i want to create trigger to track the changes and update another table at the same time. How can i find the changed column name as well as the changed value.
Please help me on this if you have any ideas on trigger.
Thanks for your post on CDC.. Not only this your posts are helpful for the DBA’s….
Hi Pinal,
We are investigating on the working process of CDC to use this.
But it seems there are many unanswered questions in this SQL Authority blog itself. We are just doubting whether we have to go with CDC or not.
Thanks,
Santhosh
Hi Pinal,
I’m wondering how CDC impact the performance of SQL Server
Thanks
Is there a way, we can track Modified by field (who deleted the record) while doing delete operation. Because if we use delete operation in a record, in CDC its not tracking the current user who has deleted the record. I need the user to be tracked in CDC for delete operation.
Can you help me in that
It’s a Very useful artical for learning CDC.
Thanks @Surpriya
How to use or work this (CDC) concept
please explain performance view between triggers vs CDT
Did you mean CDC?
I have a question regarding CDC , Assume a scenario where CDC needs to be disabled for 1-2 days , In that case how do we record changes happened in table data during that period of 1-2 days.
I know if we enable the cdc again after 2 days , changes happened during those 2 days will be lost and CDC starts capturing changes after the time you enable CDC .How do we retrieve the changes done during that period.
Is there a way to have the owner other than sa?
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