I'm looking for a strategy for extracting data from a SQL Server database that is configured for change data capture (CDC) using SSIS.
Most of the documentation I have seen around using CDC and SSIS are simple scenarios where there is one source table configured with CDS, a conditional split to determine the inserts, updates and deletes and then those corresponding actions.
My current extract consists of queries that join multiple tables together and then feeds that into the transform and load. Changes could happen in any of those tables, not just one.
One approach could be to just look for changes on the base table and do lookups or merges on the other tables. However that would miss changes on a supporting table if there is no corresponding change in the source table.
Another option would be to do individual extracts on all tables involved and then do the joining in the transformation layer. But again, there could be times that one or more tables isn't changed and isn't available for the join.
What I'm leaning towards is one of these 2 options:
- Totally separate all tables into their own ETL that then do multiple inserts and updates on the destination table. For example, only process inserts for the source table of the query and process updates for all the supporting tables after the source table has been populated.
- Store all previous extracts for a table in a versioned directory in the spirit of a "data lake" and then look up the related record from a previous version.
Is there a strategy that I'm not aware of?
I have used the CDC functions to extract data in the past. My plan was to try to use the SSIS CDC components themselves if possible.
My main reason for looking into this is to increase the frequency of our loads to be on an hourly basis instead of a nightly basis. Using CDC on my biggest tables would reduce the load, and reduce the time needed. It would still be ideal to only pull the data that has changed no matter the size.
We also have the ability to add a read only secondary to our AG to scale out reads similar to what Dave describes in his answer. However, the time taken to pull all the data out would still be the same even though the same load wouldn't be on the source system.