StarQuest Technical Documents

Quick Start Guide to Using SQDR Plus on Linux with Microsoft SQL Server (Part 1)

Last Update: 13 April 2021
Product: SQDR Plus
Version: 4.2 or later
Article ID: SQV00PU006-LS

Abstract

This Quick Start Guide describes how to install and configure all of the software you need, to perform incremental replication operations using SQDR and SQDR Plus.

This document describes preparing the source database system and installing and configuring the SQDR Plus Platform. After completing the tasks in this document, continue with the appropriate version of Quick Start Guide to Using SQDR Plus (Part 2), which describes installing, configuring, and running SQDR.

We recommend printing out this worksheet and filling in the values as directed by the instructions below.

This document is intended for first-time users of SQDR and SQDR Plus. Users who are upgrading an existing installation should refer to the SQDR Release Notes or SQDR Plus Release Notes for upgrade instructions.

If you encounter any problems while using the evaluation software, please open a support ticket with Stelo Customer Support at https://support.stelodata.com or call +1 415.669.9619 for assistance.

The estimated time to complete all tasks is approximately 2 hours.

 

SQDR Plus Overview

SQDR Plus Prerequisites:

• Tier 1 Source Computer Requirements and Preparation
  
• Tier 2 SQDR Plus Platform Requirements
  
• Tier 3 SQDR Platform Requirements
• Tier 4 Destination Requirements
  
  

Installation and Configuration Tasks:

• Download and Install the SQDR Plus Software
  
• Connect to the SQDR Control Center
  
• Add the Source Database with the SQDR Control Center
  
• Verify SQDR Plus Installation using SQDR Control Center
  

SQDR Plus Overview

The Stelo Data Replicator solution consists of several software components, and involves up to four systems, all loosely connected via TCP/IP. Figure 1 illustrates the four tiers. In some cases, multiple tiers may be running on the same physical server; for instance, Tier 2 (SQDR Plus), Tier 3 (SQDR Platform) and Tier 4 (Destination DBMS) may all be installed on the same Windows system. This document covers topics related to Tiers 1 and 2.

Figure 1: SQDR Plus Architecture

 

Tier 1: Source Database System

The source system contains the data to be replicated and may be running one of the following:

• IBM DB2 for i
• IBM DB2 for Linux, Unix, Windows (DB2 for LUW)
• Microsoft SQL Server
• Oracle
• IBM Informix
• MySQL, MariaDB, or AWS Aurora
• PostgreSQL

Tier 2: SQDR Plus Platform

This system may be running either Windows or Linux. A local control database (IBM DB2 for LUW) is used for staging transactions; when a change is made on the source system, the data is stored here temporarily until it has been retrieved by the SQDR clients and applied to the destination database.

Three Java-based services are installed on this system:

• SQDR Plus Derby service - the Apache Derby database is used as a control database for the SQDR Plus Launch Agent.
• SQDR Plus Jetty service - the SQDR Control Center web application, running under the Apache Jetty WebServer
• SQDR Plus Launch Agent service - the primary SQDR Plus service. The initial process is called the Launch Agent and is used to configure and launch Staging Agents for each configured source system.

The SQDR Plus installer includes a JRE (Java Runtime Environment) for running the three services.

The SQDR Plus platform can be configured and managed from any system using a browser and the SQDR Control Center web application.

Tier 3: SQDR Platform

The SQDR service is installed on a Windows or Linux system. The SQDR installation includes the Data Replicator Manager application (running on a Windows system) for configuring and managing the replications and the StarSQL ODBC driver for connecting to DB2 systems (including the SQDR Plus staging database). The SQDR service uses a local or remote DB2 LUW or Microsoft SQL Server database for its control database. Using a local DB2 LUW database is recommended, especially for the typical scenario where both tiers 2 and 3 reside on the same system. The SQDR installer is included in the SQDR Plus installer for Windows for simplified installation in the typical combined tier2/tier3 scenario.

SQDR on Linux is typically deployed as a Docker container, though it is also available as a set of RPM files.

