none
Implementing CDC for History tracking

    问题

  • Friends,

    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

    Murali

    2012年3月1日 4:24

答案

  • Hi,

    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,

    http://social.msdn.microsoft.com/Forums/en/sqlintegrationservices/thread/7597eea9-fab2-477b-a2a1-25ace9350ae0


    Thanks,
    Suresh
    Please use Mark or Propose as Answer if my post solved your problem and use Vote As Helpful if a post was useful.


    • 已编辑 svgSuresh 2012年3月1日 8:25 added the link for CDC
    • 已标记为答案 KJian_ 2012年3月7日 6:46
    2012年3月1日 8:22
  • 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:

    http://gallery.technet.microsoft.com/How-to-get-information-eeb542d2

    http://arbibaghdanian.blogspot.com/2011/04/change-data-capture-and-track-changes_04.html


    Best Wishes, Arbi; Please vote if you find this posting was helpful or Mark it as answered.




    2012年3月6日 23:31

全部回复

  • Hi,

    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,

    http://social.msdn.microsoft.com/Forums/en/sqlintegrationservices/thread/7597eea9-fab2-477b-a2a1-25ace9350ae0


    Thanks,
    Suresh
    Please use Mark or Propose as Answer if my post solved your problem and use Vote As Helpful if a post was useful.


    • 已编辑 svgSuresh 2012年3月1日 8:25 added the link for CDC
    • 已标记为答案 KJian_ 2012年3月7日 6:46
    2012年3月1日 8:22
  • 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:

    http://gallery.technet.microsoft.com/How-to-get-information-eeb542d2

    http://arbibaghdanian.blogspot.com/2011/04/change-data-capture-and-track-changes_04.html


    Best Wishes, Arbi; Please vote if you find this posting was helpful or Mark it as answered.




    2012年3月6日 23:31