数据库增量同步(二台SQL Server服务器的数据库之间增量传输数据)(jaime原创)

数据库增量同步(MS SQL Server)

-----------------------------------------------------------------------

--支持原创,转载时请保留下面,以供大家加我MSN,增强交流,共同学习.
--姜庭华  msn: [e-mail]
--博客:http://blog.csdn.net/jaimejth

 

数据库增量同步必须具备以下条件
一.每张表必须有主键
二.每张表必须有一个最后更新日期栏位(时间类型).(也就是说.在程序及业务处理中,如果对某条数据做了更新就必须更新最后更新日期栏位.这在现有很多系统都有这个栏位,主要便于后台管理.)

执行步骤:

一.首先建立表sys_tran_info,这张表是传输配置的基本表.

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sys_tran_info]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[sys_tran_info]

CREATE TABLE [dbo].[sys_tran_info] (
 [id] [numeric](18, 0) IDENTITY (1, 1) NOT NULL ,   --自增列
 [source_server] [varchar] (50)   NULL ,    --源服务器(链接服务器名或本机为空)
 [source_db] [varchar] (50)   NULL ,        --源数据库名称
 [target_server] [varchar] (50)   NULL ,    --目的服务器(链接服务器名)
 [target_db] [varchar] (50)   NULL ,        --目的数据库名称
 [table_name] [varchar] (100)  NOT NULL ,   --需要同步的表
 [is_close] [int] NOT NULL ,                --是否关闭该记录
 [is_tranall] [int] NOT NULL ,              --是否传输该表整个表记录
 [tag_column] [char] (100)   NULL ,         --标志列，一般是时间类型列，记录最后更新日期,如果不输入，则是每次都传全表数据。（主要用此列做为增量更新的标志）
 [is_complete] [int] NULL,                  --是否完成。当同步时，会同步更新此栏位。确定该行该表是否同步完成。
        [complete_date] datetime null
) ON [PRIMARY]

ALTER TABLE [dbo].[sys_tran_info] WITH NOCHECK ADD
 CONSTRAINT [DF_sys_tran_info_is_disable] DEFAULT (0) FOR [is_close],
 CONSTRAINT [DF_sys_tran_info_is_alldata] DEFAULT (1) FOR [is_tranall],
 CONSTRAINT [DF_sys_tran_info_is_increment] DEFAULT (0) FOR [is_complete]
*/

二.配置表sys_tran_info中的信息,将需要传输同步的表插入此表中。
(当然该表的中数据你可以手动一条一条的增加，以下只是提供批量增加的方法)
1.先批量将有主键的表的数据插入。
insert into sys_tran_info(source_server,source_db,target_server,target_db,table_name,is_close,is_tranall,is_complete)
select distinct
'[192.168.0.1]',  --源链接服务器名(如果以下存储过程的参数@is_local为1，此外为空)
'[erp_db]',       --源数据库名
'[192.168.0.2]',  --目的链接服务器名
'[erp_db_bak]',   --目的数据库名
a.name as table_name,
1,
1,
0
from sysobjects a
join syscolumns b ON a.id = b.id
where a.xtype = 'U'
      and exists(SELECT 1
     FROM sysobjects
    WHERE xtype = 'PK'
                        AND name IN (SELECT name
                                       FROM sysindexes
                                      WHERE indid IN (SELECT indid
                                                        FROM sysindexkeys
                                                       WHERE id = b.id AND colid = b.colid)))
2.注意，这样批量插入数据是没有配置标志列tag_column，所以根据表sys_tran_info中栏位is_tranalle,默认是1传输全部，只有为0时则会根据标志列增量传输。
需要手动根据具体情况配置（例如：有的最后更新日期栏位是update_date）你可以根据你的实际情况配置最后更新时间的栏位，
因为你的名字也许不会取名为update_date。

三.创建以下存储过程。（跟表sys_tran_info创建在一个数据库下面）

create procedure usp_tran_data
 @is_local int=1,
 @begin_date datetime,
 @is_continue int=0
as