Tier 4: Destination DBMS

No software is installed on this platform, but it must be accessible from the SQDR Platform.

SQDR Plus Prerequisites

We recommend printing out this worksheet and filling in the values as directed by the instructions below.

The same individual or a combination of individuals must be capable of performing in the following roles in order to successfully complete the evaluation.

• Tier 1 Database Administrator is someone with administrative authority on the source system.
• Tier 2 Administrator is someone with administrative authority to install SQDR Plus and a local DB2 LUW database on a Windows or Linux computer.
• Tier 3 Administrator is someone with administrative authority to install SQDR and either DB2 LUW or SQL Server database on a Windows or Linux computer. This may be the same role as Tier 2 Administrator in a typical combined tier2/tier3 deployment.
• Tier 4 Database Administrator is someone with administrative authority on the destination system.

 

Tier 1 Microsoft SQL Server host computer requirements

• The source system must be running SQL Server 2012 or later.
• The compatibility level of the source database must be set to 90 (SQL Server 2005) or greater.
• SQL Server must be enabled for TCP/IP communications and either listening on a known port (e.g. 1433), or (if dynamic ports are being used) SQL Server Browser must be running on the source system. Both the SQDR Plus Staging Agent and the SQDR client machine communicate with SQL Server; be sure that the ports being used by SQL Server and SQL Server Browser (UDP 1434) are not blocked by firewalls. If you are using the Windows Firewall, we recommend configuring the inbound rule by program (sqlservr.exe and sqlbrowser.exe) rather than by port.
• During creation of an SQDR Plus Staging Agent for SQL Server, you can choose whether the SQDR Plus Agent will use Windows authentication or SQL Server authentication.
  • If you choose SQL Server authentication, you should supply a user with sufficient authority to create tables and stored procedures in a new schema (e.g. SQDR) on the host database. That user will also need authority to alter the database and all subscribed tables to use CHANGE TRACKING; this may require administrative privileges. If SQDR Plus will be using Change Data Capture rather than Change Tracking, the user need authority to run sys.sp_cdc_enable_db (requires membership in the sysadmin fixed server role) and sys.sp_cdc_enable_table (requires membership in the db_owner fixed database role).. See SQDR Plus SQL Server User Authorities for details.
  • If you choose Windows authentication (not applicable if SQDR Plus is running on a Linux system), then either
    • The SQDR Jetty and Capture Agent services should be running as a Windows domain user with those capabilities. In this scenario, the local DB2 LUW services should also be run using a domain account and the SQDR service user should have admin rights in the local DB2 LUW database. See DB2 Authority Issues on Windows for additional information.
    • OR Add the machine account (MyDomain\MySQDRMachine$, where MyDomain is the domain and MySQDRMachine is the name of the system running SQDR) to SQL Server and grant it the required privileges.
    • See SQL Server Integrated Security (Windows Authentication) Issues for additional information.
• To use Change Tracking, all subscribed tables must have primary keys. If needed, you can add a column of data type timestamp and make it the primary key; see Adding a Primary Key to a Source Table.
• To use Change Data Capture:
  • All subscribed tables must have primary keys or unique indexes,
  • The source system must be running SQL Server Enterprise or Developer Edition (SQL Server 2008R2 or later) or Enterprise, Developer, or Standard Edition (SQL Server 2016sp1 or later).
  • The SQL Server Agent must be running.

Tier 2 SQDR Plus Platform requirements

• We recommend printing out this worksheet and filling in the values as directed by the instructions below.
• The SQDR Plus Platform requires TCP/IP access to the Tier 1 database host and from the Tier 3 Windows platform.
• Use sysctl (Linux) or no (AIX) to configure TCP KeepAlive Settings.
• Create the following Linux user account and record its password:
  • sqdr - SQDR client user. This account requires no special operating system authorities, but it must be named sqdr and its password must conform to the password complexity requirements of the Linux system. It is used for communication from the SQDR client platform and is the owner of the control tables and stored procedures stored in the SQDR Plus control database. You will be prompted for this user and password by the SQDR Control Center when creating a connection to the source database. It is also used for running the three SQDR Plus services.
    1. Create the user with useradd sqdr
    2. Assign it a password with passwd sqdr
    3. After installation of Db2 (below), determine the name of the Db2 SYSADM_GROUP (e.g. db2iadm1) using the command db2 get dbm cfg | grep SYSADM_GROUP
    4. Edit /etc/group and add sqdr to this group.
