MSSQL服务器数据库同步指南
增记录,1表示修改过的记录,0表示无变化的记录
ifexists(select*fromdbo.sysobjectswhereid=object_id(N''[user]'')andOBJECTPROPERTY(id,N''IsUserTable'')=1)
droptable[user]
GO
createtable[user](idintidentity(1,1),numbervarchar(4),namevarchar(10),statebit)
go
--创建触发器,维护state字段的值
createtriggert_stateon[user]
afterupdate
as
update[user]setstate=1
from[user]ajoininsertedbona.id=b.id
wherea.stateisnotnull
go
--为了方便同步处理,创建链接服务器到要同步的服务器
--这里的远程服务器名为:xz,用户名为:sa,无密码
ifexists(select1frommaster..sysserverswheresrvname=''srv_lnk'')
execsp_dropserver''srv_lnk'',''droplogins''
go
execsp_addlinkedserver''srv_lnk'','''',''SQLOLEDB'',''xz''
execsp_addlinkedsrvlogin''srv_lnk'',''false'',null,''sa''
go
--创建同步处理的存储过程
ifexists(select*fromdbo.sysobjectswhereid=object_id(N''[dbo].[p_synchro]'')andOBJECTPROPERTY(id,N''IsProcedure'')=1)
dropprocedure[dbo].[p_synchro]
GO
createprocp_synchro
as
--setXACT_ABORTon
--启动远程服务器的MSDTC服务
--execmaster..xp_cmdshell''isql/S"xz"/U"sa"/P""/q"execmaster..xp_cmdshell''''netstartmsdtc'''',no_output"'',no_output
--启动本机的MSDTC服务
--execmaster..xp_cmdshell''netstartmsdtc'',no_output
--进行分布事务处理,如果表用标识列做主键,用下面的方法
--BEGINDISTRIBUTEDTRANSACTION
--同步删除的数据
deletefromsrv_lnk.test.dbo.[user]
whereidnotin(selectidfrom[user])
--同步新增的数据
insertintosrv_lnk.test.dbo.[user]
selectid,number,namefrom[user]wherestateisnull
--同步修改的数据
updatesrv_lnk.test.dbo.[user]set
number=b.number,name=b.name
fromsrv_lnk.test.dbo.[user]a
join[user]bona.id=b.id
whereb.state=1
--同步后更新本机的标志
update[user]setstate=0whereisnull(state,1)=1
--COMMITTRAN
go
--创建作业,定时执行数据同步的存储过程
ifexists(SELECT1frommsdb..sysjobswherename=''数据处理'')
EXECUTEmsdb.dbo.sp_delete_job@job_name=''数据处理''
execmsdb..sp_add_job@job_name=''数据处理''
--创建作业步骤
declare@sqlvarchar(800),@dbnamevarchar(250)
select@sql=''execp_synchro''--数据处理的命令
,@dbname=db_name()--执行数据处理的数据库名
execmsdb..sp_add_jobstep@job_name=''数据处理'',
@step_name=''数据同步'',
@subsystem=''TSQL'',
@database_name=@dbname,
@command=@sql,
@retry_attempts=5,--重试次数
@retry_interval=5--重试间隔
--创建调度
EXECmsdb..sp_add_jobschedule@job_name=''数据处理'',
@name=''时间安排'',
@freq_type=4,--每天
@freq_interval=1,--每天执行一次
@active_start_time=00000--0点执行
go
ifexists(select*fromdbo.sysobjectswhereid=object_id(N''[user]'')andOBJECTPROPERTY(id,N''IsUserTable'')=1)
droptable[user]
GO
createtable[user](idintidentity(1,1),numbervarchar(4),namevarchar(10),statebit)
go
--创建触发器,维护state字段的值
createtriggert_stateon[user]
afterupdate
as
update[user]setstate=1
from[user]ajoininsertedbona.id=b.id
wherea.stateisnotnull
go
--为了方便同步处理,创建链接服务器到要同步的服务器
--这里的远程服务器名为:xz,用户名为:sa,无密码
ifexists(select1frommaster..sysserverswheresrvname=''srv_lnk'')
execsp_dropserver''srv_lnk'',''droplogins''
go
execsp_addlinkedserver''srv_lnk'','''',''SQLOLEDB'',''xz''
execsp_addlinkedsrvlogin''srv_lnk'',''false'',null,''sa''
go
--创建同步处理的存储过程
ifexists(select*fromdbo.sysobjectswhereid=object_id(N''[dbo].[p_synchro]'')andOBJECTPROPERTY(id,N''IsProcedure'')=1)
dropprocedure[dbo].[p_synchro]
GO
createprocp_synchro
as
--setXACT_ABORTon
--启动远程服务器的MSDTC服务
--execmaster..xp_cmdshell''isql/S"xz"/U"sa"/P""/q"execmaster..xp_cmdshell''''netstartmsdtc'''',no_output"'',no_output
--启动本机的MSDTC服务
--execmaster..xp_cmdshell''netstartmsdtc'',no_output
--进行分布事务处理,如果表用标识列做主键,用下面的方法
--BEGINDISTRIBUTEDTRANSACTION
--同步删除的数据
deletefromsrv_lnk.test.dbo.[user]
whereidnotin(selectidfrom[user])
--同步新增的数据
insertintosrv_lnk.test.dbo.[user]
selectid,number,namefrom[user]wherestateisnull
--同步修改的数据
updatesrv_lnk.test.dbo.[user]set
number=b.number,name=b.name
fromsrv_lnk.test.dbo.[user]a
join[user]bona.id=b.id
whereb.state=1
--同步后更新本机的标志
update[user]setstate=0whereisnull(state,1)=1
--COMMITTRAN
go
--创建作业,定时执行数据同步的存储过程
ifexists(SELECT1frommsdb..sysjobswherename=''数据处理'')
EXECUTEmsdb.dbo.sp_delete_job@job_name=''数据处理''
execmsdb..sp_add_job@job_name=''数据处理''
--创建作业步骤
declare@sqlvarchar(800),@dbnamevarchar(250)
select@sql=''execp_synchro''--数据处理的命令
,@dbname=db_name()--执行数据处理的数据库名
execmsdb..sp_add_jobstep@job_name=''数据处理'',
@step_name=''数据同步'',
@subsystem=''TSQL'',
@database_name=@dbname,
@command=@sql,
@retry_attempts=5,--重试次数
@retry_interval=5--重试间隔
--创建调度
EXECmsdb..sp_add_jobschedule@job_name=''数据处理'',
@name=''时间安排'',
@freq_type=4,--每天
@freq_interval=1,--每天执行一次
@active_start_time=00000--0点执行
go