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

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

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

--支持原创,转载时请保留下面,以供大家加我MSN,增强交流,共同学习.
--姜庭华  msn: jaimejth@live.cn
--博客:http://blog.youkuaiyun.com/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: jaimejth@live.cn
--博客:http://blog.youkuaiyun.com/jaimejth
declare @id numeric(18,0),
        @source_server varcha