数据库同步方案
1--所有的表添加‘datatsp和datatsp_int‘
--select * from sysobjects where xtype=‘U‘ order by name --数据库中所有的只具有一个主键表添加‘datatsp和datatsp_int‘ declare @table_name varchar(50), @sql varchar(8000), @col_key varchar(50) declare cur_tb cursor for select distinct tablename from v_table_des where iskey=‘1‘ and tablename not like ‘%bak‘ group by tablename having COUNT(*)=1 open cur_tb fetch cur_tb into @table_name while @@FETCH_STATUS=0 begin --select * from v_table_des select @col_key= colname from v_table_des where iskey=‘1‘ and tablename=@table_name select @sql=‘alter table ‘+@table_name+‘ add datatsp timestamp ‘ +‘alter table ‘+@table_name+‘ add datatsp_int bigint ‘ print @sql fetch cur_tb into @table_name end close cur_tb deallocate cur_tb
2、将主数据库分离,将mdf文件移到备份服务器上附加数据库
3、--将数据版本修改为一致--ok
--主数据库 --4--将主数据库 数据版本修改为一致 declare @table_name varchar(50), @sql varchar(8000), @col_key varchar(50) declare cur_tb cursor for select distinct tablename from v_table_des where iskey=‘1‘ and tablename not like ‘%bak‘ group by tablename having COUNT(*)=1 open cur_tb fetch cur_tb into @table_name while @@FETCH_STATUS=0 begin --select * from v_table_des select @col_key= colname from v_table_des where iskey=‘1‘ and tablename=@table_name select @sql=‘update ‘+@table_name+‘ set datatsp_int = convert(bigint,b.datatsp) from [192.168.1.163].ylbx.dbo.‘+@table_name+‘ b where ‘+@table_name+‘.‘+@col_key+‘=b.‘+@col_key+‘ print ‘‘‘+@table_name+‘‘‘ go ‘ print @sql fetch cur_tb into @table_name end close cur_tb deallocate cur_tb --分数据库 declare @i int, @start bigint, @end bigint, @sql varchar(8000), @dbname varchar(50)--数据库名称 select @i=0,@start=1,@end=50000 while @i<=19 begin select @start=1 + @i*50000, @end=50000+ @i*50000 select @dbname=‘ylbx_‘+convert(varchar(8),@start)+‘_‘+convert(varchar(8),@end) select @sql=‘ update ‘+@dbname+‘.dbo.T_DataAttachment set datatsp_int=convert(bigint,b.datatsp) from [192.168.1.163].‘+@dbname+‘.dbo.T_DataAttachment b where ‘+@dbname+‘.dbo.T_DataAttachment.PersonID=b.PersonID and ‘+@dbname+‘.dbo.T_DataAttachment.Reason=b.Reason and ‘+@dbname+‘.dbo.T_DataAttachment.Category=b.Category print ‘‘‘+@dbname+‘.dbo.T_DataAttachment‘‘ go update ‘+@dbname+‘.dbo.T_DataCertificate set datatsp_int=convert(bigint,b.datatsp) from [192.168.1.163].‘+@dbname+‘.dbo.T_DataCertificate b where ‘+@dbname+‘.dbo.T_DataCertificate.PersonID=b.PersonID print ‘‘‘+@dbname+‘.dbo.T_DataCertificate‘‘ go update ‘+@dbname+‘.dbo.T_DataFingerprint set datatsp_int=convert(bigint,b.datatsp) from [192.168.1.163].‘+@dbname+‘.dbo.T_DataFingerprint b where ‘+@dbname+‘.dbo.T_DataFingerprint.PersonID=b.PersonID and ‘+@dbname+‘.dbo.T_DataFingerprint.Finger=b.Finger print ‘‘‘+@dbname+‘.dbo.T_DataFingerprint‘‘ go update ‘+@dbname+‘.dbo.T_DataPhoto set datatsp_int=convert(bigint,b.datatsp) from [192.168.1.163].‘+@dbname+‘.dbo.T_DataPhoto b where ‘+@dbname+‘.dbo.T_DataPhoto.PersonID=b.PersonID print ‘‘‘+@dbname+‘.dbo.T_DataPhoto‘‘ go‘ print @sql select @i=@i + 1 end
4、数据库同步过程
declare @table_name varchar(50), @sql varchar(8000), @col_key varchar(50), @col_name varchar(50), @sql_upd varchar(8000), @sql_sel varchar(8000) declare cur_tb cursor for select distinct tablename from v_table_des where iskey=‘1‘ and tablename not like ‘%bak‘ --and tablename=‘t_person‘ group by tablename having COUNT(*)=1 open cur_tb fetch cur_tb into @table_name while @@FETCH_STATUS=0 begin --select * from v_table_des select @col_key= colname from v_table_des where iskey=‘1‘ and tablename=@table_name select @sql_upd=‘‘ select @sql_sel=‘‘ declare cur_ist cursor for--select * from v_table_des where tablename=‘t_person‘ and colname<>‘datatsp_int‘ select colname from v_table_des where tablename=@table_name and colname not in(‘datatsp_int‘,‘datatsp‘) open cur_ist fetch cur_ist into @col_name while @@FETCH_STATUS=0 begin if @col_name <>@col_key begin select @sql_upd=@sql_upd+@table_name+‘.‘+@col_name+‘=bb.‘+@col_name+‘,‘ end select @sql_sel=@sql_sel+@col_name+‘,‘ fetch cur_ist into @col_name end close cur_ist deallocate cur_ist select @sql=‘ --declare @max_id bigint--订阅数据库最大版本id --declare @max_id1 bigint--发布数据库最大版本id --同步主数据库--下列顺序不能打乱--‘+@table_name+‘ select @max_id=0,@max_id1=0 select @max_id=max(datatsp_int) from ‘+@table_name+‘ select @max_id1=max(CONVERT(bigint,datatsp)) from [192.168.1.163].ylbx.dbo.‘+@table_name+‘ with(nolock) --1、删除不存在 delete from ‘+@table_name+‘ where ‘+@col_key+‘ not in(select ‘+@col_key+‘ from [192.168.1.163].ylbx.dbo.‘+@table_name+‘ with(nolock) ) --2、修改 update ‘+@table_name+‘ set ‘+@sql_upd+@table_name+‘.datatsp_int=convert(bigint,bb.datatsp) from ( select a.* from [192.168.1.163].ylbx.dbo.‘+@table_name+‘ a with(nolock) where CONVERT(bigint,a.datatsp)>@max_id and CONVERT(bigint,a.datatsp)<=@max_id1 )bb where ‘+@table_name+‘.‘+@col_key+‘=bb.‘+@col_key+‘ --3、添加 set identity_insert ‘+@table_name+‘ on insert into ‘+@table_name+‘(‘+@sql_sel+‘datatsp_int) select ‘+@sql_sel+‘convert(bigint,datatsp) as datatsp_int from [192.168.1.163].ylbx.dbo.‘+@table_name+‘ with(nolock) where CONVERT(bigint,datatsp)>@max_id and CONVERT(bigint,datatsp)<=@max_id1 and ‘+@col_key+‘ not in(select ‘+@col_key+‘ from ‘+@table_name+‘) set identity_insert ‘+@table_name+‘ off ‘ print @sql fetch cur_tb into @table_name end close cur_tb deallocate cur_tb -------------------------------------------------------------------------------------------------------------------------------------------------------------
5、如果数据库比较大,采用分片同步
--执行的结果升级发布数据库和订阅数据库 declare @i int, @start bigint, @end bigint, @sql varchar(max), @dbname varchar(50)--数据库名称 select @i=0,@start=1,@end=50000 while @i<=19 begin select @start=1 + @i*50000, @end=50000+ @i*50000 --select @sql=‘union all SELECT PersonID,Data,OperatorID,OperatorTime,Data_All from ylbx_‘+convert(varchar(8),@start)+‘_‘+convert(varchar(8),@end)+‘..t_dataphoto‘ --select @sql=‘union all SELECT PersonID,Data,Img,Finger,OperatorID,OperatorTime from ylbx_‘+convert(varchar(8),@start)+‘_‘+convert(varchar(8),@end)+‘..T_DataFingerprint‘ --select @sql=‘union all SELECT * from ylbx_‘+convert(varchar(8),@start)+‘_‘+convert(varchar(8),@end)+‘..T_DataCertificate‘ select @dbname=‘ylbx_‘+convert(varchar(8),@start)+‘_‘+convert(varchar(8),@end) select @sql=‘use ‘+@dbname+‘ go alter procedure sys_sync_database as begin declare @max_id bigint--订阅数据库最大版本id declare @max_id1 bigint--发布数据库最大版本id --T_DataPhoto select @max_id=0,@max_id1=0 select @max_id=max(datatsp_int) from T_DataPhoto select @max_id=min(CONVERT(bigint,datatsp)) from [192.168.1.163].‘+@dbname+‘.dbo.T_DataPhoto with(nolock) where CONVERT(bigint,datatsp)>@max_id select @max_id1=max(CONVERT(bigint,datatsp)) from [192.168.1.163].‘+@dbname+‘.dbo.T_DataPhoto with(nolock) if @max_id1-@max_id>=500 begin select @max_id1=@max_id +500 end --1、删除不存在 delete from T_DataPhoto where PersonID not in(select PersonID from [192.168.1.163].‘+@dbname+‘.dbo.T_DataPhoto with(nolock)) --2、修改 update T_DataPhoto set T_DataPhoto.Data=bb.Data,T_DataPhoto.OperatorID=bb.OperatorID,T_DataPhoto.OperatorTime=bb.OperatorTime,T_DataPhoto.Data_All=bb.Data_All,T_DataPhoto.datatsp_int=convert(bigint,bb.datatsp) from ( select a.* from [192.168.1.163].‘+@dbname+‘.dbo.T_DataPhoto a with(nolock) where CONVERT(bigint,a.datatsp)>=@max_id and CONVERT(bigint,a.datatsp)<=@max_id1 )bb where T_DataPhoto.PersonID=bb.PersonID --3、添加 --set identity_insert T_DataPhoto on insert into T_DataPhoto(PersonID,Data,OperatorID,OperatorTime,Data_All,datatsp_int) select PersonID,Data,OperatorID,OperatorTime,Data_All,convert(bigint,datatsp) as datatsp_int from [192.168.1.163].‘+@dbname+‘.dbo.T_DataPhoto with(nolock) where CONVERT(bigint,datatsp)>=@max_id and CONVERT(bigint,datatsp)<=@max_id1 and PersonID not in(select PersonID from T_DataPhoto) --set identity_insert T_DataPhoto off --------------------------------------------------------------------------------------- --T_DataCertificate select @max_id=0,@max_id1=0 select @max_id=max(datatsp_int) from T_DataCertificate select @max_id=min(CONVERT(bigint,datatsp)) from [192.168.1.163].‘+@dbname+‘.dbo.T_DataCertificate with(nolock) where CONVERT(bigint,datatsp)>@max_id select @max_id1=max(CONVERT(bigint,datatsp)) from [192.168.1.163].‘+@dbname+‘.dbo.T_DataCertificate with(nolock) if @max_id1-@max_id>=500 begin select @max_id1=@max_id +500 end --1、删除不存在 delete from T_DataCertificate where PersonID not in(select PersonID from [192.168.1.163].‘+@dbname+‘.dbo.T_DataCertificate with(nolock)) --2、修改 update T_DataCertificate set T_DataCertificate.Data=bb.Data,T_DataCertificate.OperatorID=bb.OperatorID,T_DataCertificate.OperatorTime=bb.OperatorTime,T_DataCertificate.memo=bb.memo,T_DataCertificate.datatsp_int=convert(bigint,bb.datatsp) from ( select a.* from [192.168.1.163].‘+@dbname+‘.dbo.T_DataCertificate a with(nolock) where CONVERT(bigint,a.datatsp)>=@max_id and CONVERT(bigint,a.datatsp)<=@max_id1 )bb where T_DataCertificate.PersonID=bb.PersonID --3、添加 --set identity_insert T_DataCertificate on insert into T_DataCertificate(PersonID,Data,OperatorID,OperatorTime,memo,datatsp_int) select PersonID,Data,OperatorID,OperatorTime,memo,convert(bigint,datatsp) as datatsp_int from [192.168.1.163].‘+@dbname+‘.dbo.T_DataCertificate with(nolock) where CONVERT(bigint,datatsp)>=@max_id and CONVERT(bigint,datatsp)<=@max_id1 and PersonID not in(select PersonID from T_DataCertificate) --set identity_insert T_DataCertificate off --------------------------------------------------------------------------------------- --T_DataFingerprint select @max_id=0,@max_id1=0 select @max_id=max(datatsp_int) from T_DataFingerprint select @max_id=min(CONVERT(bigint,datatsp)) from [192.168.1.163].‘+@dbname+‘.dbo.T_DataFingerprint with(nolock) where CONVERT(bigint,datatsp)>@max_id select @max_id1=max(CONVERT(bigint,datatsp)) from [192.168.1.163].‘+@dbname+‘.dbo.T_DataFingerprint with(nolock) if @max_id1-@max_id>=2000 begin select @max_id1=@max_id +2000 end --1、删除不存在 delete from T_DataFingerprint where convert(varchar(20),PersonID)+‘‘-‘‘+convert(varchar(20),Finger) not in(select convert(varchar(20),PersonID)+‘‘-‘‘+convert(varchar(20),Finger) from [192.168.1.163].‘+@dbname+‘.dbo.T_DataFingerprint with(nolock)) --2、修改 update T_DataFingerprint set T_DataFingerprint.Data=bb.Data,T_DataFingerprint.Img=bb.Img,T_DataFingerprint.OperatorID=bb.OperatorID,T_DataFingerprint.OperatorTime=bb.OperatorTime,T_DataFingerprint.datatsp_int=convert(bigint,bb.datatsp) from ( select a.* from [192.168.1.163].‘+@dbname+‘.dbo.T_DataFingerprint a with(nolock) where CONVERT(bigint,a.datatsp)>=@max_id and CONVERT(bigint,a.datatsp)<=@max_id1 )bb where convert(varchar(20),T_DataFingerprint.PersonID)+‘‘-‘‘+convert(varchar(20),T_DataFingerprint.Finger)=convert(varchar(20),bb.PersonID)+‘‘-‘‘+convert(varchar(20),bb.Finger) --3、添加 --set identity_insert T_DataFingerprint on insert into T_DataFingerprint(PersonID,Data,Img,Finger,OperatorID,OperatorTime,datatsp_int) select PersonID,Data,Img,Finger,OperatorID,OperatorTime,convert(bigint,datatsp) as datatsp_int from [192.168.1.163].‘+@dbname+‘.dbo.T_DataFingerprint with(nolock) where CONVERT(bigint,datatsp)>=@max_id and CONVERT(bigint,datatsp)<=@max_id1 and convert(varchar(20),PersonID)+‘‘-‘‘+convert(varchar(20),Finger) not in(select convert(varchar(20),PersonID)+‘‘-‘‘+convert(varchar(20),Finger) from T_DataFingerprint) --set identity_insert T_DataFingerprint off --------------------------------------------------------------------------------------- --T_DataAttachment select @max_id=0,@max_id1=0 select @max_id=max(datatsp_int) from T_DataAttachment select @max_id=min(CONVERT(bigint,datatsp)) from [192.168.1.163].‘+@dbname+‘.dbo.T_DataAttachment with(nolock) where CONVERT(bigint,datatsp)>@max_id select @max_id1=max(CONVERT(bigint,datatsp)) from [192.168.1.163].‘+@dbname+‘.dbo.T_DataAttachment with(nolock) if @max_id1-@max_id>=500 begin select @max_id1=@max_id +500 end --1、删除不存在 delete from T_DataAttachment where convert(varchar(20),PersonID)+‘‘-‘‘+convert(varchar(50),Reason)+‘‘-‘‘+convert(varchar(50),Category) not in(select convert(varchar(20),PersonID)+‘‘-‘‘+convert(varchar(50),Reason)+‘‘-‘‘+convert(varchar(50),Category) from [192.168.1.163].‘+@dbname+‘.dbo.T_DataAttachment with(nolock)) --2、修改 update T_DataAttachment set T_DataAttachment.Data=bb.Data,T_DataAttachment.OperatorID=bb.OperatorID,T_DataAttachment.OperatorTime=bb.OperatorTime,T_DataAttachment.memo=bb.memo,T_DataAttachment.datatsp_int=convert(bigint,bb.datatsp) from ( select a.* from [192.168.1.163].‘+@dbname+‘.dbo.T_DataAttachment a with(nolock) where CONVERT(bigint,a.datatsp)>=@max_id and CONVERT(bigint,a.datatsp)<=@max_id1 )bb where convert(varchar(20),T_DataAttachment.PersonID)+‘‘-‘‘+convert(varchar(50),T_DataAttachment.Reason)+‘‘-‘‘+convert(varchar(50),T_DataAttachment.Category)=convert(varchar(20),bb.PersonID)+‘‘-‘‘+convert(varchar(50),bb.Reason)+‘‘-‘‘+convert(varchar(50),bb.Category) --3、添加 --set identity_insert T_DataAttachment on insert into T_DataAttachment(PersonID,Reason,Category,Data,OperatorID,OperatorTime,memo,datatsp_int) select PersonID,Reason,Category,Data,OperatorID,OperatorTime,memo,convert(bigint,datatsp) as datatsp_int from [192.168.1.163].‘+@dbname+‘.dbo.T_DataAttachment with(nolock) where CONVERT(bigint,datatsp)>=@max_id and CONVERT(bigint,datatsp)<=@max_id1 and convert(varchar(20),PersonID)+‘‘-‘‘+convert(varchar(50),Reason)+‘‘-‘‘+convert(varchar(50),Category) not in(select convert(varchar(20),PersonID)+‘‘-‘‘+convert(varchar(50),Reason)+‘‘-‘‘+convert(varchar(50),Category) from T_DataAttachment) --set identity_insert T_DataAttachment off end go ‘ print @sql select @i=@i + 1 end
郑重声明:本站内容如果来自互联网及其他传播媒体,其版权均属原媒体及文章作者所有。转载目的在于传递更多信息及用于网络分享,并不代表本站赞同其观点和对其真实性负责,也不构成任何其他建议。