同步两个数据库中两个表的示例

同步两个数据库中两个表的示例

测试环境及同步要求:

有数据库服务器srv1和srv2,两台电脑能互相访问,有数据

srv1.库名..author有字段:id,name,phone,

srv2.库名..author有字段:id,name,telphone,adress

要求：

srv1.库名..author增加记录则srv2.库名..author记录增加

srv1.库名..author的phone字段更新，则srv2.库名..author对应字段telphone更新～--*/

--大致的处理步骤

--1.在srv1 上创建连接服务器,以便在srv1 中操作srv2,实现同步

exec sp_addlinkedserver 'srv2','','SQLOLEDB','srv2的sql实例名或ip'

exec sp_addlinkedsrvlogin 'srv2','false',null,'用户名','密码'

go

--2.在srv1 和srv2 这两台电脑中,启动msdtc(分布式事务处理服务),并且设置为自动启动

我的电脑--控制面板--管理工具--服务--右键Distributed Transaction Coordinator--属性--启动--并将启动类型设置为自动启动

go

--3.实现同步处理

--a.在srv1..author中创建触发器,实现数据即时同步

--新增同步

create trigger tr_insert_author on author

for insert

as

set xact_abort on --如果执行Transact-SQL 语句产生运行时错误，则整个事务将终止并回滚。

insert srv2.库名.dbo.author(id,name,telphone)

select id, name, telphone from inserted

go

--修改同步

create trigger tr_update_author on author

for update

as

set xact_abort on

update b set name=,telphone=i.telphone

from srv2.库名.dbo.author b,inserted i

where b.id=i.id

go

--删除同步

create trigger tr_delete_author on author

for delete

as

set xact_abort on

delete b

from srv2.库名.dbo.author b,deleted d

where b.id=d.id

go

上面的触发器是同步更新到链接的同构sql server服务器上的，如果链接服务器不是24小时都可用的，则要定时提交更新

--在srv1中创建如下的同步处理存储过程

create proc p_process

as

--更新修改过的数据

update b set name=,telphone=i.telphone

from srv2.库名.dbo.author b,author i

where b.id=i.id and

(<> or b.telphone<>i.telphone)

--插入新增的数据

insert srv2.库名.dbo.author(id,name,telphone)

select id,name,telphone from author i

where not exists(

select * from srv2.库名.dbo.author where id=i.id)

--删除已经删除的数据(如果需要的话)

delete b

from srv2.库名.dbo.author b

where not exists(

select * from author where id=b.id)

go

--然后创建一个作业定时调用上面的同步处理存储过程就行了

企业管理器

--管理

--SQL Server代理

--右键作业

--新建作业

--"常规"项中输入作业名称

--"步骤"项

--新建

--"步骤名"中输入步骤名

--"类型"中选择"Transact-SQL 脚本(TSQL)"

--"数据库"选择执行命令的数据库

--"命令"中输入要执行的语句: exec p_process

--确定

--"调度"项

--新建调度

--"名称"中输入调度名称

--"调度类型"中选择你的作业执行安排

--如果选择"反复出现"

--点"更改"来设置你的时间安排

然后将SQL Agent服务启动,并设置为自动启动,否则你的作业不会被执行

总结：

1. 这个是用于同构的sql server服务器之间的同步

2. 没有采用增量同步技术，直接对比两张表找出不同，大数据量下不适用。

如果只是简单的数据同步,可以用触发器来实现.下面是例子:

--测试环境:SQL2000,远程主机名:xz,用户名:sa,密码:无,数据库名:test

--创建测试表,不能用标识列做主键,因为不能进行正常更新

--在本机上创建测试表,远程主机上也要做同样的建表操作,只是不写触发

器

if exists (select * from dbo.sysobjects where id = object_id(N'[test]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)

drop table [test]

create table test(id int not null constraint PK_test primary key

,name varchar(10))

go

--创建同步的触发器

create trigger t_test on test