Preserving cdc history when making schema change

Question

Preserving cdc history when making schema change

erichbrinker

Say Hey Kid

Points: 693
More actions
November 5, 2010 at 2:40 pm

#226053

Hoping I am missing something using CDC for tracking changes on tables in SQL 2008.
We have a table with CDC enabled on it. When we make a change to get history on the new column we disable cdc on the table, make the change, and then enable cdc on the table again.
This method results in loosing the previous history on the table and I was wondering if there is a way to preserve the previous history?
Any help appreciated,
Thanks
Erich

Viewing 5 posts - 1 through 4 (of 4 total)

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

Randy Emerson SSC Veteran Points: 246 More actions · Answer 1

We ran into similar situation, and this is what we are doing.

A table can have two capture instances for CDC. So we add a second capture instance of CDC for the table in question, copy data from the first capture instance to second capture instance. Finally disable the first capture instance.

The second capture instance of CDC should be enabled after the schema changes are made to the table.

erichbrinker Say Hey Kid Points: 693 More actions · Answer 2

Thanks for your help! So you just move the data from the first system table created with the first capture instance into the new 2nd table?

R.P.Rozema SSChampion Points: 12317 More actions · Answer 3

I know, I'm a bit late to the table. But no: you're not the owner of the data in the _CT tables, the CDC mechanism is, so you should not modify the data in these tables.

You should indeed -as suggested- create a 2nd capture instance on the same source table. But instead of trying to move the historic change data over into that capture instance's _CT table, move your process over. In other words, leave your old capture instance enabled while you enable a 2nd capture instance on the same table. Leave this situation running for some time and keep your client processing from the 1st capture instance until you are sure it has processed all data from the old capture instance until at least the min_lsn (sys.fn_cdc_get_min_lsn() of the 2nd instance has been processed. Only then, you stop your import process and change it to read from the 2nd capture instance, restarting the process from the lsn it stopped at in the 1st capture instance (just like you normally would do in an incremental load process). After all processes reading the same source table have been switched over from the 1st capture instance to the 2nd capture instance you can disable the 1st capture instance and you've successfully made the schema change effective without losing any changes.

Posting Data Etiquette - Jeff Moden[/url]
Posting Performance Based Questions - Gail Shaw[/url]
Hidden RBAR - Jeff Moden[/url]
Cross Tabs and Pivots - Jeff Moden[/url]
Catch-all queries - Gail Shaw[/url]

If you don't have time to do it right, when will you have time to do it over?

terisacol3 Newbie Points: 0 More actions · Answer 4

Carbohydrates also are essential meals that build muscle XtrCut[/url] - or alternatively carbs are important additives of meals that construct muscle. pick ingredients which might be high in carbohydrate that are also excessive in fibre - oatmeal, rice, and potato are all exact. http://www.strongtesterone.com/xtrcut/