数据库同步方案

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