Stelo Technical Documents

SQDR and SQDR Plus: Migrating to a New Server

Last Update: 15 December 2022
Product: SQDR Plus
Version: 4.50 and later
Article ID: SQV00PL023

Abstract

This technical document describes the process for moving an existing production SQDR replication environment (combined tier 2/3) to a new system and upgrading to the the latest product versions with minimal disruption.

The following documents describe other migration scenarios:

• SQV00PL025: SQDR Plus: Migrating to a New Server (moving only tier 2 - SQDR Plus)
• SQV00DR037 Migrating SQDR control database from SQL Server to Db2 LUW
• SQV00PL023a: Migrating to a New Server (Original Version). This is the original version of this document, which includes non-typical steps such as migrating from 32-bit to 64-bit SQDR and converting the SQDR control database from SQL Server to Db2 LUW.

 

Goals: Using this procedure accomplishes the following goals:

• Move the SQDR platform to a new machine without needing to recreate configurations or run baselines
• migration to a newer version of Db2 LUW

Benefits: You may choose to move the SQDR environment to a new system, rather than updating the existing system, for the following reasons:

• New system requirements: as we add new capabilities to SQDR to make it more powerful, the hardware and software requirements have evolved, and moving the SQDR replication product to a new system may be the best way of meeting the current system requirements.
  
• Less disruption or downtime on existing system: the existing system can continue to run while we install and prepare the new system.
  

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.

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, including Windows domain user and machine accounts if being used (e.g. for SQL Server Integrated Security).
• Verify connectivity between the systems and that you have a mechanism for transferring large files.
• These instructions assume that the original system is running SQDR 5.08 or later. If it is not, you can either update the original system to 5.08 before migration (recommended), or do a 2-step migration (to 5.08, then to current version) on the new system. You may also choose to perform the 5.08->current (5.22 or later) update on the original system.
• Note that after the update from 5.08 to 5.22, a harmless "Table Altered" condition will occur for all subscriptions. If you have a large number of subscriptions, you may want to temporarily disable email notification in Data Replicator Manager to prevent an email flood.
  

Initial setup of new system

• Install software (Db2, SQDR Plus/SQDR/StarSQL combo installer) on the new system
  
• Install other ODBC drivers (Oracle Instant Client, iAccess Client Solutions, ODBC Driver for SQL Server, etc.) if necessary.
• Install SQL Server Express if necessary (if you are using SQL Server rather then Db2 LUW for the SQDR control database)
  
• Install StarLicense Server if necessary.
  
• License Db2 and Stelo products.
  
• Install Db2 Data Studio Client and/or SQL Server Management Studio if desired
  
• Rebind StarSQL packages on the source system if necessary
  
• Create local Windows user "sqdr" with same password as original system.
• Instantiate the Db2 function/jar directory by invoking SQLJ.REPLACEJAR().

Move SQDR

• Update old machine to 5.08 or later if necessary.
• Stop SQDR, SQDR Plus Jetty & Launch Agent services service on old machine
  
• Create ODBC data sources on the new system if necessary
• Create a backup of the control database (SQDRC if using Db2 LUW; ControlDB if using SQL Server) and copy to the new system.
• On the new system, restore & upgrade the control database.
  

Move SQDR Plus Agent

On the old machine:

• Update old machine to 5.08 or later if necessary.
• If you will be updating to a newer version of Db2, run db2ckupgrade
  
• Db2 backup of SQDRP0
  
• Copy Db2 backup image and agt0 subdirectory to the new machine
• Also copy conf\sqagent.properties to the new machine for reference - you will need the encrypted password from the old system.

On the new machine:

• Stop SQDR Plus jetty & derby services
  
• Restore SQDRP0 and verify access. Update the database if necessary (e.g. if moving from Db2 10.5 or 11.1 to 11.5.x). Compact the Db2 table spaces if necessary.
  
• Copy agt0 home directory to conf directory and copy node.properties from agt0 to the conf root
  
• Use ij to export/import the 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.
• What user is the Db2 service running as, and is it local or domain?
• Determine which 64-bit ODBC drivers are needed for source or destination (e.g. ODBC driver for SQL Server, Oracle Instant Client, iAccess Client Solutions). StarSQL and SQDR ODBC drivers are installed by the combo installer.
• How is the system licensed - StarLicense Server or node-locked?
• -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 not, 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?
• Determine whether Db2 LUW or SQL Server is being used for the SQDR control database, and the name of the database e.g. SQDRC or ControlDB.
• If SQL Server is being accessed using Integrated Security (for control database, source, destination, or incremental source from an agent), be sure you understand which userID's are involved; examine what userID's are being used to run the services on the old system. You will likely need to configure services on the new machine the same way. You may need to add the machine account for the new machine to SQL Server.
• If any source or destination is being accessed using an SSL connection, determine what keystore or other SSL-related information is necessary.

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 information about SQDR

• Examine the summary lists of SQDR sources and destinations in Data Replicator Manager - do any of them use ODBC data sources (DSN's) rather than connection strings? If so, we will need to recreate the DSN's on the new system.
  • Examine the SRCDEST control table to determine which ODBC data sources and drivers are used for sources & destinations. Viewing the summary list of sources and destinations in Data Replicator Manager can also be used.
    
  • Use Regedit/Export to create a text file containing the DSN information (HKEY_LOCAL_MACHINE\SOFTWARE\ODBC\ODBC.INI)
    
  • If you have more than a few StarSQL DSN's, consider using the dsimport procedure to simplify creating the DSN's on the new system; see the Star SQL tech doc How to Distribute StarSQL Data Source(s) to Your Users for details.
• Examine the incremental groups to verify if the TOKAFKA procedure or any other stored procedures are in use for Change Data Processing, as extra steps may be involved to instantiate these stored procedures.
• Determine whether the control database is hosted by Db2 LUW or SQL Server by examining the contents of C:\ProgramData\StarQuest\SQDR\sqdr.properties.
• Examine the Notification tab in Data Replicator Manager. You may want to temporarily disable email notifications during the migration.
  

Gather Credentials

• Local Windows user "sqdr" - we will be creating an identical user on the new system
• Derby user "sqdr". The Derby passwords on the two systems do not need to match, but you do need to know both passwords.
  
• For Db2 for i source, 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 IBM i User Authorities. in most cases, we can use the SQDRADM user.
  

Verify Connectivity

• Verify access to the source from the new machine - possible issues: firewalls, different networks, DNS, exit programs that restrict access
• Verify Access to the destination from the new machine
  

We will also need a mechanism (e.g. a network folder) to transfer several files (e.g. backup of SQDRP0, SQDRC (or ControlDB backup), Derby export, and the agt0 subdirectory) from the old machine to the new one. If a file server is not available, you can share a folder (e.g. C:\temp) on either system and access it from the other.

Initial setup on the new system

• Identify drives for Db2 tablespaces and logs (e.g. S: & L:).
• Install Db2: chose custom; unselect "Enable Operating System Security". Record the password for (local user) db2admin created by the installer.
• license Db2
  db2licm -a db2std_vpc.lic
• Install Data Studio Client (optional).
• Install SQDR Plus/SQDR/StarSQL combo installer; this will also create SQDRC and configure Db2 DBM parameters.
• Install latest SQDR (if newer than what is bundled in SQDR Plus).
• If SQL Server is being used for the SQDR control database, install SQL Server Express. Note that using Db2 LUW (SQDRC) is typical for a combined tier system, but using SQL Server is also an option.
• Install and configure ODBC drivers e.g. Oracle Instant Client, MySQL, Informix, IBM iAccess Client Solutions, Microsoft ODBC 17 for SQL Server)
• Licensing for StarLicense Server:
  • Install StarLicense Server (unless using another existing machine for StarLicense Server)
  • Get IP address of new system; create & install permanent licenses
• Licensing for node-locked licenses:
  
  • Run License Configuration from SQDR or StarSQL program group
    
  • Get HostID; create & install permanent licenses
    
  • Restart SQDR service
• If StarSQL is being updated from anything earlier than 6.19, use StarAdmin to rebind packages on iSeries or Db2 LUW source. Run StarAdmin from the Tools/StarAdmin subdirectory of the SQDR Plus program group. The package collection is typically SQDR; use a userID with sufficient authority to bind packages in the SQDR collection (QSECOFR recommended). see Binding StarSQL Packages Using StarAdmin.
• Create local Windows user "sqdr". The password must match the password on original system.
• Instantiate the stored procedure JAR file ( C:\ProgramData\IBM\DB2\DB2COPY1\\function\jar\SQDR\CAPTUREPROCS_JAR.jar) by connecting to SQDRC and call sqlj.replace_jar()
• Create the directory C:\ProgramData\IBM\DB2\DB2COPY1\function\jar
• Start a db2 command prompt by entering db2
• db2> connect to SQDRC user SQDR using password
  db2> CALL sqlj.replace_jar( 'file:C:\Program Files\StarQuest\sqdrplus\capagent\UDBProcs.jar', 'CAPTUREPROCS_JAR' )
  db2> commit
  db2> disconnect all

Move SQDR

Backup the control database and copy it to the new system.

On the old system:

• Do a backup of the entire /ProgramData/StarQuest/sqdrplus directory
• Pause all I/R groups
• Stop and disable SQDR service.
• Backup the control database:
• For SQL Server:
  
  • Create a backup of. ControlDB by using the SQL Server Management Studio backup/restore function or a command like:
    
    Default instance:
    SqlCmd -E -S localhost –Q “BACKUP DATABASE ControlDB TO DISK='C:\temp\ControlDB.bak'”
    
    Named instance (where the SQL Server instance is named SQLEXPRESS):
    SqlCmd -E -S .\SQLEXPRESS –Q “BACKUP DATABASE ControlDB TO DISK='C:\temp\ControlDB.bak'”
    
    
  • Copy the backup of ControlDB to the new system.