--支持原创,转载时请保留下面,以供大家加我MSN,增强交流,共同学习.
--姜庭华  msn: [e-mail]
--博客:http://blog.csdn.net/jaimejth
declare @id numeric(18,0),
        @source_server varchar(50),
        @source_db varchar(50),
 @target_server varchar(50),
        @target_db varchar(50),
        @table_name varchar(100),
        @is_tranall int,
        @tag_column varchar(100),
        @exec_sql varchar(8000),
        @begin_date_str varchar(30),
        @error_txt varchar(100)

set @begin_date_str=convert(varchar(30),@begin_date,120)

set @exec_sql=''
if @is_continue=0
   update sys_tran_info set is_complete=0,complete_date=null

declare table_cursor CURSOR FAST_FORWARD FOR
   select id,
          source_server,
          source_db,
          target_server,
          target_db,
          table_name,
          is_tranall,
          tag_column
     from sys_tran_info
    where is_close=0
 and is_complete in (
  select case when @is_continue=1 then 0 when @is_continue=0 then 1 end
  union select case when @is_continue=0 then 0 end )
     order by id

OPEN table_cursor
FETCH NEXT FROM table_cursor INTO @id,@source_server,@source_db,@target_server,@target_db,@table_name,@is_tranall,@tag_column
WHILE @@FETCH_STATUS = 0
begin
    if @is_tranall=1
       begin
          waitfor delay '00:00:03'
          set @exec_sql='execute '+@target_server+'.'+@target_db+'.dbo.'+'sp_executesql N'' truncate table '+@target_db+'.dbo.'+@table_name+''''
          execute (@exec_sql)
           if @@error<>0
             goto tran_error 

          set @exec_sql= ' insert into '+@target_server+'.'+@target_db+'.dbo.'+@table_name+' select * from '+case when @is_local=1 then '' else @source_server+'.' end+@source_db+'.dbo.'+@table_name+' WITH (NOLOCK)'
          execute(@exec_sql)
            if @@error<>0
             goto tran_error       

       end
   else
       begin
          if @tag_column is null
            continue
          waitfor delay '00:00:03'
          --取主键关系
          set @exec_sql=    
          ' declare @master_key varchar(100)'+
          ' declare @mkey_sql varchar(1000)'+
          ' declare @mkey varchar(500)'+
          ' set @mkey_sql='''''+
          ' set @mkey='''''+
          ' declare master_key_cursor cursor fast_forward for'+
          '    select b.name AS field'+
   '     from '+@target_server+'.'+@target_db+'.dbo.sysobjects a'+
   '     join '+@target_server+'.'+@target_db+'.dbo.syscolumns b ON a.id = b.id '+
   '     where a.name='''+@table_name+''''+' and '+
          '           a.xtype = ''U'''+
   '       and exists(SELECT 1'+
   '      FROM '+@target_server+'.'+@target_db+'.dbo.sysobjects c'+
   '           WHERE c.xtype = ''PK'''+
          '                         AND c.name IN (SELECT name '+
          '                                        FROM '+@target_server+'.'+@target_db+'.dbo.sysindexes e'+
          '                                       WHERE e.indid IN (SELECT indid'+
          '                                                         FROM '+@target_server+'.'+@target_db+'.dbo.sysindexkeys f'+
   '                                                        WHERE f.id = b.id AND f.colid = b.colid))) '+
          ' OPEN master_key_cursor'+
          ' FETCH NEXT FROM master_key_cursor INTO @master_key'+
          ' WHILE @@FETCH_STATUS = 0'+
          '    begin'+
          '       set @mkey_sql=@mkey_sql+'' s.''+@master_key+''=d.''+@master_key+'' and'''+
          '       set @mkey=@mkey+@master_key+'','''+
          '       FETCH NEXT FROM master_key_cursor INTO @master_key'+
          '    end'+
          ' CLOSE master_key_cursor'+
          ' DEALLOCATE master_key_cursor'+
          ' set @mkey_sql=left(@mkey_sql,len(@mkey_sql)-3)'+
          ' set @mkey=left(@mkey,len(@mkey)-1)'+
          ' select * into #temp_date from '+case when @is_local=1 then '' else @source_server+'.' end+@source_db+'.dbo.'+@table_name+'  WITH (NOLOCK) where '+isnull(@tag_column,'')+'>='''+@begin_date_str+''''+
          ' if not exists(select 1 from #temp_date)'+
          '    return  '+
          ' declare @exe_sql varchar(2000)'+
          ' declare @filed varchar(100)'+
          ' set @exe_sql='''''+

          ' if exists(select b.name'+
   '     from '+@target_server+'.'+@target_db+'.dbo.sysobjects a'+
   '     join '+@target_server+'.'+@target_db+'.dbo.syscolumns b ON a.id = b.id '+
   '     where a.name='''+@table_name+''''+' and '+
          '           a.xtype = ''U'''+
   '       and b.colstat= 1)'+
          '   begin '+

          '     select @filed=b.name'+
   '      from '+@target_server+'.'+@target_db+'.dbo.sysobjects a'+
   '      join '+@target_server+'.'+@target_db+'.dbo.syscolumns b ON a.id = b.id '+
   '     where a.name='''+@table_name+''''+' and '+
          '           a.xtype = ''U'''+
   '       and b.colstat= 1'+
          '      set @exe_sql=''execute '+@target_server+'.'+@target_db+'.dbo.'+'sp_executesql N'''' sp_configure ''''''''allow update'''''''',''''''''1'''''''' RECONFIGURE WITH OVERRIDE '''''''+
          '      execute(@exe_sql)'+
          '      set @exe_sql=''update '+@target_server+'.'+@target_db+'.dbo.'+'syscolumns set colstat=0  where colstat=1 and id in (select id from '+@target_server+'.'+@target_db+'.dbo.'+'sysobjects where name='''''+@table_name+''''') and name=''''''+@filed+'''''''''+
          '      execute(@exe_sql)'+
          '      set @exe_sql=''execute '+@target_server+'.'+@target_db+'.dbo.'+'sp_executesql N'''' sp_configure ''''''''allow update'''''''',''''''''0'''''''' RECONFIGURE WITH OVERRIDE '''''''+
          '      execute(@exe_sql)'+
          '      set @exe_sql=''execute '+@target_server+'.'+@target_db+'.dbo.'+'sp_executesql N'''' select * into '+@table_name+'_jaime from '+@target_db+'.dbo.'+@table_name+''''''''+
          '      execute(@exe_sql)'+
          '      set @exe_sql=''execute '+@target_server+'.'+@target_db+'.dbo.'+'sp_executesql N'''' drop table '+@target_db+'.dbo.'+@table_name+''''''''+
          '      execute(@exe_sql)'+
          '      set @exe_sql=''execute '+@target_server+'.'+@target_db+'.dbo.'+'sp_executesql N'''' exec sp_rename '''''''''+@target_db+'.dbo.'+@table_name+'_jaime '''''''','''''''''+@table_name+''''''''''+''''''''+
          '      execute(@exe_sql)'+
          '      set @exe_sql=''execute '+@target_server+'.'+@target_db+'.dbo.'+'sp_executesql N'''' ALTER TABLE '+@table_name+' WITH NOCHECK ADD CONSTRAINT pk_'+@table_name+'_pk PRIMARY KEY CLUSTERED (''+@mkey+'')'+''''''''+
          '      execute(@exe_sql)'+
          '   end '+  
          ' set @exe_sql='''+         
          '  delete '+@target_server+'.'+@target_db+'.dbo.'+@table_name+
          '    from '+@target_server+'.'+@target_db+'.dbo.'+@table_name+' d ,#temp_date s '+
          '   where ''+@mkey_sql'+
          ' exec (@exe_sql'+')'+
          '      insert into '+@target_server+'.'+@target_db+'.dbo.'+@table_name+' select * from #temp_date '+
          ' drop table #temp_date ' 
         execute(@exec_sql)
           if @@error<>0
             begin
               goto tran_error
             end
       end
    update sys_tran_info set is_complete=1,complete_date=getdate() where id=@id
    FETCH NEXT FROM table_cursor INTO @id,@source_server,@source_db,@target_server,@target_db,@table_name,@is_tranall,@tag_column
end
CLOSE table_cursor
DEALLOCATE table_cursor
return

tran_error:
CLOSE table_cursor
DEALLOCATE table_cursor
set @error_txt='表: '+@table_name+' 传输失败'
RAISERROR (@error_txt,16, 1)
return