Looking to discover a work-flow that works every time...
I have both a SS source (MS-CDC) and a SS target.
What is the proper set of steps to add a column in a source table without having to re-load the target table? Is this even possible?
I noticed that adding a column seems to propagate the column to the source table and __CT table and then table gets 'suspended'.
My method of disable/reenable replication on the table, reload source seems to work...but is there a way to avoid reloading the target? To avoid losing __ct changes, I run compose CDC capture to my DW before making any DDL changes, since I haven't found a method that eliminates the reload step.
I don't understand why the table gets suspended, when Replicate has added the column to both the target and the __CT table for the target.
What are the next steps to get the table to replicate data again?
Ticket number 00112165, have a meeting setup at 11amCDT today with Shashi.
Agreed - if no changes are made to the source while suspended, everything works as described in the first set of steps. If changes happen, then the only recourse is full reload.
The ticket is for the issue that occurs if there are changes after suspension, and then the table is un-suspended and no full load is performed. Those records changed during suspension no longer update in the target (after un-suspension), but do make entries in the __CT tables.
As discussed on the call earlier today, when the table is suspended in Replicate, any data modified during that period won't be captured. Hence suggested the following approach and it worked as expected for you:
-> Add new column to the source table -> New column applied to target table and suspended in Replicate. Keep the task running and no need to stop. -> Don't make any data changes at this point to the source table. -> Disable and enable MSCDC for the table in source DB. -> Make a change to the source record -> Unsuspend the table in Replicate, Stop the task and start from Timestamp (Time when last record was changed on source) -> Changes will be captured and applied to the target table.
Please mark this as an accepted solution to close loop on this thread.
