Post reply

Is CDC really Replication (or if thats what ETL is, why did we go through all these changes?)

  • February 3, 2012 at 6:46 am

    #252306

    As i understand it, CDC reads the log for a particular table and remembers the inserts, updates and deletes that pertain to it. I am not sure if it remembers every change or just the final states, but from the description, and assuming it can replay everything, doesnt that look and quack like the replication duck?

    I read this today here

    http://www.mssqltips.com/sqlservertip/2559/ssis-interview-questions-for-data-profiling-protection-level-and-package-configuration/?utm_source=dailynewsletter&utm_medium=email&utm_content=headline&utm_campaign=201222

    wherein i came across this

    <<

    What is Change Data Capture (CDC)?

    •Change Data Capture (CDC) is a new feature in SQL Server 2008 and later versions to capture all of the modifications (insert, update and delete) made to a particular table on which the CDC feature has been enabled. Once enabled, CDC will capture all these modifications into a separate table in an asynchronous manner. SQL Server reads the transaction log for the changes to a CDC enabled table and records the changes in another table with a similar structure along with the meta information about the kind of changes. Learn more about Change Data Capture.>>

    So ill be doggone, but i am not understanding the difference between Change Data Capture and Replication.

    If you want to capture and replay the state of a database from one place to another, isnt that what Replication is for?

    as ever,

    thank you very much

    drew

  • February 4, 2012 at 4:12 am

    #1442731

    If you setup CDC and do some digging you will see the guts of the code is in sp_replcmds.

    It is therefore using identical code to replication to scan the transaction log for commands and changes. Its almost replication but not quite since replication doesnt keep a change history for its modifications. It only has the current (almost) state of a row.

  • February 4, 2012 at 3:27 pm

    #1442775

    thanks very much;

    may i ask a follow up?

    is the decision whether 1) only the (almost) final state or 2) each intervening state being part of the article determined by the type of replication and the latency between the transaction and its subsriptions meaning option 1 is Snapshot replication where its once and done, and 2) is the integration of one transaction on two different systems (transactional replication with updating subscriber...) meaning everything that happens at the subscriber is part of the same transaction (as in the ATM example, where the branch with the card asks the branch with the cash if its ok to give up the coin)

    thanks very much for your explanation

    drew

  • February 5, 2012 at 4:11 am

    #1442809

    I'm really confused by this. Is the question about replication now?

  • February 5, 2012 at 7:06 am

    #1442826

    i wanted to understand where they differ. now i think replication lets you select whether you trap final state or all changes where CDC traps all changes.

    thanks again

    drew

  • February 6, 2012 at 4:28 am

    #1443002

    You can read Comparing Change Data Capture and Change Tracking in Books Online

    Editor: removed text of article referenced. Added hot link

  • February 6, 2012 at 6:31 am

    #1443071

    i hadnt thought about much of what you raised (content vs schema, context vs last transaction); thank you for taking the time to write this, i appreciate it.

    drew

  • February 6, 2012 at 7:20 am

    #1443111

    drew.georgopulos (2/6/2012)

    i hadnt thought about much of what you raised (content vs schema, context vs last transaction); thank you for taking the time to write this, i appreciate it.

    drew

    You mean, taking the time to copy paste an article from MSDN 😀

    Even the "0 out of 1 rated this helpful Rate this topic" aren't removed.

    Need an answer? No, you need a question
    My blog at https://sqlkover.com.
    MCSE Business Intelligence - Microsoft Data Platform MVP

  • February 6, 2012 at 7:21 am

    #1443112

    johnitech.itech (2/6/2012)

    Comparing Change Data Capture and Change Tracking

    ....

    You know it is common courtesy to provide at least a reference to the material you are quoting?

    Otherwise you are just violating copyright.

    Need an answer? No, you need a question
    My blog at https://sqlkover.com.
    MCSE Business Intelligence - Microsoft Data Platform MVP

  • October 23, 2015 at 11:45 am

    #1835266

    I did create a powerpoint about CDC and just CT Change Trackinig....lots of options here.

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply