SQLSERVER CDC 功能

CDC 常用SP:

EXEC sys.sp_cdc_enable_db;
EXEC sys.sp_cdc_enable_table @source_schema= 'dbo', @source_name = 'Patient',@role_name = null ;
--specify database's role to view cdc information, use null to allow all

EXEC sys.sp_cdc_help_change_data_capture; 
--view the table and cdc_instance

EXEC sys.sp_cdc_disable_db;
EXEC Sys.sp_cdc_disable_table @source_schema= 'dbo', @source_name = 'Patient',@capture_instance='dbo_Patient'
--close the table cdc

查询CDC 的结果:

select sys.fn_cdc_map_lsn_to_time(__$start_lsn),* from [cdc].[dbo_Patient_CT]

cdc.captured_columns	为在捕获实例中跟踪的每一列返回一行	sys.sp_cdc_get_source_columns
cdc.change_tables	为数据库中的每个更改表返回一行	sys.sp_cdc_help_change_data_capture
cdc.ddl_history	针对启用了变更数据捕获的表所做的每一数据定义语言 (DDL) 更改返回一行	sys.sp_cdc_get_ddl_history
cdc.lsn_time_mapping	为每个在更改表中存在行的事务返回一行	sys.fn_cdc_map_lsn_to_time (Transact-SQL) ,sys.fn_cdc_map_time_to_lsn (Transact-SQL)
cdc.index_column	为与更改表关联的每一索引列返回一行	sys.sp_cdc_help_change_data_capture
msdb.dbo.cdc_jobs	存储用于捕获和清除作业的变更数据捕获配置参数	NA
cdc.<capture_instance>_CT	对源表启用变更数据捕获时创建的更改表。 该表为对源表执行的每个插入和删除操作返回一行，为对源表执行的每个更新操作返回两行.capture_instance格式=SchameName_TableName	cdc.fn_cdc_get_all_changes_<capture_instance> , cdc.fn_cdc_get_net_changes_<capture_instance>

__$start_lsn	binary(10)	更改提交的LSN。在同一事务中提交的更改将共享同一个提交 LSN 值。
__$seqval	binary(10)	一个事务内可能有多个更改发生，这个值用于对它们进行排序。
__$operation	int	更改操作的类型： 1 = 删除 2 = 插入 3 = 更新（捕获的列值是执行更新操作前的值）。 4 = 更新（捕获的列值是执行更新操作后的值）。
__$update_mask	varbinary(128)	位掩码，源表中被CDC跟踪的每一列对应一个位。如果 __$operation = 1 或 2，该值将所有已定义的位设置为 1。如果__$operation = 3 或 4，则只有那些对应已更改列的位设置为 1。

sys.sp_cdc_help_jobs:

job_id	uniqueidentifier	The ID of the job.
job_type	nvarchar(20)	The type of job.
maxtrans	int	The maximum number of transactions to process in each scan cycle. maxtrans is valid only for capture jobs.
maxscans	int	The maximum number of scan cycles to execute in order to extract all rows from the log. maxscans is valid only for capture jobs.
continuous	bit	A flag indicating whether the capture job is to run continuously (1), or run in one-time mode (0). For more information, seesys.sp_cdc_add_job (Transact-SQL). continuous is valid only for capture jobs.
pollinginterval	bigint	The number of seconds between log scan cycles. pollinginterval is valid only for capture jobs.
retention	bigint	The number of minutes that change rows are to be retained in change tables. retention is valid only for cleanup jobs.
threshold	bigint	The maximum number of delete entries that can be deleted using a single statement on cleanup.