• Install java-17-openjdk and configure it as the default JRE:
  • # yum install java-17-openjdk
  • # update-alternatives --config 'java' and select java-17-openjdk.x86_64

  or use this shell script to identify the path to the JRE
  #!/bin/sh
  JRE=`rpm -ql java-17-openjdk-headless | grep 'bin/java$'`
  alternatives --set java $JRE

• Verify that ports 8080, 8443, 50000, 50005, and some number of ports following 50005 (equal to the number of agents you plan to create) are not in use by another application. It is not uncommon for port 8080 to be used by a Java-based server such as tomcat or a RAID management tool:
  • Enter netstat -p -n -l | grep LISTEN | grep :8080
  • If ports 8080 and 8443 are in use, choose different jetty listening ports during the installation of SQDR Plus.
  • If port 50000 is in use, choose a different port when installing Db2. In addition, you will need to add the clientODBCString property to the configuration of all agents; see Configuration Reference in the Control Center Help.
  • If port 50005 (and some number of following ports equal to the number of agents you plan to create) are not available, choose another port range and follow the instructions in Configuring Non-standard RMI Ports.
• Install Db2 LUW (Linux, UNIX & Windows) 11.5.5fp1 or later for use as a control database and temporary store for SQDR Plus. Db2 LUW is bundled with SQDR Plus.
  
• The Db2 installer will create an instance that starts automatically with the system. Examine the properties of the instance to determine the port number; the default port number is 50000.
• Plan the location of Db2 database files and logs; we recommend using high performance drives (e.g. SSD) and isolating the disk activity of Db2 database files and logs from other system activity. We recommend specifying those locations during installation of SQDR Plus.

• If a firewall is enabled, create inbound exceptions for the following TCP ports:
  • SQDR Control Center port (jetty) - default 8080
  • SQDR Control Center SSL port (jetty) - default 8443
  • Db2 (SQDR Plus control database) port - default 50000

  For example, when using iptables firewall on Linux, enter the following:

  iptables -I INPUT -p tcp -m tcp --dport 8080 -j ACCEPT
  iptables -I INPUT -p tcp -m tcp --dport 8443 -j ACCEPT
  iptables -I INPUT -p tcp -m tcp --dport 50000 -j ACCEPT
  service iptables save
  service iptables restart

Tier 3 SQDR Platform requirements

See the Quick Start Guide to Using SQDR Plus (Part 2) for considerations for the Tier 3 Windows platform that will run SQDR.

 

Tier 4 Destination database requirements

• The destination DBMS must be accessible from the Tier 3 SQDR platform.
• If the destination is SQL Server, we recommend the following settings be applied on the destination database:
  ALTER DATABASE [mydb] SET allow_snapshot_isolation ON
  ALTER DATABASE [mydb] SET read_committed_snapshot ON

See Avoiding Locks for SQL Server Destination for details.

Summary of tasks

Perform the tasks in the order described, noting that some provide information used in subsequent steps. The detailed instructions for each task follow this summary.

1. Request to download SQDR Plus.
2. Receive the e-mail with a Registration Key and download links for StarAdmin, SQDR, and SQDR Plus.
3. Print the SQDR Plus Worksheet and use it as a guide while following the instructions below.
4. Tier 1 Database Administrator: verify pre-requisites on the host database.
5. Tier 2 SQDR Plus Platform Administrator: Download SQDR Plus using the download link from step 2, complete the pre-installation tasks (including creating users and installing a local copy of Db2 11.x for use as the SQDR Plus control database), and install the software on the SQDR Plus server.
6. Tier 1 SQDR Plus Platform Administrator or Tier 1 Database Administrator: Use a browser to connect to SQDR Control Center (SQDR Manager) to verify installation.
7. Tier 1 Database Administrator: Use SQDR Control Center to configure a connection to the source database.
8. Tier 1 Database Administrator or Tier 2 SQDR Plus Platform Administrator: Verify SQDR Plus installation and test Support functions using SQDR Control Center.
9. Continue to Quick Start Guide to Using SQDR Plus (Part 2), which describes installing, configuring, and running SQDR on the Tier 3 platform.

Step 1: Request SQDR Plus Software

Estimated Time: 5 minutes

All software packages are distributed as compressed files that you download from the StarQuest Ventures Web site. From a web browser enter the address http://www.starquest.com/ and click on Download for Trial under the Products menu. Request to download SQDR Plus.

Step 2: Receive the Download Confirmation E-mail from StarQuest

Estimated Time: 5 minutes

After submitting a request to download SQDR Plus, you will receive an e-mail containing a Registration Key and download links for the SQDR Plus, StarAdmin, and SQDR software. The registration key will be used in step 4b in Part 2 to obtain temporary license keys valid for 15 days and the download links will be used below.

Step 3: Print the SQDR Plus Worksheet

Step 4: Verify prerequisites on the Tier 1 Source Database system

See Tier 1 host computer requirements above.

Step 5: (Tier 2 SQDR Plus Platform Administrator) Download and Install the SQDR Plus Software

Estimated Time: 10 minutes

The default configuration makes the following assumptions:

• You are using a 64-bit Linux system
  
• You are using Db2 for LUW 11.5.5 for the local DB2 staging database, installed to the default location of /opt/ibm/db2/V11.5
  
• The instance name of the local Db2 for LUW system is db2inst1 and its home directory is /home/db2inst1
• You do not wish to customize the database files and log files used by the local Db2 (note that these files should be located on separate drives for best performance).
  
• You wish to install SQDR Plus to the default location of /opt/StarQuest/sqdrplus
  
• You wish to use /var/sqdrplus for SQDR Plus working directories.
  
• You do not need a silent install.
  
• The SQDR Control Center (Jetty) will be listening on ports 8080 and 8443.
  
• You wish to start the SQDR Plus services immediately after installation and at system boot.

If the default values are acceptable, you can skip Step 6, and the default configuration setup.conf.template will be copied to setup.conf by the installer.

1. Verify the Tier 2 prerequisites: Db2 LUW 11.5.5 should be installed and running, firewall exceptions defined, and Linux user sqdr created and added to the SYSADM_GROUP (e.g. db2iadm1).
2. Log on to the computer as a user with Administrator rights
3. Enter the umask command to determine how file permissions are set for newly created files. We recommend using umask 022 to ensure that the SQDR Plus program files are readable by the sqdr user.
4. Using the link provided in the confirmation e-mail, download the SQDR Plus software.
5. Extract the tar.gz file into a temporary folder. For example, if the file was downloaded to /root/Downloads:

# mkdir /tmp/sqdrplus
# cd /tmp/sqdrplus
# gunzip -c /root/Downloads/linux64.tar.gz | tar xf -

5. Change to the directory where you extracted the installer e.g. # cd /tmp/sqdrplus
6. If you need to make any changes to the default configuration, copy setup.conf.template to setup.conf (i.e., cp setup.conf.template setup.conf). Then open setup.conf in a text editor and make the desired modifications. For instance, to change the locations of DB2 database files and log files, modify the values for DB2DFTDBPATH and DB2NEWLOGPATH as directed in the comments.
7. Execute the setup script by entering ./setup to begin the installation.
8. Respond Yes ("y") to the "Are you ready to continue (y/n)? prompt and provide the password for the Launch Agent Control Database (Derby). Do not supply a sensitive password, as this value is stored in a non-secure manner. You will be prompted for this password when connecting to SQDR Control Center.
9. The installation will complete; examine the output for errors.

