SQL Server 2008的Change Data Capture(变更数据捕获)

古城微笑少年丶 2022-03-29 11:00 279阅读 0赞

在常见的企业数据平台管理中有一项任务是一直困扰SQL Server DBA们的，这就是对数据更新的监控。很多数据应用都需要捕获对业务数据表的更新。笔者见过几种解决方案：

1、在数据表中加入特殊的标志列；  
2、通过在数据表上创建触发器；  
3、通过第三方产品，例如Lumigent的Log Explorer。

其实第1种和第2中方案都不好，因为第1种方法需要在应用程序编码的时候尤为小心，如果有一段数据访问逻辑忘了更新标志位就会导致遗漏某些数据更新，而第2种方法对性能影响过于明显，因为触发器的性能开销是众所周知的。第3种方法其实属于一种叫做Log Audit的方案体系。因为SQL Server同其他关系型数据库一样，所有数据操作都会在日志中记录，因此通过分析日志就可以获得完整的数据操作历史。SQL Server其实早就有内部的API可供ISV开发者中Log Audit的方案，不过微软对这套API控制比较严格，只有签署了一堆协议的核心级合作伙伴才能了解这套API。

因此，现对业务数据更新的跟踪在SQL Server平台上一直是一件非常头疼的事情，用户需要在投入大量开发精力和投入额外采购成本之间做出选择。幸运的事，微软终于在SQL Server 2008中提供了一套半公开的Log Audit机制，就是我们所说的Change Data Capture，我们后面简称CDC。

#### CDC的工作原理 ####

我们前面说过CDC是通过分析日志获得数据操作历史信息的，那么CDC的工作原理到底是怎么样的呢？下图可以非常贴切地说明这个功能的原理：

![1361709052_1780.JPG][]

◆当DML提交到应用数据库时，SQL Server必须写入日志，并在缓存中更新数据，然后在检查点将内存中的数据刷回数据文件。  
◆CDC的内部进程根据CDC的设置，在日志文件中提取更新历史信息，并将这些个更新信息写入对应的更新跟踪表。  
◆DBA或开发人员通过调用CDC的函数来访问更新跟踪表，提取感兴趣的更新历史信息，并通过ETL应用程序更新数据仓库。  
◆理论上面更新跟踪表事会无限制增长的，因此CDC内部有一个清理进程，在默认情况下更新跟踪信息在写入跟踪表三天后会被自动清理。

#### CDC的配置 ####

由于CDC是一项比较高端的功能，因此只有在SQL Server 2008的企业版、开发版和评估版中才能找到CDC功能。

启用数据库级别的CDC

要启用CDC功能，首先需要一个sysadmin服务器角色的成员用户激活数据库级别的CDC。

USE testdb  
GO  
EXEC sys.sp\_cdc\_enable\_db  
GO

如果想知道一个数据库是否启用了CDC功能，可以通过查询sys.databases系统目录的is\_cdc\_enabled字段。当一个数据库启用CDC功能后，SQL Server会自动在这个数据库中创建cdc架构和cdc用户，所有CDC相关的数据表和用户函数都会存放在cdc架构下。

CDC功能启用后，SQL Server会首先在cdc架构下创建五张表用于记录一些CDC的原数据，分别是ddl\_history，change\_tables，captured\_columns，index\_columns和lsn\_time\_mapping。

数据库启用了CDC后，接下来我们就需要在数据表上启用CDC了(需db\_owner角色或sysadmin的用户), 设置后，DBA可以查询sys.tables系统目录的is\_tracked\_by\_cdc字段

USE testdb  
GO

EXEC sys.sp\_cdc\_enable\_table  
@source\_schema = N'dbo',  
@source\_name   = N't\_test\_orders',  --源表名,  即将生成的"更改表"名为:  cdc.dbo\_t\_test\_orders\_CT,位于同一db下.  
@role\_name     = NULL,                        -- 需显示指定是否要限定"更改表"的访问角色.  
@filegroup\_name = N'PRIMARY',        --指定"更改表"存放的文件组，以减少对"源表"文件组的io影响  
@supports\_net\_changes = 1               --启用净更新, "源表"必须有主键或唯一分键。查询net\_changes时会merge掉中间的更新，比较耗性能  
\--忽略了一个参数就是@captured\_column\_list，这个参数可以对表中特定的某些字段启用更新跟踪。  
GO

"更改表"cdc.dbo\_t\_test\_ordrs\_CT,增加了\_$start\_lsn，\_$end\_lsn，\_$seqval，\_$operation和\_$update\_mask五个新的字段. \_$operation字段是代表DML操作类型，1是delete，2是insert，3是update的旧值，4是update的新值。  
$update\_mask字段是表示一个字段列表的掩码，那些在DML操作中被更新了的字段位为1，而没有更新的字段位为0。如更新第1列就是0x01, 更新第1，2列就是0x11.

如果"源表"新增加了列需要跟踪，需要先禁用，再启用跟踪.

USE testdb  
GO  
EXEC sys.sp\_cdc\_disable\_table  
@source\_schema = N'dbo',  
@source\_name   = N't\_test\_orders',  
@capture\_instance = N'dbo\_t\_test\_orders'       --注意不是更新表名"dbo\_t\_test\_orders\_CT", 少了CT  
GO

#### 查询和操作"更改表":  ####

sys.fn\_cdc\_get\_min\_lsn,  sys.fn\_cdc\_get\_max\_lsn(),  cdc.lsn\_time\_mapping,  sys.fn\_cdc\_map\_time\_to\_lsn**(****)**cdc.fn\_cdc\_get\_all\_changes\*, cdc.fn\_cdc\_get\_net\_changes\*

DECLARE @from\_lsn binary(10), @to\_lsn binary(10)  
SET @from\_lsn = sys.fn\_cdc\_get\_min\_lsn('dbo\_t\_test\_orders')  
SET @to\_lsn   = sys.fn\_cdc\_get\_max\_lsn()  
SELECT \* FROM cdc.fn\_cdc\_get\_all\_changes\_dbo\_t\_test\_orders(@from\_lsn, @to\_lsn, N'all')

#### CDC管理和监控 ####

监视变更数据捕获进程: select \* from sys.dm\_cdc\_log\_scan\_sessions

变更数据捕获日志扫描会话期间遇到的每个错误: sys.dm\_cdc\_errors  
**捕捉作业:sp\_MScdc\_capture\_job,   清除作业sp\_MScdc\_cleanup\_job**

#### 对部署CDC的建议 ####

◆CDC激活会显著增加日志文件的读操作。  
◆CDC激活后更新跟踪表会产生额外的写入，并消耗存储空间。  
◆CDC激活后，原数据表的聚簇索引尺寸会影响到CDC产生的IO数据量，而原始数据表上的非聚簇索引则不会。  
◆CDC激活后，被选定进行更新跟踪的列键值属性同样会影响到CDC产生的IO数据量和存储空间。

参考网址：[http://www.chinaz.com/program/2008/0604/30598\_4.shtml][http_www.chinaz.com_program_2008_0604_30598_4.shtml]

[1361709052_1780.JPG]: https://img-my.csdn.net/uploads/201302/24/1361709052_1780.JPG
[http_www.chinaz.com_program_2008_0604_30598_4.shtml]: http://www.chinaz.com/program/2008/0604/30598_4.shtml