CDC-更改数据捕获存储过程 (Transact-SQL)-学习
背景：
在SQLServer2008之前，对数据变更的捕获通常使用触发器、时间戳等低效高成本的功能来实现，所以很多系统都没有做数据变更或者仅仅对核心表做监控。
适用环境：
仅在SQLServer2008（含）以后的企业版、开发版和评估版中可用。
官方介绍：
更改数据捕获存储过程 (Transact-SQL)
变更数据捕获使得在启用表上发生的数据操纵语言 (DML) 活动的历史记录能够以方便的关系格式提供。使用以下存储过程可以配置变更数据捕获、管理变更数据捕获代理作业和为更改数据使用者提供当前元数据。
详解：
CDC功能主要捕获SQLServer指定表的增删改操作，由于任何操作都会写日志（哪怕truncate），所以CDC的捕获来源于日志文件。日志文件会把更改应用到数据文件中，同时也会标记符合要求的数据标记为需要添加跟踪的项。然后通过一些配套函数，最后写入到数据仓库中。大概流程：
具体操作：（下面例子是项目中用到的直接贴出来）
跟一般操作一样首先要打开这个功能，然后才可以开始监控
--打开CDC USE DB_Name --数据库名称 GO EXECUTE sys.sp_cdc_enable_db; GO
检查是否打开
--检查是否成功 SELECT is_cdc_enabled, CASE WHEN is_cdc_enabled=0 THEN 'CDC功能禁用'ELSE 'CDC功能启用'END 描述 FROM sys.databases WHERE NAME = 'DB_Name'
也可以在用户和架构中查看
系统表中也会有增加
打开数据库的CDC功能之后就是打开数据库的具体业务表的监控了
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 = ] 'index_name' ] [,[ @captured_column_list = ] 'captured_column_list' ] [,[ @filegroup_name = ] 'filegroup_name' ] [,[ @partition_switch = ] 'partition_switch' ]
[ @source_schema = ] 'source_schema' 是源表所属的架构的名称。source_schema 的数据类型为 sysname，无默认值，并且不能为 NULL。 [ @source_name = ] 'source_name' 是对其启用变更数据捕获的源表的名称。source_name 的数据类型为 sysname，无默认值，并且不能为 NULL。 source_name 必须存在于当前数据库中。不能对 cdc 架构中的表启用变更数据捕获。 [ @role_name = ] 'role_name' 是用于控制更改数据访问的数据库角色的名称。role_name 为 sysname，并且必须指定。如果显式设置为 NULL，则没有控制角色用于限制对更改数据的访问。 如果当前存在该角色，则使用它。如果不存在该角色，则会尝试创建具有指定名称的数据库角色。在尝试创建该角色之前，将删除角色名称字符串右侧的空格。如果调用方无权在数据库中创建角色，则存储过程操作将失败。 [ @capture_instance = ] 'capture_instance' 是用于命名特定于实例的变更数据捕获对象的捕获实例的名称。capture_instance 为 sysname，并且不能为 NULL。 如果未指定，则该名称将从源架构名称加上源表名称中派生而来，格式为 schemaname_sourcename。capture_instance 不能超过 100 个字符，并且在数据库中必须是唯一的。不管是指定的还是派生的，都将删除 capture_instance 字符串右侧的任何空格。 源表最多可以有两个捕获实例。有关详细信息，请参阅sys.sp_cdc_help_change_data_capture (Transact-SQL)。 [ @supports_net_changes = ] supports_net_changes 指示是否对此捕获实例启用净更改查询支持。supports_net_changes 为 bit，如果此表有主键，或者有已使用 @index_name 参数进行标识的唯一索引，则此参数的默认值为 1。否则，此参数默认为 0。 如果为 0，则只生成查询所有更改的支持函数。 如果为 1，则还会生成查询净更改所需的函数。 如果将 supports_net_changes 设置为 1，则必须指定 index_name，或者源表必须具有已定义的主键。 [ @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，则所有列都将包括在更改表中。 列名称必须是源表中的有效列。必须包括在主键索引中定义的列，或在 index_name 所引用的索引中定义的列。 captured_column_list 是以逗号分隔的列名称列表。可以选择将列表中的单个列名称放在双引号 ("") 或方括号 ([]) 中。如果列名称包含嵌入的逗号，则必须将该列名称引起来。 captured_column_list 不能包含以下保留的列名称：__$start_lsn、__$end_lsn、__$seqval、__$operation 和 __$update_mask。 [ @filegroup_name = ] 'filegroup_name' 是要用于为捕获实例创建的更改表的文件组。filegroup_name 为 sysname，并且可以 NULL。如果指定，则必须为当前数据库定义 filegroup_name。如果为 NULL，则使用默认文件组。 我们建议为变更数据捕获的更改表创建一个单独的文件组。有关详细信息，请参阅配置变更数据捕获。 [ @allow_partition_switch= ] 'allow_partition_switch' 指示是否可以对启用了变更数据捕获的表执行 ALTER TABLE 的 SWITCH PARTITION 命令。allow_partition_switch 为 bit，默认值为 1。 对于非分区表，此开关设置始终为 1，并忽略实际的设置。如果对于非分区表将此开关显式设置为 0，则发出警告 22857 以指示已忽略此开关设置。如果对于分区表将此开关显式设置为 0，则发出警告 22356 以指示将禁止源表上的分区切换操作。最后，如果此开关设置显式设置为 1 或允许默认为 1 并且被启用的表已分区，则发出警告 22855 以指示不会阻塞分区切换。如果发生任何分区切换，则变更数据捕获不会跟踪由此切换产生的更改。这将导致使用更改数据时数据不一致。
EXECUTE sys.sp_cdc_enable_table @source_schema = N'dbo' , @source_name = N'User_Info' , @role_name = NULL , @capture_instance=N'CDC_User_Info' , @captured_column_list ='UserInfoID,UserName,NickName,RealName,Photo,Phone,IDCard,AddTime,Status,RoleType,Province,City,District' , @index_name='PK_USER_INFO' ; GO
语法 --查看配置 --sys.sp_cdc_help_change_data_capture --[ [ @source_schema = ] 'source_schema' ]--表的架构名 --[, [ @source_name = ] 'source_name' ] --表名称 EXEC sys.sp_cdc_help_change_data_capture 'dbo','User_Info'
--执行结果
--查看是否启用
SELECT is_tracked_by_cdc
FROM SYS.TABLES
WHERE NAME='User_Info'
AND SCHEMA_ID=SCHEMA_ID('DBO')
下面开始测试一下这个监控是否起效：
注意：必须在 SQL Server 配置管理器中打开SQL 代理，
直接在SQL里面做测试：
SELECT * FROM cdc.CDC_User_Info_CT
--插入测试 INSERT INTO [dbo].[User_Info] ([UserName],[Password],[NickName],[Email],[RealName],[IDCard],[Photo],[Sex],[Phone],[Profile],[Description] ,[AddTime],[Province],[City],[District],[Birthday],[RoleType],[Status],[VIPCardNumber],[VerifiedStatus] ,[PayPassword],[QQ],[LastLoginTime],[IsShowRealName]) VALUES ('test','test','test','test','test','test','test',1,'test','test','test','2015-02-03 00:00:00.000','test','test','test','1983-03-18 00:00:00.000',1,1,1,1,'test','test','2015-02-03 00:00:00.000',1) SELECT * FROM cdc.CDC_User_Info_CT GO
--修改测试 UPDATE [dbo].[User_Info] SET[Sex] = 0 ,[Phone] = '4' ,[QQ] = '4111114' WHERE UserInfoID=1
SELECT * FROM dbo.User_Info SELECT * FROM cdc.CDC_User_Info_CT GO
--删除测试 DELETE FROM [dbo].[User_Info] WHERE UserInfoID=99999
SELECT * FROM dbo.User_Info SELECT * FROM cdc.CDC_User_Info_CT GO
从测试看来已经达到了我们想要的监控效果了！存储过程部分也就这么几个，其他的可以参考最前面那个表格！
接下来看看如何使用CDC函数吧：
使用CDC的函数来获取更改
1、 cdc.fn_cdc_get_all_changes_<捕获实例> (Transact-SQL)
针对在指定日志序列号 (LSN) 范围内应用到源表的每项更改均返回一行。如果源行在该间隔内有多项更改，则每项更改都会表示在返回的结果集中。除了返回更改数据外，四个元数据列还提供了将更改应用到另一个数据源所需的信息。行筛选选项可控制元数据列的内容以及结果集中返回的行。当指定“all”行筛选选项时，针对每项更改将只有一行来标识该更改。当指定“all update old”选项时，更新操作会表示为两行：一行包含更新之前已捕获列的值，另一行包含更新之后已捕获列的值。
此枚举函数是在对源表启用变更数据捕获时创建的。此函数名称是派生的，采用 cdc.fn_cdc_get_all_changes_capture_instance 格式，其中 capture_instance 是在对源表启用变更数据捕获时为捕获实例指定的值。
语法： cdc.fn_cdc_get_all_changes_capture_instance ( from_lsn , to_lsn , '<row_filter_option>' ) <row_filter_option> ::= { all | all update old } 参数： from_lsn LSN 值，它表示要包含在结果集中的 LSN 范围的低端点。from_lsn 的数据类型为 binary(10)。 结果集中仅包含 cdc.[capture_instance]_CT 更改表中 __$start_lsn 中的值大于或等于 from_lsn 的行。 to_lsn LSN 值，它表示要包含在结果集中的 LSN 范围的高端点。to_lsn 的数据类型为 binary(10)。 结果集中仅包含 cdc.[capture_instance]_CT 更改表中 __$start_lsn 中的值小于或等于 from_lsn 或等于 to_lsn 的行。 <row_filter_option> ::= { all | all update old } 控制元数据列的内容和结果集中所返回的行的选项。 可以是下列选项之一： all 返回指定 LSN 范围内的所有更改。对于由更新操作导致的更改，此选项只返回在应用更新之后包含新值的行。 all update old 返回指定 LSN 范围内的所有更改。对于由更新操作导致的更改，此选项将返回在更新之前包含列值的行和更新之后包含列值的行。
--1、cdc.fn_cdc_get_all_changes_<捕获实例> (Transact-SQL) -- 查询某个捕获实例的最小到最大变化lsn -- all update old 查询结果要修改前的源数据 _$operation=3 | all 只查询变化的，不带修改前的源数据玩 即查询结果中没有 _$operation=3的数据 DECLARE @from_lsn binary(10), @to_lsn binary(10) SET @from_lsn = sys.fn_cdc_get_min_lsn('CDC_User_Info') SET @to_lsn = sys.fn_cdc_get_max_lsn() SELECT @from_lsn AS 'Start',@from_lsn AS 'End' SELECT * FROM cdc.fn_cdc_get_all_changes_CDC_User_Info (@from_lsn, @to_lsn, N'all')--|(@from_lsn, @to_lsn, N'all update old') GO
2、通过 sys.fn_cdc_map_time_to_lsn (Transact-SQL) 函数 获取某个时间段内的数据变化
为指定的时间返回 cdc.lsn_time_mapping 系统表中 start_lsn 列中的日志序列号 (LSN) 值。可以使用此函数系统地将日期时间范围映射到基于 LSN 的范围，以供变更数据捕获枚举函数 cdc.fn_cdc_get_all_changes_<capture_instance> 和cdc.fn_cdc_get_net_changes_<capture_instance> 返回此范围内的数据更改。
语法： sys.fn_cdc_map_time_to_lsn ('<relational_operator>', tracking_time ) <relational_operator> ::= { largest less than | largest less than or equal | smallest greater than | smallest greater than or equal } 参数： '<relational_operator>' { largest less than | largest less than or equal | smallest greater than | smallest greater than or equal } 用于标识这样的非重复 LSN 值：该值在 cdc.lsn_time_mapping 表中，且其关联的 tran_end_time 在与 tracking_time 值比较时满足关系。 relational_operator 的数据类型为 nvarchar(30)。 tracking_time 是要进行匹配的日期时间值。tracking_time 的数据类型为 datetime。 注释： 若要了解如何使用 sys.fn_cdc_map_time_lsn 将日期时间范围映射到 LSN 范围，请考虑以下情况。假定使用者希望每日提取更改数据。也就是说，使用者只需要给定日午夜之前（包括午夜）的更改。此时间范围的下限应为无限接近前一天午夜的时间点（但不包括前一天午夜）。上限应为给定日的午夜（包括午夜）。下例说明了如何使用 sys.fn_cdc_map_time_to_lsn 函数系统地将此基于时间的范围映射到基于 LSN 的范围，以供变更数据捕获枚举函数返回此范围内的所有更改之用。 DECLARE @begin_time datetime, @end_time datetime, @begin_lsn binary(10), @end_lsn binary(10); SET @begin_time = '2007-01-01 12:00:00.000'; SET @end_time = '2007-01-02 12:00:00.000'; SELECT @begin_lsn = sys.fn_cdc_map_time_to_lsn('smallest greater than', @begin_time); SELECT @end_lsn = sys.fn_cdc_map_time_to_lsn('largest less than or equal', @end_time); SELECT * FROM cdc.fn_cdc_get_net_changes_HR_Department(@begin_lsn, @end_lsn, 'all '); 关系运算符 'smallest greater than' 用于将更改限制为在前一天的午夜后发生的更改。如果具有不同 LSN 值的多个项共用在 cdc.lsn_time_mapping 表中标识为下限的 tran_end_time 值，则该函数将返回最小的 LSN，以确保所有项均包含在内。对于上限，关系运算符 ‘largest less than or equal to’ 用于确保该范围包含此日的所有项（包括其 tran_end_time 值为午夜的那些项）。如果具有不同 LSN 值的多个项共用标识为上限的 tran_end_time 值，则此函数将返回最大的 LSN，以确保所有项均包括在内。
--2、通过 sys.fn_cdc_map_time_to_lsn (Transact-SQL) 函数 获取某个时间段内的数据变化 --比如：查询 2015-02-10 15:20-15:30 这十分钟之内我所做的修改 SELECT * FROM cdc.CDC_User_Info_CT --查询这个是为了方便直观的看到两个查询结果的对比 DECLARE @begin_time datetime, @end_time datetime, @begin_lsn binary(10), @end_lsn binary(10); SET @begin_time = '2015-02-10 15:20:00.000'; SET @end_time = '2015-02-10 15:30:00.000'; SELECT @begin_lsn = sys.fn_cdc_map_time_to_lsn('smallest greater than', @begin_time); SELECT @end_lsn = sys.fn_cdc_map_time_to_lsn('largest less than or equal', @end_time); SELECT * FROM cdc.fn_cdc_get_all_changes_CDC_User_Info(@begin_lsn, @end_lsn, 'all update old');
很明显可以看到只有两条数据是在这十分钟内捕获到的；
3、sys.fn_cdc_map_lsn_to_time 查询变更时间：
为指定的日志序列号 (LSN) 返回 cdc.lsn_time_mapping 系统表的 tran_end_time 列中的日期和时间值。您可以使用此函数系统地将 LSN 范围映射到更改表中的日期范围。
--3、查询变更时间 SELECT [__$operation] , CASE [__$operation] WHEN 1 THEN '删除' WHEN 2 THEN '插入' WHEN 3 THEN '更新（捕获的列值是执行更新操作前的值）' WHEN 4 THEN '更新（捕获的列值是执行更新操作后的值）' END [类型], sys.fn_cdc_map_lsn_to_time([__$start_lsn]) [更改时间] , UserName , UserInfoID FROM cdc.CDC_User_Info_CT
====================================================================================================================================================================
技术跟新换代步伐太快，基础还是不会变的。打好基础，坚实自己的步伐，任何技术来了学习都会很快的。
大家共同学习，共同成长。
我QQ 624859714