Step 6: (Tier 2 SQDR Plus Platform Administrator or Tier 1 Database Administrator) Connect to the SQDR Control Center

The SQDR Control Center (formerly known as SQDR Manager) is a web-based application. For best results, we recommend using the Chrome web browser. If you are using Internet Explorer on a Windows Server system and Enhanced Security Configuration is enabled, open Internet Options from the Tools menu and add the URLs described below to the Trusted Sites zone on the Security tab; you may also need to enable Javascript.

To verify the SQDR Plus installation, connect to the SQDR Control Center with a browser by either selecting the SQDR Control Center shortcut in the SQDR Plus program group (Windows only), or by starting a browser and entering the URL http://127.0.0.1:8080/SQDRManager (assuming you accepted the default jetty port values during installation).

You can also connect to the SQDR Control Center from another machine by starting a browser and entering the URL http://mysqdrplus:8080/SQDRManager or https://mysqdrplus:8443/SQDRManager (where mysqdrplus is the hostname of the SQDR Plus platform). Be sure that the firewall has been configured to accept incoming connections on these ports.

If you select the SSL connection and are using the self-signed SSL certificate included with the installation, the browser will issue a warning about the certificate; allow an exception to connect.

When prompted for credentials, enter user SQDR (upper-case) and the Launch Agent Control Database (Derby) password you supplied during installation.

If the browser is unable to connect to the SQDR Control Center, check the following:

• Verify that all three SQDR Plus services are started. The command ps -eaf | grep sqdr | wc -l should return seven lines (wrapper and java process for each service, plus the command you just entered). You can check the status of each service by entering

  # cd /etc/init.d
  # ./sqdr-derby status
  # ./sqdr-jetty status
  # ./sqdr-capagent status

• If the services are not started, examine the Launch Agent log and Derby log, as well as wrapper.log for each of the three services. The default location of the log files is /var/sqdrplus.
• If the services are running, verify that the ports are not being blocked by a firewall.
• Verify that the ports are not in use by another application. Stop the SQDR Plus services and view the ports in use by entering netstat -t -n -p -l. It is not uncommon for port 8080 to be in use by a Java-based server such as tomcat or a RAID management tool.
• If anti-virus software is installed, verify that it is not interfering with operation.

Specifying the location of the DB2 LUW control database files and logs: If you wish to specify the location of the database files or logfiles used for the local DB2 LUW control database (for example, you wish to place them on a high speed drive) and you did not specify the desired locations during installation of SQDR Plus, change them now by editing the Launch Agent's configuration and restarting the Launch Agent service - see Specifying the location of DB2 database files or Specifying the location of DB2 logfiles in Tips for the SQDR Plus DB2 LUW Staging Database.

Step 7: (Tier 1 Source Database Administrator task) Add the Source Database with the SQDR Control Center

Estimated Time: 5 minutes

1. Connect to SQDR Control Center with a browser as described in the previous section.
2. Logon as user SQDR, using the Launch Agent Control Database (Derby) password as supplied during installation.
3. Select Add Database from the Databases drop-down menu.

 

4. Choose the appropriate source database type (Microsoft SQL Server) and select Continue. You will see the following screen:

5. Enter the following values:

• Database: source SQL Server database name.
• Host Name : Host Name or IP address of the source system. If you are using a named instance of SQL Server, specify the instance name here - e.g. mysqlhost\MYINSTANCE.
• Port Number: enter the port of the source system. This field can be left blank if SQL Server is listening on the default port (1433) or if SQL Server Browser is running on the source system, which is typical if you are using a named instance and dynamic ports. See the Microsoft KB article Configure a Server to Listen on a Specific TCP Port for further information.
  
• Userid: a SQL Server user on the source system with authority to create tables and stored procedures in a new schema (e.g. SQDR) on the host database and to alter the database and all subscribed tables to use CHANGE TRACKING.
  
