Data Synchronization part 1

This article was created to assist our customers with data synchronization.

In our examples we will be synchronizing between different physical SQL servers, the same methodology can be used for any database

Here is table creation script

CREATE TABLE TBL_SOURCE
(
PRIMARY_KEY int NOT NULL IDENTITY (1,1),
DATA varchar(50) NULL,
UPDATE_FLAG varchar(5) NULL
) ON
[PRIMARY]
GO
ALTER TABLE TBL_SOURCE ADD CONSTRAINT
PK_TBL_SOURCE PRIMARY KEY CLUSTERED
(PRIMARY_KEY) WITH( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF,
ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON
[PRIMARY]
GO
CREATE TABLE TBL_TARGET
(PRIMARY_KEY int NOT NULL,
DATA varchar(50) NULL,
UPDATE_FLAG varchar(5) NULL
) ON
[PRIMARY]
GO
ALTER
TABLE TBL_TARGET ADD CONSTRAINT
PK_TBL_TARGET PRIMARY KEY CLUSTERED
(PRIMARY_KEY)
WITH( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON
[PRIMARY]
GO

Objective

• Load new records only into target table
• Once loading is completed source records must be marked as updated

Assumptions

• Data is only inserted into source table
• There are no changes to the source records
• There are no deletions to the source records

Implementation

Reader

Reads new records from the source table using the following SQL

Select
TBL_SOURCE.PRIMARY_KEY,
TBL_SOURCE.DATA,
TBL_SOURCE.UPDATE_FLAG
From
TBL_SOURCE
Where
TBL_SOURCE.UPDATE_FLAG = 'N'

Transformer 1

Writer 1

Adds new records only

for every incoming record runs the following SQL

select count(*) as rec_count from target table where PRIMARY_KEY=?

if rec_count is zero new records is inserted into the target table

Once loading is completed the following SQL is executed to mark processed records in the target table.

update TBL_TARGET
SET UPDATE_FLAG='Y'
WHERE UPDATE_FLAG='N'

Using this approach allow easy identification of newly inserted records in case of failure

Sorter

Sorter is used to delay passing records to writer 2 until writer 1 is completed

Transformer 2

Writer 2

Updates records in the source table

update TBL_SOURCE
SET UPDATE_FLAG = 'Y'
where PRIMARY_KEY=?

Data Synchronization Part 2