关于开启CDC实例的二三事_ibmcdc写文件间隔-优快云博客

注：关于SQL SERVER 2012在SP3补丁级别下对表开启CDC，若该表有频繁的MERGE操作，则向CDC表中插入数据时会报违反唯一约束错误，尽管此时表中并无数据。及时在关闭CDC后重新开启，仍不能解决。此问题系SQL SERVER的一个BUG,已在 SP4补丁中修复。所以若遇到此问题，升级补丁即可。 use test go /*在可以对表启用变更数据捕获之前，必须先对数据库启用变更数据捕获。若要确定数据库是否已启用变更数据捕获，请查询 sys.databases 目录视图中的 is_cdc_enabled 列。若要启用数据库，请使用 sys.sp_cdc_enable_db 存储过程。对表启用变更数据捕获时，将生成一个更改表以及一个或两个查询函数。更改表充当捕获进程从事务日志中提取的源表更改的存储库。查询函数则用于从更改表中提取数据。这些函数的名称按以下方式派生自 capture_instance 参数：所有更改函数： cdc.fn_cdc_get_all_changes_ < capture_instance > 净更改函数： cdc.fn_cdc_get_net_changes_ < capture_instance > 如果源表是数据库中第一个要启用变更数据捕获的表，并且数据库不存在事务发布，则 sys.sp_cdc_enable_table 还将为数据库创建捕获和清理作业。它将 sys.tables 目录视图中的 is_tracked_by_cdc 列设置为 1。*/

--STEP 1:
--对目标库显式启用CDC：该存储过程的作用域是整个目标库。
包含元数据、DDL触发器、cdc架构和cdc用户。
EXEC sys.sp_cdc_enable_db
/*错误号 15517 :某个/些存储过程使用了具有WITHEXECUTE AS 的选项。
使其在当前库具有了某个架构，但是当在别的地方执行时，由于没有这个架构，所以就报错
解决方法: ALTER AUTHORIZATION ON DATABASE::[AdventureWorks] TO [sa] */
--禁用数据库
EXEC sys.sp_cdc_disable_db
--查询数据库是否启用成功：1 成功； 0 失败
select is_cdc_enabled from sys.databases where name='test'
--STEP 2:
--对目标表启用CDC:
--使用db_owner角色的成员执行sys.sp_cdc_enable_table为每个需要跟踪的表创建捕获实例。
--然后通过sys.tables目录视图中的is_tracked_by_cdc列来判断是否创建成功。
--默认情况下会对表的全部列做捕获。如果只需要对某些列做捕获，
--可以使用@captured_column_list参数指定这些列。
--如果要把更改表放到文件组里的话，最好创建单独的文件组（最起码与源表独立）
--如果不想控制访问角色，则@role_name必须显式设置为null。

sys.sp_cdc_enable_table
[ @source_schema = ] 'source_schema',
--是源表所属的架构的名称
--是对其启用变更数据捕获的源表的名称。source_name 必须存在于当前数据库中。
--不能对 cdc 架构中的表启用变更数据捕获。
[ @role_name = ] 'role_name'
--是用于控制更改数据访问的数据库角色的名称。
--如果显式设置为 NULL，则没有控制角色用于限制对更改数据的访问。
[,[ @capture_instance = ] 'capture_instance' ]
--用于命名特定于实例的变更数据捕获对象的捕获实例的名称，源表最多两个捕获实例
[,[ @supports_net_changes = ] supports_net_changes ]
[,[ @index_name = ] 'index_name' ]
[,[ @captured_column_list = ] 'captured_column_list' ]
--标识将包括在更改表中的源表列。如果为 NULL，则所有列都将包括在更改表中。
--captured_column_list 是以逗号分隔的列名称列表。
[,[ @filegroup_name = ] 'filegroup_name' ]
--是要用于为捕获实例创建的更改表的文件组。如果为 NULL，则使用默认文件组。
--是否可以对启用了变更数据捕获的表执行 ALTER TABLE 的 SWITCH PARTITION 命令。

