Saturday, September 17, 2016

Change Data Capture (CDC)

In order to enable it we have to enable on 2 level.
1.       Database Level - EXEC SYS.SP_CDC_ENABLE_DB
Once CDC enables, it creates some tables and stored procedures to keep track of records.

2.       Table Level - Before running below query first make sure that SQLServerAgent is running.

Once CDC enables on table, it creates one more table.

Insert, update and delete operations on dbo.Sales table will insert log record in cdc.dbo_Sales_CT.
1.       Blank tables

2.       Insert new records to dbo.Sales will make an entry in cdc.dbo_Sales_CT table to keep log of insert with flag 2 in __$operation column.

3.       Update a record will make 2 entry in cdc.dbo_Sales_CT to indicate old as well as new row with flag 3 and 4 respectively in __$operation column.

4.       Delete a record will make one entry in cdc.dbo_Sales_CT to indicate deleted row with flag 1 in __$operation column.

No comments:

Post a Comment