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跟踪的每一列对应一个位。如果__SQL Server 多表数据增量获取和发布 2.3operation = 3 或 4,则只有那些对应已更改列的位设置为 1。
SQL Server 多表数据增量获取和发布 2.3
image.png

二、验证DDL

ALTER TABLE dbo.Department ADD remark NVARCHAR(20) NOT NULL DEFAULT(0)
SQL Server 多表数据增量获取和发布 2.3
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
SQL Server 多表数据增量获取和发布 2.3
image.png
--4. 查看配置
EXEC sp_cdc_help_jobs
SQL Server 多表数据增量获取和发布 2.3
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
SQL Server 多表数据增量获取和发布 2.3
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

SQL Server 多表数据增量获取和发布 2.3
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')
SQL Server 多表数据增量获取和发布 2.3
image.png
上一篇:IIS开启GZIP压缩效率对比及部署方法


下一篇:【双11背后的技术】AliCloudDB——双11商家后台数据库的基石