StarQuest Technical Documents

Using SQDR for Limited Incremental Replication from SQL Server (Advanced)

Last Update: 17 September 2007
Product: StarQuest Data Replicator
Version: 3.30 or later
Article ID: SQV00DR018

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.

This document differs from "Using SQDR for Limited Incremental Replication from SQL Server" in that the tracking table is created on the destination database instead of on the SQL Server source database. This method allows the final update of the tracking table to occur in the same unit of work as the replication to ensure that the process either succeeds or fails as a whole unit. 

For best results performing incremental replication from DB2 (for iSeries or Windows/Linux/UNIX) 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 users who are familiar with the techniques of creating and altering tables on the source and destination DBMS systems, and creating and using SQL Server Linked Servers. 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).

In this document, the sample SQL statements to be executed on the destination host use DB2 syntax. If your destination database is not DB2, you may need to modify the SQL statements to support your DBMS.

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 tracking table on the destination DBMS 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 of replication.
  3. Create a SQL Server linked server using StarSQL (or another ODBC driver). This linked server will be used in the SQDR subscription criteria to access the tracking table on the remote database.
  4. Modify the SQDR service properties to allow the source processing to occur even if the replication fails.
  5. Create the SQDR subscription with BEFORE and AFTER processing, 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".

alter table 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.

  1. Create a tracking table on the destination DBMS to store row identification values.

  1. Create a tracking table in the destination database. This table should be created with three columns: one as a varchar (128), called TNAME and two as binary (8) types, called CURMAX and LASTMAX. The example uses the table name SUBTRACK.

CREATE TABLE SUBTRACK
(SUBNAME VARCHAR (128) NOT NULL,
CURMAX CHAR (8) FOR BIT DATA WITH DEFAULT,
LASTMAX CHAR (8) FOR BIT DATA WITH DEFAULT,
PRIMARY KEY (SUBNAME))

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.

  1. Create a unique index so that this table can be updated by the SQL Server linked server.

    CREATE UNIQUE INDEX SUBTRACKIX ON SUBTRACK (SUBNAME)

  2. The row must be first initialized with the subscription name and afterwards the timestamp values will be maintained by the SQDR subscription process. This example assumes that the subscription name will be STABLE (which is also the name of the source table in this example).

INSERT INTO SUBTRACK (SUBNAME) VALUES ('STABLE')

  1. Create a SQL Server linked server using StarSQL (or another ODBC driver).

  1. Launch the SQL Server 2000 Enterprise Manager or SQL Server 2005 Management Studio.
  2. Under the Security folder (SQL Server 2000) or the Server Objects folder (SQL Server 2005), right click on Linked Servers and select New Linked Server.
  3. Enter in a name for the Linked Server and choose "Other data source". Do not use any spaces or special characters in the Linked Server name.
  4. Select "Microsoft OLE DB Provider for ODBC Drivers" as the Provider Name. On SQL Server 2005, enter MSDASQL for the Product Name. For non-StarSQL users, consult the SQL Server documentation or the documentation for your driver if you are unsure of the linked server property values.
  5. In the "Data source" field, enter in the name of the ODBC system data source you wish to use. Note that you must enter the Data Source name exactly as it is shown in the ODBC Data Source Administrator.
  6. For SQL Server 2000, click the "Security" tab and select "Be made with this security context." For SQL Server 2005, select the Security page and select "Be made using this security context". Enter a valid remote user/login (user ID) and password for the remote host.
  7. For a StarSQL data source, leave the remaining fields blank. If you are using another ODBC driver, refer to the documentation for your driver.

  1. Modify the SQDR service properties.

  1. In the Replication Manager, select the name of the computer that represents the service in the left pane, right-click, and select Properties.
  2. Under the Global tab, uncheck the box "Use single transaction for source processing" and click OK.
  3. Stop and restart the service by right-clicking the service in the left pane and selecting Stop Service. Restart the service by right-clicking the service and selecting Start Service.

  1. Create the SQDR subscription.

  1. In the StarQuest Data Replication Manager, right-click the SQL Server source you want to use and select Insert Subscription.
  2. On the Source pane of the Subscription Wizard, select the source table to replicate and click Next. (Click Refresh to display the available source objects.)
  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:
    1. Enter the following SQL in the SOURCE processing field to be executed BEFORE the replication, modifying the [linkedserver] and [schema] to use the actual linked server name and table schema, and changing [database] to the actual name of the source database.

    UPDATE [linkedserver]..[schema].SUBTRACK SET CURMAX = (select convert (binary(8), max(tscol) from [database].dbo.stable)) WHERE SUBNAME = 'STABLE'

    1. Enter the following SQL in the DESTINATION processing field to be executed AFTER the replication:

    UPDATE [schema].SUBTRACK SET LASTMAX=CURMAX WHERE SUBNAME='STABLE'

  5. 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.
  6. 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.
  7. 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.
  8. After 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.
  9. On the Criteria pane, enter the following SQL clause changing [linkedserver] to the name of the linked server created in the previous step and [schema] to the name of the table schema on the destination host:

    where (stable.tscol > convert (TIMESTAMP,(SELECT LASTMAX FROM [linkedserver]..[schema].SUBTRACK)))
    and (stable.tscol <= convert (TIMESTAMP,(SELECT CURMAX FROM [linkedserver]..[schema].SUBTRACK)))

    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.

  10. Click OK in the subscription to save the changes.

The next time the subscription runs, only the newly inserted records will be replicated to the destination table.

Applying This Solution to Other Tables

To apply this solution to another source table in the same SQL Server database, complete only Steps 1, 2C, and 5.  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 once 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 [linkedserver] to the name of the linked server created previously and [schema] to the name of the table schema on the destination host

delete from dbo.stable where tscol <= (SELECT LASTMAX FROM [linkedserver]..[schema].SUBTRACK)

DISCLAIMER

The information in technical documents comes without any warranty. The author(s) or distributor(s) will not accept responsibility for any damage incurred directly or indirectly through use of the information contained in these documents. The information in technical documents may be gathered from various sources, including IBM, Microsoft, and other organizations.