SQLServer2008提供一套半公开的LogAudit机制--ChangeDataCapture

　　SQLServer2008提供一套半公开的LogAudit机制--ChangeDataCapture

　　对于大多数 DBA 来说，数据更新监控并不容易。 SQL Server 2008 提供了一种半公开的日志审计机制——变更数据捕获，将为我们解决这个问题。

　　在常见的企业数据平台管理中有一个一直困扰着SQL Server DBA的任务，那就是数据更新的监控。许多数据应用程序需要捕获对业务数据表的更新。我已经看到了几种解决方案：

　　1、在数据表中添加特殊标志列；

　　2、通过在数据表上创建触发器；

　　3、通过第三方产品，例如 Lumigent 的日志浏览器。

　　其实第一种和第二种方案都不好，因为第一种方法在编写应用程序的时候需要特别小心。如果有一条数据访问逻辑忘记更新标志位，就会错过一些数据更新。 ，而第二种方法对性能的影响太明显了，因为触发器的性能开销是众所周知的。第三种方法其实属于一种叫做Log Audit的方案系统。因为SQL Server和其他关系型数据库一样，所有的数据操作都会记录在日志中，所以通过分析日志可以得到完整的数据操作历史。 SQL Server其实对于日志审计方案有ISV开发者的内部API，但是微软对这组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是一个比较高端的功能，所以CDC功能只能在SQL Server 2008的企业版、开发版和评估版中找到。

　　在数据库级别启用 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、capture_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架构中创建一个数据表来记录从日志中解析出来的更新历史信息。

　　[编辑推荐]

　　sql注入通过函数判断数据库类型详解

　　获取系统日期的SQL函数

　　sql函数遍历BOM表的方法

　　判断闰年的SQL函数

　　删除指定字符串前后字符串的SQL函数