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