Database synchronization Scenarios

All the tables add ' datatsp and Datatsp_int '

--select * from sysobjects where xtype= ' you ' ORDER by name--database all have only one primary key table to add ' datatsp and Datatsp_int ' declare @table_name varchar (), @sql varchar (8000), @col_key varchar (DECLARE) CUR_TB cursor forselect distinct tablename from V_table_des where iskey= ' 1 ' and tablename not as '%bak ' GROUP by tablenamehaving COUNT (*) =1open Cur_tbfetch CUR_TB into @table_namewh Ile @ @FETCH_STATUS =0begin--select * from v_table_desselect @col_key = colname from v_table_des where iskey= ' 1 ' and [email Protected]_nameselect @sql = ' ALTER TABLE ' [email protected]_name+ ' Add datatsp timestamp ' + ' ALTER TABLE ' [email protected] _name+ ' Add datatsp_int bigint ' Print @sqlfetch cur_tb into @table_nameendclose cur_tbdeallocate CUR_TB

2. Detach the primary database and move the MDF file to the backup server to attach the database

3 、--Modify the data version to a consistent--ok

--The primary database--4--changes the primary database data version to a consistent declare @table_name varchar (8000), @sql varchar, @col_key varchar DECLARE CUR_TB  Cursor Forselect DISTINCT tablename from v_table_des where iskey= ' 1 ' and TableName ' is '%bak ' GROUP by tablenamehaving COUNT (*) =1open Cur_tbfetch cur_tb into @table_namewhile @ @FETCH_STATUS =0begin--select * from V_table_desselect @col_key = ColName from v_table_des where iskey= ' 1 ' and [email protected]_nameselect @sql = ' Update ' [email protected]_ Name+ ' Set datatsp_int = Convert (BIGINT,B.DATATSP) from [192.168.1.163].ylbx.dbo. ' [email protected]_name+ ' b where ' [email protected]_name+ '. ' [email protected]_key+ ' =b. ' [email protected]_key+ ' print ' [email protected]_name+ ' Go ' print @ SQLFetch cur_tb into @table_nameendclose cur_tbdeallocate cur_tb--sub-database declare @i int, @start bigint, @end bigint, @sql varchar (8000), @dbname varchar (50)--database name Select @i=0, @start =1, @end =50000while @i<=19beginselect @start =1 + @i* 50000, @end =50000+ @i*50000select @dbname = ' ylbx_ ' +convert (varchar (8), @start) + ' _ ' +convert (varchar (8), @end) Select @sql = ' Update ' [email  protected]+ '. dbo. T_dataattachment set Datatsp_int=convert (BIGINT,B.DATATSP) from [192.168.1.163]. ' [email protected]+ '. dbo. T_dataattachment b where ' [email protected]+ '. dbo. T_dataattachment.personid=b.personid and ' [email protected]+ '. dbo. T_dataattachment.reason=b.reason and ' [email protected]+ '. dbo. T_dataattachment.category=b.categoryprint ' [email protected]+ '. dbo. T_dataattachment ' goupdate ' [email protected]+ '. dbo. T_datacertificate set Datatsp_int=convert (BIGINT,B.DATATSP) from [192.168.1.163]. ' [email protected]+ '. dbo. T_datacertificate b where ' [email protected]+ '. dbo. T_datacertificate.personid=b.personid print ' [email protected]+ '. dbo. T_datacertificate ' goupdate ' [email protected]+ '. dbo. T_datafingerprint set Datatsp_int=convert (BIGINT,B.DATATSP) from [192.168.1.163]. ' [email protected]+ '. dbo. T_datafingerprint b where ' [Email protected]+ '. dbo. T_datafingerprint.personid=b.personid and ' [email protected]+ '. dbo. T_datafingerprint.finger=b.fingerprint ' [email protected]+ '. dbo. T_datafingerprint ' goupdate ' [email protected]+ '. dbo. T_dataphoto set Datatsp_int=convert (BIGINT,B.DATATSP) from [192.168.1.163]. ' [email protected]+ '. dbo. T_dataphoto b where ' [email protected]+ '. dbo. T_dataphoto.personid=b.personid print ' [email protected]+ '. dbo. T_dataphoto ' Go ' print @sqlselect @[email protected] + 1end

4. Database synchronization Process

