SQL Server 多表数据增量获取和发布 2.3
一、验证DML SELECT COUNT(1) AS '原总行数' FROM dbo.Person /* 原总行数 0 */ --1. Insert 插入5条数据 INSERT INTO Department( Name ) VALUES ('部门0000000009') GO 5 --2. Update UPDATE Department SET Name = substring(Name,0,10)+'_Update' --3. Delete DELETE FROM Department WHERE id>4 --查看捕获到的数据变更信息 SELECT * FROM cdc.dbo_Department_CT 列名 数据类型 说明 __$start_lsn binary(10) 更改提交的LSN。在同一事务中提交的更改将共享同一个提交 LSN 值。 __$seqval binary(10) 一个事务内可能有多个更改发生，这个值用于对它们进行排序。 __$operation Int 更改操作的类型：1 = 删除2 = 插入3 = 更新（捕获的列值是执行更新操作前的值）。4 = 更新（捕获的列值是执行更新操作后的值）。 __$update_mask varbinary(128) 位掩码，源表中被CDC跟踪的每一列对应一个位。如果__operation = 3 或 4，则只有那些对应已更改列的位设置为 1。 image.png 二、验证DDL ALTER TABLE dbo.Department ADD remark NVARCHAR(20) NOT NULL DEFAULT(0) image.png 三、相关脚本 --1. 返回所有表的变更捕获配置信息 EXECUTE sys.sp_cdc_help_change_data_capture; --2. 查看对某个实例(即表)的哪些列做了捕获监控: EXEC sys.sp_cdc_get_captured_columns @capture_instance='dbo_Department' --3. 查找配置信息： SELECT * FROM msdb.dbo.cdc_jobs image.png --4. 查看配置 EXEC sp_cdc_help_jobs image.png --5. -------------------- 修改配置 ---------------------- --显示原有配置： EXEC sp_cdc_help_jobs GO --更改数据保留时间为24*60分钟 （默认4320） EXECUTE sys.sp_cdc_change_job @job_type = N'cleanup', @retention=1440 GO --停用作业 EXEC sys.sp_cdc_stop_job N'cleanup' GO --启用作业 EXEC sys.sp_cdc_start_job N'cleanup' GO --再次查看 EXEC sp_cdc_help_jobs GO image.png --7. 最近进行的会话的平均滞后时间 SELECT latency FROM sys.dm_cdc_log_scan_sessions WHERE session_id = 0 --8. 最近会话的平均吞吐量 ( 每个会话期间每秒处理的平均命令数 ) SELECT command_count/duration AS [Throughput] FROM sys.dm_cdc_log_scan_sessions WHERE session_id = 0 --9. 使用 sys.fn_cdc_map_lsn_to_time 函数.( Sys.fn_cdc_map_time_to_lsn 略 ) SELECT [__$operation] , CASE [__$operation] WHEN 1 THEN '删除' WHEN 2 THEN '插入' WHEN 3 THEN '更新（捕获的列值是执行更新操作前的值）' WHEN 4 THEN '更新（捕获的列值是执行更新操作后的值）' END [类型], sys.fn_cdc_map_lsn_to_time([__$start_lsn]) [更改时间] , * FROM cdc.dbo_Department_CT image.png --10.按照时间范围查询CDC结果 DECLARE @from_lsn BINARY(10),@end_lsn BINARY(10) DECLARE @start_time DATETIME = '2018-08-01' DECLARE @end_time DATETIME ='2018-08-30' SELECT @from_lsn=sys.fn_cdc_map_time_to_lsn('smallest greater than or equal',@start_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_Department(@from_lsn,@end_lsn,'all') image.png
