Change Data Capture

— starting in 2008, for Enterprise Edition
— see also Change Tracking for other versions

check excelent article by Paul Randal

cdc_esquema
cdc_esquema

–1 Check if CDC is enabled in DB of the instance
USE master
GO
SELECT [name], database_id, is_cdc_enabled
FROM sys.databases
GO

–2 enable cdc on a
USE Pruebas
GO
EXEC sys.sp_cdc_enable_db
GO
— check with step 1
— schema cdc is added // SystemTables cdc.* added

— 3 enable cdc for cdc_tabl

USE Pruebas
GO
SELECT [name], is_tracked_by_cdc
FROM sys.tables
GO
— 3 Agent running?
— enable cdc on table cdc_tabl
USE Pruebas
GO
EXEC sys.sp_cdc_enable_table
@source_schema = N’dbo’,
@source_name = N’cdc_Tabl’,
@role_name = NULL
— ,@captured_column_list = ‘[id],[nnn]’ — optional
GO

— 4 check contents
select * from cdc_tabl
select * from cdc.dbo_cdc_Tabl_CT

–5 change date and check again
update cdc_tabl set nnn = ‘CHECK CDC’ where id = 3;
delete cdc_tabl where id = 2;

/*
_$update_mask 0x03 -> 0b11 :both columns modified
0x02 -> 0b10 : count from right, second column modified
*/

— check time of a change
select * from [cdc].[lsn_time_mapping]
where start_lsn = 0x00000034000062880003

— TVF created while enabling cdc for table
— Following query should do retrieve data which was modified in the past 24 hours..

USE Pruebas
GO
DECLARE @begin_time DATETIME, @end_time DATETIME,
@begin_lsn BINARY(10), @end_lsn BINARY(10);
SELECT @begin_time = GETDATE()-1, @end_time = GETDATE();
SELECT @begin_lsn = sys.fn_cdc_map_time_to_lsn(‘smallest greater than’, @begin_time);
SELECT @end_lsn = sys.fn_cdc_map_time_to_lsn(‘largest less than or equal’, @end_time);
SELECT *
FROM cdc.fn_cdc_get_all_changes_dbo_cdc_Tabl(@begin_lsn,@end_lsn,’all’)
GO

–6 Capture Instance.
USE Pruebas;
GO
EXEC sys.sp_cdc_help_change_data_capture
GO

— disable cdc on table

USE Pruebas;
GO
EXECUTE sys.sp_cdc_disable_table
@source_schema = N’dbo’,
@source_name = N’cdc_Tabl’,
@capture_instance = N’dbo_cdc_Tabl’; — from previous step
GO
— 7 disable cdc on DB
USE Pruebas
GO
EXEC sys.sp_cdc_disable_db
GO