Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 123»»»

Implementing Change Data Capture in Microsoft® SQL Server 2008 Expand / Collapse
Author
Message
Posted Thursday, October 23, 2008 12:56 AM


Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Wednesday, December 2, 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
Posted Thursday, October 23, 2008 2:39 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, March 4, 2014 2:50 AM
Points: 10, Visits: 80
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
Posted Thursday, October 23, 2008 5:00 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Yesterday @ 5:02 PM
Points: 841, Visits: 1,285
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
Posted Thursday, October 23, 2008 7:16 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Tuesday, August 26, 2014 9:18 AM
Points: 207, Visits: 200
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
Posted Thursday, October 23, 2008 7:48 AM
Say Hey Kid

Say Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey Kid

Group: General Forum Members
Last Login: Saturday, September 27, 2014 12:26 PM
Points: 676, Visits: 433
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
Posted Thursday, October 23, 2008 7:53 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, February 2, 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
Posted Thursday, October 23, 2008 9:08 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Friday, October 25, 2013 7:43 AM
Points: 1,384, Visits: 644
I can see a need for this
but 2008 rendition has too much overhead





Post #590527
Posted Thursday, October 23, 2008 9:10 AM
Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Thursday, July 17, 2014 10:56 AM
Points: 3,924, Visits: 1,607
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
Posted Thursday, October 23, 2008 9:13 AM
Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Thursday, July 17, 2014 10:56 AM
Points: 3,924, Visits: 1,607
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
Posted Thursday, October 23, 2008 9:22 AM
Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Thursday, July 17, 2014 10:56 AM
Points: 3,924, Visits: 1,607
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 »

Add to briefcase 123»»»

Permissions Expand / Collapse