StarQuest Technical Documents

SQDR Plus: Distributed Network Examples

Last Update: 27 May 2021
Product: SQDR Plus
Version: 4.50 and later
Article ID: SQV00PL043

Abstract

This technical document provides several real-world examples of how the SQDR four tier architecture can be used to provide database replication across remote networks.

The tiers of the SQDR architecture are:

• Tier 1 - source database system (e.g. Db2 for i, Db2 LUW, Informix, SQL Server)
  
• Tier 2 - SQDR Plus (incremental staging agent) - Windows or Linux
  
• Tier 3 - SQDR replication service - Windows
  
• Tier 4 - destination database system

For details, see the Overview in the SQDR Plus Quick Start Guide.

Note that these tiers can be combined in various ways - for example, Tiers 2 and 3 are often run on the same Windows VM, typically dedicated for SQDR. When remote networks are involved, placing Tiers 3 and 4 on the same system or the same network often enhances performance.

In some of the examples below, another tier (Tier 3 proxy) is introduced, using the port forwarding function of netsh interface portproxy to provide indirect access to internal servers. A previous version of this technical document used StarPipes and SQL Server Linked Server to provide the same function.

See the following technical documents for additional examples:

• Configuring SQDR Cloud Services
• Preparing IBM i for use with SQDR Cloud Services

Contents:

• Example 1 - SQL Server to SQL Server (push deployment)
• Example 2 - SQL Server to SQL Server (pull deployment)
• Example 3 - Informix to Postgres (pull deployment)

Examples 1 - SQL Server to SQL Server (push deployment)

Overview

A SaaS (Software as a Service) vendor provides software solutions for healthcare and human services organizations. The vendor is using SQDR to securely distribute data from SQL Server located in its data center to SQL Server located at the client's data center or located in the cloud (under control of the client).

The goal is to minimize software that needs to be installed and configured on the client's machine. All communication transmitted on the WAN is encrypted, and the number of ports that are exposed are limited and restricted to specific IP addresses.

Most of the systems involved in replication are located in the SaaS vendor's data center.

For Push deployment, only the destination SQL Server is located on the client's machine; no additional software is installed. No incoming ports to the SaaS vendor's data center are opened, and only the SQL Server destination port (e.g. 1433) is exposed on the client's tier 4 system.

Architecture

In the Push Deployment, tiers 1, 2, and 3 are all located within the SaaS vendor's data center, with no incoming ports exposed, and only the SQL Server destination port (e.g. 1433) is exposed on the client's tier 4 system.

Push deployment has the following advantages:

• No software is installed or configured on the client machine.
• Simpler setup and maintenance.
• The SaaS vendor retains full access to all components of replication.
• No ports are opened at the SaaS vendor's site.

Example 2 - SQL Server to SQL Server (pull deployment)

The SaaS vendor in Example 1 also implemented Pull deployment, which results in better replication performance.

For Pull deployment, SQDR is installed on the client's system as well. It can be configured to use a control database located at and under the control of the SaaS vendor, or a control database running on a local copy of Db2 LUW or SQL Server.

No incoming ports are needed on the client's system, but the SaaS vendor's firewall is configured as below.

This method adds a new tier "Tier 3 Proxy" using netsh interface portproxy for access to SQL Server (Tier 1) and to Db2 LUW (Tier 2). It may also provide access to Db2 LUW to host SQDR control databases for each client, and a local copy of SQDR used for creating import scripts.

Architecture

In the Pull Deployment, tiers 1, 2 and 3 Proxy are located within SaaS vendor's data center, with ports 448 (Db2 LUW proxy on tier 3 Proxy) and 1433 (SQL Server proxy on tier 3 Proxy) exposed for access from the client's system. Tiers 3 (SQDR service) and 4 (destination) are located on the client's machine, with no incoming ports exposed.

Pull deployment has the following advantages:

• Performance is greatly improved, since SQDRSVC (tier 3) is colocated with (or at least closer to) the tier 4 destination.
• No ports are opened on the client machine.
• By placing the SQDR control database in the SaaS vendor data center, it is not necessary to install Db2 LUW or SQL Server on the the tier 3 system for use as the SQDR control database, and StarQuest and the SaaS vendor have control and visibility of the health of the SQDR service running on the client machine by executing SQL queries on this database, which can provide functionality similar that of the Data Replication Manager.

In the Pull deployment, the tiers are divided as follows:

• Tier 1 (T1) - SQL Server source
• Tier 2 (T2) - SQDR Plus
  Create a new agent for each client
  Db2 LUW: Each agent will create a SQDRPn staging database.
  Db2 LUW (for remote control database scenario): Create a new SQDR control database for each client; this database is named after the client.
  
• Tier 3 Proxy (T3P)
  
  • netsh interface portproxy is used to configure connections to Db2 LUW on Tier 2 and to SQL Server source on Tier 1.
  • This system also has a copy of SQDRSVC that can be used in the Push deployment. It also is used to develop import scripts for subscription additions and revisions; these scripts are then sent to the customer to run on the client system.

The above systems are within the SaaS vendor's data center.

The firewall allows incoming connections on port 50448 (DRDA) and 1433 (SQL Server) from the tier 3/4 external system to the Tier 3 Proxy system.

• Tier 3/4: SQDRSVC & SQL Server destination. This system is located either in the cloud or at the client's site. Best performance is obtained because SQDRSVC communicates to the SQL Server destination with a memory-based connection if they reside on the same system.
• SQDRSVC communicates with the T1 SQL Server source through SQL Server proxy port on T3P for Baseline and for reading SQ_PROPERTIES table for connection info to theT2 system
• SQDRSVC communicates through Db2 port proxy on T3P for access to the T2 SQDR Plus staging database (SQDRPn) for obtaining incremental changes and (optionally) for access to its control database.
  

Deployment

Perform the following steps when adding a new client site:

Obtain a list of tables from the SaaS vendor

The SaaS vendor will provide a list of tables that should be replicated to the particular client. The import file will be in TAB format - <schema> <table_name> <object_type>; make sure there are no blank lines at the end of the file.

The SaaS vendor will also provide a list of any new tables should be published, since this process can be used for pushing subscription changes (e.g. additional tables or schema changes) to existing clients as well as setting up a new client.

The import list can be used in either of the following methods:

• Using the Publish function of SQDR Control Center (tier 2), import the list and publish the tables and add requirePublication=true to the configuration. Using this method, Data Replicator Manager will see only the desired tables; select them all.
• Import the list when creating subscriptions using Data Replicator Manager on the Tier 3 proxy.

 

On Tier 1:

• No special preparation.
  

On Tier 2:

• Configure SSL on Db2 LUW, using the same port that will defined on Tier 3 Proxy (e.g. 50448). See Configuring Db2 LUW for SSL for details.
• Create a new agent. This will create a new SQDR schema on the tier 1 system, and a new SQDRPn SQDR Plus staging database on tier 2.
• If desired, edit the configuration and add requirePublication=true, and import and publish the list of tables to be replicated.

On Tier 3 Proxy:

• For remote control database scenario:
  • Create and prepare SQDR control database with a name that represents the client.
    CREATE DATABASE <CLIENT> ON D: DBPATH ON L:
    
  • Run StarAdmin to create StarSQL packages in schema STARSQL in the SQDR database.
    
  • Run SQDR Configuration to create empty control tables in the SQDR control database.
• Create the forwarding ports:

netsh interface portproxy add v4tov4 listenport=50448 connectaddress=<T2 IP address> connectport=50448 protocol=tcp
netsh interface portproxy add v4tov4 listenport=1433 connectaddress=<T1 IP address> connectport=1433 protocol=tcp

Firewall:

Allow incoming connections (50448 & 1433) from the new client system to the Tier 3 Proxy system.

Prepare Subscription Script on Tier 3 Proxy:

 

On Tier 3 Proxy (which has a local copy of SQDR):

Using an I/R group from SQL Server source (destination TEMPLATE) to a local SQL Server destination
Note - it doesn't matter which customer's source database we use, since they are identical; it is the import list obtained from the SaaS vendor that may differ depending on the client. The Schedule properties of the group should be configured as "On demand" since we don't actually need to run the group.

1. Delete any existing members remaining from previous export activities.
   
2. Right click on the Members folder under the group and select Insert Members.
   
3. On the source panel
   • If you set up publications from an import list on tier 2, you can select all the tables available from the source.
   • Alternately, select Import... and import the list of tables to be replicated.
     
4. On the Destination panel, select Allow UPSERT. The other values use the defaults of Create every time subscription is Saved, Truncate table before replication, Use native-loader (except in certain cases, such as using the SQDR Microsoft SQL Server ODBC driver).
   
5. After creating the subscriptions, if desired create additional groups and divide the subscriptions. For instance, you may want to move all the %HISTORY tables into a separate group; this is easily done by using the Filtering function.
6. For each group, right-click on the group and select Export Subscriptions.
   
7. Edit the resulting JSON files, replacing TEMPLATE with the actual database name of the destination.
   
8. Supply the JSON files and sqdr.properties to the client.

New client Tier 3/4:

1. Install the recommended ODBC Driver for SQL Server.(e.g. 17.7).
2. If SQDR will be using a local control database, install Db2 LUW or SQL Server. More typically, SQDR will be using the control database located on tier 2 and accessed through tier 3 proxy.
3. Install SQDR.
4. License SQDR, StarSQL, SQDR Plus.
5. (for remote SQDR control database) edit etc\hosts:
   Run Command window as administrator
   cd \windows\system32\drivers\etc
   Run notepad hosts and add
   x.x.x.x SaaSHostname

Where x.x.x.x is the IP address of the SaaS vendor's firewall, and SaaSHostname is the name that appears in the agentODBCString value in SQ_PROPERTIES on the source system. SQDR uses that hostname to connect to the staging database to pick up incremental changes.

5. Configure SQDR:
   For a local Db2 LUW control database, create the database (SQDRC), use StarAdmin to bind StarSQL packages, and use SQDR Configuration to configure the connection and create control tables.
   For a local SQL Server control database, create the database (e.g. ControlDB) and use SQDR Configuration to configure the connection and create control tables.
   For a remote control database, SQDRSVC will use the SQDR control database created on tier 2 and routed via StarPipes on tier 3 Proxy.
6. Either:

• Copy sqdr.properties (created on the tier 3 proxy) to C:\ProgramData\StarQuest\sqdr and run SQDR Configuration, accepting all defaults.
  OR
  
• Use SQDR Configuration to connect to the control database via SaaSHostname/port 50448 (which connects to StarPipes) and specify the database name specific to the client.

6. Start the SQDR service. For a remote control database, this will take approximately 5 minutes because SQDR has to read the control database over a WAN connection.
7. Create SQL Server source: SaaSHostname/port 1433; SQL Server authentication. This will connect to the the proxy SQL Server port on tier 3 Proxy. On the Advanced panel, specify the desired database.
8. Create destination: local SQL Server. We suggest using Integrated Security. On the Advanced panel, specify the desired database and object schema (typically dbo).
9. Create incremental groups. On the schedule page, set to On Demand.
10. Right-click on the source and select Pause.
11. Create subscriptions using the JSON script produced above. For each group:
    

• Expand the I/R group and right-click on Members
  
• Select Import Members and select the JSON script

This will create the subscriptions and the destination tables. Since creating the subscriptions for a large number of tables may take a while, you can start additional instances of Data Replicator Manager to import additional groups.

After the subscriptions are created, unpause the Source, change the schedule page of the I/R group to As Needed, and SQDR will start running the baselines.

Example 3 - Informix to Postgres (pull deployment)

A large corporation is using SQDR to replicate data from an Informix source database located on an internal network to a Postgres database hosted at AWS (Amazon Web Services).

In this split tier configuration, the tiers are:

• Tier 1 - Informix source (internal network) - no direct access from AWS.
  
• Tier 2 - SQDR Plus agent (internal network) The customer requested the use of Linux for this tier. In addition, Db2 LUW on this system is used for proxy access to the Informix source (no direct connection from Tier 3 to Tier 1 is allowed).
  
• Tier 3 - SQDR service (Windows instance at AWS)
  
• Tier 4 - Postgres destination (AWS)

Firewall: Access to the internal network from the AWS Windows instance is restricted to port 50448 (SSL access to Db2 LUW on tier 2).

This section is included for historical reasons, as using the IBM Db2 ODBC driver (DRDA) to access Informix has some limitations. Our current recommendation is to

• Use either the bundled SQDR Informix driver or the IBM Informix driver
• Configure port forwarding of port 9088 using netsh interface portproxy (Windows) or iptables (Linux).
• Configure the firewall to allow port 9088
• If desired, configure Informix server (T1) and ODBC client (T3) for SSL/TLS communications and use that port instead of 9088.

Deployment

On Tier 1 (Informix source):

1. Use the USERMAPPING, REMOTE_SERVER_CFG, and REMOTE_USERS_CFG configuration parameters and related configuration files to specify that User authentication will be performed by the tier 2 system.
2. Verify the DRDA port being used by Informix (default 9089).
3. Prepare the source as described in the Quick Start Guide to Using SQDR Plus v4 on Linux with Informix (Part 1):

• Verify that logging is enabled for the source database.
  
• Create the Change Data Capture database (syscdcv1) if necessary.
  
• Create the user SQDR (agent user) and grant authorities.

On Tier 2 (Linux):

1. Create local Linux user sqdr and add it to the db2iadm group in /etc/group. This user will be used to run the SQDR Plus services, and to access the tier 2 staging database from tier 3.
2. Install Db2 LUW and SQDR Plus.
3. Configure SSL for Db2 LUW. If you are not using a public Certificate Authority, export the CA certificate for use on tier 3. See Configuring Db2 LUW for SSL for details.
4. Create a new agent. This will create a new SQDR schema on the tier 1 system, and a new SQDRPn SQDR Plus staging database on tier 2.
5. Configure tier 3 to tier 2 communication to use SSL: Examine the agentODBCString property in SQ_PROPERTIES in the tier 1 source database; this contains the connection string that the SQDR service uses to connect to the Tier 2 staging database (SQDRPn). Modify the connection string to use SSL and supply that string as the value for clientODBCString in the Agent configuration; use the Plus sign to add the clientODBCString property. For example, you may change
   
   DRIVER={StarSQL 32}; HostName=mytier2.mydomain.com;Port=50000;
   Server=SQDRP0;PkgColId=STARSQL
   TO
   DRIVER={StarSQL 32}; HostName=mytier2.mydomain.com;Port=50448; Server=SQDRP0;PkgColId=STARSQL;Netlib=SQSSL.DLL
   
   After saving the configuration and restarting the agent, the agentODBCString property in SQ_PROPERTIES in the tier 1 source database will contain the new connection string.
6. If there are any existing clients to this agent, refresh the source (so it retrieves the revised connection string) by making a change to the source in Data Replicator Manager. For instance, you can change the notification address on the Advanced panel, and then change it back.
7. Configure Db2 LUW to allow proxy access to the Informix source:
   db2 catalog tcpip node ifxserv remote ifxserv server 9089
   db2 catalog database ifxdb as ifxdb at node ifxserv authentication SERVER
   If the Informix database name is longer than the Db2 limit of 8 characters, use the following command to associate that name with the dbname of ifxdb:
   db2 catalog dcs db ifxdb as ifx_long_name
   
8. If a firewall is running on Linux, add an exception for port 50448.
   

On Tier 3 (Windows instance at AWS):

1. Install Db2 LUW.
2. Install SQDR.
3. License SQDR, StarSQL, SQDR Plus.
4. Create local Db2 LUW database SQDRC.
5. If desired, install StarAdmin and bind packages in SQDRC.
6. Configure SQDR to use local SQDRC database for its control database.
7. Install the Postgres ODBC driver.
8. Edit etc\hosts:
   Run Command window as administrator
   cd \windows\system32\drivers\etc
   Run notepad host and add
   x.x.x.x Hostname

Where x.x.x.x is the IP address of the internal network's firewall, and Hostname is the name that appears in the agentODBCString value in SQ_PROPERTIES on the source system. SQDR uses that hostname to connect to the staging database to pick up incremental changes.

9. If necessary, import the CA certificate (from tier 2 setup above) into the GSKit certificate store used by Db2 and complete the Db2 client SSL configuration. See Configuring Db2 LUW for SSL for details.
10. Configure SQDR source to Informix source, using the IBM Db2 ODBC driver and specifying SSL connectivity. Specify user credentials. Here is sample connection string:
    
    Hostname=mydb2server;port=50448;Database=ifxdb;
    Protocol=TCPIP;Authentication=SERVER;SECURITY=SSL;
    Ssl_client_keystoredb=C:\SSL\keystore.kdb; Ssl_client_keystash=C:\SSL\keystore.sth
    
    
11. Configure SQDR destination to Postgres.

See the Accessing Informix using IBM Db2 ODBC Driver through StarPipes or through Db2 LUW section of the Tips for Informix Data Replication technical document for additional considerations; you may need to uncheck Quote SQL identifiers on the Subscription Wizard panel to avoid a syntax error when creating subscriptions.

On Tier 4 (destination):

No setup required.