通过作业定时同步两个数据库.doc

通过作业,定时同步两个数据库-定时同步服务器上的数据-例子:-测试环境,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(Nuser) and OBJECTPROPERTY(id, NIsUserTable) = 1)drop table userGOcreate table user(id int identity(1,1),number varchar(4),name varchar(10),state bit)go-创建触发器,维护state字段的值create trigger t_state on userafter updateasupdate user set state=1from user a join inserted b on a.id=b.idwhere a.state is not nullgo-为了方便同步处理,创建链接服务器到要同步的服务器-这里的远程服务器名为:xz,用户名为:sa,无密码if exists(select 1 from master.sysservers where srvname=srv_lnk)exec sp_dropserver srv_lnk,droploginsgoexec sp_addlinkedserver srv_lnk,SQLOLEDB,xzexec sp_addlinkedsrvlogin srv_lnk,false,null,sago-创建同步处理的存储过程if exists (select * from dbo.sysobjects where id = object_id(Ndbo.p_synchro) and OBJECTPROPERTY(id, NIsProcedure) = 1)drop procedure dbo.p_synchroGOcreate proc p_synchroas-set XACT_ABORT on-启动远程服务器的MSDTC服务-exec master.xp_cmdshell isql /Sxz /Usa /P /qexec 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.userwhere id not in(select id from user)-同步新增的数据insert into srv_lnk.test.dbo.userselect id,number,name from user where state is null-同步修改的数据update srv_lnk.test.dbo.user setnumber=b.number,name=from srv_lnk.test.dbo.user ajoin user b on a.id=b.idwhere b.state=1-同步后更新本机的标志update user set state=0 where isnull(state,1)=1-COMMIT TRANgo-创建作业,定时执行数据同步的存储过程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_jobsc