Post reply

Feedback CDC change data capture

  • April 13, 2018 at 9:50 pm

    #410416

    World like to get feedback from folks who are using change data capture to track DML. Currently we use triggers but it is painful till maintain those .

  • April 14, 2018 at 4:02 am

    #1986850

    CDC is a nice feature to track changes. We have enabled it on one of our primary database on our production server with a retention period of 3 days. We extract the changed records using SSIS dtsx package and save them to a different server(Server 2) where it will collect all the records from the day we enabled CDC on our production server. All the other business processes that needs to track changes, like, data warehouse jobs will pull the data from Server 2.

    We never experienced any issues with CDC except that the database grows very fast because it captures 1 record as INSERT and 2 records for an UPDATE operation on the source.
    The CDC process saves changed data records it in its own schema called CDC. For example, if a table is enabled with CDC any record inserted, updated or deleted are captured along with attributes $start_lsn and $_operation code (there are few additional as well), the common values for $_operation are 1,2,3 and 4, Where 1 is delete, 2 is insert, 3 is before update and 4 is after update of a source record. To make this process operational, it requires a better server with enough RAM and huge disk space, and will definitely benefit in the long run. In my opinion, this feature is lot better for tracking data changes when compared to triggers.

    -- CDC enabled database can be restored on other instances without any issues.
    -- Can be enabled or disabled easily with few DDL statements.
    -- Creates its own schema, functions and SQL Agent Jobs by default.

    Good Luck.

    =======================================================================

  • April 15, 2018 at 7:19 pm

    #1986876

    IrfanHyd - Saturday, April 14, 2018 4:02 AM

    -- CDC enabled database can be restored on other instances without any issues.

    If you use KEEP_CDC option. 😉

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply