利用Sqlserver的CDC功能实现2张表的同步更新

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;

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;

EXECUTEsys.sp_cdc_enable_table

@source_schema = 'dbo'

, @source_name = 't_cdc_ta'

, @role_name = 'cdc_Admin'--可以自动创建

, @capture_instance=DEFAULT

执行完后，我们会发现，在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

-- 删除

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

此时，就可以看到写好的存储过程了

在写一个游标的代码，一条一条遍历日志表的数据，嵌套刚才存储过程

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