SQL Server

SQL Server: Similarities and Dissimilarities between Change Tracking and Change Data Capture in SQL Server

- by Amit Bansal - 5 Comments.

Hi Friends,

I spent last weekend reading about Change Tracking (CT). I have worked with Change Data Capture (CDC) a couple of times but really never got the right scenario to implement Change Tracking.

CT, like CDC tracks changes to a particular table in a particular database. In many ways, they are similar but their dissimilarities are what will interest you. So here is a quick look in to the similarities and dissimilarities:

How are they similar?

  1. Both needs to be enabled on per database basis
  2. Both needs to be enabled on per table basis to track changes in that particular table
  3. Both create system tables where the change information is recorded
  4. Both can track whether a column was changed

How are they dissimilar?

   
  1. CT only tracks whether a change has been done or not; does not track the data values (old and new). CDC also tracks the data values (old and new)
  2. CT only gives you the last change (final change) in case a series of changes has been done to the data. CDC gives you the complete history of the changes.
  3. CT adds a hidden column to the table being tracked to uniquely identify the rows. CDC doesn’t do that.
  4. CT records changes synchronously. CDC records asynchronously.
  5. CT uses TempDB. CDC uses transactional log.
  6. CT doesn’t need SQL Agent. CDC needs SQL Agent.
  7. CT is available on lower editions. CDC is only available in Enterprise Edition & Higher.

You are welcome add more similarities and dissimilarities in the comments section below.

Now, let me put out an obvious question; what are the right usage scenarios for CT & CDC? Hope to see some answers…

 

 

   

Related Posts

SQL Server Query Cost, Memory Grant & SQLRESERVATIONS Clerk

SQL Server Memory Troubleshooting

Performance Monitor Counters

About Amit Bansal

Amit Bansal is always brainstorming around SQL Server. Despite working with SQL since 1997, he is amazed that he keeps learning new things every single day. SQL Server is AB's first love, and his wife does not mind that. He tries to share as much and spreads the SQL goodness. Internals and Performance Tuning excites him, and also gives him sleepless nights at times, simply because he is not a genius, but quite a hard worker and does not give up. It has been a long and exciting journey since 1997, you can read here: http://sqlmaestros.com/amit-bansal/ He is on Twitter: https://www.twitter.com/A_Bansal

View all posts by Amit Bansal →

5 Comments on “SQL Server: Similarities and Dissimilarities between Change Tracking and Change Data Capture in SQL Server”

  2. CDC sucks…Sorry for using that foul word but it really does.

    It does not provide in built ways by which you can understand the login which made the changes.

    Do a schema change on a table where CDC is configured I bet you are gonna loose all the tracking that CDC logs.

    Reply

  3. Sachin, before you display such strong sentiments; I advise you that you should know which feature should be used for which purpose…

    Do you know why CDC does not give login name? Was it so difficult for the product team to add another coulmn in the tracking tables displaying the login name?

    Here is your answer: CDC stands for Change Data Capture – means this features only cpatures changes to data. Who made the change is an auditing question. And if you want an answer to that, you have to implenment “Full event auditing” in SQL Server 2008.

    CDC is primarily built for ETL purposes; and MS team does not want people to start using it for auditing purposes. If that happens, many DBAs might land up using CDC for auditing and will recrod way too much infromation only to get the login name. ANd this become the wrong use of a good feature.. and then another DBA will again complain – it just sucks 🙂

    FOr your other question; need to look into implementation…

    Reply

  4. The reason why I am showing such strong sentiment was because after we had implemented CDC we wanted to add a index on a table.As soon as it was added CDC deleted everything which it had logged and we lost a hell lot audit data.This is not dcoumented anywhere except on some SQL forums and as a community contributor it is our duty to make the readers aware of such issues and not just post the good side of things.

    One can easily get carried away in implementing CDC as an auditing tool and then taste the sour fruits of it later.

    Also can you please explain what is CDC meant for and “MS team does not want it for auditing purpose” because if you are tracking changes(which CDC does )then it means it is auditing.

    Also as far as adding a custom column in the CDC table for displaying login name CDC just does not seems to like that just logs the schema name instead.

    As you said Data Auditing is much much more felxible tool and should be used instead of CDC for auditing which we did and works very fine.I totally agree with that…

    Reply

Leave a Reply

Your email address will not be published.