2

We are following an agile development process, which in this case means that we frequently make changes to the database schema, e.g. adding new columns, moving data to other columns, etc.

Is there a mechanism to update the CDC table to the new schema, and are there any best practices to how you deal with captured data when migrating the database schema?

  • 1
    Why are you using CDC to start with? CDC targets occasionally connected systems that would have even bigger problem on a fast changing schema. So I suspect you're using the wrong tool. What requirements do you try to address with CDC? – Remus Rusanu Jun 19 '13 at 19:59
2

The mechanism for schema changes is that you disable CDC for the table, and then re-enable it. This of course means you lose all the history, so you'll also want to build in a mechanism to offload that somewhere if you want history from before the most recent schema change.

Maybe a better answer is, once you've tested CDC initially, keep it disabled until you're comfortable that the schema is more stable.

2

I agree with Aaron that for the purpose of a development system you may want to prove and re-prove at certain intervals and perhaps disable it through transitions presenting a more stable environment at the target.

Please note that there are commercial tools out there which can readily perform this task.

Attunity, which has a full featured 'Replicate' product. Define a source DB; define a target; include or exclude desired tables (wildcards); optionally define some per-table or global transformations; and 'click' to replicate. All source data changes, as well as metadata changes will be applied on the target in near real-time. Even new tables will show up... if they match the (dynamic) table name matching criteria. It may be overkill for a development environment, but hey if that's what you need... There may well be other products that can achieve this.

Disclaimer: I work for Attunity

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.