This question is based on both T-SQL as well as SSIS. We have around 120 history tables which would be loaded with the history records from the Main tables. Following is an example of a Main table and its corresponding history table
Main table - Patient
Columns - PatientID (Identity), PatientFirstName, PatientLastName, Suffix, DOB, etc...
History table - PatientHistory
Columns - PatientHistoryID(IDentity), PatientID , PatientFirstName, PatientLastName, Suffix, DOB, etc...
So almost all of the History tables would be having the same columns from the Main table along with the identity field for the history table.
At present we have After triggers for Update which pulls the records from the deleted table and inserts these records into to history table. I have been asked to implement it without triggers.
My question is - Can we implement CDC for this and is it the best option performance wise as well
This can be implemented in a simple update statement. But, You have to look after many factors.
1. Frequency and number of updates happen during certain period of time.
2. Frequency of data changes for a record/patient.
3. Data loading/avaiability and number of users accessing the data etc
You can update history table whenever you are updating main table which would be simple update statment in a single Stored proc. you can combine both updates(main table update and history table update) with in single transaction. Thats how you can implement/solve current issue. As this will not hamper any of your performance.
If you want to implement CDC, in my opinion you would be reading almost all data if you do not have any flag column that helps to identify modified records. This may put overheads on your process.
Please refer the link below for implementing CDC,
Please use Mark or Propose as Answer if my post solved your problem and use Vote As Helpful if a post was useful.
The implementation of CDC will benefit you a lot. It is NOT causing any slowness in the database that you think, it is async process. I am using CDC on a production database for nearly a year.
I am not using CDC for all the tables, lets say I have nearly 1000 tables. I am using CDC for around 100 tables. Next, when you enable CDC on a table store that in different filegroup.
Check these samples:
Best Wishes, Arbi; Please vote if you find this posting was helpful or Mark it as answered.