SQLServer CDC实现数据变更捕获

背景

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


原理


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

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

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


注意事项

1.  SQL Server的版本必须是2008或以上;

2.  不能同时使用内存优化表(SQL Server2014或以上版本才有的功能)。否则会出现以下错误:


3.  @@SERVERNAMEserverproperty('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', 
    @source_name = N'myUser',
    @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,0,10)+'_Update'
--3. Delete
DELETE FROM myUser WHERE id>4

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



列名

数据类型

说明

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


验证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', 
    @source_name = N'myUser',
    @capture_instance = 'dbo_myUser'

TRUNCATE TABLE myUser

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
*/
--定义相关变量
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)),1,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,0,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
	,DATEDIFF(ms, @cdcInsertEndTime, @updateEndTime)/1000.0 AS updateElapsedSeconds
	,DATEDIFF(ms, @updateEndTime, @cdcUpdateEndTime)/1000.0 AS cdcUpdateElapsedSeconds
	,DATEDIFF(ms, @cdcUpdateEndTime, @deleteEndTime)/1000.0 AS deleteElapsedSeconds
	,DATEDIFF(ms, @deleteEndTime, @cdcDeleteEndTime)/1000.0 AS cdcDeletedElapsedSeconds


对源表性能影响

 

cdc

1cdc实例

2cdc实例

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)),1,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,@iMax 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', 
		@source_name = N'myUser',
		@capture_instance = @captureInstance
	SET @i=@i+1
END
--开启两个实例
EXECUTE sys.sp_cdc_enable_table
    @source_schema = N'dbo'
  , @source_name = N'myUser'
  , @role_name = 'cdc_Admin'
  , @capture_instance='dbo_myUser_sync'

EXECUTE sys.sp_cdc_enable_table
    @source_schema = N'dbo'
  , @source_name = N'myUser'
  , @role_name = 'cdc_Admin'
  , @capture_instance='dbo_myUser_cache'

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


参考文摘:

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

http://blog.csdn.net/dba_huangzj/article/details/8130448

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

评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值