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
Nice Article and i read a similar once before but this one is so simple to understand.. really you have a art to present things in very simple way…. see u change my thinking about indexes i dropped 4 after came back to Delhi
Hello sir,
One of the best article i have ever read.
I have one question
what, you are explaining about the Mask,
that it stores in hexadecimal and after convert it into
bit you know which columns are updated.
what is the limit of that mask storing.
i have table which has 185 columns so any easy way to
find which column i have updated or
can i get the name of the columns which are updated.
one things is
if i update with same value
example column id has value – 1 and
i update it with again 1 then that
cdc records that thing ?
Looks like CDC is very much scaleable compared to trigger based tracking (our good old method )
I have a system of triggers now that provides an undo/redo feature along with history commands that show a record’s history of changes. I’m wondering if CDC can be used to such functionality. Any thoughts?
very good article and simple also. Now my question, is there any problem in using the CDC system that sql server provides, coz as i was searching for some pros and cons for using CDC i came across a link https://docs.microsoft.com/en-us/previous-versions/technet-magazine/cc987538(v=msdn.10)#id0110025 which tells some comparison b/w change tracking and change data capture. Now is there any problem like performance hit in any way can occur when using the cdc facility
Please reply!
Thanks in advance
Hi pinalDave,
Great article, it helped a lot. I have two
Is there any disadvantage of CDC that we have identified till now incase of real-time data transfer?
How good is the performance and does CDC create any additional overhead on the server?
Is it possible that CDC can replication process used in sql? Can it be compared?
Awaiting your reply in anticipation
Thanks in advance!
Hi Dave,
Could you please tell me how to change database schema when CDC ia enabled.
I asked microsoft regarding this question
The answer was
Back up cdc table data in temp table
disable the cdc
apply schema changes
Enable cdc
Copy data back to cdc table
This is not feasible for real world. Do you have any idea.
Thanks.
Eva
Hi Pinal,
Before I start my query I would like to say that whenever I have face any problem I have also got the solution through your Forum. It really helpful for us
Thanks a million for all your efforts.
I was trying to apply CDC on our Server where I would like to store ALL changes done on database. I was unable to figure out how do you track date and time at which those changes were carried out because when I go through the LOGS I only get information about the CHANGES but it doesn’t mention when this changes were carried out.
Please let me know if I am missing something on tracing the time at which those changes where carried out.
Thanks
Neil
Dear pinalDave
I Red one article about introduction to Change Data Capture (CDC) in SQL Server 2008 which is Posted in simple-Talk.In that (a mirror of the tracked table is created with the same column structure of the original table+ Additional columns for tracking the Changes)..
Does it Increases the DB Size while Executing?
Hello Ajith,
When CDC is enabled for a table the history of all changes is stored in a change table. As this table reserves size on disk, db size obviously increases. But using CDC retention based cleanup policy its size is maintained within a limit.
Regards,
Pinal Dave
Hi Pinal,
while search on google i have found out this alternative where you can add columns to existing CDC table to capture alternative information.
Simply alter the cdc.schema_TableName_CT table and add a column with a default. The columns I have added are
username varchar(30) null default(suser_sname())
auditdte datetime2 null default(getdate())
but i am not sure on impact it may carried out on Database.
can you please let me know if there will any impact on database when I add additional columns which are not created by CDC
Regards
Neil
Hi I also need Client Application name in CDC table.I created new column on CDC table with defauly and APP_Name() but it stores SQL Server AGent Job in the column value.
can anyone help me please ?
Store client application name in the user table and hence it will be mirrored in the cdc equivalent?
Hi,
Is there any way to track IP Address to find who did the changes in the table?
Hi Dave
I have a principal-mirror servers setup without witness and enabled CDC on a database 1 specific table on principal server. Whenever I failover I create the jobs(cleanup and capture) after failover was made(as BOL is saying) but the capture of the data seems is not happening on the newly principal server.
If I failover back the capturing is wk ok on the initial principal.
Any ideea if I miss something?
Thx for help!
Hi Pinal,
CDC doesnt give me details on who made the changes.For this I can go with Audits.Other than this do you recommend any other methods.
Thank You,
Anup
Hello Pinal,
YOur blog was very useful for auditing. I want to know that how can we take backup and restore of cdc tables created under system tables.
I increased retention to 1 week and backup database weekly.And if any issues want to restore the database and check cdc data.
Thanks,
Hi Pinal,
Great article! You simplified the steps to make it easy to implement.
Is there a way to also capture the User who made the modification?
Is it as simple as adding a column called UserName with a DEFAULT VALUE of SUSER_SNAME() to the CT Table?
Or is there a better way of doing this?
Hello,
Initial setup was clean and easy. When I dig further I am noticing the cleanup job is calling functions and stored procedures that do not exist.
For example:
sp_MScdc_cleanup_job calls the following functions and SP’s that do not exist.
fn_cdc_is_db_enabled
sp_cdc_get_cleanup_retention
sys.sp_cdc_cleanup_job_internal
Is anyone actually using this in a production envirnoment? Do I need to be running SQL2008 R2?
Thanks,
Randy
OK, I as able to find the stored procedures in mssqlsystemresource.mdf database, although I still am looking for the above function.
Thanks,
Randy
Hi Pinal,
I do not have SQL Server 2008 and working with SQL server 2005, is there anything similar that i can do to my SQL 2005 version like adding some SP just like CDC that would give me the same functionality ??????
Thanks
Farooq
Hi Pinal,
I tried implementing the CDC and Configured Transctional Replication on the same database.. when i am doing this.. the CDC at the database level looks good..but CDC at the table level is disabling/tweeking the metadata tables of CDC..
So, Could you plzz suggest me on thiss……
Thanks
Anil Inampudi