同步两个数据库的示例

 

同步两个数据库的示例

 

有数据

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

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

 

要求：

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

srv1.库名..author的phone字段更新，则srv1.库名..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--属性--启动--并将启动类型设置为自动启动

 

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

企业管理器

--管理

--SQL   Server代理

--右键作业

--新建作业

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

--"步骤"项

--新建

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

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

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

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

--确定

--"调度"项

--新建调度

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

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

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

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

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

 

设置方法:

我的电脑--控制面板--管理工具--服务--右键   SQLSERVERAGENT--属性--启动类型--选择"自动启动"--确定.

 

--3.实现同步处理的方法2,定时同步

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

create   proc   p_process  

as

--更新修改过的数据

update   b   set   name=i.name,telphone=i.telphone

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

where   b.id=i.id   and

(b.name <> i.name   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   Server2000,远程服务器名:xz,用户名为:sa,无密码,测试数据库:test  

  --服务器上的表(查询分析器连接到服务器上创建)  

  create   table   [user](id   int   primary   key,number   varchar(4),name   varchar(10))   

  go  

  --以下在局域网(本机操作)  

  --本机的表,state说明:null   表示新增记录,1   表示修改过的记录,0   表示无变化的记录  

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

  drop   table   [user]  

  GO  

  create   table   [user](id   int   identity(1,1),number   varchar(4),name   varchar(10),

state   bit)  

  go  

  --创建触发器,维护state字段的值  

  create   trigger   t_state   on   [user]  

  after   update  

  as  

  update   [user]   set   state=1  

  from   [user]   a   join   inserted   b   on   a.id=b.id  

  where   a.state   is   not   null  

  go  

   

  --为了方便同步处理,创建链接服务器到要同步的服务器  

  --这里的远程服务器名为:xz,用户名为:sa,无密码  

  if   exists(select   1   from   master..sysservers   where   srvname='srv_lnk')  

  exec   sp_dropserver   'srv_lnk','droplogins'  

  go  

  exec   sp_addlinkedserver     'srv_lnk','','SQLOLEDB','xz'  

  exec   sp_addlinkedsrvlogin   'srv_lnk','false',null,'sa'  

  go  

   

  --创建同步处理的存储过程  

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

  drop   procedure   [dbo].[p_synchro]  

  GO  

  create   proc   p_synchro  

  as  

  --set     XACT_ABORT   on  

  --启动远程服务器的MSDTC服务  

  --exec   master..xp_cmdshell   'isql   /S"xz"   /U"sa"   /P""   /q"exec   master..xp_cmdshell   ''net   start   msdtc'',no_output"',no_output  

   

  --启动本机的MSDTC服务  

  --exec   master..xp_cmdshell   'net   start   msdtc',no_output  

   

  --进行分布事务处理,如果表用标识列做主键,用下面的方法  

  --BEGIN   DISTRIBUTED   TRANSACTION  

  --同步删除的数据  

  delete   from   srv_lnk.test.dbo.[user]  

  where   id   not   in(select   id   from   [user])  

   

  --同步新增的数据  

  insert   into   srv_lnk.test.dbo.[user]  

  select   id,number,name   from   [user]   where   state   is   null  

   

  --同步修改的数据  

  update   srv_lnk.test.dbo.[user]   set  

  number=b.number,name=b.name  

  from   srv_lnk.test.dbo.[user]   a  

  join   [user]   b   on   a.id=b.id  

  where   b.state=1  

   

  --同步后更新本机的标志  

  update   [user]   set   state=0   where   isnull(state,1)=1  

  --COMMIT   TRAN  

  go  

   

  --创建作业,定时执行数据同步的存储过程  

  if   exists(SELECT   1   from   msdb..sysjobs   where   name='数据处理')   

  EXECUTE   msdb.dbo.sp_delete_job   @job_name='数据处理'  

  exec   msdb..sp_add_job   @job_name='数据处理'  

   

  --创建作业步骤  

  declare   @sql   varchar(800),@dbname   varchar(250)  

  select   @sql='exec   p_synchro'     --数据处理的命令  

  ,@dbname=db_name() --执行数据处理的数据库名  

   

  exec   msdb..sp_add_jobstep   @job_name='数据处理',  

  @step_name   =   '数据同步',  

  @subsystem   =   'TSQL',  

  @database_name=@dbname,  

        @command   =   @sql,  

  @retry_attempts   =   5,   --重试次数  

  @retry_interval   =   5     --重试间隔  

   

 

  --创建调度  

  EXEC   msdb..sp_add_jobschedule   @job_name   =   '数据处理',    

  @name   =   '时间安排',  

  @freq_type   =   4,   --每天  

  @freq_interval   =   1, --每天执行一次  

  @active_start_time   =   00000 --0点执行  

  go