利用Sqlserver的CDC功能实现2张表的同步更新
|
Sqlserver利用CDC功能实时同步两张表数据 在开启CDC功能前，记得要把sqlserver的代理服务器打开。、 二 CDC功能大概介绍 ? 三 具体同步步骤以及截图 首先创建这样2张表 create database db1; create databasemonitor; 然后创建表结构，如下
2对数据库开启CDC功能 USEdb1 --检查是否开启成功 SELECT? is_cdc_enabled, ??????? CASE WHEN is_cdc_enabled = 0 THEN ‘CDC功能禁用‘ ???????????? ELSE ‘CDC功能启用‘ ??????? END 描述 FROM??? sys.databases WHERE?? NAME = ‘db1‘ ? ? ? ? 然后可以发现数据库db1里的一些变化 db1的安全性里的用户添加了cdc，架构也添加了cdc ? 在sqlserver代理里，我们也会发现，增加了目标库的capture和cleanup，一个负责捕获变化，一个负责清除变化。 ? ? 3对某张表具体开启cdc功能 GO EXECsys.sp_cdc_enable_table?????? ?????? @source_schema =‘dbo‘??????? ?????? ??,@source_name=‘t_cdc_ta‘??????? ?????? ??,@role_name=null?????? ?????? ??,@capture_instance=NULL???????? ?????? ??,@supports_net_changes=1??????? ?????? ??,@index_name=null??????? ?????? ??,@captured_column_list=null?????? ?????? ? ?,@filegroup_name=default?????? ?????? ??,@allow_partition_switch=1 ? 这里补充说明下，source_schema是表的拥有者，source_schema是表名。 role_name可以写null，但是如果设置了比如设置成cdc_Admin，那么可以在角色一栏自动创建的 ? ? ? ? 具体语句 USE db1; GO EXECUTEsys.sp_cdc_enable_table ??? @source_schema = ‘dbo‘ ?,@source_name = ‘t_cdc_ta‘ ?,@role_name = ‘cdc_Admin‘--可以自动创建 ?,@capture_instance=DEFAULT GO 执行完后，我们会发现，在db1的系统表里会生成这样一张表 ? ? 这张表就是针对目标表的增删改操作会生成相关的监听数据，写进这张表里，下面我们来事例下 4测试cdc功能，看看日志表情况（记录目标表的变化情况） use db1; insert intodbo.t_cdc_ta(id,name,addr,ttime) values (1,‘zjm‘,‘addr1‘,‘2017-10-1909:56:15.000‘), (2,‘zjm2‘,‘addr2‘, (3,‘zjm3‘,‘addr3‘, (4,‘zjm4‘,‘addr4‘,‘2017-10-1909:56:15.000‘) ? update dbo.t_cdc_taset name=‘xxq‘ where id=1 delete fromdbo.t_cdc_ta where id=3 ? 此时我们看下日志表的具体情况 ? select * from [db1].[cdc].[dbo_t_cdc_ta_CT] ? ? ? 右边边框是目标表里的数据，对于[__$operation]列，相当于对于增删改打了个标签，1代表删除，2代表插入，3代表update的旧数据，4代表update的新数据。 ? ? 5如何针对日志表对结果表同步 Use monitor CREATE? PROC?[dbo].[p_merge] @oper INT, @id INT, @name VARCHAR(20), @addr VARCHAR(20), @ttime DATETIME AS -- 删除 ?? IF @oper=1 ?? BEGIN ???? DELETE FROM? dbo.t_cdc_ta ???? WHERE [email?protected] ?? END ?? ELSE IF @oper=2? --? 新增 ??? BEGIN ????? INSERT INTO? dbo.t_scdc_ta(id,NAME,ttime) ????? VALUES(@id,@name,@addr,@ttime) ?? END ?? ELSE?IF @oper=4?? -- 更新 ?? BEGIN ??? UPDATE?dbo.t_cdc_ta ????? SET?[email?protected],[email?protected],[email?protected] ????? WHERE [email?protected]??? ?? END ?? GO ? 此时，就可以看到写好的存储过程了 在写一个游标的代码，一条一条遍历日志表的数据，嵌套刚才存储过程 ? ? use db1 ?declare?@oper INT ?declare?@id INT ?declare?@name VARCHAR(20) ?declare?@addr VARCHAR(20) ?declare?@ttime DATETIME ? ? --定义一个游标 declare user_curcursor for select __$operation,id,ttime fromdb1.cdc.dbo_t_cdc_ta_CT? --打开游标 open user_cur [email?protected]@fetch_status=0 begin --读取游标 ??? fetch next from user_cur into @oper,@id,@ttime ?????? use?monitor ?????? set identity_insert? t_cdc_ta?on ?????? exec dbo.p_merge @oper,@ttime ? ?? end close user_cur deallocate user_cur ? ? 上面的同步脚本写好之后，下面给一个企业级同步的方案 比如我想5分钟执行一次同步数据 当第一次开启捕获，相关的存储过程写好之后， 可以写一个脚本，专门进行同步，然后同步完后删除日志表的数据。 把这样的脚本放进sqlserver自带的作业计划里，设置执行间隔为5分钟。 同步脚本只需要在以上游标代码基础上再加上一段删除日志表数据即可，具体如下： --写一个游标，进行最终的同步操作 use db1 ?declare?@oper INT ?declare?@id INT ?declare?@name VARCHAR(20) ?declare?@addr VARCHAR(20) ?declare?@ttime DATETIME ? ? --定义一个游标 declare user_curcursor for select __$operation,ttime fromdb1.cdc.dbo_t_cdc_ta_CT? --打开游标 open user_cur [email?protected]@fetch_status=0 begin --读取游标 ??? fetch next from user_cur into @oper,@ttime ? ?? end close user_cur deallocate user_cur ? --删除日志表数据 use db1 delete from cdc.dbo_t_cdc_ta_CT ? ? 相关截图如下 Sqlserver代理里作业一栏右击新建作业，然后设置相关的一些内容。 ? ? 在步骤一栏新建，然后打开sql脚本文件，会自动加载进去sql语句。 ? ? ? ? ? 然后点击计划，新建一个计划，设置相关的时间间隔，执行频率情况。比如设置5分钟，那么就会自动5分钟执行一次脚本，先同步数据，再删除表数据。 ? ? ? ? 备注补充： 一可能遇到的问题 1因为我设置sqlserver自带的那个cleanup功能时间频次啥的，5分钟让清空日志表数据一次，可是执行了好几遍，日志表始终无法清空数据，所以才在同步数据脚本里在每次执行好同步后跟一句清空日志表数据的sql语句， 实现人为的cleanup。 2因为在执行同步脚本时也是需要执行时间的，在这个过程中，目标表的捕获还在开启中，万一我同步的时候，目标表还在不断增删改，最后那个清空日志表的sql会不会把没有同步的数据记录也删除了，这里存在一个纰漏地方，暂时没想到更好的解决方法。 如果你有更好的想法解决这个问题，可以评论给我帮助。 ? ? ? 二 CDC的一些存储过程和函数链接，链接页面里往下翻有相关内容。 （编辑：李大同） 【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容! |