Description

Trigger Based Replication

Currently SQLWays Replicator utility supports only trigger based replication, i.e. the replication is performed with the help of additionally created triggers in the source database.

In common the whole process is as follows:

Perform the migration of DDL and data using the SQLWays utility. This is going to be the starting point of the replication process, as at this point the two databases contain the same information;
SQLWays utility is being run against the source tables with the option REPLICATION_CONVERT=Yes from the COMMON section of the options file. The result of it is the set of additional replication triggers and tables generated automatically;
Replication triggers and tables are loaded into the source database. From this point the information regarding the statements performed over the selected tables is being collected;
Run the SQLWays Replicator Utility with the appropriate options for the number of iterations to perform and the pause duration between the checks for the data to be replicated.

Note: Although we are talking about the replication process here, the SQLWays utility is required for both initial data migration and Replication scripts generation.

Overview Of Generated Files For Replication

File Name	Description
swt_replication.sql	Contains DDL for the SWT_Replication table, which is the main replication control table that contains the name of the tables being replicated and the current and previous replication counters for each table being replicated. Also it contains the INSERT statements that populate the SWT_Replication table with the table names being replicated and the RC and CRC initial values.
swr_tables.sql	Contains DDL statements for SWR_* tables, which are the replication tables created for each table in the replication process. These tables are updated by the replication triggers and contain information about the current iteration number, PK or unique values for the data INSERTED, DELETED or UPDATED and the trigger action for the data.
SWR_Triggers.sql	Contains DDL for Replication triggers that are created on base tables and collect the actions and PK/UC values.
DropTriggersSts.sql	Supporting script that contains DROP TRIGGER statements for all the Replication triggers.
DeleteSts.sql	Supports the script that contains DELETE statement for each SWR_* replication table where SWC_RC is less or equal to the CRC value from SWT_Replication table.
TruncateSts.sql	Supports script that contains TRUNCATE statements for each SWR_* replication table.
TableList.sql	Contains the list of tables together with their PK or UC columns. The table name and the list of corresponding columns follow each other one-by-one.

Example for swt_replication.sql

create table SWD_RepDb..SWT_Replication(TableName varchar(30), RC int default 1, CRC int default 0)
GO
insert into SWD_RepDb..SWT_Replication(TableName) values('con_crsref')
GO

Example for swr_tables.sql

create table SWD_RepDb..SWR_con_crsref(SWC_id numeric(10,0) identity,company INT not null, orig_acct INT not null, SWC_action char(1) not null, SWC_RC int)
GO

Example for SWR_Triggers.sql

create trigger SWT_con_crsref_U on con_crsref
for update
as
begin
   declare @RC int
   select @RC=RC from SWD_RepDb..SWT_Replication where TableName = 'con_crsref'
   if update(company) or update(orig_acct)
   begin
      insert into SWD_RepDb..SWR_con_crsref(company, orig_acct, SWC_action, SWC_RC) select company, orig_acct, 'D', @RC from deleted 
      insert into SWD_RepDb..SWR_con_crsref(company, orig_acct, SWC_action, SWC_RC) select company, orig_acct, 'I', @RC from inserted 
   end
   else
      insert into SWD_RepDb..SWR_con_crsref(company, orig_acct, SWC_action, SWC_RC) select company, orig_acct, 'U', @RC from inserted 
end
GO
create trigger SWT_con_crsref_D on con_crsref
for delete
as
begin
   declare @RC int
   select @RC=RC from SWD_RepDb..SWT_Replication where TableName = 'con_crsref'
   insert into SWD_RepDb..SWR_con_crsref(company, orig_acct, SWC_action, SWC_RC) select company, orig_acct, 'D', @RC from deleted 
end
GO
create trigger SWT_con_crsref_I on con_crsref
for insert
as
begin
   declare @RC int
   select @RC=RC from SWD_RepDb..SWT_Replication where TableName = 'con_crsref'
   insert into SWD_RepDb..SWR_con_crsref(company, orig_acct, SWC_action, SWC_RC) select company, orig_acct, 'I', @RC from inserted 
end
GO

Example for DropTriggersSts.sql

IF EXISTS(SELECT * FROM dbo.sysobjects where id = object_id('SWT_con_crsref_U') and type = 'TR')
  drop trigger SWT_con_crsref_U
GO
IF EXISTS(SELECT * FROM dbo.sysobjects where id = object_id('SWT_con_crsref_D') and type = 'TR')
  drop trigger SWT_con_crsref_D
GO
IF EXISTS(SELECT * FROM dbo.sysobjects where id = object_id('SWT_con_crsref_I') and type = 'TR')
  drop trigger SWT_con_crsref_I
GO

Example for DeleteSts.sql

delete from SWD_RepDb..SWR_con_crsref where SWC_RC <= (select CRC from SWD_RepDb..SWT_Replication where TableName = 'con_crsref')
GO

Example for TruncateSts.sql

truncate table SWD_RepDb..SWR_con_crsref
GO

Example for TableList.sql

con_crsref
company
orig_acct

tb_replicator_01
col1

Ispirer Migration Knowledge Base

Table of Contents