|
本帖最后由 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;
效果图如下:
|
|