Sqlserver利用CDC功能实时同步两张表数据
一 适用环境
仅在SQLServer2008（含）以后的企业版、开发版和评估版中可用。
在开启CDC功能前，记得要把sqlserver的代理服务器打开。、
二 CDC功能大概介绍
CDC（change data capture）功能主要捕获SQLServer指定表的增删改操作，由于任何操作都会写日志（哪怕truncate），所以CDC的捕获来源于日志文件。日志文件会把更改应用到数据文件中，同时也会标记符合要求的数据标记为需要添加跟踪的项。然后通过一些配套函数，最后写入到数据仓库中。大概流程如图：
三 具体同步步骤以及截图
1准备工作
现在假设有2个数据库和2张表，db1是我们的目标数据库，里面有一张目标表t_cdc_ta，对目标表的所有增删改查操作，我们想要同步到结果库monitor里的结果表t_cdc_ta里。
首先创建这样2张表
create database db1;
create databasemonitor;
然后创建表结构，如下
CREATETABLE [t_cdc_ta]
(
[id] [int] IDENTITY(1,1) PRIMARY KEY NOT NULL,
[name] [varchar](20) NULL,
[addr] [varchar](20) NULL,
[ttime] [datetime] NULL
)
2对数据库开启CDC功能
开启cdc功能
USEdb1
GO
EXEC sys.sp_cdc_enable_db
-验证
--检查是否开启成功
SELECT is_cdc_enabled ,
CASE WHEN is_cdc_enabled = 0 THEN 'CDC功能禁用'
ELSE 'CDC功能启用'
END 描述
FROM sys.databases
WHERE NAME = 'db1'
--0 :未开启cdc 1：开启cdc
然后可以发现数据库db1里的一些变化
db1的安全性里的用户添加了cdc，架构也添加了cdc
在sqlserver代理里，我们也会发现，增加了目标库的capture和cleanup，一个负责捕获变化，一个负责清除变化。
3对某张表具体开启cdc功能
USE db1;
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','2017-10-1909:56:15.000'),
(3,'zjm3','addr3','2017-10-1909:56:15.000'),
(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 id=@id
END
ELSE IF @oper=2 -- 新增
BEGIN
INSERT INTO dbo.t_scdc_ta(id,NAME,addr,ttime)
VALUES(@id,@name,@addr,@ttime)
END
ELSE IF @oper=4 -- 更新
BEGIN
UPDATE dbo.t_cdc_ta
SET NAME=@name,addr=@addr,ttime=@ttime
WHERE id=@id
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 ,name,addr,ttime fromdb1.cdc.dbo_t_cdc_ta_CT
--打开游标
open user_cur
while@@fetch_status=0
begin
--读取游标
fetch next from user_cur into @oper,@id,@name,@addr,@ttime
use monitor
set identity_insert t_cdc_ta on
exec dbo.p_merge @oper,@id,@name,@addr,@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,id ,name,addr,ttime fromdb1.cdc.dbo_t_cdc_ta_CT
--打开游标
open user_cur
while@@fetch_status=0
begin
--读取游标
fetch next from user_cur into @oper,@id,@name,@addr,@ttime
use monitor
set identity_insert t_cdc_ta on
exec dbo.p_merge @oper,@id,@name,@addr,@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的一些存储过程和函数链接，链接页面里往下翻有相关内容。