SQLServer数据库CDC数据同步配置
SQLServer数据库CDC数据同步配置
SQL Server 开启CDC数据同步
1、数据库需要开启代理服务。
开启⽅式：
a). 点击开始菜单àSQL Serverà配置管理⼯具àSQLserver服务àSQLserver代理à（右键）启动
b). 打开电脑服务，到SQLserver 代理，点击左侧状态
2、数据库配置
a).⾸先查看数据库是否已经开启CDC服务
SELECT name,is_cdc_enabled FROM sys.databases WHERE is_cdc_enabled = 1;
返回已经开启CDC的数据库，如果为空，则所有的库都没有开启CDC服务，转到步骤b，对数据库开启CDC。如果有结果，则转到步骤c。
b).对数据库开启CDC服务
USE test;          -- 切换数据库
EXECUTE sys.sp_cdc_enable_db; -- 开启CDC功能
检查是否开启成功：
SELECT
is_cdc_enabled,
CASE WHEN is_cdc_enabled=0 THEN 'CDC功能禁⽤' ELSE 'CDC功能启⽤' END 描述
FROM    sys.databases
WHERE  NAME = ‘test’ –- 你的数据库名称
创建成功后，将⾃动添加CDC⽤户和CDC架构。
在⽤户和架构下⾯可以看到cdc⽤户和cdc架构
c.查看当前已经开启CDC的数据表。
SELECT name,is_tracked_by_cdc FROM sys.tables WHERE is_tracked_by_cdc = 1;
d.开启表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' ]
⽰例：
对'USRALMHS'表开启变更捕获
EXEC sys.sp_cdc_enable_table
@source_schema= 'dbo',      --源表架构
@source_name = 'USRALMHS',  --源表
@role_name = 'CDC_Role'    --⾓⾊（将⾃动创建）
GO
--如果不想控制访问⾓⾊，则@role_name必须显式设置为null。
查询是否成功
SELECT  name ,
is_tracked_by_cdc ,
CASE WHEN is_tracked_by_cdc = 0 THEN 'CDC功能禁⽤'
ELSE 'CDC功能启⽤'
END 描述
FROM    sys.tables
WHERE  OBJECT_ID = OBJECT_ID('dbo. USRALMHS ')
对表开启成功后，可以查看数据库，在数据库系统表下增加了很多表。
在SQLserver 代理中多了两个作业
在可编程性-》函数-》表值函数⾥，也多了两个函数
3、测试
a)      向表中插⼊数据
insert into test.dbo.USRALMHS  select top 1000 * from alarm.dbo.USRALMHS_copy3
在DBO_USRALMHS_CT中查看：
会有同样的1000 条数据，唯⼀不同的是在DBO_USRALMHS_CT中会多⼏个字段，分别代表不同的含义，其中最主要的是 __$operation 代表含义 1 删除、2插⼊、3更新前的内容、4更新后的内容 @bglsn 开始时间的时间戳  @edlsn 结束时间的时
b)      测试更新和删除操作（⽣成数据的__$operation 不同）
省略
4、分析（系统⾃带数据库）
a)      分析存储过程
丰收(打一字)
---查询当前作业配置
SELECT * FROM MSDB.dbo.cdc_jobs
--或者使⽤
USE AdventureWorks2008R2;
GO
EXEC sys.sp_cdc_help_jobs;
GO
1.sys.sp_cdc_add_job
在当前数据库中创建变更数据捕获清理或捕获作业
1.创建捕获作业
USE AdventureWorks2008R2;
GO
EXEC sys.sp_cdc_add_job
@job_type = N'capture';
GO
2.创建清理作业
---创建清理作业，作业连续运⾏，更改数据⾏将在更改表中保留2880分钟，清除时使⽤⼀条语句最多删除4000条记录
USE AdventureWorks2008R2;
GO
EXEC sys.sp_cdc_add_job
@job_type = N'cleanup'
,@start_job=1
,@retention=2880
,@threshold =4000
2.sys.sp_cdc_change_job
修改当前数据库中变更数据捕获清除或捕获作业的配置
--仅在使⽤ sp_cdc_stop_job 停⽌作业并使⽤ sp_cdc_start_job 重新启动该作业后，对该作业所做的更改才会⽣效
1.更改捕获作业
--将每个循环扫描最多处理的事务数更改为200，为了从⽇志中提取所有⾏⽽要执⾏的最⼤扫描循环50次
USE AdventureWorks2008R2;
GO
EXECUTE sys.sp_cdc_change_job
@job_type = N'capture',
@maxtrans = 200,
@maxscans = 50;
GO
2.更改清除作业，将记录保留时间更改为3440分钟
USE AdventureWorks2008R2;
GO
EXECUTE sys.sp_cdc_change_job
@job_type = N'cleanup',
@retention = 3440;
GO
3.sys.sp_cdc_cleanup_change_table
根据指定的 low_water_mark 值从当前数据库的更改表中删除⾏，重置更改表中的最⼩ __$start_lsn，并删除⼩于该值的数据.
将同时清除cdc.HR_Department_CT,cdc.lsn_time_mapping表的记录
USE AdventureWorks2008R2;
GO
EXEC sys.sp_cdc_cleanup_change_table
@capture_instance =N'HR_Department',
@low_water_mark=0x0000037D000000D30008,
@threshold=2000;
SELECT sys.fn_cdc_increment_lsn(sys.fn_cdc_get_max_lsn())
4.sys.sp_cdc_drop_job
从 msdb 中删除当前数据库的变更数据捕获清除或捕获作业。
--下例删除 AdventureWorks2008R2 数据库的清除作业和捕获作业
USE AdventureWorks2008R2;
GO
EXEC sys.sp_cdc_drop_job @job_type = N'cleanup';
USE AdventureWorks2008R2;
GO
EXEC sys.sp_cdc_drop_job @job_type = N'capture';
5.sys.sp_cdc_disable_db
对当前数据库禁⽤变更数据捕获
禁⽤当前对数据库中的所有表启⽤的变更数据捕获。与变更数据捕获相关的所有系统对象（如更改表、作业、存储过程和函数）都将被删除。sys.databases ⽬录视图中的数据库条⽬的 is_cdc_enabled 列设置为 0。
如果在禁⽤变更数据捕获时为数据库定义了很多捕获实例，则长时间运⾏事务可能导致 sys.sp_cdc_disable_db 的执⾏失败。通过在运⾏ sys.sp_cdc_disable_db 之前使⽤sys.sp_cdc_disable_table 禁⽤单个捕获实例，可以避免此问题。
USE AdventureWorks2008R2;
GO
EXECUTE sys.sp_cdc_disable_db;
GO
6.sys.sp_cdc_disable_table
对当前数据库中指定的源表和捕获实例禁⽤变更数据捕获
删除与指定的源表和捕获实例相关联的变更数据捕获更改表和系统函数。它会删除任何与来⾃变更数据捕获系统表的指定捕获实例相关联的⾏，并将 sys.tables ⽬录视图中的表项的 is_tracked_by_cdc 列设置为 0。
---下例对 HumanResources.Department 表禁⽤了变更数据捕获
USE AdventureWorks2008R2;
GO
EXEC sys.sp_cdc_disable_table
@source_schema = N'HumanResources'
, @source_name = N'Department'
, @capture_instance = N'HR_Department' ---这⾥是定义的实例名称，在⼀开始创建捕获的时候创建的，这⾥也可以制定ALL（禁⽤表HumanResources.Department的所有捕获）,
7.sys.sp_cdc_enable_db
对当前数据库启⽤变更数据捕获。必须先对数据库执⾏此过程，然后才能对该数据库中的任何表启⽤变更数据捕获。变更数据捕获可记录应⽤到所启⽤的表中的插⼊、更新和删除活动，同时采⽤易于使⽤的关系格式提供变更详细信息。此操作将为已修改的⾏捕获反映了所跟踪源表列结构的列信息，同时还捕获将更改应⽤到⽬标环境所需的元数据。
将创建以全数据库为作⽤域的变更数据捕获对象，包括元数据表和 DDL 触发器。它还会创建 cdc 架构和 cdc 数据库⽤户，并将 sys.databases ⽬录视图中的数据库条⽬的
is_cdc_enabled 列设置为 1。
USE AdventureWorks2008R2;
GO
EXECUTE sys.sp_cdc_enable_db;
GO
8.sys.sp_cdc_enable_table
--对需要进⾏跟踪的表启动CDC,sys.sp_cdc_enable_table
/*
为当前数据库中指定的源表启⽤变更数据捕获。对表启⽤变更数据捕获时，应⽤于此表的每个数据操纵语⾔ (DML) 操作的记录都将写⼊事务⽇志中。
变更数据捕获进程将从⽇志中检索此信息，并将其写⼊可通过使⽤⼀组函数访问的更改表中。
*/
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' ]--⽤于唯⼀标识源表中的⾏的唯⼀索引的名称。index_name 为 sysname，并且可以为 NULL。如果指定，则 index_name 必须是源表的唯⼀有效索引。如果指定 index_name，则标识的索引列优先于任何定义的主键列，就像表的唯⼀⾏标识符⼀样。
[,[ @captured_column_list = ] 'captured_column_list' ]--需要对哪些列进⾏捕获。captured_column_list 的数据类型为 nvarchar(max)，并且可以为 NULL。如果为 NULL，则所有列都将包括在更改表中。
[,[ @filegroup_name = ] 'filegroup_name' ]--是要⽤于为捕获实例创建的更改表的⽂件组。
丛林 电影
[,[ @partition_switch = ] 'partition_switch' ]--指⽰是否可以对启⽤了变更数据捕获的表执⾏ ALTER TABLE 的 SWITCH PARTITION 命令。allow_partition_switch 为 bit，默认值为 1。
9.sp_cdc_generate_wrapper_function
⽣成⽤于为 SQL Server 中可⽤的变更数据捕获查询函数创建包装函数的脚本
EXEC sys.sp_cdc_generate_wrapper_function
10. sys.sp_cdc_help_change_data_capture
返回当前数据库中为变更数据捕获启⽤的每个表的变更数据捕获配置。最多可为每个源表返回两⾏，为每个捕获实例返回⼀⾏。
---返回制定表的捕获信息
USE AdventureWorks2008R2;
GO
EXECUTE sys.sp_cdc_help_change_data_capture
@source_schema = N'HumanResources', --架构名
@source_name = N'Department';--表名
GO
--返回所有表的捕获信息
USE AdventureWorks2008R2;
GO
EXECUTE sys.sp_cdc_help_change_data_capture
11.sys.sp_cdc_get_captured_columns
返回指定捕获实例所跟踪的捕获源列的变更数据捕获元数据信息。
USE AdventureWorks2008R2;
GO
EXECUTE sys.sp_cdc_get_captured_columns
@capture_instance = N'HR_Department';
GO
12.sys.sp_cdc_get_ddl_history
返回⾃对指定的捕获实例启⽤变更数据捕获后与该捕获实例关联的数据定义语⾔ (DDL) 更改历史记录。
与查询表是⼀样的结果
SELECT * FROM cdc.ddl_history
USE AdventureWorks2008R2;
GO
EXECUTE sys.sp_cdc_get_ddl_history
@capture_instance = N'HR_Department';
学习雷锋手抄报内容
13.sp_cdc_help_jobs
报告关于当前数据库中所有变更数据捕获清除或捕获作业的信息。因为⼀个数据库只会在第⼀个表创建捕获的时候创建作业
所以这⾥只需要在当前库执⾏就可以。
sys.sp_cdc_help_jobs
USE AdventureWorks2008R2;
GO
EXEC sys.sp_cdc_help_jobs;
GO
14.sp_cdc_scan
执⾏变更数据捕获⽇志扫描操作，需要进⾏捕获的时候使⽤，默认情况下5会⾃动进⾏捕获。
如果变更数据捕获正在使⽤ SQL Server 代理捕获作业，则 sys.sp_MScdc_capture_job 将内部调⽤ sys.sp_cdc_scan。如果变更数据捕获⽇志扫描操作已经处于活动状态，或数据库启⽤了事务复制，则⽆法显式执⾏此过程。此存储过程应当由需要⾃定义⾃动配置的捕获作业的⾏为的管理员使⽤。
2022年立夏时间USE AdventureWorks2008R2;
GO
EXEC sp_cdc_scan
15.sys.sp_cdc_start_job,
wifi密码启动和停⽌当前数据库的变更数据捕获清除或捕获作业。
---启动清除作业
USE AdventureWorks2008R2;
GO
EXEC sys.sp_cdc_start_job @job_type = N'cleanup';
---停⽌捕获作业
USE AdventureWorks2008R2;
GO
EXEC sys.sp_cdc_stop_job @job_type = N'capture';
GO
USE AdventureWorks2008R2;
GO
EXEC sys.sp_cdc_stop_job @job_type = N'cleanup';
GO
b)      分析函数
1.fn_cdc_get_all_changes_capture_instance
针对在指定⽇志序列号 (LSN) 范围内应⽤到源表的每项更改均返回⼀⾏
USE AdventureWorks2008R2;
GO
DECLARE @from_lsn binary(10), @to_lsn binary(10)
房产过户手续SET @from_lsn =

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。