控制表法同步异构数据库数据

目标：同步不同数据库、不同结构的两张表同步。表的名称、列名、甚至数据类型都不相同，同步时尽量要求同步即时，且对网络的依赖程度最小化。

同步环境：1 两台SQL Server 服务器 预装SQL Server 2012

                2 构造两个不同的数据库及不同的数据表.

同步系统架构：

步骤1：源数据表数据导入目标数据表。

建立源和目标表栏位的对应表。（通过建立视图，不在导入数据时，进行数据转换。）

导入源表中的数据到目标表。完毕后可以使用Toad对比工具进行比对。

步骤2：新建Trigger表和控制表

1) 在源表中增加SyncGuid栏位 ，guid数据 类型 。

2) 新增控制表sync_tablename_TR_CONTROL用以记录变动的源表数据记录。

CREATE TABLE [dbo].[SYNC_TableName_TR_CONTROL](

[PK] [uniqueidentifier] NULL,

[Opt] [char](1) NULL,

[CREATETIME] [datetime2](7) NOT NULL,

CONSTRAINT [PK_sync_TableName_tr_control_createtime] PRIMARY KEY CLUSTERED

(

[CREATETIME] ASC

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

) ON [PRIMARY]

 

步骤3 ：新增触发器TR_TableName_Sync

CREATE TRIGGER [dbo].[TR_TABLENAME_SYNC] ON DBO.[TABLENAME]

AFTER INSERT, UPDATE, DELETE

--NOT FOR REPLICATION

AS

BEGIN

SET NOCOUNT ON;

DECLARE @PK UNIQUEIDENTIFIER;

IF ( ( EXISTS ( SELECT 1

FROM inserted ) )

AND ( EXISTS ( SELECT 1

FROM deleted ) )

) --For Update

BEGIN

--select @PK=rowguid from inserted;

INSERT INTO dbo.SYNC_TABLENAME_TR_CONTROL

( PK, Opt )

SELECT SYNCGUID ,

'U'

FROM inserted;

END

ELSE

IF ( EXISTS ( SELECT 1

FROM inserted )

AND NOT EXISTS ( SELECT 1

FROM deleted )

) --For Insert

BEGIN

--select @PK=rowguid from inserted;

INSERT INTO dbo.SYNC_TABLENAME_TR_CONTROL

( PK, Opt )

SELECT SYNCGUID ,

'I'

FROM inserted;

END

ELSE

IF ( NOT EXISTS ( SELECT 1

FROM inserted )

AND EXISTS ( SELECT 1

FROM deleted )

) --For Delete

BEGIN

SELECT @PK = SYNCGUID

FROM deleted;

DELETE FROM dbo.SYNC_TABLENAME_TR_CONTROL

WHERE PK = @PK;

INSERT INTO dbo.SYNC_TABLENAME_TR_CONTROL

( PK, Opt )

SELECT SYNCGUID ,

'D'

FROM deleted

END

END

 

步骤3 ：检测源表中新增、删除和修改的数据是否可以加入控制跟踪表。

方法：使用比较工具比较源和目标表的差异。同时测试trigger是否可以正常触发。

结论：

1) 源表上栏位的新增对trigger没有影响。

2) 对同时处理（删除/修改/添加）两条以上的数据，会触发两次。

3) 源和目标表的同步PK值必须要相同。

步骤4 ：建立SSIS同步信息。

注意点：

1) 其中PK要使用String的格式。使用(DT_STR,38,1252)ROWGUID来转换。

2) 在Update 时需要更新除PK和Syncguid 之外的所有列。

3) 如果源和目标表中都存在自增类型的列，则自增列不参与同步。

4) 如果源和目标两边都存在自增型数据列，此次同步设计部设计该列的同步。

 

控制流：

数据流：