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 12»»

Incremental Data Loading Using CDC Expand / Collapse
Author
Message
Posted Saturday, January 15, 2011 1:23 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: 2 days ago @ 12:19 PM
Points: 18, Visits: 207
Comments posted to this topic are about the item Incremental Data Loading Using CDC
Post #1048369
Posted Monday, January 17, 2011 3:40 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, July 5, 2011 6:35 AM
Points: 2, Visits: 53
Hi Mark,

Thanks for writing this article it was interesting to see a different way of implementing CDC incremental loading. I have also implemented an incremental load using CDC and I found that the really important part is ensuring synchronisation between the data warehouse tables and the tracked tables. You mention a couple of methods for resynchronisation in your article and I have an additional method which works quite reliably for me which I thought you might be interested in.

In my solution I have a SQL replication step between the production OLTP and the data warehouse. This gives me control over the data flow into the monitored tables and allows me to pause replication apply changes to the data warehouse. Once finished I can then restart the data flow and CDC monitoring without losing any of the change data. This of course requires the replication subscriptions to be setup not to expire during the stoppage.

Cheers

Martin
Post #1048670
Posted Monday, January 17, 2011 5:49 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 8:15 AM
Points: 13,017, Visits: 10,800
Nice article, thanks for sharing.



How to post forum questions.
Need an answer? No, you need a question.
What’s the deal with Excel & SSIS?

Member of LinkedIn. My blog at LessThanDot.

MCSA SQL Server 2012 - MCSE Business Intelligence
Post #1048710
Posted Monday, January 17, 2011 5:54 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Tuesday, July 29, 2014 8:47 AM
Points: 2,296, Visits: 1,427
got knowledge of incremental data loading thanks for the post
Post #1048712
Posted Monday, January 17, 2011 7:29 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, July 11, 2014 7:34 AM
Points: 22, Visits: 52
Mark:

Great article. Nice clarification and demonstration of CDC.
It has often been a problem explaining to programmers the
intent of CDC and to execute the project correctly.

One note, there are no footnotes to your previsous two article.
For completeness in discussing CDC, can you either post or send
them to me.

Thanks!
Post #1048753
Posted Monday, January 17, 2011 10:56 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: 2 days ago @ 12:19 PM
Points: 18, Visits: 207
casinc815,

The links are at the beginning. They are: Article 1 and Article 2.

-Mark
Post #1048869
Posted Monday, January 17, 2011 11:52 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, July 11, 2014 7:34 AM
Points: 22, Visits: 52
thanks, Mark!
Post #1048893
Posted Monday, March 5, 2012 1:42 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, March 3, 2014 9:33 AM
Points: 10, Visits: 79
I read the article about Incremental Data Loading using CDC - but can you create an article and demo step by step on how to capture changes from a bulk load and incremental load from a source that does not have CDC (like SQL Server 2005). most of my tables don't have datetime column so I don't know if the record is new or updated by using datetime alone. Can you help me?

I need to do a bulk load initially from 3 servers merged into one server database and perform incremental updates realtime or each night.
Post #1261811
Posted Friday, June 7, 2013 2:54 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Thursday, September 25, 2014 4:46 AM
Points: 1,070, Visits: 907
Still a good article, but would be even better if you addressed some of the potential pitfalls of it.

Like runaway transaction log growth if you stop being able to clean out the system properly.



Post #1461005
Posted Wednesday, June 12, 2013 11:54 AM


SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, July 23, 2014 12:10 PM
Points: 49, Visits: 279
Hello,
Great article, I would like to know more about how to handle disruptions:
- Server crash
- SQL Server instance restart
- CDC package fails half way
- CDC Package fails due to time out or network error
- CDC Package fails due to transaction log full
- Source table DDL Changes like add a column and populate it
Should I just rerun the CDC in Reprocessing mode?
In my scenario I track a table which is updated once a day.


BI Guy
Post #1462763
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse