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 the target table
Once loading is completed source records must be marked as updated

Assumptions

Data is only inserted into the 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 to easily identify newly inserted records in case of failure

Sorter

The 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=?