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