加入收藏 | 设为首页 | 会员中心 | 我要投稿 李大同 （https://www.lidatong.com.cn/）- 科技、建站、经验、云计算、5G、大数据,站长网!
当前位置： 首页 > 站长学院 > MsSql教程 > 正文

SQLServer CDC实现数据变更捕获

发布时间：2020-12-12 13:11:52 所属栏目：MsSql教程 来源：网络整理
导读：背景 ? ? ? ??在SQL Server2008之前，对数据变更的捕获通常使用触发器、时间戳等低效高成本的功能来实现，所以很多系统都没有做数据变更或者仅仅对核心表做监控。有了CDC， 则查看数据变更捕获就一目了然了（包括DDL的变更）。一直很想写一篇关于CDC的文章，

列名

数据类型

说明

__$start_lsn

binary(10)

更改提交的LSN。在同一事务中提交的更改将共享同一个提交 LSN 值。

__$seqval

binary(10)

一个事务内可能有多个更改发生，这个值用于对它们进行排序。

__$operation

Int

更改操作的类型：

1 = 删除

2 = 插入

3 = 更新（捕获的列值是执行更新操作前的值）。

4 = 更新（捕获的列值是执行更新操作后的值）。

__$update_mask

varbinary(128)

位掩码，源表中被CDC跟踪的每一列对应一个位。如果__$operation?= 1 或 2，该值将所有已定义的位设置为 1。如果= 3 或 4，则只有那些对应已更改列的位设置为 1。


验证DDL

ALTER TABLE dbo.myUser ADD remark NVARCHAR(20) NOT NULL DEFAULT(0)
GO
SELECT * FROM cdc.ddl_history



相关脚本

--1. 返回所有表的变更捕获配置信息
EXECUTE sys.sp_cdc_help_change_data_capture;
--2. 查看对某个实例(即表)的哪些列做了捕获监控:
EXEC sys.sp_cdc_get_captured_columns @capture_instance='dbo_myUser'
--3. 查找配置信息：
SELECT * FROM msdb.dbo.cdc_jobs




--4. 查看配置
EXEC sp_cdc_help_jobs




--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



--6. 删除作业：（暂不执行）
EXEC sys.sp_cdc_drop_job@job_type = N'cleanup' -- nvarchar(20)
GO

--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_myUser_CT




性能测试


由于可见，CDC是异步读取日志的，捕获数据变更会有延迟。

?测试SQL：

/* --清空初始数据，可省略
EXECUTE sys.sp_cdc_disable_table 
    @source_schema = N'dbo',@capture_instance = 'dbo_myUser'

TRUNCATE TABLE myUser

EXECUTE sys.sp_cdc_enable_table
    @source_schema = N'dbo',@capture_instance=NULL
*/
--定义相关变量
SET NOCOUNT ON
DECLARE @i INT,@iMax INT,@cdcCount INT,@insertBeginTime DATETIME,@insertEndTime DATETIME,@cdcInsertEndTime DATETIME,@updateEndTime DATETIME,@cdcUpdateEndTime DATETIME,@deleteEndTime DATETIME,@cdcDeleteEndTime DATETIME
		
SET @i = 1
SET @iMax = 10000
SELECT @cdcCount=COUNT(1) FROM cdc.dbo_myUser_CT WITH(NOLOCK)

SET @insertBeginTime=GETDATE()
--1. insert
WHILE @i<=@iMax
BEGIN
	INSERT INTO myUser(	userName ) VALUES ( SUBSTRING(CAST(NEWID() AS VARCHAR(50)),20 ) )
	SET @i = @i + 1	
END
SET @insertEndTime=GETDATE()

WHILE (SELECT COUNT(1) FROM cdc.dbo_myUser_CT WITH (NOLOCK))-@cdcCount != @iMax
BEGIN
	WAITFOR DELAY '0:0:0.01'
END
SET @cdcInsertEndTime=GETDATE()

--2. update
;WITH t AS (
	SELECT ROW_NUMBER() OVER (ORDER BY id DESC) rowNum,id FROM dbo.myUser	
)
UPDATE myUser SET userName = substring(userName,10)+'_Update' WHERE id IN (
	SELECT id FROM t WHERE rowNum<=@iMax	
)
SET @updateEndTime=GETDATE()

WHILE (SELECT COUNT(1) FROM cdc.dbo_myUser_CT WITH (NOLOCK))-@cdcCount != 3*@iMax
BEGIN
	WAITFOR DELAY '0:0:0.01'
END
SET @cdcUpdateEndTime=GETDATE()

--3. delete
;WITH t AS (
	SELECT ROW_NUMBER() OVER (ORDER BY id DESC) rowNum,id FROM dbo.myUser	
)
DELETE myUser WHERE id IN (
	SELECT id FROM t WHERE rowNum<=@iMax	
)
SET @deleteEndTime=GETDATE()

