We're about to start using CDC to track changes to a large number of tables in our db's. But since our datamodel is rather frequently updated, I'm wondering what is the best method for easily keeping CDC in a working order, also considering that during the update there are a bunch of standardized DML operations we don't want to keep any track of.
So I'd just like to run my current idea for doing things by you guys, and if you have better ideas, I'd like to hear how to simplify and optimize the process?
- Enabling CDC for the first time, we have a script that will dynamically find the schemas from a list of all the auditable tables, and create new audit tables of those schemas (one audit table for each table tracked by CDC).
- We then dynamically create a stored procedure that will read the data from CDC and transfer all the new rows to those audit tables, doing whatever necessary modifications on the way.
- Then we manually update the CDC_Cleanup agent job with a new first step to always run the procedure before cleaning data, as well as create a new job that will run that procedure every X minutes.
- Finally we create a DDL trigger on the database checking every ALTER / DROP TABLE command against the list of CDC tables, so as to prevent changes that could break CDC, and return an error warning of said issue.
Now the actual question concerns the datamodel updates (ie. when a new software version is released). This is the part where it gets tricky. The following is an idea of how I thought I would go about this.
- Exec the stored procedure and add all latest CDC records to the audit tables (there is no traffic at this point, as the external services are off).
- Disable CDC on database OR disable CDC on each table (whichever is fastest), and disable the timed job (tricky because with alwaysOn we need to do this on every replica, unless we make some kind of an SSIS job using the listener to call the procedure on the primary replica only)
- Run all the data modification scripts (updates, inserts etc)
- Update the datamodel
- Run a manually maintained script, which will update the audit tables schemas to match the real tables they're keeping track of.
- Enable CDC on the tables, at this point the real tables, CDC tables and audit tables should all have matching schemas.
- Recreate the stored procedure from the updated definitions dynamically from the primary tables.
- Modify & enable the CDC jobs and re-enable the DDL trigger again.
Now, this seems like a huge hassle with a lot of manual labor, especially considering we have multiple different environments to manage.
Is there any way to change this to a more dynamic approach? Can you see any obvious flaws or ways to optimize this whole process?
Basically, we just want to disable CDC before changes, enable it after, and make sure all the tables tracked by CDC don't break up even if their schemas were altered?