Change Data Capture Question(s)

Question

I'm trying to get up to speed on CDC by using it to create a very simple replication process. Let's say I have CDC set up for tables 'A' and 'B', which happen to have a FK relationship where 'A' is the parent and 'B' is the child. I want to replicate changes to these two tables to another DB.

I'm thinking of creating an SSIS package (or something) that will read the CDC tables associated with 'A' and 'B' to move the changes over, on some regular basis. The problem I'm trying to find a solution for is this. It seems to me that, because there is a FK relationship between 'A' and 'B', I have to know in what order to process the changes. For example, if rows are being inserted in both tables, I probably have to insert rows into 'A' first, before 'B'. Or, if rows are being deleted from both tables, I probably have to delete rows from 'B' first and then 'A'.

So, my question is this. Since this can get enormously complex as I add more tables, how do I get around this issue?

Answer 1

That should be anon issue: you simply apply the changes in the order they where applied on the primary. Since the changes passed the validation criteria on the primary, they should pass it on the replica as well. cdc.fn_cdc_get_all_changes returns the change sequence number is _$seqval, derived from the change LSN. You simply follow this sequence number and apply the operations one by one.