SqlServer Change Data Capture（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;