• Password: password for the Userid.
• If you select Windows Security, you do not supply a Userid and password, but the SQDR Plus services should be running as a Windows user that has the authority to perform the above tasks. This option is not applicable if SQDR Plus is running on a Linux system.
  
• Control schema: use the default value (SQDR) unless there are multiple SQDR Plus systems accessing this host database. SQDR Plus will create this schema on the host database and populate it with control tables and stored procedures.
• Change Tracking or Change Data Capture: choose the desired method of acquiring changes. Change Data Capture offers a number of advantages over Change Tracking (more granular changes, support for replication using unique indexes rather as well as primary keys, ability to use the compareChangeData property to enhance performance), but requires that the source system be running either the Standard Edition (SQL Server 2016sp1 & later) or the Enterprise or Developer Edition of SQL Server (any supported version), and that SQL Server Agent be running. Change Tracking works with all editions of SQL Server.
  

6. Select the Next button. You will see the following screen:

7. Enter the following values:

• Stage to local DB2 database: enabled (default)
• The values below Stage to local DB2 database refer to the local DB2 for LUW database system which was installed as part of the Tier 2 SQDR Plus Platform Prerequisites above.
• Choose an existing database from the Control Database dropdown,.or enter the following values to create a new database:
  • Port Number: enter the port of the local DB2 database. Default is 50000.
  • Userid: enter sqdr.
  • Password: enter the password of the Windows or Linux user sqdr created on the SQDR Plus platform as part of the Tier 2 SQDR Plus Platform Prerequisite above.

.

8. Select the Next button and configure the logging and optional email notification values. You can leave these fields blank during the installation and configure the email notification feature at a later time.

9. Select Next to create the Agent. This operation will take several minutes.
10. Verify that the new DB2 LUW control database (e.g. SQDRP0) was created on the desired drive (as specified in task 3 in Step 7 above) - e.g. if you specified the D: drive, expand D:\DB2 and look for a folder named SQDRP0.
11. When you see a plus sign next to Databases, expand Databases and select the new database.
12. Choose Start/Restart Agent from the Database drop-down menu.

Selecting Start/Restart Agent creates the Staging Agent on the SQDR Plus system and creates configuration tables and stored procedures on the source system. This process may take several minutes; a green checked icon will appear next to the database name when the process is complete. If the icon remains red or yellow, additional troubleshooting may be required before proceeding.

Step 8: (DB2 Administrator task) Verify SQDR Plus Installation using SQDR Control Center

Estimated Time: 5 minutes

Use the SQDR Control Center to verify that the Capture Agent is running and to test the Publication and Support functions.

1. Connect to SQDR Control Center with a browser and logon as described above.
2. Expand Databases and select the desired database. Verify that the Staging Agent is running by observing that there is a green checked icon next to the database name.
3. Verify that the SQDR Plus platform can send SQDR Plus support logs to StarQuest Customer Support by selecting Send Logs to StarQuest from the Support drop-down menu. This will collect and transfer support files directly to StarQuest. If the SQDR Plus platform is not able to reach external ftp sites, select Save Logs to local file system (as zip file) to create and store the support files on an alternative computer. Send these files to StarQuest Customer Support if requested to do so.
4. Test the Publication function. Note that publishing tables before creating subscriptions is required for Oracle source databases.

 

1. Select the Agent and then select the Publications panel in the right window.
2. Next select Add Publication from the Resource drop-down menu or the Plus sign from the top of the window.
3. For DB2 for i and Oracle source databases, you will be prompted for the credentials of a user with sufficient authority to grant access to the Agent user (e.g. SQDR) for the tables to be subscribed to; this will typically be a user with SECOFR or SYSTEM authority.
4. Enter schema and table names (you can use SQL wildcards like %).
5. Click Search
6. Select one or more tables to be published. A state of "Published" indicates a successful publication.

Next Steps

Continue with the appropriate version of Quick Start Guide to Using SQDR Plus (Part 2), which describes configuring and running SQDR on the Tier 3 platform.