SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Implementing Change Data Capture in Microsoft® SQL Server 2008


Implementing Change Data Capture in Microsoft® SQL Server 2008

Author
Message
Suresh Babu Yaram
Suresh Babu Yaram
Valued Member
Valued Member (57 reputation)Valued Member (57 reputation)Valued Member (57 reputation)Valued Member (57 reputation)Valued Member (57 reputation)Valued Member (57 reputation)Valued Member (57 reputation)Valued Member (57 reputation)

Group: General Forum Members
Points: 57 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
pete callaghan
pete callaghan
Grasshopper
Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)

Group: General Forum Members
Points: 20 Visits: 82
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?
Peter Schott
Peter Schott
UDP Broadcaster
UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)

Group: General Forum Members
Points: 1485 Visits: 1919
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. Smile
LeeFAR
LeeFAR
SSC-Addicted
SSC-Addicted (420 reputation)SSC-Addicted (420 reputation)SSC-Addicted (420 reputation)SSC-Addicted (420 reputation)SSC-Addicted (420 reputation)SSC-Addicted (420 reputation)SSC-Addicted (420 reputation)SSC-Addicted (420 reputation)

Group: General Forum Members
Points: 420 Visits: 322
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.



cliffb
cliffb
SSC Eights!
SSC Eights! (803 reputation)SSC Eights! (803 reputation)SSC Eights! (803 reputation)SSC Eights! (803 reputation)SSC Eights! (803 reputation)SSC Eights! (803 reputation)SSC Eights! (803 reputation)SSC Eights! (803 reputation)

Group: General Forum Members
Points: 803 Visits: 438
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.



Jon Jaques
Jon Jaques
Forum Newbie
Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)

Group: General Forum Members
Points: 3 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.
danschl
danschl
SSCommitted
SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)

Group: General Forum Members
Points: 1546 Visits: 644
I can see a need for this
but 2008 rendition has too much overhead



SanjayAttray
SanjayAttray
SSCarpal Tunnel
SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)

Group: General Forum Members
Points: 4397 Visits: 1619
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.
SanjayAttray
SanjayAttray
SSCarpal Tunnel
SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)

Group: General Forum Members
Points: 4397 Visits: 1619
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.
SanjayAttray
SanjayAttray
SSCarpal Tunnel
SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)

Group: General Forum Members
Points: 4397 Visits: 1619
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.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search