解析SQL 2008的Change Data Capture功能_Mssql数据库教程

      在常见的企业数据平台管理中有一项任务是一直困扰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的工作原理到底是怎么样的呢？下图可以非常贴切地说明这个功能的原理：

　　图1

◆当DML提交到应用数据库时，SQL Server必须写入日志，并在缓存中更新数据，然后在检查点将内存中的数据刷回数据文件。

◆CDC的内部进程根据CDC的设置，在日志文件中提取更新历史信息，并将这些个更新信息写入对应的更新跟踪表。

◆DBA或开发人员通过调用CDC的函数来访问更新跟踪表，提取感兴趣的更新历史信息，并通过ETL应用程序更新数据仓库。

◆理论上面更新跟踪表事会无限制增长的，因此CDC内部有一个清理进程，在默认情况下更新跟踪信息在写入跟踪表三天后会被自动清理。

CDC的配置

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

启用数据库级别的CDC

要启用CDC功能，首先需要一个sysadmin服务器角色的成员用户激活数据库级别的CDC，这个过程可以通过sys.sp_cdc_enable_db_change_data_capture存储过程来完成。如果想知道一个数据库是否启用了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角色的用户可以通过存储过程sys.sp_cdc_enable_table_change_data_capture来启用对某张数据表的更新跟踪，一张数据表最多可以设置两个跟踪实例。每个跟踪实例中可以设置对原始数据表的所有列或部分列进行更新跟踪。如果想知道数据表是否进行了更新跟踪，DBA可以查询sys.tables系统目录的is_tracked_by_cdc字段。

对一张数据表启用CDC跟踪实例后，SQL Server会在cdc架构下创建一张数据表用于记录从日志中解析出来的更新历史信息。

一段CDC的评估脚本

为了评估CDC功能，我特地写了一段脚本如下：

1、首先创建一个测试数据库；

2、然后激活TestCDC数据库上的更新捕获功能；

以下为引用的内容： USE TestCDC GO EXEC sp_cdc_enable_db_change_date_capture; GO

执行了存储过程sp_cdc_enable_db_change_data_capture后，就会在数据库TestCDC中看到有一些新的表被创建了，分别是ddl_history，change_tables，captured_columns，index_columns和lsn_time_mapping，并且这5张表都是在cdc架构下。

3、然后在TestCDC数据库中创建测试表

以下为引用的内容： USE TestCDC GO CREATE TABLE dbo.Product (ProductID int PRIMARY KEY NOT NULL, ProductName nvarchar(100), Category nvarchar(50)) GO

4、在dbo.Product表上激活更新跟踪

以下为引用的内容： EX EC sp_cdc_enable_table_change_data_capture 'dbo', 'Product', @role_name= NULL, @supports_net_changes =1;

分享：看Sql server 2005 找出子表树
同事在准备新老系统的切换，清空一个表的时候往往发现这个表的主键被另一个表用做外键，而系统里有太多层次的引用.所以清起来相当麻烦 用下面这个脚本可以做到找出一个特定表的引用