--为test表开启变更捕获
exec sys.sp_cdc_enable_table @source_schema='dbo',
@source_name='test',
@role_name=N'cdc_Admin',
@capture_instance=DEFAULT
--查询标是否启用成功：1 成功； 0 失败
SELECT name ,
is_tracked_by_cdc ,
CASE WHEN is_tracked_by_cdc = 0 THEN 'CDC功能禁用'
ELSE 'CDC功能启用'
END 描述
FROM sys.tables
WHERE OBJECT_ID IN( OBJECT_ID('dbo.test'))

cdc.<capture_instance>_CT
可以看到，这样命名的表，是用于记录源表更改的表。
对于insert/delete操作，会有对应的一行记录，而对于update，会有两行记录。
对于```__$operation```列：1 = 删除、2= 插入、3= 更新（旧值）、4= 更新（新值）
对于__$start_lsn列：由于更改是来源与数据库的事务日志，所以这里会保存其事务日志的开
始序列号（LSN）

--查询已经开启的捕获实例
exec sys.sp_cdc_help_change_data_capture
--查看对某个实例(即表)的哪些列做了捕获监控:
EXEC sys.sp_cdc_get_captured_columns
@capture_instance = 'dbo_eco_data_ind_machineryequipment' -- sysname
--查找配置信息
SELECT * FROM msdb.dbo.cdc_jobs
--查看当前配置
EXEC sp_cdc_help_jobs
--maxtrans:捕获作业每次循环时要处理的最大事务数
--maxscans:每次循环次数
--continuous:1:连续运行；0:间隔运行
--pollinginterval：日志扫描周期之间的秒数
--retention:变更数据保留的分钟数
--threshold：删除项，可以通过使用上清除的单个语句删除注册表项的最大数量？？
--更改数据保留时间为分钟
EXECUTE sys.sp_cdc_change_job
@job_type = N'cleanup',
@retention=100
GO
--禁用CDC
EXEC sys.sp_cdc_disable_table
@source_schema = 'dbo',
@source_name = 'test',
@capture_instance = 'all';
--启用
EXEC sys.sp_cdc_enable_table
@source_schema = 'dbo',
@source_name = 't1',
@role_name = null;
--
--停用作业
EXEC sys.sp_cdc_stop_job N'cleanup'
GO
--启用作业
EXEC sys.sp_cdc_start_job N'cleanup'
GO
--再次查看
EXEC sp_cdc_help_jobs
GO
--删除作业
EXEC sys.sp_cdc_drop_job @job_type = N'cleanup' -- nvarchar(20)
GO
--创建作业
EXEC sys.sp_cdc_add_job
@job_type = N'cleanup',
@start_job = 0,
@retention = 5760
--DDL变更捕获,保证代理处于开启状态
CDC除了捕获数据变更之外，还能捕获DDL操作的变化。前提是先要确保SQLServer 代理的
启用，其实CDC功能都需要确保sql 代理正常运行，因为所有操作都通过代理中的两个作业来
实现的。
--查询DDL记录
SELECT * FROM cdc.ddl_history
--使用CDC函数来获取更改
--
DECLARE @from_lsn binary(10), @to_lsn binary(10)
SET @from_lsn =
sys.fn_cdc_get_min_lsn('dbo_eco_data_ind_machineryequipment')
SET @to_lsn = sys.fn_cdc_get_max_lsn()
SELECT * FROM cdc.fn_cdc_get_all_changes_HumanResources_Department
(@from_lsn, @to_lsn, N'all update old');
GO
--获取某个时间段的更改信息：
--先根据日志序列号（logsequence number ,LSN）来获取跟踪变更数据：
--Sys.fn_cdc_map_time_to_lsn获取变更范围内的最大、最小LSN值。可以使用：
--Smallest greater than;smallest greater than orequal;
--largest less than;largest less than or equal.
--查询某个时间段插入的数据：
--插入数据
INSERT INTO dbo.test(id,name)
VALUES('1','abc')
INSERT INTO dbo.test(id,name)
VALUES('4','abc1')
go
--检查数据

