Change Tracking (CT) vs. Change Data Capture (CDC) in SQL server 2008

Vishvvas
Posted by in Sql Server category on for Intermediate level | Points: 250 | Views : 8032 red flag
Rating: 5 out of 5  
 1 vote(s)

This article explores differences in the features (of SQL server 2008 and higher) of providing tracking and auditing abilities i.e. the change tracking (CT) and data capture (CDC) abilities.

Introduction

In the prior articles on www.dotnetfunda.com , we have explored CT i.e. change tracking in SQL server 2008 http://www.dotnetfunda.com/articles/article1931-ct-in-sql-server-2008-r2.aspx and CDC at http://www.dotnetfunda.com/articles/article1924-cdc-in-sql-server-2008-r2-part-i.aspx and http://www.dotnetfunda.com/articles/article1926-cdc-in-sql-server-2008-r2-part-ii.aspx.

We have seen practical example as well as intrinsic of CDC. With these features available, it is not easy to choose either of them.  At the first glance they don’t seem to be too different. There would be scenarios wherein there could be some overlap among the requirements or needs.

Essentially most of the system/ applications need tacking abilities with different complexity and priority. Many of the situation demands what changed and when. Also “who changed it” could be an important facet in secured system, applications especially in finance, banking domains.

Change Tracking and Change Data Capture are 2 new features in SQL server 2008 and higher, to address such needs and they offer betterment than the custom or adhoc solutions in terms of atomicity, configurability and flexibility.

Let’s try to deep dive in the differences of CT and CDC.

Objective

To learn about the difference in CT and CDC features of SQL server (version 2008 and higher) leading to choose either of them.


Using the code

Change tracking doesn’t capture the changes in the data but senses that what row/s have changed in a table and change data capture actually captures the changed data too and is evident from the name itself. Let’s run through the comparison points tabulated for quick glance.

 

Feature / Point

Change Tracking

Change Data Capture

DML Changes- latest changes in data

YES

YES

The data which has undergone changes i.e. historical data for all changes

NO

YES

How many times the data has changed

NO

YES

Whether the column data has changed?

YES

YES

Capturing mechanism

Synchronous

Asynchronous

Performance Overhead

Minimal even though part of DML operation

Minimal as reads the log and not part of DML operation

Data Storage Requirement

Low

Higher

Querying the tracked information

Little complex: Join the tracked and source table and query

Straightforward: Querying the tracked tables

Compatibility with ADO.NET Synch services

YES

NO

Configuration efforts

Low

Medium

Synchronization (like replication)

Can help to detect conflicts in case of two way synchronization

Lot of configuration need to be done to make it work in replication scenario

Clean-up mechanism

Can be turned off

Can’t be turned off


Summary and Conclusion

Definitely having 2 features to provide the tracking and auditing abilities is good scenario but it is not easy to choose one of them in practical scenario. In the scenario where historical data is important, CDC is way to go. This generally happens in enterprise application scenario and in case where on tracking information and the latest copy of data can do, CT is to go for. This is generally applicable in small systems, applications.

Of course, this can go beyond the boundaries of small or complex applications and actually depend upon the requirements.

So no clear-cut boundaries and area for employing these techniques but this comparison shall help to deduce which technique to settle for but one thing is sure that one won’t need cumbersome custom solutions to help tacking and auditing.

HAPPY PROGRAMMING!!!


Reference

http://msdn.microsoft.com/en-us/library/bb933875.aspx

http://msdn.microsoft.com/en-us/library/bb964713.aspx

http://msdn.microsoft.com/en-us/library/bb933874.aspx

Page copy protected against web site content infringement by Copyscape

About the Author

Vishvvas
Full Name: Vishwas Sutar
Member Level: HonoraryPlatinum
Member Status: Member,MVP
Member Since: 5/30/2011 2:13:10 AM
Country: India

http://www.dotnetfunda.com
Extensive and rich experience across gamut of technologies and programming languages like PB,VB,C++,VB.NET, C#, Classic ASP,ASP.NET, ASP.NET MVC.

Login to vote for this post.

Comments or Responses

Posted by: Ashuthinks on: 8/8/2012 | Points: 25
Something very new for me... great article :)




Login to post response

Comment using Facebook(Author doesn't get notification)