DECLARE @table_name varchar, @sql varchar (8000), @col_key varchar (@col_name varchar), @sql_upd varchar (8000) , @sql_sel varchar (8000) DECLARE CUR_TB cursor forselect distinct tablename from v_table_des where iskey= ' 1 ' and TableName n  OT like '%bak '--and tablename= ' T_person ' GROUP by tablenamehaving COUNT (*) =1open Cur_tbfetch CUR_TB into @table_namewhile @ @FETCH_STATUS =0begin--select * from v_table_desselect @col_key = colname from v_table_des where iskey= ' 1 ' and [Email&nbs P;protected]_nameselect @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 [email protected]_ Name and ColName not in (' Datatsp_int ', ' datatsp ') open Cur_istfetch cur_ist into @col_namewhile @ @FETCH_STATUS =0beginif @ Col_name <> @col_keybeginselect @[email protected][email protected]_name+ '. ' [email protected]_name+ ' =bb. ' [email protected]_name+ ', ' Endselect @[email protected][email protected]_name+ ', ' fetch cur_ist into @col_name endclose cur_istdeallocate cur_istselect @sql = '--declare @max_id bigint--subscription Database maximum version id--declare @max_id1 bigint--Publish database maximum version id--synchronize primary database--The following order cannot be disrupted--' [email  Protected]_name+ ' Select @max_id =0, @max_id1 =0select @max_id =max (datatsp_int) from ' [email protected]_name+ ' Select @max_id1 =max (CONVERT (BIGINT,DATATSP)) from [192.168.1.163].ylbx.dbo. ' [email protected]_name+ ' with (nolock)--1, delete does not exist delete from ' [email protected]_name+ ' where ' [email  Protected]_key+ ' Not in (select ' [email protected]_key+ ' from [192.168.1.163].ylbx.dbo. ') [email protected]_name+ ' with (NOLOCK))--2, modify Update ' [email protected]_name+ ' Set ' [email protected] [email protected]_name+ '. Datatsp_int=convert (BIGINT,BB.DATATSP) from (select A.* from [192.168.1.163].ylbx.dbo .‘ [Email protected]_name+ ' A With (NOLOCK) where CONVERT (BIGINT,A.DATATSP) > @max_id and CONVERT (bigint,a.datatsp ) <[email protected]_ID1) bbwhere ' [email protected]_name+ '. ' [email protected]_key+ ' =bb. ' [email protected]_key+ '--3, add set identity_insert ' [email protected]_name+ ' OnInsert into ' [email  Protected]_name+ ' (' [email protected]_sel+ ' Datatsp_int) select ' [email protected]_sel+ ' Convert (bigint, DATATSP) as Datatsp_int from [192.168.1.163].ylbx.dbo. ' [email protected]_name+ ' with (NOLOCK) where CONVERT (BIGINT,DATATSP) > @max_id and CONVERT (BIGINT,DATATSP) < [Email protected]_id1 and ' [email protected]_key+ ' Not IN (select ' [email protected]_key+ ' from ' [email  protected]_name+ ') set Identity_insert ' [email protected]_name+ ' off ' print @sqlfetch CUR_TB into @table_ Nameendclose cur_tbdeallocate Cur_ TB----------------------------------------------------------------------------------------------------------------------- --------------------------------------

5, if the database is larger, use Shard synchronization

--Execution results upgrade the publication database and subscription database declare @i int, @start bigint, @end bigint, @sql varchar (max), @dbname varchar (50)--database name Select @i=0, @start =1, @end =50000while @i<=19beginselect @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 ' [email protected]+ ' goalter procedure sys_sync_databaseasbegindeclare @max_id bigint--subscription database maximum version ID declare @max_ ID1 bigint--Release database maximum version ID--t_dataphotoselect @max_id =0, @max_id1 =0select @max_id =max (datatsp_int) from T_ Dataphotoselect @max_Id=min (CONVERT (BIGINT,DATATSP)) from [192.168.1.163]. ' [email protected]+ '. dbo. T_dataphoto with (NOLOCK) where CONVERT (BIGINT,DATATSP) > @max_idselect @max_id1 =max (CONVERT (BIGINT,DATATSP)) from [ 192.168.1.163]. ' [email protected]+ '. dbo. T_dataphoto with (nolock) if @[email protected]_id>=500beginselect @[email protected]_id +500end--1, Delete does not exist for delete from T_dataphoto where PersonID is not in (select PersonID from [192.168.1.163]. ' [email protected]+ '. dbo. T_dataphoto with (NOLOCK))--2, modify 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]. ' [email protected]+ '. dbo. T_dataphoto A with (NOLOCK) where CONVERT (BIGINT,A.DATATSP) >[email protected]_id and CONVERT (bigint,a.datatsp ) <[email protected]_id1) Bbwhere t_dataphoto.personid=bb. personid--3, add--set identity_insert t_Dataphoto OnInsert 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]. ' [email protected]+ '. dbo. T_dataphoto with (NOLOCK) where CONVERT (BIGINT,DATATSP) >[email protected]_id and CONVERT (BIGINT,DATATSP) < [Email protected]_id1 and PersonID not in (select PersonID from T_dataphoto)--set Identity_insert T_dataphoto Off-----------------------------------------------------------------------------------------T_ Datacertificateselect @max_id =0, @max_id1 =0select @max_id =max (datatsp_int) from T_datacertificateselect @max_id =min ( CONVERT (BIGINT,DATATSP)) from [192.168.1.163]. ' [email protected]+ '. dbo. T_datacertificate with (NOLOCK) where CONVERT (BIGINT,DATATSP) > @max_idselect @max_id1 =max (CONVERT (BIGINT,DATATSP) ) from [192.168.1.163]. ' [email protected]+ '. dbo. T_datacertificate with (NOLOCK) if @[email protected]_id>=500beginselecT @[email protected]_id +500end--1, delete does not exist delete from t_datacertificate where PersonID not in (select PersonID from [19 2.168.1.163]. ' [email protected]+ '. dbo. T_datacertificate with (NOLOCK))--2, modify 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]. ' [email protected]+ '. dbo. T_datacertificate A with (NOLOCK) where CONVERT (BIGINT,A.DATATSP) >[email protected]_id and CONVERT (bigint, A.DATATSP) <[email protected]_id1) Bbwhere t_datacertificate.personid=bb. personid--3, add--set identity_insert t_datacertificate OnInsert 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]. ' [email protected]+ '. dbo. T_datacertificate with(NOLOCK) where CONVERT (BIGINT,DATATSP) >[email protected]_id and CONVERT (BIGINT,DATATSP) <[email  Protected]_id1 and PersonID not in (select PersonID from t_datacertificate)--set Identity_insert t_datacertificate Off-----------------------------------------------------------------------------------------T_ Datafingerprintselect @max_id =0, @max_id1 =0select @max_id =max (datatsp_int) from T_datafingerprint select @max_id =min ( CONVERT (BIGINT,DATATSP)) from [192.168.1.163]. ' [email protected]+ '. dbo. T_datafingerprint with (NOLOCK) where CONVERT (BIGINT,DATATSP) > @max_idselect @max_id1 =max (CONVERT (BIGINT,DATATSP) ) from [192.168.1.163]. ' [email protected]+ '. dbo. T_datafingerprint with (nolock) if @[email protected]_id>=2000beginselect @[email protected]_id + 2000end--1, delete does not exist delete from T_datafingerprint where convert (varchar (), PersonID) + "-" +convert (varchar), Finger No in (varchar (PersonID) + "-" +convert (varchar), Finger) from [192.168.1.163]. ' [email protected]+ '. dbo. T_datafingerprint with (NOLOCK))--2, modify 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]. ' [email protected]+ '. dbo. T_datafingerprint A with (NOLOCK) where CONVERT (BIGINT,A.DATATSP) >[email protected]_id and CONVERT (bigint, A.DATATSP) <[email protected]_id1) Bbwhere convert (varchar), T_datafingerprint.personid) + "-" +convert ( varchar ((), T_datafingerprint.finger) =convert (varchar), BB. PersonID) + "-" +convert (varchar), BB. Finger)--3, add--set identity_insert t_datafingerprint OnInsert 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]. ' [email protected]+ '. dbo. T_datafingeRprint with (NOLOCK) where CONVERT (BIGINT,DATATSP) >[email protected]_id and CONVERT (BIGINT,DATATSP) <[ EMAIL&NBSP;PROTECTED]_ID1 and CONVERT (varchar), PersonID) + "-" +convert (varchar), Finger) not in (select Convert (varchar (), PersonID) + "-" +convert (varchar), Finger) from T_datafingerprint)--set Identity_insert t_ Datafingerprint off-----------------------------------------------------------------------------------------T_ Dataattachmentselect @max_id =0, @max_id1 =0select @max_id =max (datatsp_int) from T_dataattachment select @max_id =min ( CONVERT (BIGINT,DATATSP)) from [192.168.1.163]. ' [email protected]+ '. dbo. T_dataattachment with (NOLOCK) where CONVERT (BIGINT,DATATSP) > @max_idselect @max_id1 =max (CONVERT (BIGINT,DATATSP)) from [192.168.1.163]. ' [email protected]+ '. dbo. T_dataattachment with (nolock) if @[email protected]_id>=500beginselect @[email protected]_id + 500end--1, delete does not exist delete from t_dataattachment where convert (varchar (), PersonID) + "-" +convert (VArchar (+), Reason) + "-" +convert (varchar), Category) not (select CONVERT (varchar), PersonID) + "-" +convert ( varchar (+), Reason) + "-" +convert (varchar), Category) from [192.168.1.163]. ' [email protected]+ '. dbo. T_dataattachment with (NOLOCK))--2, modify 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]. ' [email protected]+ '. dbo. T_dataattachment A with (NOLOCK) where CONVERT (BIGINT,A.DATATSP) >[email protected]_id and CONVERT (bigint, A.DATATSP) <[email protected]_id1) Bbwhere convert (varchar), T_dataattachment.personid) + "-" +convert ( varchar (+), T_dataattachment.reason) + "-" +convert (varchar (20), t_dataattachment.category) =convert (varchar), Bb. PersonID) + "-" +convert (varchar), BB. Reason) + "-" +convert (varchar), BB. Category)--3, add--set identity_inserT t_dataattachment OnInsert 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]. ' [email protected]+ '. dbo. T_dataattachment with (NOLOCK) where CONVERT (BIGINT,DATATSP) >[email protected]_id and CONVERT (bigint,datatsp ) &LT;[EMAIL&NBSP;PROTECTED]_ID1 and CONVERT (varchar), PersonID) + "-" +convert (varchar), Reason) + "-" + CONVERT (varchar), Category) not in (select CONVERT (varchar), PersonID) + "-" +convert (varchar), Reason + "-" +convert (varchar (), Category) from t_dataattachment)--set identity_insert t_dataattachment offendgo ' Print @ Sqlselect @[email protected] + 1end

　　

Database synchronization Scenarios