SqlServer Change Data Capture(CDC)数据变更捕获

  最近在使用SqlServer2008r2数据库做系统的时候,在某些重要的、经常涉及到修改的表上,想加上一些恢复机制,一开始想找找看看有没有类似Oracle数据库闪回那样的功能,后来发现CDC的功能可以实现我的需求,SQLServer还有一个功能叫更改跟踪,但是它只记录那些表或者列发生了更改,但是不保存历史数据,所以忽略它。所以接下来就针对CDC做了一些研究和大家分享。

  基本概念

  变更数据捕获用于捕获应用到 SQL Server 表中的插入、更新和删除活动,并以易于使用的关系格式提供这些变更的详细信息。变更数据捕获所使用的更改表中包含镜像所跟踪源表列结构的列,同时还包含了解所发生的变更所需的元数据。变更数据捕获提供有关对表和数据库所做的 DML 更改的信息。通过使用变更数据捕获,您无需使用费用高昂的方法,如用户触发器、时间戳列和联接查询等。

  SqlServer Change Data Capture(CDC)数据变更捕获

  数据变更历史表会随着业务的持续,变得很大,所以默认情况下,变更数据历史会在本地数据库保留3天(可以通过视图msdb.dbo.cdc_jobs的字段retention来查询,当然也可以更改对应的表来修改保留时间),每天会通过SqlServer后台代理任务,每天晚上2点定时删除。所以推荐定期的将变更数据转移到数据仓库中。

  启用变更数据捕获  

  在为各个表创建捕获实例之前,必须先由 sysadmin 固定服务器角色的成员对数据库启用变更数据捕获。通过在数据库上下文中运行 sys.sp_cdc_enable_db (Transact-SQL) 存储过程可实现这一点。若要确定数据库是否已启用此功能,请在 sys.databases 目录视图中查询 is_cdc_enabled 列。

  当对数据库启用了变更数据捕获之后,将为数据库创建 cdc 架构、cdc 用户、元数据表和其他系统对象。cdc 架构包含变更数据捕获元数据表,当对源表启用了变更数据捕获之后,各个更改表将用作更改数据的存储库。cdc 架构还包含用于查询更改数据的关联系统函数。

  变更数据捕获要求采用独占方式使用 cdc 架构和 cdc 用户。如果某数据库中当前存在名为 cdc 的架构或数据库用户,那么在删除或重命名此架构或用户之前,不能对此数据库启用变更数据捕获。

  

--查看数据库是否起用CDC  
 USE master
GO
SELECT [name], database_id, is_cdc_enabled
FROM sys.databases
GO --数据库起用CDC
USE Demo01
GO
EXEC sys.sp_cdc_enable_db
GO --关闭数据库CDC
USE Demo01
go
exec sys.sp_cdc_disable_db
go --查看表是否启用CDC
USE Demo01
GO
SELECT [name], is_tracked_by_cdc
FROM sys.tables
GO --启用表的CDC,前提是数据库启用CDC
--@role_name 指定角色的目的是控制对更改数据的访问。指定的角色可以为现有的固定服务器角色或数据库角色。如果指定的角色还不存在,则会自动创建具有该名称的数据库角色。sysadmin 或 db_owner 角色的成员对于更改表中的数据拥有完全访问权限。如果不适用角色需要显示指定为null。
--使用自己新建测测试表User来操做,列为id(主键,自增),name,address。
USE Demo01
GO
EXEC sys.sp_cdc_enable_table
@source_schema = 'dbo',
@source_name = 'user',
@capture_instance='user',
@role_name = NULL
GO --关闭表上的CDC功能
USE Demo01
GO
EXEC sys.sp_cdc_disable_table
@source_schema = 'dbo',
@source_name = 'user',
@capture_instance='user'
GO

--可能不记得或者不知道开启了什么表的捕获,返回所有表的变更捕获配置信息

EXECUTE sys.sp_cdc_help_change_data_capture;
GO

--查看对某个实例(即表)的哪些列做了捕获监控:

EXEC sys.sp_cdc_get_captured_columns
@capture_instance = 'user'

--查找配置信息 -retention 变更数据保留的分钟数
SELECT * FROM msdb.dbo.cdc_jobs

--更改数据保留时间为分钟
EXECUTE sys.sp_cdc_change_job
@job_type = N'cleanup',
@retention=1440
GO

--停止捕获作业
exec sys.sp_cdc_stop_job N'capture'
go
--启动捕获作业
exec sys.sp_cdc_start_job N'capture'
go

--开始在user表中进行修改,增加和删除操作

select * from [user];

update [user] set name='Alex' where id=6;
insert into [user] (name,address) values ('你好','4756');
delete from [user] where ID=5;

--查看变更表中的数据,_$operation的含义:1 = 删除,2 = 插入,3 = 更新(旧值),4 = 更新(新值)

select * from CDC.user_CT;

--按照时间范围查询CDC结果
DECLARE @bglsn VARBINARY(10)=sys.fn_cdc_map_time_to_lsn('smallest greater than or equal','2016-3-1 12:00:00.997')
DECLARE @edlsn VARBINARY(10)=sys.fn_cdc_map_time_to_lsn('largest less than or equal',GETDATE())
SELECT * FROM CDC.user_CT
WHERE [__$operation] in (1,2,3,4) AND [__$start_lsn] BETWEEN @bglsn AND @edlsn

--sys.fn_cdc_map_lsn_to_time 查询变更时间:
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]) [更改时间] ,
name ,address ,id FROM cdc.user_CT;


   

  注意:  

  上面都是简单介绍,在使用中过程中肯定会有各种问题,建议可以参考一下官方文档 https://technet.microsoft.com/zh-cn/library/cc280519(v=sql.105).aspx

  如果数据库以同一数据库名称还原到同一服务器,变更数据捕获将保持启用状态。如果数据库还原到其他服务器,默认情况下将禁用变更数据捕获,并删除所有相关的元数据。若要保留变更数据捕获,还原数据库时请使用 KEEP_CDC 选项。

上一篇:salesforce零基础学习(一百零五)Change Data Capture


下一篇:jQuery 中ready与load事件