menu

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.
EXEC SYS.SP_CDC_ENABLE_TABLE @SOURCE_SCHEMA=N'dbo',
@SOURCE_NAME=N'Sales', @ROLE_NAME=NULL

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