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

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

内容导读

互联网集市收集整理的这篇技术教程文章主要介绍了利用Sqlserver的CDC功能实现2张表的同步更新，小编现在分享给大家，供广大互联网技能从业者学习和参考。文章包含5871字，纯文字阅读大概需要9分钟。

内容图文

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

内容总结

以上是互联网集市为您收集整理的利用Sqlserver的CDC功能实现2张表的同步更新全部内容，希望文章能够帮你解决利用Sqlserver的CDC功能实现2张表的同步更新所遇到的程序开发问题。如果觉得互联网集市技术教程内容还不错，欢迎将互联网集市网站推荐给程序员好友。

内容备注

版权声明：本文内容由互联网用户自发贡献，该文观点与技术仅代表作者本人。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容，请发送邮件至 gblab@vip.qq.com 举报，一经查实，本站将立刻删除。

内容手机端

扫描二维码推送至手机访问。

本文链接：https://qyyshop.com/info/917245.html

来源：【匿名】

【上一篇】SQL Server 跨服务器同步或定时同步数据库【下一篇】一个odbc连mssql分页的类

更多 ►

【利用Sqlserver的CDC功能实现2张表的同步更新】教程文章相关的互联网学习教程文章

PHP得到mssql的存储过程的输出参数功能实现_PHP教程

在开发过程中可能会遇到无法取得MSSQL存储过程的输出参数，很多朋友都不知道该怎么办，本文将详细介绍PHP得到mssql的存储过程的输出参数功能实现，需要了解的朋友可以参考下代码如下:$conn=mssql_connect("127.0.0.1","user","passwd"); mssql_select_db("mydb"); $stmt=mssql_init("pr_name",$conn);// $a=50001; mssql_bind($stmt,"RETVAL",$val,SQLVARCHAR); //用于直接返回return -103此类的值。 mssql_bind($stmt,"@outvar",...

PHP得到mssql的存储过程的输出参数功能实现_PHP

PHP得到mssql的存储过程的输出参数功能实现_php技巧

PHP得到mssql的存储过程的输出参数功能实现

在开发过程中可能会遇到无法取得MSSQL存储过程的输出参数，很多朋友都不知道该怎么办，本文将详细介绍PHP得到mssql的存储过程的输出参数功能实现，需要了解的朋友可以参考下代码如下:<? $conn=mssql_connect("127.0.0.1","user","passwd"); mssql_select_db("mydb"); $stmt=mssql_init("pr_name",$conn);// $a=50001; mssql_bind($stmt,"RETVAL",$val,SQLVARCHAR); //用于直接返回return -103此类的值。 mssql_bind($stmt,"@outva...

asp.net+ajax+sqlserver自动补全功能实现解析

代码下载说明：数据库连接字符串在web.config文件中，为方便运行使用的是官方的Northwind数据库。参考(向其作者致敬)： ² http://www.loveweb8.com/plus/demo.php?aid=57这个例子是html源码。利用jquery.autocomplete插件利用js实现了自动补全功能。由于我的需要是结合sqlserver数据库表实现自动补全功能。就下来就是将数据库表转为js数组，自然而然就想到了ajax。 ² Asp.NetAjax的两种基本开发模式这篇文章中的第二部...

[转]使用SQLServer2008的CDC功能实现数据变更捕获

USE AdventureWorks ; GO EXECUTE sys . sp_cdc_enable_table @source_schema = NPerson , @source_name = NADDRESS , @role_name = Ncdc_Admin -- 可以自动创建 , @capture_instance = DEFAULT GO EXECUTE sys . sp_cdc_enable_table @source_schemaUSE AdventureWorks; GO EXECUTE sys.sp_cdc_enable_table@source_schema = NPerson, @source_name = NADDRESS, @role_name = Ncdc_Admin--可以自动创建, @capture_instance=DEFAUL...

使用SQLServer2008的CDC功能实现数据变更捕获

ROW_NUMBERSQLServer2005的LIMIT功能实现(ROW_NUMBER()排序函

SQL Server 2005新增了一个ROW_NUMBER()函数，通过它可实现类似MySQL下的LIMIT功能。下面的语法说明摘自SQL Server 2005的帮助文件语法： ROW_NUMBER() OVER([ ] ) 备注： ORDER BY子句可确定在特定分区中为行分配唯一ROW_NUMBER的顺序。参数：将FROM子句生成的结果集划入应用了ROW_NUMBER函数的分区。确定将ROW_NUMBER值分配给分区中的行的顺序。有关详细信息，请参阅ORDER BY子句(Transact-SQL)。返回类型： bigint 类似于...

利用MSSQL复制功能实现与Oracle数据库同步

1.分别建立链接对数据库进行操作，SQLServer可以用ADO.NET,操作Oracle可以用OLEDB或者用System.Data.OracleClient(需要添加引用才能用) 这种方案的优点就是简单，各自写各自的数据库操作代码，缺点就是不能将两个数据库中的表直接进行联合查询，链接Oracle的　　1.分别建立链接对数据库进行操作，SQLServer可以用ADO.NET,操作Oracle可以用OLEDB或者用System.Data.OracleClient(需要添加引用才能用)这种方案的优点就是简单，各自写...

SQLServer2008CDC功能实现数据变更捕获脚本

CDC:Change Data Capture 代码如下:--步骤：本文中以GPOSDB为例 --第一步、对目标库显式启用CDC：--在当前库使用sys.sp_cdc_enable_db。返回0（成功）或1（失败）。--注意，无法对系统数据库和分发数据库启用该功能。且执行者需要用sysadmin角色权限。--该存储过程的作用域是整个目标库。包含元数据、DDL触发器、cdc架构和cdc用户。--使用以下代码启用：USE GPOSDB --要启用CDC的数据库GOEXECUTE sys.sp_cdc_enable_db;GO--在一开...

ROW_NUMBERSQLServer2005的LIMIT功能实现(ROW_NUMBER()排序函数)

语法： ROW_NUMBER() OVER([ <partition_by_clause>] <order_by_clause>) 备注： ORDER BY子句可确定在特定分区中为行分配唯一ROW_NUMBER的顺序。参数： <partition_by_clause> 将FROM子句生成的结果集划入应用了ROW_NUMBER函数的分区。 <order_by_clause> 确定将ROW_NUMBER值分配给分区中的行的顺序。有关详细信息，请参阅ORDER BY子句(Transact-SQL)。返回类型： bigint 类似于MySQL的LIMIT功能语法实例：代码如下: SELECT *...