SQL数据同步工具

zhouf · 发表于 2020-6-16 14:08:21

本帖最后由 zhouf 于 2020-6-17 09:54 编辑

SQL数据同步工具

前言
我们经常会存在将服务器A的数据，同步到服务器B中的某个数据库；本方案是通过A/B两台服务器之间建立链接服务器的方式做同步据；不适用于广域网；

业务场景：
我们需要将来源服务器 A 192.168.1.5、数据库名 HSTIIP、表名 mmMaterial 中 (物料大类=面料)的数据，
同步到目标数据库 B 192.168.1.6、数据库名 EDI、表名 edi_material 中(物料大类=面料)；

实现步骤：

一、确保两台服务器上对应的数据库都已创建；

二、在目标服务器B (192.168.1.6) 数据库 (EDI) 上执行以下脚本

2.1.  执行附件中的文件 01_DBTrigger.sql
      数据库触发器,用于记录表结构变化日志 (可选)
2.2.  执行附件中的文件 02_DBCommonTools.sql
      通用工具函数封装脚本
2.3.  执行附件中的文件 03_DBSyncDataTools.sql
      数据同步工具脚本

2.4. 添加链接服务器，指向来源服务器A (192.168.1.5)

IF EXISTS(SELECT * FROM master.sys.servers WHERE name='MesDB')
EXEC master.dbo.sp_dropserver @server=N'MesDB', @droplogins='droplogins';
GO
IF NOT EXISTS(SELECT * FROM master.sys.servers WHERE name='MesDB')
BEGIN
EXEC master.dbo.sp_addlinkedserver @server=N'MesDB',@srvproduct=N'SQLNCLI',@provider=N'SQLOLEDB'
,@datasrc=N'192.168.1.5,1433'; --指向地址 1.5
EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'MesDB',@locallogin=NULL,@useself=N'False'
,@rmtuser=N'sa',@rmtpassword=N'password'; --连接 1.5 数据库的SQL用户与密码
EXEC master.dbo.sp_serveroption @server=N'MesDB',@optname=N'collation name',@optvalue=N'Chinese_PRC_CI_AS';
EXEC master.dbo.sp_serveroption @server=N'MesDB',@optname=N'connect timeout',@optvalue=N'5'; --seconds
EXEC master.dbo.sp_serveroption @server=N'MesDB',@optname=N'query timeout',@optvalue=N'30'; --seconds
EXEC master.dbo.sp_serveroption @server=N'MesDB',@optname=N'rpc', @optvalue=N'true';
EXEC master.dbo.sp_serveroption @server=N'MesDB',@optname=N'rpc out', @optvalue=N'true';
END
GO

复制代码

三、根据业务需要，定义目标服务器B (192.168.1.6) 数据库 (EDI) 的中间表结构，例如：

CREATE TABLE dbo.edi_2wms_customer
(iId int identity(1,1) primary key
,bTrans bit not null default(0),tTransTime datetime,sTransError nvarchar(500)
,iDataStatus tinyint not null default(1),CHECK (iDataStatus IN (1,2,3)) /*数据状态(1新增或修改,2禁用,3物理删除)*/
,tCrateTime datetime not null default(getdate()) /*记录写入时间*/
,sCustomerNo nvarchar(30) not null /*客户编号(业务唯一键,中间表数据重复时以tCreateTime最新数据为准)*/
,sCustomerName nvarchar(200) not null /*客户名称*/
,sContacts nvarchar(30) /*联系人*/
,sTelphone nvarchar(30) /*联系电话*/
,bUsable bit not null default(1)
,sRefId nvarchar(50) /*来源系统明细主键*/
,tTimestamp timestamp
);

复制代码

四、在目标服务器B (192.168.1.6) 数据库 (EDI) 上添加接口配置
从来源服务器 MesDB.HSTIIP.dbo.vw_2wms_customer 中，将数据同步到目标数据库 EDI.dbo.edi_2wms_customer，两个对象的关联比较字段为（sRefId）；

