StarQuest Technical Documents

SQDR Plus: Distributed Network Examples (previous version)

Last Update: 14 March 2019
Product: SQDR Plus
Version: 4.50 and later
Article ID: SQV00PL043A

Abstract

This is an older version of this technical document included for historical purposes, demonstrating proxy techniques using StarPipes, SQL Server Linked Server, and Db2 Passthrough. In the current version of this technical document, we now recommend using netsh interface portproxy instead.

 

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 StarPipes, SQL Server Linked Server, or DB2 LUW passthrough to provide indirect access to internal servers.

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", running StarPipes, a SQL Server Express proxy server, DB2 LUW hosting 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 (StarPipes on tier 3 Proxy) and 1433 (SQL Server Express 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 - SQL Server source
• Tier 2 - SQDR Plus
  Create a new agent for each client
  DB2 LUW: Each agent will create a SQDRPn staging database.
• Tier 3 Proxy
  
  • StarPipes:
    SSL listener configured on port 448.
    Routes are configured to the DB2 LUW databases on Tier 2.
  • DB2 LUW (for remote control database scenario): Create a new SQDR control database for each client; this database is named after the client
  • SQL Server Express: This database instance has no actual tables (other than a SQDR control table) .but is acting as a proxy to tier 1 SQL Server source tables and SQDR Plus SQ_* tables using Synonyms. These are configured with a script.
  • 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 448 (StarPipes/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 tier 1 SQL Server source through SQL Server proxy on tier 3 Proxy for Baseline and for reading SQ_PROPERTIES table for connection info to the tier 2 system
• SQDRSVC communicates through StarPipes on tier 3 Proxy for access to the tier 2 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:

• 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.
• StarPipes:
  • The StarPipes listener is configured for SSL with an idle timeout of 600 seconds to avoid zombie connections
  • Configure the default route to DB2 LUW running on the tier 2 system, so it is not necessary to create explicit routes for the SQDRPn staging databases.
  • Add a route to the new SQDR control database (remote control database scenario).
• SQL Server Express:
  On the SQL Server Express proxy server on Tier 3 Proxy, each client will have a linked server to the tier source database as well as a local proxy database (named for the client) containing synonyms for the source tables and the SQ_* tables on the tier 1 SQL Server. This is accomplished by customizing and running the following SQL scripts in SQL Server Studio:
  • 0_CreateDatabase.sql - Creates the local SQL Server database. Change the name of the database and execute the script.
    
  • 1_CreateLinkedServer.sql - Create linked server to the source system specific to the client. Change the name of the linked server, the password for the tier 1 system, and the tier 1 SQL Server instance name (if necessary). The name of the linked server should match that of the local database.
  • 2_Create_SQ_PROPERTIES.sql - Create the SQ_PROPERTIES table in the local SQL Server database. Change the name of the database.
    
  • 3_Edit_SQ_Properties.sql - Copies the contents of this table from the tier 1 source system (using Linked Server) and modifies the clientODBCString property. Change the name of the database and the linked server.
  • 4_CreateSynonymsScript.sql - This creates a script to creates synonyms to the source tables. Note that the collection of tables in the source database may vary between clients, so this needs to be run for each client for the correct source database. Edit the database name.
  • 5_RunSynonymsScript.sql - Edit the script created in step 4, adding
    use mydatabase;
    GO
    
  • 6_Create_ControlDB.txt - this describes the DB2 LUW operations described above (creating a local DB2 LUW database, running StarAdmin, running SQDR configuration, and adding to StarPipes).
    

Firewall:

Allow incoming connections (448 & 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.3).
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 the tier 3 proxy created above.
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 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 remote control database, SQDRSVC will use the SQDR control database created on tier 2 and routed via StarPipes on tier 3 Proxy. Either

• Copy sqdr.properties (created on the tier 3 proxy) to C:\ProgramData\StarQuest\sqdr and run SQDR Configuration, accepting all defaults.
• Use SQDR Configuration to connect to the control database via SaaSHostname/port 448 (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 proxy SQL Server instance running 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.

Note that if you are using a remote control database, the Data Replication Manager on the client machine cannot be used to create or modify subscriptions, since there is no access to the TABLEINFO stored procedure when the source is accessed through the SQL Server proxy running on Tier 3 Proxy.

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)

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 SQDR Troubleshooting 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.