StarQuest Technical Documents

Upgrading to StarQuest Data Replicator (SQDR) 3.6x (DEPRECATED)

Last Update: 17 May 2010
Product: StarQuest Data Replicator
Version: 3.60 or later
Article ID: SQV00DR005

Abstract

Deprecation Note: This document was written in 2010 for users upgrading to SQDR 3.60; 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.

 

This technical document provides instructions for upgrading the StarQuest Data Replicator (SQDR) for Windows software and discusses considerations related to the new features and functionality of the software.

The procedures outlined in this document are intended for existing SQDR users. If you are installing SQDR for the first time, refer to the appropriate SQDR Quick Start Guide at http://www.starquest.com/Supportdocs/browseQuickStarts.shtml. The SQDR Plus Quick Start Guides include instructions for installing both SQDR and SQDR Plus.

.

Solution

Follow the procedures below to upgrade the SQDR software.

Before You Begin

• StarSQL Users: For best results, upgrade StarSQL to V5.53 or later prior to upgrading SQDR for Windows. In addition, we recommend the following:
  • Stop the SQDR service before upgrading StarSQL to avoid having to reboot the computer.
  • After the StarSQL upgrade is complete, bind new packages on the DB2 server if the previously installed version was V5.52 or earlier. Refer to the technical document Binding StarSQL Packages using StarAdmin for detailed instructions.
• SQDR Plus Users: SQDR V3.6x for Windows requires the use of SQDR Plus V3.65 or later. Upgrade SQDR Plus on the iSeries/UDB system prior to upgrading SQDR for Windows.
• Perform a full backup of the SQDR control database (e.g., ControlDB) using the SQL Server backup tool.
• Have available the original SQDR installation source files for the version currently in use, in the event that you need to revert back to the previous release. Contact StarQuest Customer Support if you no longer have the installation image available.
• Record the SQDR service properties as the upgrade process will revert the settings to their default values. To access the service properties:
  1. Launch the Data Replicator Manager from the StarQuest Data Replicator program group.
  2. In the left pane, locate the SQDR service, as represented by the topmost node labeled with the name of the local computer.
  3. Right-click on the service and select Properties. Review and record the settings under each tab.
• Have available the SQL Server system administrator (i.e., "sa") password.

SQDR Upgrade Instructions

The StarQuest Data Replicator software is distributed as a compressed file that you download from the StarQuest Ventures web site. Contact StarQuest Customer Support for download instructions.

Perform the SQDR Upgrade

Start the upgrade installer using the instructions below. The SQDR installer will automatically detect the existing version and upgrade the files as needed.

1. Log on as an Administrator.
2. Download SQDR and uncompress the file into a temporary directory.
3. Run the setup.exe program from the SQDR installer image in the temporary directory.
4. At the upgrade confirmation prompt, click OK to start the upgrade process.

At the end of the upgrade, the SQDR Configuration will automatically launch. If prompted to restart the computer, cancel the SQDR Configuration and restart the computer. Once the computer has restarted, start the SQDR Configuration using the shortcut in the StarQuest Data Replicator program group.

Run the SQDR Configuration

It is mandatory to run the SQDR Configuration to update the SQDR control database tables.

1. If the SQDR Configuration is not already running, start it using the shortcut in the StarQuest Data Replicator program group.
2. In the first pane of the Configuration wizard, select the SQL Server data source to access the SQDR control database and enter the SQL Server credentials for a user who is a member of the sysadmin server role (e.g., the "sa" user). Click Next to proceed.
3. Select the option Use an existing control database and tables and click Next.
4. From the Database drop down list, select the SQDR control database (e.g., ControlDB) and enter the schema "dbo" as the owner. Click Next.
5. Select Automatic as the Service Startup Type and choose the System Account as the Log On As user. If you would like to change the Data Replicator service properties, we recommend doing this in Windows Services once the SQDR configuration has been completed. Choose the correct Service Locale for the local Windows system and click Next.
6. Review the configuration summary, choose to start the Data Replicator Service and Manager and click Finish to complete the Data Replicator configuration.

Post-Upgrade Instructions

After upgrading and running the configuration, follow the instructions below to configure the SQDR service and set new Source/Destination default configuration values.

1. If the Data Replicator Manager is not already running, start it using the shortcut in the StarQuest Data Replicator program group.
2. In the left pane, locate the SQDR service, as represented by the topmost node labeled with the name of the local computer. Right-click on the service and select Properties.
3. Modify the service properties as needed, using the previous settings recorded in the Before You Begin section. Click OK when finished to close the service properties. Stop and restart the service in order for the settings to take effect.
4. Open the properties for each Source and Destination and set a default Database and/or Schema under the Advanced tab. These settings will be used as default values when creating new subscriptions and are required when using the new Copy Member feature. Note: Use caution when making changes to the User ID/Password values in the Source/Destination as inadvertent changes can be disruptive to existing subscriptions.

Upgrade Considerations for SQDR Plus Users

SQDR includes new features related to incremental replication processing that may be benefical to many users. Review the considerations and recommendations below. Note that support for these new features requires the use of SQDR Plus V3.65 or later for iSeries/UDB. Upgrade SQDR Plus on the iSeries/UDB system prior to upgrading SQDR for Windows.

Restart and Recovery

This feature allows you to resume an interrupted baseline, appending new rows to the last committed row in the destination table, without having to start the baseline from the beginning.

• If the ability to restart a baseline is required, modify the SQDR service properties to set the ODBC and/or BCP commit interval to a relatively low non-zero value.
• SQDR Plus for iSeries users: Support for baseline recovery will only take place for incremental replication (IR) subscriptions which contain the “RRN” source definition. For existing IR subscriptions already using RRN, no change is required. For those subscriptions which do not have the "Create target unique index using source table RRN" option enabled, use Merge with Source on the Columns dialog and Refresh on the Indexes dialog of the subscription properties to add this column. Note that this requires a new baseline and should only be undertaken if/when the subscription requires recovery support.

Commit Limits

New options in the Advanced dialog for the properties of an incremental group allow you to control the commitment limits (Row Limit and Transaction Limit) when applying changes to the target database in an incremental replication. Therefore, it is no longer necessary to configure the "MaxTrans" or "SingleTrans" settings in SQDR Plus. Use the instructions below to remove these options.

1. Stop the SQDR subsystem (iSeries) or the SQDR Plus service/daemon (DB2 for Windows/UNIX/Linux).
2. Connect to the database managed by SQDR Plus and run the following SQL statement:

   DELETE FROM SQDR.SQ_PROPERTIES  WHERE UPPER(PROPERTYNAME)='MAXTRANS'

3. Open the SQDR Plus sqagent.properties file for editing.
   • iSeries users: using Navigator, edit the /home/sqdr/sqagent.properties file, or run the OS400 command EDTF ‘/home/sqdr/sqagent.properties’.
   • DB2 for Windows users: use the Capture Agent Configuration shortcut in the SQDR Plus for Windows program group to open the sqagent.properties file.
   • DB2 for UNIX/Linux users: edit the file /var/sqdrplus/<database_name>/conf/sqagent.properties file.
     
4. Comment out any occurrence of the keyword “singleTrans" by preceding the line with a hash mark (“#”). Save the sqagent.properties file.
5. Start the SQDR subsystem (iSeries) or the SQDR Plus service/daemon (DB2 for Windows/UNIX/Linux).

Review the incremental replication group options Row Limit and Transaction Limit to determine if they are suitable for your environment. In most cases, the default values are adequate unless specific application requirements dictate otherwise (e.g., single transactions on the destination are required or there is a limited amount of log space).

Incremental Replication Group Options

• The new incremental group Subscription Defaults “Ignore Deletes” and “Use Unique Constraints” should not be enabled unless recommended by StarQuest Customer Support.

Incremental Replication Subscription Options

• The new Merge with Source feature on the Columns dialog of the subscription propeties can be used to update a subscription configuration with source schema changes, while preserving user customizations. If SQDR detects and reports a schema change, use Merge with Source if you want to preserve customizations, or use Rebuild Column List to add all current source table columns to the subscription configuration and revert destination column settings to their default values.
• Use of the new incremental subscription destination options “Use existing table for baseline", "Append replicated rows to existing data”, and "Ignore Deletes" should be considered by users who want to accumulate changes into an archive database while maintaining the current source images. Contact StarQuest Customer Support for guidance on using these options.

Additional Documentation

Refer to the SQDR Documentation Addendum for additional details on the new SQDR features and functionality.