• For Db2 LUW:
  
  • Use db2 list application to make sure there are no active connections to SQDRC.
    
  • db2 deactivate db SQDRC
  • If you are updating to a major new version of Db2, copy db2ckupgrade.exe from the installer media of the new version of Db2 (DB2\WINDOWS\UTILITIES) and run it on SQDRP0. Take corrective measures if necessary.

  ...db2\Windows\utilities\db2ckupgrade SQDRC -l db2ckupgrade.log -u adminuser -p password

  • Create a Db2 backup of SQDRC. The compress parameter is optional.

  db2 backup database SQDRC to C:\temp compress

  • Copy the backup of SQDRC to the new system.

 

On the new system:

• Create (64-bit) ODBC DSN's for sources & destinations if necessary
• Stop the SQDRSVC service if it is running.
• Restore the control database from backup, dropping the fresh SQDRC created by the SQDR Plus installer if it exists.
  • SQL Server: use SSMS or a command like:
    
    Default instance:
    SqlCmd -E -S localhost –Q “RESTORE DATABASE ControlDB FROM DISK=’C:\temp\ControlDB.bak'”
    
    Named instance (where the SQL Server instance is named SQLEXPRESS):
    SqlCmd -E -S .\SQLEXPRESS –Q “RESTORE DATABASE ControlDB FROM DISK=’C:\temp\ControlDB.bak'”
    
    
  • Db2:
    db2 deactivate db SQDRC
    db2 drop db SQDRC
    db2 restore db SQDRC from C:\temp ON S: DBPATH ON L:
    
    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).
  • If you are migrating to a new version of Db2, you will need to run db2 upgrade db SQDRC and several other recommended commands; see Updating to Db2 11.5.5 for details.
  • If you are performing a minor update of Db2 (e.g. 11.5.4 to 11.5.5), run db2updv115 -d SQDRC
  • Verify that you can connect to the database
    db2 connect to SQDRC user sqdr
    db2 disconnect SQDRC
• Use Data Replicator Configuration to configure access to the control database and update the control tables if necessary.
• Start SQDR Service and Data Replicator Manager and verify that you can see all the subscriptions and groups. Note that all the incremental groups are paused.
• At this point, we have completed the move of tier 3 - i.e. we now have a split tier configuration.

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
• Stop and disable SQDR Plus jetty and SQDR Plus CapAgent services. Leave SQDR Plus Derby running.
• 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.
• If you are updating to a major new version of Db2, copy db2ckupgrade.exe from the installer media of the new version of Db2 (DB2\WINDOWS\UTILITIES) and run it on SQDRP0. Take corrective measures if necessary.

...db2\Windows\utilities\db2ckupgrade SQDRP0 -l db2ckupgrade.log -u adminuser -p password

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

db2 backup database SQDRP0 to C:\temp compress

• Copy the backup of SQDRP0 and the agt0 directory to the new system.
  

On the new system:

• Stop Jetty & Launch Agent services.
• Place agt0 directory in /ProgramData/StarQuest/sqdrplus/conf/
• 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 sqagent.properties file of the original system.
  
• Use the ij interactive SQL tool to export selected contents of the Derby table SQ_AGENTS on the old system to a text file and import that file into the same table on the new system. 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.
  

1. On both systems, temporarily add the following line to /ProgramData/StarQuest/sqdrplus/derby/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. Examine the resulting text file e.g. in Notepad and make sure that it contains only the rows of interest.
5. Transfer the resulting text file to the new system.
6. 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);

7. The Derby user APP is no longer needed and can now be removed from derby.properties.

 

• If the location of the StarQuest ProgamData has changed - for example, when migrating to a clustered configuration where the ProgramData directory 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
  
  RESTORE DATABASE SQDRP0 from C:\TEMP ON S: DBPATH ON L:

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

• If you are migrating to a new version of Db2 (e.g. from 10.5 or 11.1 to 11.5.x), you will need to run db2 upgrade db SQDRP0 and several other recommended commands; see Updating to Db2 11.5.5 for details.
• If you are performing a minor update of Db2 (e.g. 11.5.0 to 11.5.5), run db2updv115  -d SQDRP0.
• Optional: update the database parameters for these recommended values:
  db2 update db cfg for SQDRP0 using CATALOGCACHE_SZ 1000
  db2 update db cfg for SQDRP0 using LOGFILSIZ 8192
  
• Verify that you can connect to the database
  db2 connect to SQDRP0 user sqdr
  db2 disconnect SQDRP0
• Note the size of tablespace directory on S: - 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
db2 ALTER TABLESPACE USERSPACE1 REDUCE MAX
db2 ALTER TABLESPACE SQDRSPACE LOWER HIGH WATER MARK
db2 ALTER TABLESPACE SQDRSPACE 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
• Optional: Create a new backup of SQDRP0 just in case
  db2 backup database SQDRP0 to C:\temp 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 the source is Db2 for i, stop the agent and Update iSeries host components. You will need QSECOFR credentials on the iSeries; you may be able to use the user SQDRADM. Restart the agent.
  

In Data Replicator Manager:

• Refresh the source (pick up the new ODBC connection string) by viewing its properties and making a change (e.g. change the notification address to Disabled, click OK, change it back, and click OK)
• Examine SQDR.SRCDEST in the SQDR control database (SQDRC) 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.