一. CDC(Change Data Capture)原理
异步读取事务日志,记录DML操作发生的时间,类型和实际影响的数据变化,然后将这些数据记录到启用CDC时自动创建的表中。
二. 对比Trigger方式的实现
由于是异步读取的,因此对整体性能的影响不大,远小于通过Trigger实现的数据变化记录;
三. 应用方法
1. 数据库启用CDC
Use test
Use test
EXEC sys.sp_cdc_enable_db;
2. 启用Agent
3. 表启用CDC
EXEC sys.sp_cdc_enable_table
@source_schema = N'dbo',
@source_name = N'tb',
--跟踪的表名
@capture_instance = N'dbo_tb_col',
--本跟踪的名字
@role_name = NULL,
@captured_column_list = N'id,col1,col2';
--需要跟踪的列,如果不设置,则表示跟踪所有列
4. 启用CDC后,会创建的一些系统表
(1)cdc.captured_columns 记录所有要跟踪的列
(1)cdc.captured_columns 记录所有要跟踪的列
(2)cdc.change_tables 创建了跟踪的表
(3)cdc.<capture_instance>_CT 实际记录变更的表,每建立一个跟踪,就会自动生成一张对应的表
列名 | 描述 |
__$start_lsn | 数据变动所属的LSN |
__$end_lsn | 2012后才支持,2008中为NULL |
__$seqval | 表示该变动,是事务中第几行改动 |
__$operation | 1 = delete 2 = insert 3 = update (old values) Column data has row values before executing the update statement. 4 = update (new values) Column data has row values after executing the update statement. |
__$update_mask | 表示某列是否改动过,对应bit为1则有变动 |
所有要跟踪的列 | 例如,该表有5个要跟踪的列,那么就有5列 |
(4)cdc.ddl_history
记录DDL操作
(5)cdc.lsn_time_mapping
LSN及时间对应表
5. CDC是通过Agent实现的,因此在代理中会看到创建的作业
6. 主要的几个相关系统函数
(1)sys.fn_cdc_get_min_lsn( 'capture_instance_name' )
sys.fn_cdc_get_max_lsn( 'capture_instance_name' )
返回要跟踪的表的最大,最小 start_lsn 值
(2)cdc.fn_cdc_get_all_changes_<capture_instance> ( from_lsn , to_lsn , '<row_filter_option>' )
每一个变动返回一行
(3)cdc.fn_cdc_get_net_changes_<capture_instance> ( from_lsn , to_lsn , '<row_filter_option>' )
每一行变动返回一行