Implementing Change Data Capture in Microsoft® SQL Server 2008

  • Comments posted to this topic are about the item Implementing Change Data Capture in Microsoft® SQL Server 2008

    Suresh Yaram
    DatabaseArchitect,
    Integrators Office - Technology Advisory Group - CHB
    Satyam Computer Services Limited
    A-2, Satyam Gateway, Hyderabad,
    Off - (40) 3065 3097
    Mobile - +91-99085-50642

  • A detailed but clear explanation - many thanks.

    Obviously there must be associated performance hit. Does anyone yet have any real-world experience of how CDC might impact performance?

  • Agreed - a good explanation and much easier to read than a lot of more detailed white papers. My only comment on a possible enhancement would have been to show some sample output from the CDC records. I can see a potential use for this to load a real time warehouse of data, but can also see issues with performance and space. Even worse if something goes wrong and we can't query this database prior to purge.

    I'm going to give this a try locally and see what I can come up with. I'd also love to hear about anyone using this in the real world. 🙂

  • I see this as a way to better ensure auditing of data changes. In the past, we have had to build "history" tables and implement triggers on the base tables to capture the history of changes to the data. Using CDC, it seems like this requirement is much simpler and encompassing. My clients generally deal with sensitive data, therefore we often have to provide an audit trail of who changed what when. Thanks for the article.

  • I pulled in the scripts and hit an error on the disable cdc call from the scripts document referenced at the bottom of the article.

    EXEC sys.sp_cdc_disable_table

    @source_schema = 'dbo'

    , @source_name = 'Employee'

    , @capture_instance = 'dbo_Employee'

    , @supports_net_changes = 1

    Throws an error:

    Msg 8144, Level 16, State 2, Procedure sp_cdc_disable_table, Line 0

    Procedure or function sp_cdc_disable_table has too many arguments specified.

    Books online specifies the syntax as:

    sys.sp_cdc_disable_table

    [ @source_schema = ] 'source_schema' ,

    [ @source_name = ] 'source_name'

    [ , [ @capture_instance = ] 'capture_instance' | 'all' ]

    So if you get rid of the support_net_changes it runs ok.

    Besides auditing, another use of CDC is for data warehouseing and reporting. No need to write specialized code to pull in the changed records, you have a nice method of handling that using CDC without relying on the MERGE functionality in 2008 or Slowly Changing Dimensions tasks in SSIS which can be a performance hinderance on large data sets.

  • What I want to know is whether the new CDC capability can handle BLOB data such as long text and ntext fields. While trying to do CDC with SSIS 2005, I discovered that the use of embedded SQL made BLOB data a real problem, and was completely unsupported.

  • I can see a need for this

    but 2008 rendition has too much overhead

  • Nice article and worth giving it a try for this new feature. Now, with this feature in 2008, I hope there would be less need to create a history table and those update and delete triggers on tables to capture any data manipulation.

    Would definitely try in test environment in couple of days.

    SQL DBA.

  • danschl (10/23/2008)


    I can see a need for this

    but 2008 rendition has too much overhead

    Agreed. That's the reason I would not implement 2008 in my project at least for another year or so. By that time we would have a broad idea about 2008 behavior. Its pro's and con's.

    SQL DBA.

  • Couple of other articles on Change data capture are here with good explanation. I read them sometime back and are very useful.

    http://www.databasejournal.com/features/mssql/article.php/3720361

    http://www.databasejournal.com/features/mssql/article.php/3725476

    http://weblogs.sqlteam.com/derekc/archive/2008/01/28/60469.aspx

    SQL DBA.

  • This paper discusses the implementation of Change Data Capture in SQL Server 2008 and how it is superior to the traditional change tracking mechanisms in the context of data warehousing applications.

    Yes, that is exactly how I understood the suggested article. Read it twice... 🙂

    It is clearly covering a topic "How to turn CDC on SQL server on".

    Still did not understand: how is it superior to the traditional change tracking?

    CDC is just a tool, not a solution. With multiple limitations it currently has, it can not be used for Auditing and could only be used as a part of the "History of changes" tracking solution...

  • Is there something similar for SQLServer 2005?

    []s

    Rubem Rocha

    Manaus, AM - Brazil

  • djpardalrocha (10/23/2008)


    Is there something similar for SQLServer 2005?

    []s

    Rubem Rocha

    Manaus, AM - Brazil

    CDC is new with SQL 2008. You may be able to implement a similar mechanism through triggers on the tables in 2005 and 2000, but there is nothing out of the box that will do this in 2005.

  • Good article, well executed. I've been very excited about this development and can't wait for a chance to compare it to the traditional methods (e.g. triggers and time stamps) I've used in the past. While like others I am guarded in my enthusiasm as a result of MS's half-baked implementations of functionality in the past, my reading on this is it will save a lot of time. Thanks for taking the time and effort to work this out for us.

    😎 Kate The Great :w00t:
    If you don't have time to do it right the first time, where will you find time to do it again?

  • I haven't had the chance to test out CDC yet so my comments do not in any way address reliability, particularly as Kate points out, with MS's past track record. However a number of posters question performance and benefit over traditional methods (eg triggers). A key point about CDC is that SQLServer uses the transaction log to work out the changes. This means that the auditing is done in the background by the server after the user transaction has committed. This is a fundamental point. The trigger method requires that auditing is done as part of the user transaction and so increases the overall time for the transaction to completed, including any potential contention with other transactions. CDC makes it a background task which obviously still uses resources in terms of cpu and cache, but it does not delay the transaction or impact the user experience, assuming you're server has adequate resources to handle the workload of the application, which it would need regardless of CDC or triggers.

    Cheers

    Roddy

Viewing 15 posts - 1 through 15 (of 26 total)

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