SQL Server 变更数据捕获(CDC)

概述  

变更数据捕获用于捕获应用到 SQL Server 表中的插入、更新和删除活动，并以易于使用的关系格式提供这些变更的详细信息。变更数据捕获所使用的更改表中包含镜像所跟踪源表列结构的列，同时还包含了解所发生的变更所需的元数据。

对表开启了变更捕获之后，对该表的所有DML和DDL操作都会被记录，有助于跟踪表的变化。

测试环境： SQL SERVER 2008 R2,案例库AdventureWorks2008R2

目录

•  概述
•  配置变更数据捕获
  •  启动数据库捕获
  •  设置跟踪表
  •  数据库的变化
•  对象分析
  •  插入测试数据
  •  分析系统表
  •  分析存储过程
  •  分析函数
  •  分析系统视图
•  总结

配置变更数据捕获

启动数据库捕获

--开启cdc
USE [AdventureWorks2008R2]
EXECUTE sys.sp_cdc_enable_db
GO
---如果开启数据库捕获报错误:15517，使用下面的方法处理
ALTER AUTHORIZATION ON DATABASE::[AdventureWorks2008R2] TO [sa]

---查看是否设置成功，1代表开启cdc
SELECT is_cdc_enabled  FROM SYS.databases WHERE name='AdventureWorks2008R2'

设置跟踪表

为当前数据库中指定的源表启用变更数据捕获。对表启用变更数据捕获时，应用于此表的每个数据操纵语言 (DML) 操作的记录都将写入事务日志中。变更数据捕获进程将从日志中检索此信息，并将其写入可通过使用一组函数访问的更改表中。

格式：

sys.sp_cdc_enable_table 
    [ @source_schema = ] 'source_schema', ---表所属的架构名
    [ @source_name = ] 'source_name' ,----表名
    [ @role_name = ] 'role_name'---是用于控制更改数据访问的数据库角色的名称。
    [,[ @capture_instance = ] 'capture_instance' ]--是用于命名变更数据捕获对象的捕获实例的名称，这个名称在后面的存储过程和函数中需要经常用到。
    [,[ @supports_net_changes = ] supports_net_changes ]---指示是否对此捕获实例启用净更改查询支持如果此表有主键，或者有已使用 @index_name 参数进行标识的索引，则此参数的默认值为 1。否则，此参数默认为 。
    [,[ @index_name = ] 'index_name' ]--用于标识源表中的行的索引的名称。index_name 为 sysname，并且可以为 NULL。如果指定，则 index_name 必须是源表的有效索引。如果指定 index_name，则标识的索引列优先于任何定义的主键列，就像表的行标识符一样。
    [,[ @captured_column_list = ] 'captured_column_list' ]--需要对哪些列进行捕获。captured_column_list 的数据类型为 nvarchar(max)，并且可以为 NULL。如果为 NULL，则所有列都将包括在更改表中。
    [,[ @filegroup_name = ] 'filegroup_name' ]--是要用于为捕获实例创建的更改表的文件组。
  [,[ @partition_switch = ] 'partition_switch' ]--指示是否可以对启用了变更数据捕获的表执行 ALTER TABLE 的 SWITCH PARTITION 命令。allow_partition_switch 为 bit，默认值为 1。

注意:

对表启用变更数据捕获时，将生成一个更改表以及一个或两个查询函数。更改表充当捕获进程从事务日志中提取的源表更改的存储库。查询函数则用于从更改表中提取数据。这些函数的名称按以下方式派生自 capture_instance 参数：

所有更改函数： cdc.fn_cdc_get_all_changes_ < capture_instance >
净更改函数： cdc.fn_cdc_get_net_changes_ < capture_instance >

1.如果源表是数据库中个要启用变更数据捕获的表，并且数据库不存在事务发布，则 sys.sp_cdc_enable_table 还将为数据库创建捕获和清理作业。
它将 sys.tables 目录视图中的 is_tracked_by_cdc 列设置为 1。
2.对表启用变更数据捕获时，SQL Server 代理不必正在运行。但是，只有当 SQL Server 代理正在运行时，捕获进程才会处理事务日志并将条目写入更改表。

USE AdventureWorks2008R2;
GO
EXEC sys.sp_cdc_enable_table
    @source_schema = N'HumanResources'
  , @source_name = N'Department'
  , @role_name = N'cdc_admin'--增加的角色
  , @capture_instance = N'HR_Department'--实例名 
  , @supports_net_changes = 1
  , @index_name = N'AK_Department_Name' 
  , @captured_column_list = N'Name, GroupName'--跟踪的字段
  , @filegroup_name = N'PRIMARY';
GO
---查询表是否启动跟踪,值为1标示已启动
SELECT is_tracked_by_cdc  FROM SYS.tables WHERE name='Department'

数据库的变化

•  增加的系统表

• 增加的系统视图

• 增加的系统存储过程

• 增加的函数