WHILE (SELECT COUNT(1) FROM cdc.dbo_myUser_CT WITH (NOLOCK))-@cdcCount != 4*@iMax
BEGIN
	WAITFOR DELAY '0:0:0.01'
END
SET @cdcDeleteEndTime=GETDATE()

SELECT @insertBeginTime AS insertBeginTime,@insertEndTime AS insertEndTime,@cdcInsertEndTime AS cdcInsertEndTime,@updateEndTime AS updateEndTime,@cdcUpdateEndTime AS cdcUpdateEndTime,@deleteEndTime AS deleteEndTime,@cdcDeleteEndTime AS cdcDeleteEndTime

SELECT DATEDIFF(ms,@insertBeginTime,@insertEndTime)/1000.0 AS insertElapsedSeconds,DATEDIFF(ms,@insertEndTime,@cdcInsertEndTime)/1000.0 AS cdcInsertElapsedSeconds,@cdcInsertEndTime,@updateEndTime)/1000.0 AS updateElapsedSeconds,@updateEndTime,@cdcUpdateEndTime)/1000.0 AS cdcUpdateElapsedSeconds,@cdcUpdateEndTime,@deleteEndTime)/1000.0 AS deleteElapsedSeconds,@deleteEndTime,@cdcDeleteEndTime)/1000.0 AS cdcDeletedElapsedSeconds


对源表性能影响

?

无cdc

1个cdc实例

2个cdc实例

Insert

6.382

8.167（-27.96%）

8.168（-27.98%）

Update

6.502

7.547（-16.07%）

8.324（-28.02%）

Select

6.482

6.221（持平）

6.227（持平）

Delete

6.524

7.138（-9.4%）

7.248（-11.1%）


均由SQL Query Stress 工具测试。100个线程，每个线程运行100次。

结论：

CDC对表的增、删、改有影响，对查询无影响。

?

测试SQL:

1.??????insert

INSERT INTO myUser(???? userName ) VALUES ( SUBSTRING(CAST(NEWID() AS VARCHAR(50)),20) )

2.??????update

UPDATE [dbo].[myUser] SET userName='test'WHERE id=cast(ceiling(rand() * 10000) as int)

3.??????select

select top 50 * from dbo.myUser WHEREid=cast(ceiling(rand() * 10000) as int)

4.??????delete

delete from dbo.myUser WHERE id=cast(ceiling(rand()* 10000) as int)

?

以下是开启两个捕获实例的脚本 
--禁用本表中的所有捕获实例
DECLARE @t TABLE (rowNum INT IDENTITY(1,1),capture_instance NVARCHAR(200))
INSERT INTO @t (capture_instance) 
SELECT capture_instance FROM cdc.change_tables WHERE source_object_id=object_id('dbo.myUser')

DECLARE @i INT,@captureInstance NVARCHAR(200)
SELECT @i=1,@iMax=ISNULL(MAX(rowNum),0) FROM @t

WHILE @i<=@iMax
BEGIN
	SELECT @captureInstance=capture_instance FROM @t WHERE rowNum=@i
	EXECUTE sys.sp_cdc_disable_table 
		@source_schema = N'dbo',@capture_instance = @captureInstance
	SET @i=@i+1
END
--开启两个实例
EXECUTE sys.sp_cdc_enable_table
    @source_schema = N'dbo',@role_name = 'cdc_Admin',@capture_instance='dbo_myUser_sync'

EXECUTE sys.sp_cdc_enable_table
    @source_schema = N'dbo',@capture_instance='dbo_myUser_cache'

--查看当前表拥有的实例
EXEC sys.sp_cdc_help_change_data_capture


参考文摘：

https://technet.microsoft.com/zh-cn/library/cc645937.aspx

http://www.voidcn.com/article/p-gpprurdu-ov.html

http://www.cnblogs.com/Joe-T/p/4312806.html

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!

背景

? ? ? ??在SQL Server2008之前，对数据变更的捕获通常使用触发器、时间戳等低效高成本的功能来实现，所以很多系统都没有做数据变更或者仅仅对核心表做监控。有了CDC， 则查看数据变更捕获就一目了然了（包括DDL的变更）。一直很想写一篇关于CDC的文章， 网上的也够多了， 但我这篇重在测试其实用性吧。


原理


1.??????CDC有一个独立的进程的。它是异步地读取日志文件。如果某部分更改没有被进程读到，那么此时日志截断也是没有效果的，很显然需要这样来保证。

2.??????多次更新同一行的某一列数据，只返回最后更新的结果。

