Stelo Technical Documents

SQDR Plus: Migrating to a New Server

Last Update: 19 March 2024
Product: SQDR Plus
Version: 4.50 and later
Article ID: SQV00PL025

Abstract

This technical document describes the process for moving an existing production SQDR replication environment to a new system. This note describes moving only tier 2 (SQDR Plus) to a new system and assumes that tier 3 (SQDR) will not be migrated; see SQV00PL023 SQDR and SQDR Plus: Migrating to a New Server for a more extensive migration involving both tier 2 and tier 3 (SQDR Plus and SQDR).

This document assumes that the original and new systems are running similar operating systems (e.g. migrating from 64-bit Windows to 64-bit Windows or 64-bit Linux to 64-bit Linux). It also assumes that both systems are running similar versions of Db2 LUW (e.g. 11.5).

The process below describes moving only one agent, with a home directory named agt0 and a staging database named SQDRP0. If you have multiple agents, use the appropriate directory and database names. It also assumes that there is no need to keep the existing production replications in operation during the move - i.e. we can stop all SQDR services on the old system, rather than stopping and moving one agent at a time.

On Linux, use the root user to install and license Db2 and install the SQDR Plus software, and use the instance owner (e.g. db2inst1) to perform Db2 operations such as RESTORE.

Overview

Preparation

• Download software in advance of the migration
  
• Examine the existing environment to determine what needs to be moved to or recreated on the new system
• Make sure that we know the user credentials that will be needed
• Verify connectivity between the systems
  

Initial setup of new system

• Install and license Db2 LUW on the new system
  
• Install SQDR Plus on the new system
  
• Install Db2 Data Studio Client if desired
  
• Rebind StarSQL packages on the source system if necessary
  
• Create local Windows or Linux user sqdr with same password as original system.
• Instantiate the Db2 function/jar directory by creating a temporary agent with SQDR Control Center, or by invoking SQLJ.REPLACEJAR().

Move SQDR Plus Agent

On the old machine:

• Pause I/R groups and stop SQDR
• Stop SQDR Plus Jetty & Launch Agent services service on old machine
  
• Db2 backup of SQDRP0
  
• Copy Db2 backup image and agt0 subdirectory to the new machine
• Export SQ_AGENTS table from the Derby database.

On the new machine:

• Stop SQDR Plus jetty & derby services
  
• Restore SQDRP0 and verify access. Compact the Db2 table spaces if necessary.
  
• Copy agt0 home directory to conf directory and copy node.properties from agt0 to the conf root
  
• Import Derby table SQ_AGENTS
• Update host components (Db2 for i source)
  
  

Preparation

Environment

Examine the existing environment to determine what needs to be moved to or recreated on the new system:

• What is the operating system? - special considerations apply if Windows 2003 or a 32-bit OS.
• What version & licensing model of Db2 LUW is running - e.g. Db2 10.5, 11.1, 11.5; Express, Workgroup, Community, Standard, etc. Use the commands and db2level and db2licm -l.
• In some cases, SQDR Control Center on the existing system may have been configured to use a port other than 8080 - e.g. existing application ServeRAID already using 8080. Determine if the new system also has the requirement. Even it does, you may wish to continue using the non-default port if you have existing bookmarks on other systems.
• What are the versions of SQDR & SQDR Plus?

Gather information about SQDR Plus

• Determine the database (e.g. SQDRP0) and home directory (e.g. agt0) being used by the agent. The home directory is displayed on the list of agents when you select the top item (system name) in Control Center; to obtain the database name, select the agent and expand the Configuration settings window and examine controlDbUrl.
• Check agent's configuration for non-default properties such as clientODBCString property

Gather Credentials

• Local Windows or Linux user sqdr - we will be creating an identical user on the new system
• Derby user sqdr - we will be creating a snapshot replication to replicate specific contents of the Launch Agent control database to the new system. The Derby passwords on the two systems do not need to match, but you do need to know both passwords.
  
• If you are also updating the version of SQDR Plus and the source is Db2 for i, you will need a QSECOFR type of user on the iSeries - this is needed to update the host components and to bind packages in the SQDR collection. See SQDR Plus IBMi User Authorities
  

Verify Connectivity

• Access to the source from the new machine - possible issues: firewalls, different networks, DNS, exit programs that restrict access
• Access to the destination from the new machine
• If firewall is enabled on the old machine, make sure that it has exceptions for Derby (port 1527)
  

We will also need a mechanism (e.g. a network folder) to transfer several files (backup of SQDRP0 and the agt0 subdirectory) from the old machine to the new one. Use a zip or tar utility to move the agt0 subdirectory.

Initial setup on the new system

• Identify drives or folders for Db2 tablespaces and logs (e.g. E: & L: on Windows; /DB2/data and /DB2/logs on Linux)..
• Install DB2: choose custom. On Windows: unselect "Enable Operating System Security". Record the password for (local user) db2admin (db2inst1 on Linux) created by the installer.
• license Db2
  db2licm -a db2std_vpc.lic
• Install Data Studio Client (optional).
• Install SQDR Plus. Since we are moving only SQDR Plus, not SQDR, you can choose to skip the installation of SQDR (Windows). A fresh install of SQDR Plus will also create the SQDRC database (for use as the SQDR control database) and customize Db2 DBM (Database Manager) parameters.
• Create local Windows or Linux user sqdr. The password must match the password on original system.
• Instantiate the stored procedure JAR file:
  Windows : C:\ProgramData\IBM\DB2\DB2COPY1\\function\jar\SQDR\CAPTUREPROCS_JAR .jar
  Linux: /home/db2inst1/sqllib/function/jar/SQDR/CAPTUREPROCS_JAR.jar
  using either of the following methods:
• -- Create a temporary SQL Server database and an agent to it
  • On the old machine, use SQL Server Management Studio to create a scratch SQL Server database.
  • Create an agent to that database
  • Delete the temporary agent - make sure SQDRP0 gets dropped and agt0 subdirectory deleted.
  • On the old machine, use SQL Server Management Studio to drop the scratch database.
• -- Connect to SQDRC and call sqlj.replace_jar()
• Create the directory C:\ProgramData\IBM\DB2\DB2COPY1\function\jar (Windows) or /home/db2inst1/sqllib/function/jar (Linux)
• db2> connect to SQDRC user sqdr using password
  db2> CALL sqlj.replace_jar( 'file:C:\Program Files\StarQuest\sqdrplus\capagent\UDBProcs.jar', 'CAPTUREPROCS_JAR' )
  or
  db2> CALL sqlj.replace_jar( 'file:/opt/stelo/sqdrplus/capagent/UDBProcs.jar', 'CAPTUREPROCS_JAR' )
  db2> commit
  db2> disconnect SQDRC

   

Move SQDR Plus Agent

The procedure below moves one agent at a time to reduce downtime. If multiple agents share a staging database, you will need to move all of those agents at the same time.

On the old system:

• Make sure we know the following info for the agent to be moved:
  - agt0 directory
  - SQDRP0 database
  - the name of the agent as known to Derby
• Verify that there are no active connections to the SQDRP0 database with DB2 LIST APPLICATION. Since we have already stopped SQDR, Jetty & Capagent services, it is unlikely that there will be any active connections.

• Create a Db2 backup of SQDRP0 - be sure to specify COMPRESS. This could take up to 30 minutes if the table spaces have grown.

Windows: db2 backup database SQDRP0 to E:\temp compress
Linux: db2 backup database SQDRP0 to /tmp compress

• Copy the backup of SQDRP0 and the agt0 directory to the new system. If you are using ftp, be sure to specify binary transfer mode for the Db2 backup and the zip or tar archive of agt0.
• Also copy sqdrplus\conf\sqagent.properties to the new machine for reference
  

On the new system:

• Stop Jetty & Launch Agent services
• Place agt0 directory in \ProgramData\StarQuest\sqdrplus\conf\ (Windows) or /var/sqdrplus/conf (Linux)
• Copy conf/agt0/node.properties to conf (rename the original just in case)
• Edit \ProgramData\StarQuest\sqdrplus\conf\sqagent.properties - replace the encrypted password with the encrypted password value of the conf\sqagent.properties file from the original system.
  
  
• Move the contents of the Derby table SQ_AGENTS. This only needs to be done once for all agents. If you prefer to move only one agent, specify a criteria of CONTROLDB='SQDRP0' instead of CONTROLDB <> 'SQDRPLUS'. Additional considerations apply if multiple agents share a staging database.
  
  
• Use the ij interactive SQL tool to export selected contents of the Derby table on the old system to a text file and import that file into the same table on the new system:

1. On both systems, temporarily add the following line to /ProgramData/StarQuest/sqdrplus/derby.properties & restart the SQDR-Derby service:
   derby.user.APP=mypassword
   
2. On the old system, start ij from the SQDR Plus Program group and enter the following to export the rows for all Staging Agents, ignoring the row related to the Launch Agent. The number of rows should match the number of agents.

ij> connect 'jdbc:derby://localhost:1527/SQDRPLUS;user=APP;password=mypassword';
ij> CALL SYSCS_UTIL.SYSCS_EXPORT_QUERY ('SELECT * FROM SQDR.SQ_AGENTS WHERE controlDB <> ''SQDRPLUS''', 'c:\temp\sq_agents.csv',null,null,null);

4. Transfer the resulting text file to the new system.
5. On the new system, start ij and enter the following to import the text file:

ij> connect 'jdbc:derby://localhost:1527/SQDRPLUS;user=APP;password=mypassword';
ij> set schema SQDR;
ij> CALL SYSCS_UTIL.SYSCS_IMPORT_TABLE (null,'SQ_AGENTS','c:\temp\sq_agents.csv',null,null,null,0);

 

Note: on Linux, you can also transfer the entire directory /var/sqdrplus/derby/SQDRPLUS instead of using ij export/import.

• If the location of the StarQuest ProgamData has changed - for example, when migrating to a clustered configuration where ProgramData is located on the shared S: drive rather than C: - then update the DIRECTORY column of the Derby SQ_AGENTS table. See Changing SQDR Plus ProgramData directory.
• Restore SQDRP0
  Windows: RESTORE DATABASE SQDRP0 from E:\TEMP ON E: DBPATH ON L: comprlib db2compr.dll
  Linux: RESTORE DATABASE SQDRP0 from /tmp ON /DB2/data DBPATH ON /DB2/logs comprlib libdb2compr.so

Windows: If you get an authorization error for the RESTORE operation and the Db2 commands described in the next steps, shift/right-click on "Db2 Command Window" in Program group and select "run as another user", and enter the credentials for user db2admin (which you just created as part of the installation of Db2).

• Note the size of tablespace directory on E:or /DB2/data; if it is large (> 1gb), then reduce it by reorganizing all tables in the SQDR schema; see Tips for the SQDR Plus Db2 LUW Staging Database:
  

db2 connect to SQDRP0
db2 -x "SELECT 'reorg table',substr(rtrim(tabschema)||'.'||rtrim(tabname),1,50),';' FROM SYSCAT.TABLES WHERE (type= 'T') AND (tabschema = 'SQDR')" > reorg_sqdr.sql
db2 -tvf reorg_sqdr.sql
db2 ALTER TABLESPACE USERSPACE1 REDUCE MAX

• If desired, connect to SQDRP0 and run these commands:
  db2 update db cfg using AUTO_MAINT ON
  db2 update db cfg using AUTO_TBL_MAINT ON
  db2 update db cfg using AUTO_RUNSTATS ON
  db2 update db cfg using AUTO_REORG ON
• Verify that you can connect to the database
  db2 connect to SQDRP0 user sqdr
  db2 disconnect SQDRP0
• Create a new backup of SQDRP0 just in case
  Windows: db2 backup database SQDRP0 to E:\temp compress
  Linux: db2 backup database SQDRP0 to /tmp compress
  
• Start SQDR Plus and verify that the agent is listed.
• Starting the agent updates the ODBC connection string in SQ_PROPERTIES on the source.
• If you are also updating the version of SQDR Plus, stop the agent and update iSeries host components - you will need QSECOFR credentials on the iSeries. Restart the agent.
  

In Data Replicator Manager:

• Start the SQDR service.
• Refresh the source (pick up the new ODBC connection string) by viewing its properties and making a change (e.g. change the notification address, click OK, change it back, and click OK)
• Examine SQDR.SRCDEST in the SQDR control database (SQDRC for Db2 LUW; ControlDB for SQL Server) to make sure connection strings and dbms_type look OK.
• Resume the paused I/R groups.

Troubleshooting

Symptom:

The following error appeared when logging on to Control Center after a migration:

Login request failed.
Unable to connect.java.net.BindException:
Error connecting to server localhost on port 1,527 with message Address already in use: connect.

The error indicates a problem connecting to Derby (port 1527).

The following error was noticed in the derby log:

ERROR 08004: Connection authentication failure occurred. Reason: Invalid authentication.

And this error was found in the Agent's wrapper.log:

javax.crypto.BadPaddingException: Given final block not properly padded. Such issues can arise if a bad key is used during decryption.

Solution:

Confirm that the following step was performed during migration:

Edit \ProgramData\StarQuest\sqdrplus\conf\sqagent.properties - replace the encrypted password with the encrypted password value of the conf\sqagent.properties file from the original system.