StarQuest Technical Documents

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

Last Update: 2 March 2015
Product: SQDR Plus
Version: 4.2 or later
Article ID: SQV00PU006-LS

Abstract

StarQuest Product Release Notes 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 StarQuest Customer Support at http://support.starquest.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:

Installation and Configuration Tasks:

SQDR Plus Overview

The StarQuest 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.

SQDR Plus architecture

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

Tier 2: SQDR Plus Platform

This system may be running either Windows or Linux. A local control database (IBM DB2 for LUW Express) 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 system. The SQDR installation includes the Data Replicator Manager application 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.

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 Express database on a Windows or Linux computer.
  • Tier 3 Administrator is someone with administrative authority to install SQDR and either DB2 Express or SQL Server database on a Windows 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 2008R2 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, different privileges may be needed. 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 have primary keys.
  • 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), and the SQL Server Agent must be running.

StarQuest Product Release Notes

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.
  • Install DB2 Workgroup for LUW (Linux, UNIX & Windows) 11.1 for use as a control database and temporary store for SQDR Plus. DB2 Express is bundled with SQDR Plus.

    Before installing DB2, plan what user account will be used to run the DB2 services. In most cases, this will be a local user e.g. db2admin. However, if you will be running the SQDR Plus services using a domain user rather than the default of Local System Account (for instance, when accessing a SQL Server source database using Windows authentication), then the DB2 services must also use a domain account (e.g. mydomain\db2admin); that account should be a local Administrator, and should be created by the domain admistrator before installing DB2.

    Perform the following steps to install DB2 on Windows; installation on Linux is similar:

    1. Verify that the drive where you intend to install DB2 has 8.3 naming enabled - see the 8.3 naming discussion in Tips for the SQDR Plus DB2 LUW Staging Database for details. We have found that 8.3 naming is typically enabled for the system drive (C:), but disabled for any additional drives.
    2. Extract the contents of the compressed archive (e.g. v11.1.4fp4_ntx64_server_t.exe) downloaded from StarQuest
    3. Run Setup.exe
    4. From the Setup Launchpad, select Install a Product
    5. Under DB2 Express, select the Install New button
    6. Accept the license agreement and select Next
    7. For Installation Type, select Custom
    8. Click Next through several screens, accepting the default settings. If desired, you can choose to install the DB2 software in a location other than the default.
    9. On the screen Set user information for the DB2 Administration Server, either
      • Enter a password for the local user db2admin (Windows) or db2inst1 (Linux) to be created by the installer; this password should meet the password policy requirements of your operating system.
      • OR - if you will be using domain accounts for DB2 and SQDR Plus services (e.g. for SQL Server Windows authentication), enter the credentials for an existing domain user e.g. mydomain\db2admin; that user should be a member of the local Administrators group. If the installing user is not a domain administrator, you will get the expected warning The domain ID and password cannot be verified because domain administrator privileges are required. Be sure you have entered the correct credentials and continue the installation.
      1. Leave the checkbox Use the same account for the remaining DB2 services enabled.
      2. Click Next through several screens.
      3. On Set up notifications, set up a notification SMTP server if desired.
      4. On Enable operating system security for DB2 objects, unselect the checkbox Enable operating system security.
      5. On the final screen, Click Finish
  • 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.
  • 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. 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. 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.
  • 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

  • Verify that the above ports 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

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.

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 Workgroup 11.1 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 10.5 for the local DB2 staging database, installed to the default location of /opt/ibm/db2/V10.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 Express for LUW 10.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. Using the link provided in the confirmation e-mail, download the SQDR Plus software.
  4. 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 -

  1. Change to the directory where you extracted the installer e.g. # cd /tmp/sqdrplus
  2. 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.
  3. Execute the setup script by entering ./setup to begin the installation.
  4. 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.
  5. 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.

configure

 

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

config1

  1. 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.
  1. Select the Next button. You will see the following screen:

config2

  1. 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.
    • 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.
.

  1. 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.

add1

  1. Select Next to create the Agent. This operation may take a while.
  2. 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.
  3. When you see a plus sign next to Databases, expand Databases and select the new database.
  4. Choose Start/Restart Agent from the Database drop-down menu.

add2

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.


DISCLAIMER

The information in technical documents comes without any warranty or applicability for a specific purpose. 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 instructions may need to be modified to be appropriate for the hardware and software that has been installed and configured within a particular organization.  The information in technical documents should be considered only as an example and may include information from various sources, including IBM, Microsoft, and other organizations.