3.??????CDC有两个作业：捕获作业和清除作业。捕获操作由捕获作业完成，每个扫描周期最多可处理 1000 个事务，并在两个周期之间停顿 5 秒钟。为了变更日志表不至于过大，还有一个清除作业（默认三天（4320分钟）清除一次，凌晨2点执行）。如果希望能手动控制清除，应禁用清除作业。


注意事项

1．? SQL Server的版本必须是2008或以上；

2．? 不能同时使用内存优化表（SQL Server2014或以上版本才有的功能）。否则会出现以下错误：


3.? @@SERVERNAME、serverproperty('servername')两者（本地服务器名和服务器实例的属性必须一致）必须一致。下面脚本可将两者调整成一致。如果执行后两者仍不一致，需要重启SQL Server服务。

if serverproperty('servername') <> @@servername 
begin 
    declare @server sysname 
    set @server = @@servername 
    exec sp_dropserver@server =@server 
    set @server = cast(serverproperty('servername') as sysname) 
    exec sp_addserver@server = @server,@local = 'LOCAL' 
    PRINT 'ok' 
end 
 
select @@SERVERNAME,serverproperty('servername')

4.??????必须开启SQL Sever代理服务。CDC功能必须通过作业来实现。

5.??????开启CDC功能的表，无法使用 TRUNCATE TABLE 。可以先禁用，执行完truncate再启用cdc。

6.??????如果表结构发生变化，则捕获实例表中：新增列无法捕获到、删除列保持NULL、修改列类型会发生强制转换。为保险起见，应禁用捕获实例，然后再启用。

7.??????在查询CDC相关表时，建议加上With(NOLOCK)，否则易产生阻塞或死锁。

8.??????一个表最多只能有两个捕获实例。

如果更新表时并未实际修改值，则不会有产生捕获（对应的捕获实例表不会增加相应的行）。


表准备

Use cdcTest
	GO
IF OBJECT_ID('dbo.myUser','U') IS NOT NULL
BEGIN
	DROP TABLE dbo.myUser
END
GO
CREATE TABLE dbo.myUser(
	id INT IDENTITY(1,1)  PRIMARY KEY,userName NVARCHAR(20)	
)
GO

实现数据变更捕获

1．? 对目标库显式启用CDC：

USE cdcTest
GO
EXECUTE sys.sp_cdc_enable_db;
GO

如果有错误号 15517 的错误。（某个/些存储过程使用了具有WITH EXECUTEAS 的选项）

可执行：

ALTER AUTHORIZATION ON DATABASE::[cdcTest] TO [sa]


查看是否启用：

SELECT  is_cdc_enabled,CASE WHEN is_cdc_enabled=0 THEN 'CDC功能禁用'ELSE 'CDC功能启用'END [描述]
FROM    sys.databases
WHERE   [name]='cdcTest'

创建成功后，会在测试库自动添加CDC用户和架构。


2. ?对目标表启用CDC:

EXECUTE sys.sp_cdc_enable_table
    @source_schema = N'dbo',@source_name = N'myUser',@role_name = 'cdc_Admin'--会自动生成自定义 'cdc_Admin' 角色 如果不想控制访问角色，则@role_name必须显式设置为null,@capture_instance=NULL

如出现以下错误，请参考上面注意事项中的第3点。 




--查看是否已启用：
SELECT  name,is_tracked_by_cdc,CASE WHEN is_tracked_by_cdc = 0 THEN 'CDC功能禁用'
             ELSE 'CDC功能启用'
        END 描述
FROM    sys.tables
WHERE   OBJECT_ID= OBJECT_ID('myUser')

--如何禁用？
--表级禁用
EXECUTE sys.sp_cdc_disable_table 
    @source_schema = N'dbo',@capture_instance = 'dbo_myUser'
--数据库级禁用
USE cdcTest
GO 
EXEC sys.sp_cdc_disable_db 
GO


验证DML

SELECT COUNT(1) AS '原总行数' FROM myUser
/* 
原总行数
0
*/
--1. Insert 插入5条数据
INSERT INTO myUser(	userName ) VALUES ( SUBSTRING(CAST(NEWID() AS VARCHAR(50)),1,20 ) )
GO 5
--2. Update 
UPDATE myUser SET userName = substring(userName,10)+'_Update'
--3. Delete
DELETE FROM myUser WHERE id>4

--查看捕获到的数据变更信息
SELECT * FROM cdc.dbo_myUser_CT



    相关内容
      推荐文章
        站长推荐
        热点阅读

          【免责声明】本站内容转载自互联网，其发布内容言论不代表本站观点，如果其链接、内容的侵犯您的权益，烦请提交相关链接至邮箱bqsm@foxmail.com我们将及时予以处理。

          建议您使用1920×1080分辨率、谷歌浏览器Google Chrome、Microsoft Edge以获得本站的最佳浏览效果

          Copygight © 2008-2022 https://www.lidatong.com.cn/ All Rights Reserved. 李大同