Change Data Capture feature of SQL Server do not work with BAM Archiving.

This topic is related with the issue arise from change data capture (CDC feature of SQL Server) enabled for activity tables active and completed. The Bam arching/purging activity when set up stops change data capture.

We designed a solution for one of our major client where we migrate the data from BAM tables to DB2 using SSIS Package.

The SSIS Package uses the CDC feature to track the newly created data/records in the BAM active/completed tables in order to migrate these new data to DB2. Since, it is recommended that the BAM archiving/purging is much needed for BAM tables/database maintenance  to minimize the disk space usage.

As soon as we created the BAM purging activity, schedule and executed the Job, the CDC stopped working. This is because the BAM archiving/purging creates a partition table for all the activity tables by re-naming the original table, thus the original table gets renamed and the new data do not get captured in the renamed partition table, stopping the change data capture. The renamed table get replaced in the Change_Table table of CDC. This Change_Table table is a kind of master table for the CDC.

The screen print is after setting up archiving activity for BAM activity/view.

image

Conclusion, please refrain yourself by not using CDC if you are using BAM feature. Microsoft recommend to set up archiving/purging activity in order to have a control on disk space. If anyone know resolution for this issue please write back.

http://www.sharptalktech.com

Thanks,

Shadab