I'm learning 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 wondering how best to handle the following scenario. Let's say a user enters a new row in 'A' and then a child row in 'B'. When I go to move this data to my target DB, I need to make sure I move the data in the correct order or I'll get
a FK constraint error in the target DB.
The only way I know to do this is as follows:
1) Query the cdc.lsn_time_mapping table and get all the new LSN values in ascending order.
2) Look for each LSN value in each cdc table by executing the fn_cdc_get_all_changes_??? UDF associated with each cdc table
3) When an LSN value is found in a table, move the data in that to the target DB
4) Repeat #2 until done
With only two tables, as in this example, this is a trivial process. But if I have hundreds of tables with CDC enabled, step #2 could really be slow since I could be quering every CDC enabled table looking for the curre
View Complete Post