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

最新推荐文章于 2024-06-27 13:26:38 发布

置顶

jaimejth

最新推荐文章于 2024-06-27 13:26:38 发布

阅读量9.3k

点赞数 1

本文链接：https://blog.csdn.net/jaimejth/article/details/5683157

版权

本文介绍了如何在SQL Server环境中实现数据库增量同步，包括必备条件、配置信息表`sys_tran_info`、批量插入数据和创建存储过程`usp_tran_data`进行数据传输。详细步骤和示例代码展示了如何在两台服务器间进行增量数据同步。

摘要由CSDN通过智能技术生成

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

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

--支持原创,转载时请保留下面,以供大家加我MSN,增强交流,共同学习.
--姜庭华 msn: jaimejth@live.cn
--博客: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。