启用和禁用变更数据捕获 (SQL Server)（微软的说明）

SQL Server 变更数据捕获(CDC)

概述  

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

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

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

目录

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

配置变更数据捕获

启动数据库捕获

 

--开启cdc USE [AdventureWorks] EXECUTE sys.sp_cdc_enable_db GO ---如果开启数据库捕获报错误:15517，使用下面的方法处理 --ALTER AUTHORIZATION ON DATABASE::[AdventureWorks] TO [sa] ---查看是否设置成功，1代表开启cd SELECT is_cdc_enabled FROM SYS.databases WHERE name='AdventureWorks'

 

设置跟踪表

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

格式：

 

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

注意: 对表启用变更数据捕获时，将生成一个更改表以及一个或两个查询函数。更改表充当捕获进程从事务日志中提取的源表更改的存储库。查询函数则用于从更改表中提取数据。这些函数的名称按以下方式派生自 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 AdventureWorks; 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'

 

数据库的变化

 增加的系统表

 

增加的系统视图

 

增加的系统存储过程

增加的函数

 

增加的用户与角色 

---所有cdc有关的对象 SELECT * FROM SYS.all_objects WHERE name LIKE '