serving the solutions day and night

Pages

Friday, May 10, 2013

CDC - Change Data Capture

 Change Data Capture (CDC) records insert, update and delete activity on the table data (Audit trail for table).


The source of change data for change data capture is the SQL Server transaction log.

Enabling CDC, 2 steps process - 1) Database Level and  2) Table Level

Database Level
Run the stord procedure "exec sys.sp_cdc_enable_db"
CDC has been enabled on the database level. It created couple of tables in the system folder
To enable this functionality, the user must be a server sysadmin role.


cdc.captured_columns - it just keep the record of what kind of columns is to track
cdc.change_tables - it tracks which tables has been enabled for CDC
cdc.ddl_history
cdc.index_columns
cdc.lsn_time_mapping - long sequence number
cdc.systranschemas


It also create many stoed procdures in the system stored procedures, it starts with cdc.sp_ & sys.sp_cdc_

Table Level - Run the stored proecedure to enable CDC on the table level, to ensure that SQLServerAgenet is running
To enable this functionality, the user must be a server sysadmin or db_ownner role.

EXEC sys.sp_cdc_enable_table
@source_schema = N'dbo',
@source_name   = N'MyTable',
@role_name     = N'MyRole', --Except sysadmin or db_owner, all other users must have SELECT permission on all the captured columns of the source table or NULL not use a role
@filegroup_name = N'MyDB_CT', -- filegroup must already exist,
@supports_net_changes = 1

exec sys.sp_cdc_enable_table @source_schema= N'sales',@source_name=N'CurrencyRate', @role_name=NULL
EXEC sys.sp_cdc_enable_table @source_schema = N'dbo', @source_name=N'Customer', @role_name=NULL

It will create one more table, cdc.sales_CurrenyRate_CT and cdc.dbo_Customer_CT - it will capture all the activites of insert, update and delete for the currencydrate and customer table

If you modify the data, see the activities in the cdc.sales_CurrenyRate_CT table.
The column __$seqval can be used to order more changes that occur in the same transaction.
_$operation field - it helps to track insert, update and delete operation on the data.
The column __$operation records the operation that is associated with the change: 1 = delete, 2 = insert, 3 = update (before image), and 4 = update (after image)
The column __$update_mask is a variable bit mask with one defined bit for each captured column.

Disable
EXEC sys.sp_cdc_disable_db

EXEC sys.sp_cdc_disable_table @source_schema = N'dbo', @source_name   = N'MyTable', @capture_instance = N'dbo_MyTable'




Error 1
Msg 22830, Level 16, State 1, Procedure sp_cdc_enable_db_internal, Line 193
Could not update the metadata that indicates database AdventureWorks2012 is enabled for Change Data Capture. The failure occurred when executing the command 'SetCDCTracked(Value = 1)'. The error returned was 15517: 'Cannot execute as the database principal because the principal "dbo" does not exist, this type of principal cannot be impersonated, or you do not have permission.'. Use the action and error to determine the cause of the failure and resubmit the request.

Solution - Ran 'exec sp_changedbowner sa'  - changed the owner to 'sa' (or some other owner that is dbo),

Error 2
SQLServerAgent is not currently running so it cannot be notified of this action.

Solution - Run SQLServerAgenet

No comments: