StarQuest Technical Documents

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

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

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 Regedit to configure TCP KeepAlive Settings.
• Create the following Windows account and record its password:
  • sqdr - SQDR client user. This account requires no special operating system authorities and can be either a local or a domain account, but we recommend using the name sqdr and its password must conform to the password complexity requirements of the Windows 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. We recommend configuring this user as "Password never expires" and uncheck "User must change password at next logon". To use a name other than "sqdr", see Tier 2 UserID Considerations.
• 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 -an | findstr LISTENING | findstr :8080
  • Open the Resource Monitor from the Performance tab of Task Manager and examine the Listening Ports displayed on the Network tab.
  • 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

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

1. Verify the Tier 2 prerequisites: DB2 Workgroup for LUW 11.5.5 should be installed and running, firewall exceptions defined, and Windows user sqdr created.
2. Using the link provided in the confirmation e-mail, download the SQDR Plus software. Right-click and select Properties of the zip file and unblock it if necessary. extract the .zip file in a temporary folder.
3. Log on to the computer as a user with Administrator rights, and change to the directory where you extracted the installer.
4. Execute setup.exe to launch the installer.
5. The following dialog will appear; click Install whether or not you plan to install SQDR on this system.
   

   

6. The following dialog will appear; click Yes to install SQDR on this machine (typical combined tier 2 & 3 scenario).
   

   

7. A progress dialog is displayed while SQDR is installed.
8. On the Welcome dialog, click Next.
9. Accept the License Agreement and click Next.
10. The Service Parameters dialog will appear :

11. Enter the following values and press Next:

• Password for Launch Agent Control Database (Derby): Enter a password that will be used for access to the Derby control database used by the Launch Agent. 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.
• Port for SQDR Control Center (jetty): default 8080
• SSL Port for SQDR Control Center (jetty): default 8443

12. The DB2 Parameters dialog will appear; this allows you to specify the location of the database files and logs used by the local DB2 LUW staging database.

    

13. Select the desired drive locations for DB2 database files and DB2 logs.
14. The default value for DB2 Instance is obtained from the DB2INSTANCE environment variable. Change this value if you will be using a different DB2 instance for the staging databases (e.g. in a Windows cluster environment).
15. The name of the DB2 service is displayed for informational purposes. The value may vary depending on the version and edition of DB2 being used, or if you are using a Windows clustered environment.
16. Select Next.
17. Accept to install SQDR Plus in the default directory of C:\Program Files\StarQuest\sqdrplus or browse to select an alternate directory. Click Next to continue.
18. Accept to place SQDR Plus Program Data in the default location of C:\ProgramData\StarQuest or browse to select an alternate directory. Click Next to continue.
19. Click Install to begin the installation or click the Back button to make any changes.
20. A command window will appear briefly while the installer creates a DB2 database SQDRC for use as SQDR control database and updates DB2 database manager parameters. Review the results and and hit Enter to close the window when the operations complete.
21. After the installation is complete, click Finish.
22. Open the Services control panel and verify that all three SQDR Plus services are active.

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:

• Open the Services control panel and verify that all three SQDR Plus services are started.
• If the services are not started, use the SQDR Plus program group shortcuts to 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
  C:\ProgramData\Application Data\StarQuest\sqdrplus.
• Examine the Windows event log for any errors related to starting the services.
• 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 -a -o or by opening the Resource Monitor from the Performance tab of Task Manager and examining the Listening Ports displayed on the Network tab. 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.