StarQuest Technical Documents

Using SQDR Plus for Bidirectional Synchronization

Last Update: 13 April 2021
Product: SQDR Plus
Version: SQDR Plus 4.x
Article ID: SQV00PU005

Abstract

SQDR Plus provides a mechanism for bidirectional synchronization between two similar host database systems running Db2 for i, Db2 for Linux, UNIX & Windows (Db2 LUW), or Oracle.

This mechanism can be used to provide a hot backup system that can quickly be moved into production if the primary system fails. After the primary system is recovered, any changes made on the secondary system will automatically be replicated to the primary system.

Another use would be Load Sharing or Load Balancing of applications or user groups, although additional considerations may apply if both databases are being updated simultaneously as no built-in mechanism exists for collision detection and resolution.

Solution

Prerequisites:

• A Windows system configured to run SQDR.
• A Windows or Linux system configured to run SQDR Plus. In most cases, SQDR and SQDR Plus reside on the same Windows system. See the Quick Start Guide for SQDR Plus for installation instructions.
• Both hosts should be running the same DBMS system (Db2 for i, Db2 for LUW, or Oracle).
• If you plan to use backup/restore to create a snapshot, both hosts should be of similar processor architecture and operating system. For example, you can use backup/restore when replicating between two Windows systems running Db2 for LUW, but not between a Windows system and UNIX system running Db2 for LUW. The version of the DBMS should also be considered: in most cases, the target of a restore operation must be at the same version or higher than the system where the backup was created.
• SQDR Plus Agents configured for both host systems
• All tables involved in the replication must have unique constraints, such as primary keys or unique indexes, and no tables may have foreign constraints.

In the procedure below, we will designate one host system as the primary (the “P” system) and one as the secondary (the "Q" system).

This procedure involves the use of several advanced features of SQDR and SQDR Plus.

Refer to the SQDR help file for details on TableChecker (a utility in the Tools subfolder), “Append replicated rows to existing data”, “Use unique indexes” and “Manual Synchronization” (options on the destination pane for IR subscriptions), “Use Unique Constraints" (option on the Advanced pane for an IR group), and the ability to copy IR subscriptions from one group to another.

Refer to the Reference section of the SQDR Plus Documentation for details on the filterUserID configuration setting, which is used to prevent replication loops by instructing the Capture Agent to bypass staging transactions associated with the specified userID.

Procedure

1. SQDR Plus configuration for PRIMARY host server (the “P” system):
   1. Verify prerequisites.
   2. Create and start a SQDR Plus Agent to the host system.
      
   3. Create a userID (e.g. SQDRP) on the host system that will be used by the SQDR Client to access this system, both as a source & as a destination. It will be used as a filtering UID to avoid replication loops.
   4. Using SQDR Manager, edit the configuration of the Agent. Select the Plus sign to add a new property, and configure filterUserID=SQDRP.
      
   5. Saving the configuration will restart the Capture Agent.
2. SQDR Plus configuration on SECONDARY host server (the “Q” system):
   
   1. Verify prerequisites.
      
   2. Create and start a SQDR Plus Agent to the host system. .
      
   3. Verify Capture Agent is running.
      
   4. Create a userID (e.g., SQDRQ) on the host system that will be used by SQDR to access the secondary system.
      
   5. Using SQDR Manager, edit the configuration of the Agent. Select the Plus sign to add a new property, and configure filterUserID=SQDRP.
   6. Saving the configuration will restart the Capture Agent.
      
3. SQDR Installation:
   
   1. Install SQL Server or Db2 LUW if necessary for use as the SQDR control database. In the typical case where SQDR and SQDR Plus reside on the same Windows system, SQDR will use a local Db2 LUW database named SQDRC.
      
   2. In the case of Db2 hosts, use StarAdmin to bind packages on both Db2 host servers.
      
   3. Install SQDR if necessary.
      
   4. License all software.
   5. Restart SQDR.
      
      
4. Insure that the database on the secondary machine is an identical copy of the primary:
   • One way to do this (if the two hosts systems are of similar processor architecture and operating system, and you are planning to replicate all or a significant part of the database) is a database save/restore operation. This has the advantage of preserving table privileges and can be faster when copying a large database.
   • You can also make the initial copy using SQDR:
     1. Create a Snapshot Group of subscriptions SS_PtoQ with “Replicate Indexes” and schedule “On Demand”.
     2. Insert snapshot subscriptions of the tables on the primary system to be replicated to the secondary system; use the “Create when Run” destination option.
     3. Run the group SS_PtoQ.
     4. Define privileges on the replicated tables if necessary.
5. Define SQDR subscriptions:
   
   1. Create source and destination for each host server, using the “P”-“Q” names to assist identification.
      
   2. Create IR Group “PtoQ” for PRIMARY -> SECONDARY. On the Advanced pane, select the checkbox “Use Unique Constraints."
      
   3. Create IR Group “QtoP” for SECONDARY -> PRIMARY. On the Advanced pane, select the checkbox “Use Unique Constraints."
      
   4. Create a set of IR subscriptions in Group PtoQ. On the destination pane, specify “Use Existing Table”, “Append replicated rows to existing data”, and “Manual Synchronization.” This effectively bypasses any baseline activity , and results in “Relaxed Apply Rules.”
      
   5. Pause both groups.
      
   6. Copy IR subscriptions from Group PtoQ to Group QtoP.
   7. Run Group QtoP.
      
   8. Resume both groups.
6. Test Replication:
   
   1. Update PRIMARY host source tables, watch updates flow.
   2. Update tables on SECONDARY host system and watch updates flow.
   3. Use TableChecker (in the Tools subdirectory of the SQDR installation) to verify that the two systems are in sync. See the SQDR help file for instructions on using TableChecker.