DECLARE @bglsn VARBINARY(10)=sys.fn_cdc_map_time_to_lsn(
'smallest greater than or equal',
'2012-10-12 12:00:00.997')
DECLARE @edlsn VARBINARY(10)=sys.fn_cdc_map_time_to_lsn(
'largest less than or equal',GETDATE())
SELECT id,name
FROM cdc.dbo_test_CT
WHERE [__$operation]=2 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]) [更改时间] ,
ID,
NAME
FROM cdc.dbo_test_CT123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162
–获取LSN边界 SELECT sys.fn_cdc_get_max_lsn(),–[数据库级别的最大LSN] sys.fn_cdc_get_min_lsn(‘cdc.dbo_test_CT’)–[捕获实例的lsn] /*原表开启CDC后，在表上新增列，是不会自动捕获到CDC中的。需要重新激活新添加的列的CDC。 CDC支持捕获表的两个实例。所以，可以执行以下步骤：
1.添加新列 2.添加新的cdc-capture inctance 3.将数据从旧表移动到新表 4.禁用旧的cdc实例 5.此解决方案阻止您停止收集更改，并且不会丢失数据。*/
--添加新列
alter table test add loc varchar(20) null
--添加新的cdc-capture inctance
exec sp_cdc_enable_table
@source_schema = N'dbo',
@source_name = N'test',
@role_name = NULL,
@filegroup_name =null,
@capture_instance = 'loc'--新的系统表名称（cdc.loc_ct）
```12345678910
–将数据从旧表移动到新表
INSERT INTO cdc.loc_ct
(__$start_lsn, __$end_lsn,__$seqval,__$operation,__$update_mask,Id,Name)
SELECT
__$start_lsn,
__$end_lsn,
__$seqval,
__$operation,
__$update_mask,
Id,
Name
FROM cdc.dbo_test_CT1234567891011
–禁用旧的cdc实例 EXEC sp_cdc_disable_table @source_schema = N’dbo’, @source_name = N’test’, @capture_instance = ‘dbo_test’ –此操作会更改捕获实例名称,禁用cdc实例后，原来的’cdc.dbo_test_ct’系统表会被删除，只有新建的’loc_ct’. –若想保证cdc实例名称不变，可以进行第二步反操作。新建cdc实例为’dbo_test’,插入数据，并禁用’loc’. exec sp_cdc_enable_table @source_schema = N’dbo’, @source_name = N’test’, @role_name = NULL, @filegroup_name =null, @capture_instance = ‘dbo_test’–新的系统表名称（cdc.loc_ct） –将数据从旧表移动到新表
INSERT INTO cdc.dbo_test_CT
(__$start_lsn, __$end_lsn,
__$seqval,__$operation,__$update_mask,Id,Name)
SELECT
__$start_lsn,
__$end_lsn,
__$seqval,
__$operation,
__$update_mask,
Id,
Name
FROM cdc.loc_CT123456789101112
--禁用旧的cdc实例
EXEC sp_cdc_disable_table
@source_schema = N'dbo',
@source_name = N'test',
@capture_instance = 'loc'
12345
–原表中数据不需要保留,则直接禁用cdc实例后，在启用cdc.此时系统表中的表也将删除并重建。 –禁用cdc实例 EXEC sp_cdc_disable_table @source_schema = N’dbo’, @source_name = N’test’, @capture_instance = ‘dbo_test’ –启用cdc实例 EXECUTE sys.sp_cdc_enable_table @source_schema = N’dbo’ , @source_name = N’eco_info_pro’ , @role_name = N’cdc_Admin’–可以自动创建 , @capture_instance=DEFAULT
————————————————
版权声明：本文为优快云博主「半世笙箫」的原创文章，遵循CC 4.0 BY-SA版权协议，转载请附上原文出处链接及本声明。
原文链接：https://blog.youkuaiyun.com/weixin_38357227/article/details/78431820