INSERT INTO dbo.saSyncDataHdr
(sSyncCode,sSyncName,iSyncMode,bUsable,iSort
,sSourceLinkServer,sSourceDBName
,sSourceObjName,sSourceWhere
,sSourceGroupBy,sSourceHaving
,sDestObjName,sDestWhere,sRelationKeyCols
,sDestMinCols,sDestMaxCols,sDestSumCols)
SELECT
sSyncCode=N'2wms_customer',sSyncName=N'客户档案'
,iSyncMode=1+2 /*1新增,2修改,4删除*/,bUsable=1,iSort=1 /*排序*/
,sSourceLinkServer='MesDB' --目标服务器B上创建的“链接服务器名称”
,sSourceDBName='HSTIIP' --来源数据库名称
,sSourceObjName='dbo.vw_2wms_customer' --来源数据库上,准备创建的视图名称
,sSourceWhere='1=1' --查询来源表数据的范围,因为这是直接创建视图,所以设置条件为1=1
,sSourceGroupBy='' --对于某些数据需要分类汇总时,可以使用些配置, 例如将明细到SN级别的数据汇总到SKU极, 再同步
,sSourceHaving='' --与 sSourceGroupBy 同步使用
,sDestObjName='dbo.edi_2wms_customer' --目标数据库上的实体表名,数据将写入到这张表
,sDestWhere='1=1' --如果接口表不区分面料/辅料,可以配置成([物料大类字段名]='面料代码')
,sRelationKeyCols='sRefId' --这个很重要,是来源表与目标表比较差异的字段名,多个以逗号分隔
,sDestMinCols='' --写入到目标数据表前,哪些字段取最小值,多个字段时以逗号分隔
,sDestMaxCols='' --写入到目标数据表前,哪些字段取最大值,多个字段时以逗号分隔
,sDestSumCols='' --写入到目标数据表前,哪些字段求和,多个字段时以逗号分隔
;

复制代码

五、准备在来源数据库中创建对应的视图，方便取数；

5.1. 在目标服务器B (192.168.1.6) 数据库 (EDI) 上执行以下脚本生成视图模板

EXEC dbo.spsaGenerateSyncDataViewSQL @sSyncCode='2wms_customer';

复制代码

5.2. 生成出来的模块如下效果：

-- 2wms_customer 客户档案
IF OBJECTPROPERTY(OBJECT_ID(N'dbo.vw_2wms_customer'), N'IsView')=1
DROP VIEW dbo.vw_2wms_customer
GO
CREATE VIEW dbo.vw_2wms_customer
AS
SELECT
sCustomerNo=cast(a.sCustomerNo as nvarchar(30)) --非空;无默认值;客户编号(业务唯一键,中间表数据重复时以tCreateTime最新数据为准)
,sCustomerName=cast(a.sCustomerName as nvarchar(200)) --非空;无默认值;客户名称
,sContacts=cast(a.sContacts as nvarchar(30)) --无默认值;联系人
,sTelphone=cast(a.sTelphone as nvarchar(30)) --无默认值;联系电话
,bUsable=cast(a.bUsable as bit) --非空;是否有效
,sRefId=cast(a.sRefId as nvarchar(50)) --无默认值;来源系统明细主键
FROM dbo.tableName a(NOLOCK)
GO

复制代码

六、在来源服务器A (192.168.1.5) 数据库HSTIIP 中创建所需要的视图

6.1. 在来源服务器A (192.168.1.5) 数据库HSTIIP 上，执行附件中的文件 02_DBCommonTools.sql
通用工具函数脚本, 重点是此文件中的对象
SELECT dbo.fnpbCurrTimestamp(); /*获取当前数据库当前时间戳,因为无法通过@@DBTS获取另外一台服务器数据库的当前时间戳*/

6.2. 在(五.2)的基本上修改后，脚本示例如下：

--2wms_customer 客户档案
IF OBJECTPROPERTY(OBJECT_ID(N'dbo.vw_2wms_customer'), N'IsView')=1
CREATE VIEW dbo.vw_2wms_customer
AS
SELECT
sCustomerNo=CAST(a.sCustomerCode as nvarchar(30))
,sCustomerName=CAST(a.sCustomerName as nvarchar(200))
,sContacts=CAST(a.sContactMan as nvarchar(30))
,sTelphone=CAST(a.sTelNo as nvarchar(30))
,bUsable=CAST(a.bUsable AS BIT)
,sRefId=CAST(a.iId AS NVARCHAR(50))
,tTimestamp=CAST(a.tTimeStamp AS BINARY(8)) --有时间戳后,数据同步时才不会全表检索
FROM dbo.cuCustomer a(NOLOCK)
GO

复制代码

6.3. 在来源服务器A (192.168.1.5) 数据库HSTIIP 上，执行以上示例脚本；

七、回到目标数据库B (192.168.1.6) 数据库 (EDI) 上，准备执行数据同步；

7.1. 执行以下脚本，自动生成目标表与来源视图的字段映射关系；(仅适用于新增的接口配置)

EXEC dbo.spsaFillSyncDataColsMap;

复制代码

7.2 执行以下脚本，测试同步过程，可以返回数据同步过程的执行脚本；

EXEC dbo.spsaSyncDataSingle @sSyncCode='2wms_apply_fab',@bDebug=1; --不做数据同步,只查看代码
--或
EXEC dbo.spsaSyncDataSingle @sSyncCode='2wms_apply_fab'; --执行数据同步
--或
EXEC dbo.spsaSyncDataAll; --同步所有数据接口

复制代码

7.3 创建 SQLServer Job ，自动执行所有接口数据同步；

EXEC dbo.spsaGenerateSyncDataJob;

复制代码

八、查看接口数据同步状态

执行以下脚本
EXEC dbo.spsaQuerySyncDataStatus;
效果图如下：

		自动登录	找回密码
密码			立即注册