StarQuest Technical Documents

Using SQDR for Limited Incremental Replication from SQL Server

Last Update: 19 September 2007
Product: StarQuest Data Replicator
Version: 3.30 or later
Article ID: SQV00DR017

Abstract

This document describes a method for using StarQuest Data Replicator (SQDR) to simulate real-time replication from SQL Server to any database management system that SQDR supports (DB2, Oracle, or SQL Server). This is particularly useful for organizations that use SQL Server editions which do not include the Replication feature, such as the Standard and Workgroup editions.

For best results performing incremental replication from DB2 for iSeries or DB2 for Linux, UNIX, and Windows to SQL Server, use SQDR with SQDR Plus. To download a free trial of SQDR Plus, go to http://www.starquest.com.

Solution

This document is intended for SQL Server users who are familiar with the techniques of creating and altering tables. In addition, this solution is designed to work only with source tables in which new records are inserted, but no records are updated or deleted.

All of the SQL Server SQL statements included in the instructions below can be executed in the Query Analyzer (SQL Server 2000) or the Management Studio New Query window (SQL Server 2005).

Summary

Following are the general steps that you need to perform. Each step is described in more detail in the Detailed Procedures section.

1. Add a timestamp column to the SQL Server source table. The timestamp value will serve as the row identification value and will be used in the SQDR subscription criteria to limit the replication result set so that only new records are replicated.
2. Create a SQL Server tracking table to store the row identification value of the last record replicated to the destination table and the last record inserted into the source table at the time the replication started.
3. Create the SQDR subscription with BEFORE and AFTER processing on the source server, and with a criteria that limits the result set to be replicated.

Detailed Procedures

1. Add a timestamp column to the SQL Server source table.

The SQL Server source table to be replicated must have a timestamp field. If one does not exist, add a non-nullable timestamp column to the table. The example below adds a non-nullable column called "tscol" to a source table called stable. Execute the following SQL statement changing [database] to the actual name of the source database.

alter table [database].dbo.stable add tscol timestamp not null

NOTE: Do not confuse the timestamp data type with the datetime data type. The timestamp data type is an auto-generated binary value used for "row-versioning." It does not actually preserve the date or time and thus is better suited than the datetime data type for this specific application because it is guaranteed to be a unique value regardless of system date/time changes on the SQL Server system.

2. Create a SQL Server table to store row identification values.

   1. Create a new SQL Server table in the database that contains the table to be replicated to DB2 (i.e., the "Source" table). This table should be created with three columns: one as a varchar (128), called subname, and two as binary (8) types, called curmax and lastmax. The example uses the table name dbo.subtrack.

   create table dbo.subtrack (subname varchar(128) not null primary key, curmax binary (8) default 0, lastmax binary (8) default 0)

   The subname column will store the name of the SQDR subscription. The curmax column will store the maximum timestamp of the rows in the source table to be replicated to the destination host at the start of the replication. The lastmax column will hold the timestamp value of the last row successfully replicated to the destination host.

2. The row must be first initialized with the name of the subscription (to be created later) but afterwards the timestamp values will be maintained by SQL statements executed in the SQDR subscription process. Execute the following SQL statement:

insert into dbo.subtrack (subname) values ('stable')

3. Create the SQDR subscription.
   1. In the Replication Manager, right-click the SQL Server "Source" and insert a new subscription.
   2. On the Source pane, select the source table to replicate and click Next.
   3. On the Destination pane, check the radio button "Append replicated rows to existing data". Configure the other options as desired.
   4. On the Processing pane:
   5. Enter the following SQL in the Source processing field to be executed BEFORE the replication, changing [database] to the actual name of the source database:

   update [database].dbo.subtrack set curmax = (select max(tscol) from [database].dbo.stable) where subname = 'stable'

   6. Enter the following SQL in the Source processing field to be executed AFTER the replication, changing [database] to the actual name of the source database:

   update [database].dbo.subtrack set lastmax = curmax where subname = 'stable'

   7. On the Schedule pane, set the subscription to run on a regular interval. Be sure to allow enough time for the replication and the processing to complete before the subscription attempts to run again.
   8. Name the subscription with the same name as used for the subname value that was inserted into the subtrack table. In this example, the subscription name is stable.
   9. After the subscription has been created, right-click the subscription and select Run Subscription. This will replicate the complete source table to the destination table and initialize the timestamp values in the subtrack table.
   10. Once the subscription has successfully completed, double-click on the subscription to open the properties. On the Destination panel, ensure that the Use Existing Table option is selected.
   11. On the Criteria pane, enter the following SQL clause, changing [database] to the actual name of the source database:

   where (stable.tscol > (select lastmax from [database].dbo.subtrack where subname = 'stable')) and (stable.tscol <= (select curmax from [database].dbo.subtrack where subname = 'stable'))

   Click the Verify button to verify that the SQL statement does not contain any syntax errors. It is normal at this stage for the result set returned using this criteria to be zero.

Applying This Solution to Other Tables

To apply this solution to another source table in the same SQL Server database, complete only Steps 1, 2b, and 3. If you wish to use this solution for a table in another database, you must perform all of the detailed procedures.

Source Table Management

In some circumstances you may find it necessary to delete all of the records in the source table after they have been successfully replicated to the destination database. This can easily be accomplished without impacting the destination database. Using the same table and column names as in the previous examples, the following SQL statement deletes all of the records from the source table that have already been replicated to the destination table. Execute this SQL statement, changing [database] in both instances to the name of the database where the tables exist.

delete from [database].dbo.stable where tscol <= (select lastmax from [database].dbo.subtrack)

Site Map
Products | Trial Download | Customer Support | Partners | About StarQuest | Press & Events | Contact StarQuest

©Copyright 2008 StarQuest Ventures, Inc. All rights reserved.

Legal Notice • Privacy Policy
Email contact@starquest.com with site comments.