StarQuest Technical Documents

SQDR and SQDR Plus: Migrating to a New Server (original)

Last Update: 16 June 2021
Product: SQDR Plus
Version: 4.50 and later
Article ID: SQV00PL023a

Abstract

This technical document describes the process for moving an existing production SQDR replication environment to a new system and ugrading to the the latest product versions with minimal disruption.

This is the original version of this document, which included several steps (migrating from 32-bit to 64-bit SQDR, converting the SQDR control database from SQL Server to Db2 LUW) that are not typically necessary, and may require revision. The current version of this document is here.

This document was developed during the migration of an actual customer environment that had been in production for several years, and describes several upgrade steps that are typically not needed for more recent installations. The following documents describe simpler migrations that are typical subsets of the tasks performed in this procedure:

• 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

 

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 64-bit SQDR
• migration to a newer version of Db2 LUW
• convert the SQDR control database from SQL Server to 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.
• Simpler updates: future updates can be accomplished with a single combination installer that contains both SQDR Plus and SQDR (64-bit).
  
• Fewer services on the new machine: since SQL Server is no longer required for the SQDR control database, there are fewer services to install, run, and maintain on the new system.
  

This document assumes that SQL Server will not be installed on new machine, and the SQDR control database will be converted to Db2 LUW. If you choose to continue to use SQL Server for the control database, several steps can be skipped.

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
• Verify connectivity between the systems
  

Initial setup of new system

• Install software (Db2, ODBC Driver for SQL Server, SQDR Plus/SQDR/StarSQL combo installer) on the new system
  
• Install other ODBC drivers (e.g. Oracle Instant Client, iAccess Client Solutions) if necessary.
  
• Install StarLicense Server if necessary.
  
• License Db2 and StarQuest products.
  
• Install Db2 Data Studio Client 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 creating a temporary agent with SQDR Control Center, or by invoking SQLJ.REPLACEJAR().

Move SQDR

• Stop SQDR, SQDR Plus Jetty & Launch Agent services service on old machine
  
• Update the SQDR (SQL Server format) control database to 4.5 format
  
• Create ODBC data sources on the new system if necessary
  
• Using Data Replicator Configuration on the new machine and a temporary control database, convert the SQDR control database to Db2 LUW

Move SQDR Plus Agent

On the old machine:

• 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

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
  
• Using a temporary control database, replicate one row of SQ_AGENTS from Derby (old system) to Derby (new system)
• 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.
• Determine which 64-bit ODBC drivers are needed for source or destination (e.g. ODBC driver SQL Server, Oracle Instant Client, iAccess Client Solutions). StarSQL is 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, 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 information about SQDR

• Examine the SQDR sources and destinations - 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 table to determine which ODBC data sources and drivers are used for sources & destinations.
  
• Use Regedit/Export to create a text file containing the DSN information (HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\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
  http://docs.starquest.com/Supportdocs/techStarSQL/StarSQLODBC/Installation/SQ003_DistributingDataSources.shtml
• 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.
  

Gather Credentials

• Local Windows 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.
  
• 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 IBMi User Authorities
• SQL Server user - we will be accessing SQL Server on the old system for the following purposes:
  • updating the SQDR control database to SQDR 4.5 format
  • replicating the SQDR control database to Db2 LUW on the new system
  • creating a scratch database that will be used to create a temporary agent on the new system (to instantiate Db2 function/jar mechanism)
    

Verify Connectivity

• Verify that we can connect from the new machine to SQL Server on the old machine - possible issues: firewall, TCP/IP enabled in SQL Server Configuration Manager; dynamic ports (start SQL Server Browser service on the old machine)
• Verify that we can connect to Derby on the old machine - possible issues: firewall
• Access to the iSeries 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) and SQL Server by program name e.g.:

"D:\Program Files\Microsoft SQL Server\MSSQL11.SQLEXPRESS\MSSQL\Binn\sqlservr.exe"
"C:\Program Files (x86)\Microsoft SQL Server\90\Shared\sqlbrowser.exe"

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. If nothing else, 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. E: & 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).
• install Microsoft ODBC 17 for SQL Server driver.
• Install and configure other ODBC drivers e.g. Oracle Instant Client, MySQL, Informix, IBM iAccess Client Solutions.
• 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) 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
• 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 SQDRC

   

Move SQDR

Use SQDR on the new system and ControlDB scripts to convert and replicate SQDR control database.

On the old system:

• Freeze subscription changes
• Do a backup of the entire /ProgramData/StarQuest/sqdrplus directory
• Pause all I/R groups
• Stop and disable SQDR, SQDR Plus jetty and SQDR Plus Capagent services. Leave SQDR Plus Derby running.
• Use SQL Server Management Studio backup/restore function to clone the control database e.g. ControlDB to ControlDB45. When restoring from the backup, specify different names for the container and log files
• If SQDR on the original is older than 4.02, update ControlDB45 to 4.02 format by running the 4.1x version of updatedb.sql in SQL Server Studio. See Upgrade Considerations for SQDR 4.5/Upgrading the Control Database from SQDR 3.x.
• Use SQL Server Management Studio to execute SQL commands to change "StarSQL 32" to "StarSQL (64-bit)" in connection strings; see Upgrading to 64-bit SQDR for details:

use ControlDB45;
go

update [dbo].[srcdest]
set connection_string_override =
'DRIVER={StarSQL (64-bit)};' +
substring([connection_string_override],21,
len(connection_string_override)-20)
where connection_string_override like
'DRIVER={StarSQL 32};%' and
dbtype=3;
GO

update [dbo].[srcdest]
set dsn='StarSQL (64-bit)'
where dsn='StarSQL 32' and dbtype <> 3 and
coalesce(connection_string_override,'')<>'';
GO

On the new system:

• Create (64-bit) ODBC DSN's for sources & destinations if necessary
• Use Data Replicator Configuration to use the newly created ControlDB45 on SQL Server and update it to 4.5 format. To use a connection string, choose the SQL Server Native Client 11 driver, enter the string Server=oldsys\instance; user=sa or some other ID, and select ControlDB45 from the dropdown list of databases. You can verify the converted SQL Server controlDB by starting up SQDR - you should see the configuration.
• Run Data Replicator Configuration to create empty control tables (schema SQDR) in SQDRC. These tables will be the target of the snapshot replication from SQL Server.
• Create a local Windows user sqdrtemp (or some name other than sqdr).
• Use Data Replicator Configuration a second time to create empty control tables (schema SQDRTEMP) in SQDRC. We will use this controlDB for replicating the actual controlDB. Use the StarSQL (64-bit) connection string Server=SQDRC;HostName=127.0.0.1;Port=50000, connect with the userID sqdrtemp, and select the schema SQDRTEMP.
• Set up a snapshot replication from SQL Server on old system to Db2 LUW SQDRC on new system:
  • Source: Microsoft ODBC 17 for SQL Server Driver - Server=oldsys/instance
    user=sa or some other ID
    Destination: StarSQL (64-bit) - Server=SQDRC;HostName=127.0.0.1;Port=50000
    user=sqdr
  • Use the script ControlDB.txt to import the list of tables and the script ControlDBMaps.tsv to import the column definitions. The subscriptions should be designed to Use existing tables, Append replicated rows to existing data, and be placed into a snapshot group with concurrency=1 and Halt on Errors Only.
  • Run the snapshot replication.
• After replicating the controlDB, create a backup of SQDRC and use Data Replicator Configuration to switch to the Db2 LUW controlDB that has just been replicated (schema SQDR). Specify sqdr as the user.

You should now see all the subscriptions and groups, and all the groups are paused.

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.
• 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 E:\temp compress

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

On the new system:

• Stop Jetty & Launch Agent
• 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 sqdr.properties file of the original system.
  
  
• Move the rows of interest in Derby SQ_AGENTS by using one of the following methods. 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);

OR

• Use SQDR on the new system to do a snapshot of the rows of interest in Derby SQ_AGENTS:

Source:
ODBC Driver: StarSQL (64-bit)
Server=SQDRPLUS;HostName=old-sys;Port=1527
Advanced - turn off incremental

Destination:
ODBC Driver: StarSQL (64-bit)
Server=SQDRPLUS;HostName=127.0.0.1;Port=1527

Create a snapshot subscription:
Table: SQ_AGENTS
Destination: Use existing table
Criteria: CONTROLDB <> 'SQDRPLUS'

• 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 E:\TEMP ON E: DBPATH ON L: comprlib db2compr.dll

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: - 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
  db2 backup database SQDRP0 to E:\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 necessary, stop the agent and Update iSeries host components - you will need QSECOFR credentials on the iSeries. 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, 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.