Implementing Change Data Capture in Microsoft® SQL Server 2008

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » Article Discussions » Article Discussions by Author » Discuss content posted by Suresh Babu Yaram » Implementing Change Data Capture in...

27 posts, Page 1 of 3

1 2 3 »»»

Implementing Change Data Capture in Microsoft® SQL Server 2008

Rate Topic

Display Mode

Topic Options

Author

Message

Suresh Babu Yaram

Posted Thursday, October 23, 2008 12:56 AM

Valued Member

Group: General Forum Members
Last Login: Wednesday, December 02, 2009 2:56 AM
Points: 53, Visits: 40

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

Post #590277

pete callaghan

Posted Thursday, October 23, 2008 2:39 AM

Grasshopper

Group: General Forum Members
Last Login: Sunday, August 22, 2010 2:08 PM
Points: 10, Visits: 79

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?

Post #590310

Peter Schott

Posted Thursday, October 23, 2008 5:00 AM

Right there with Babe

Group: General Forum Members
Last Login: 2 days ago @ 11:01 AM
Points: 739, Visits: 927

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. :)

Post #590366

LeeFAR

Posted Thursday, October 23, 2008 7:16 AM

SSC Veteran

Group: General Forum Members
Last Login: Monday, February 13, 2012 8:30 AM
Points: 207, Visits: 192

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.

Post #590429

cliffb

Posted Thursday, October 23, 2008 7:48 AM

Say Hey Kid

Group: General Forum Members
Last Login: Friday, May 03, 2013 11:41 AM
Points: 675, Visits: 426

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:

[color=#FF0000]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.[/color]

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.

Post #590449

Jon Jaques

Posted Thursday, October 23, 2008 7:53 AM

Forum Newbie

Group: General Forum Members
Last Login: Thursday, February 02, 2012 3:39 PM
Points: 1, Visits: 18

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.

Post #590454

danschl

Posted Thursday, October 23, 2008 9:08 AM

Ten Centuries

Group: General Forum Members
Last Login: Monday, February 11, 2013 8:37 AM
Points: 1,382, Visits: 641

I can see a need for this
but 2008 rendition has too much overhead

Post #590527

SanjayAttray

Posted Thursday, October 23, 2008 9:10 AM

Hall of Fame

Group: General Forum Members
Last Login: Friday, March 15, 2013 2:43 PM
Points: 3,924, Visits: 1,554

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.

Post #590529

SanjayAttray

Posted Thursday, October 23, 2008 9:13 AM

Hall of Fame

Group: General Forum Members
Last Login: Friday, March 15, 2013 2:43 PM
Points: 3,924, Visits: 1,554

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.

Post #590530

SanjayAttray

Posted Thursday, October 23, 2008 9:22 AM

Hall of Fame

Group: General Forum Members
Last Login: Friday, March 15, 2013 2:43 PM
Points: 3,924, Visits: 1,554

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.

Post #590543

« Prev Topic | Next Topic »

27 posts, Page 1 of 3

1 2 3 »»»

Permissions