StarQuest Technical Documents

Upgrade Considerations for SQDR 4.5 (DEPRECATED)

Last Update: 25 Oct 2019
Product: StarQuest Data Replicator
Version: SQDR 4.5 or later
Article ID: SQV00DR033

Abstract

Deprecation Note: This document was written in 2015 for users upgrading to SQDR 4.50; much of this information is not relevant to users of current versions of SQDR. For current information, see Upgrade Considerations for SQDR and SQDR Plus.

 

SQDR 4.50, released in April 2015, is a major revision, using a new internal architecture while retaining the familiar easy-to-use Data Replicator Manager interface. In addition, SQDR is now available as a 64-bit application, and can use either IBM DB2 for LUW or Microsoft SQL Server for its control database, simplifying installation when SQDR and SQDR Plus are installed together to enable incremental replication.

The technical document describes issues related to upgrading to SQDR 4.5 through 5.08 from earlier versions of SQDR.

 

Solution

Overview of Changes
64-bit version of SQDR
Updating 32-bit SQDR to v4.5
StarSQL (ODBC driver for DB2)
Upgrading the Control Database from SQDR 4.0x or 4.1x
Upgrading the Control Database from SQDR 3.x
SQL Server considerations
Using IBM DB2 for LUW as the control database
Deprecation of Older Windows Operating Systems
Troubleshooting

Overview of Changes

The following summary was previously documented in the Release Notes, and has been moved here for historical purposes:

• Windows XP, Windows Vista, Windows Server 2003 and Windows Server 2008 are no longer supported.
• This version of SQDR includes an update to StarSQL; if you are currently using a version of StarSQL earlier than 6.19, you will need to rebind StarSQL packages on the host computer.
• The SQDR installer installs or updates StarSQL if StarSQL is not present or already installed at v6.1 or later. If a version of StarSQL prior to v6.1 is present, StarSQL is not updated. We recommend uninstalling older versions (pre-6.1) of StarSQL prior to installing SQDR so that you will automatically receive updates to StarSQL when SQDR is updated.
• Be sure to use a SQL Server Native 11 ODBC driver or ODBC Driver 17.x or 13.1 for SQL Server rather than the older "SQL Server" ODBC driver for the SQDR control database and SQL Server sources and destinations. We recommend using ODBC Driver 17.3, ODBC Driver 13.1, or SQL Server Native 11.
• Be sure to use SQL Server 2008 or later (SQL Server 2008R2 or later recommended) or IBM DB2 for LUW 10.5 or 11.1 (11.1 recommended) for the SQDR control database; if you are currently using SQL Server 2000 or 2005 for the SQDR control database, please contact StarQuest support for advice.
• If the SQDR service is configured for autostart, Data Replicator Configuration will configure the SQDR service to automatically restart if the service should fail for any reason. Use the Services Control Panel to change the values on the Recovery tab after running Data Replicator Configuration if you wish to use different settings.
• When upgrading from any version of StarQuest Data Replicator earlier than 3.70 or when uninstalling SQDR, if the installer appears to stall after displaying "Unregistering modules", move the main installer window to see if a hidden dialog is waiting for input.

The following considerations apply to upgrading from a version of SQDR earlier than 4.01:

• Advanced users using the key logging function (enablekeylog.vbs) may use the truncate command to ensure that ir_keylog is empty when upgrading from any version of StarQuest Data Replicator earlier than 3.65. For example, if the control database is called ControlDB, you can run the following from SQL Server Management Studio: TRUNCATE TABLE [ControlDB].[dbo].[ir_keylog], where [ControlDB] should contain the actual name used for SQDR in the deployment.
• If you wish to use some of the new incremental replication features such as copying an incremental subscription, you must update the sources and destinations by specifying a default Database and/or Object Schema on the Advanced pane of the properties of a source or destination.
• If you wish to use the restart/recovery feature, refresh each incremental subscription by opening its properties, saving, and then re-running it.
• If SQDR Plus is configured to use the maxTrans or singleTrans setting, this setting should be removed from the SQ_PROPERTIES table and/or the sqagent.properties configuration file prior to upgrading; contact StarQuest support if you need assistance removing these settings. Refer to "Specifying Commit Limits for Incremental Apply" in the online help for information on configuring similar functionality in the Replication Manager.

SQDR Plus: The following information was relocated from the SQDR Plus release notes:

• If you are currently using DB2 LUW 10.1 for the local staging database, you must upgrade to DB2 LUW 10.5 before creating any additional staging agents. You may continue to use existing agents with DB2 10.1, though we recommend updating to 10.5 or 11.1 at your earliest convenience.
• The browser-based management tool for SQDR Plus has been renamed from SQDR Manager to SQDR Control Center, but existing documentation, URL's, etc may still use the original name SQDR Manager; these two names can be considered as interchangeable.
• If you used SQDR Plus 4.70 or earlier to create publications to tables on a DB2 for LUW source database, those publications should be deleted and recreated.
• Users who are upgrading from StarQuest Data Replicator Plus Classic (v3x) should contact StarQuest support for assistance.

 

64-bit version of SQDR

SQDR 4.1x and earlier was distributed as a 32-bit application, though frequently it was installed on 64-bit operating systems such as Windows Server 2008R2 and 2012. Customers have the choice of continuing to use the 32-bit version of SQDR on a 64-bit operating system; however, we recommend upgrading to the 64-bit of SQDR to take advantage of enhanced performance and to be prepared for future upgrades. In addition, SQDR Plus (used for incremental replication) is available only as a 64-bit application, and is bundled with the 64-bit version of SQDR; if you are installing SQDR and SQDR Plus on the same system (the typical scenario for an incremental replication scenario), you can used the combined installer to simplify installing and updating both products.

See Upgrading to 64-bit SQDR for details.

Updating 32-bit SQDR to v4.5

If you choose to not update to 64-bit SQDR at this time, you can continue to use the 32-bit version of SQDR by using the 32-bit installer for SQDR, available by request from StarQuest support. Run both SQDR and SQDR Plus installers to upgrade both components to 4.5.

After updating SQDR, be sure to run Data Replicator Configuration to upgrade the control database.

The SQDR Plus installer will offer to install the 64-bit version of SQDR. Bypass this installation by doing the following:

1. The following dialog will appear; click Install, even though you do not wish to install 64-bit SQDR.
   

   

2. The following dialog will appear; click No to skip the installation of 64-bit SQDR on this machine:
   

   

 

StarSQL (ODBC driver for DB2)

The SQDR installer installs or updates StarSQL if StarSQL is not present or already installed at v6.1 or later. If a version of StarSQL prior to v6.1 is present, StarSQL is not updated. We recommend uninstalling older versions (pre-6.1) of StarSQL prior to installing SQDR so that you will automatically receive updates to StarSQL when SQDR is updated.

If you are currently using a version of StarSQL earlier than 6.19, you will need to rebind StarSQL packages on the host computer; see Binding StarSQL Packages Using StarAdmin. If you are using SQDR Plus for incremental replications, there is no need to use the standalone StarAdmin installer; StarAdmin can be found in the Tools subdirectory of the SQDR Plus program group.

Upgrading the Control Database from SQDR 4.0x or 4.1x

You must run Data Replicator Configuration to update your existing control database to the 4.5 format; either select the checkbox to run the Configuration on the final screen of the installation wizard, or select Data Replicator Configuration from the Program Group, and choose "Use an existing control database and tables".

Upgrading the Control Database from SQDR 3.x

If you are upgrading from SQDR 3.x, you must first upgrade the control database to the 4.02 format, and then use the Data Replicator Configuration to update it to the 4.5 format (as described above). To do this, you can either install SQDR 4.1x as an intermediate step and run Data Replicator Configuration, or execute the SQL script updatedb.sql from SQL Server Studio or using osql or SqlCmd after specifying the target database with USE ControlDB (where ControlDB is the name of your control database).

SQL Server considerations

Use SQL Server 2008 or later (SQL Server 2008R2 or later recommended) or IBM DB2 for LUW 10.1 or 10.5 (10.5 recommended) for the SQDR control database; SQL Server 2000 and 2005 are no long supported for use as the SQDR control database, though they can still be used as source and destination databases.

Be sure to use a SQL Server Native ODBC driver (or "ODBC Driver 11 for SQL Server") rather than the older "SQL Server" ODBC driver for the SQDR control database and SQL Server sources and destinations. We recommend using the latest SQL Server Native driver (currently SQL Server Native 11), with the exception of source and destination connections to SQL Server 2000, which require SQL Server Native 10. You can install multiple versions of SQL Server Native client on the same machine.

Using IBM DB2 for LUW as the control database

A highlight of the new features is the ability to use IBM DB2 for LUW in place of Microsoft SQL Server for the control database; this simplifies installation for incremental replication scenarios, as DB2 for LUW is already present for use by SQDR Plus.To convert an existing SQL Server control database to DB2 for LUW, see Migrating SQDR control database from SQL Server to DB2 LUW.

Deprecation of older Windows Operating Systems

Windows Server 2003, Windows XP, Windows Vista, and Windows 2008 (non-R2) are no longer supported.

Troubleshooting

Issue: SQDR must be able to connect to both source and destination systems on startup after an update to 4.50 to determine the database type; this information is added to the control database. If SQDR is unable to connect to the source or destination database due to invalid or expired passwords or temporary connectivity issues, you may encounter undesired behavior after correcting the password or connectivity issues.

For instance, a snapshot replication to a SQL Server destination that required a password change encountered ODBC syntax errors when creating keys, slower performance, and destination database options were unavailable in Data Replicator Manager.

Solution: Restart the SQDR service.