StarQuest Technical Documents

Migrating SQDR Plus Subscriptions from a Test Environment to a Production Environment

Last Update: 19 July 2010
Product: SQDR Plus and SQDR
Version: 3.63x or later
Article ID: SQV00PL004

Abstract

This document provides instructions on how to migrate SQDR Plus incremental replication subscriptions from a test environment to a production environment.

Before You Begin

• Install SQDR Plus on the production source database system. For assistance, follow the instructions in the SQDR Plus Quick Start Guide, available on the StarQuest Customer Support web page.
• On the SQDR client system, install the ODBC drivers needed to connect to the source and destination databases.

Solution

These instructions use the terms "source" to refer to the DB2 system on which SQDR Plus is installed, "destination" to refer to the target database, and "SQDR system" to refer to the system on which SQL Server and SQDR reside. Below is an overview of the steps involved, and each step is further described in the sections that follow.

On the Test SQDR system:

Step 1: Prepare incremental replication groups and record SQDR service settings.

Step 2: Perform a backup of the SQDR control database.

Step 3: Record the ODBC data source names.

On the Production SQDR system:

Step 3: Create the ODBC data sources.

Step 4: Install and configure SQDR.

Step 5: Restore the SQDR control database.

Step 6: Create destination database and import or restore tables.

Step 7: Verify the properties of the SQDR Sources and Destinations.

Step 8: Synchronize SQDR Plus subscriptions with the production source database system.

On the Test SQDR system:

Step 1: Prepare incremental replication groups and record SQDR service settings.

1. Launch the Data Replicator Manager.
2. Right-click each incremental replication group and select Pause Updates.
3. Right-click each incremental replication group a second time and select Properties. Under the Schedule tab, select the On Demand radio button. Click OK to save the group properties.
4. Right-click the SQDR service node in the left pane and select Properties. Record all of the settings under each tab.

Step 2: Perform backups of the SQDR control database and, optionally, the destination database.

1. Using the SQL Server Management Studio, perform a full backup of the SQDR control database (typically named ControlDB).
2. Locate the backup file (e.g. ControlDB.bak) and copy it to a temporary folder on the production SQDR system.
3. Optionally, perform a backup of the destination database objects on the test system and copy it to the new destination database server.

Step 3: Record the ODBC data source names.

1. Open the Data Replicator Manager.
2. Right-click a Source, select Properties, and record the DSN name of the corresponding ODBC data source. Repeat for all of the Sources and Destinations.

On the Production SQDR system:

Step 3: Create the ODBC data sources.

The ODBC data sources on the production SQDR system must be created with the exact same names as those on the Test SQDR system. 

1. Launch the Windows ODBC Data Source Administrator, which is typically found in the menu path Settings –>Control Panel –>Administrative Tools –>Data Sources (ODBC).
2. Create one or more data sources that connect to the production source database. Be sure that the Data Source Name of each matches exactly the name of the ODBC Source DSN recorded in Step 3.
3. Create one or more data sources that connect to the production destination database. Be sure that the Data Source Name of each matches exactly the name of the ODBC Destination DSN recorded in Step 3.

Step 4: Install and configure SQDR.

The version of SQDR that you install must be the exact same version of the SQDR running on the test system. Contact StarQuest Customer Support if you no longer have the SQDR installation media for that version. After the subscriptions have been migrated, you can upgrade to a more recent version of SQDR, if desired.

Run the setup.exe program from the SQDR installer image.

1. At the end of the installation, with the Run Configuration Wizard option enabled, click Finish.
2. In the first pane of the Configuration Wizard, select the SQL Server data source that connects to the local SQL Server installation and enter the user ID required to log in to the SQL Server. Click Next to proceed.
3. Select the option 'Create a new control database' and click Next. Select a name for the control database and leave the schema field blank. The name of the control database for the production environment does not need to match the name of the control database used for the Test SQDR system.
4. Choose whether to start the Data Replicator service automatically when the computer is started or manually when the service is needed. Select the option to start the service using the Local System Account (which can be changed at a later time through Windows Services) and click Next.
5. Uncheck the options to start the Data Replicator Service and the Data Replicator Manager after the configuration is complete. Click Finish to save the Data Replicator configuration.

Step 5: Restore the SQDR control database.

1. Launch the SQL Server Management Studio.
2. Right-click the new control database and select Tasks –>Restore –>Database.
3. Under the General tab, check the 'From device' radio button as the 'Source for restore' and click the ellipses (...) button to the right of the field to open the Specify Backup dialog.
4. In the Specify Backup dialog, select File as the Backup media. Click Add and navigate to the .bak file that contains the backup of the control database for the Test SQDR system. Click OK twice to return to the Restore Database dialog.
5. On the General tab, check the box under the Restore column for the backup set and verify that the 'To database' field shows the name of the new control database that you created for the production environment in Step 4 as you configured SQDR.
6. On the left pane, click the Options page.
7. Check the box 'Overwrite the existing database' and select the 'Leave database ready to use...' as the Recovery state.
8. Under the 'Restore the database files as' section, select the files to restore by clicking the ellipses (...) button to the right of the fields. The first file should be the .mdf file for the new control database (e.g. ControlDB.MDF), typically located in C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data. The second file should the .ldf file for the new control database (e.g. ControlDB_log.LDF), whichi is likely to be located in the same directory as the .mdf file.
9. Click OK to start the restore process. When complete, verify that the new control database contains the same tables as the control database on the Test SQDR system.

Step 6: Create destination database.

1. If necessary, create a new database on the database server that will serve as the destination. Be sure to create a database that has the same name as the destination database accessed by the existing SQDR subscriptions.
2. Create the destination tables in one of the following ways:
   • If the SQDR subscriptions on the test SQDR system use the Destination option "Create object when subscription is saved" or "Use existing table", you will need to manually create the destination tables. Import the tables from the test destination database or restore the contents of the test destination database from a backup.
   • If the SQDR subscriptions on the test SQDR system use the Destination option "Create every time baseline is Run", you do not need to manually create the tables as they will be automatically created later in Step 8.

Step 7: Verify the properties of the SQDR service, sources, and destinations.

1. Launch the Data Replicator Manager.
2. If the license dialog appears, add the license keys that were provided by StarQuest for using the products.
3. Right-click the service in the left pane and select Properties. Set the properties under each tab, using the values recorded in Step 1, so that they match the Test system. If any changes were made, right-click the service and select Stop Service. Again, right-click and select Start Service.
4. Review the properties for each Source and update the User ID and Password entries, if needed. Under the Advanced tab, set a Notification Address (even if this value was not set on the Test system) and a default Database and/or Schema for the source.
5. Review the properties for each Destination and update the User ID and Password entries, if needed. Configure it with a default Database and/or Schema under the Advanced tab.

Step 8: Synchronize SQDR Plus subscriptions with the production source database system.

The synchronization process requires that all of the incremental replication subscriptions run baseline replications. We recommend that you perform the procedures in this step during a time when the source tables are not heavily used.

1. Launch the Data Replicator Manager.
2. The incremental replication groups should still be paused. If not, right-click each group and select Pause Updates.
3. Right-click each incremental replication group and select Reset I/R Group. Click OK on the confirmation prompt.

This will re-register the subscriptions on the new production system and flag the member subscriptions as needing baseline replications. If the group is set to run baseline replications "As Needed", the baseline replications will automatically start after several minutes. If the group is configured to run baseline replications "On Demand", right-click on the group and select Run Group. Alternatively, enable the group schedule to automatically start baseline replications at a specific time in the future.

After the baseline replications complete, the SQDR service will begin polling for source table changes according to the interval configured in the group properties. At this point, you may modify the group schedule properties to run "As Needed", if desired.