StarQuest Technical Documents

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

Last Update: 07 July 2008
Product: SQDR Plus and SQDR
Version: 3.4x 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 Part One of 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 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 back up 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.

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 a back up of the SQDR control database.

  1. Using the SQL Server Management Studio, perform a full back up of the Test SQDR control database (typically named ControlDB).
  2. Locate the back up file (e.g. ControlDB.bak) and copy it to a temporary folder on the production SQDR system.

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

Step 4: Install and configure SQDR.

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, and the Windows account under which the Data Replicator service will run.
  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 back up 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), 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. On the destination database server, create a new database that will serve as the destination. Be sure to create a database that has the same name as the destination database accessed by the Test SQDR system.

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

  1. Launch the Data Replicator Manager. Right-click the service in the left pane and select Start Service.
  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).
  5. Review the properties for each Destination and update the User ID and Password entries, if needed.
  6. Right-click the service in the left pane and select Stop Service.
  7. Close the Data Replicator Manager.

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 snapshot 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 SQL Server Management Studio, open a New Query window and execute the following SQL statement against the control database:

    update ir_subscription set capture_configured = 0,
    updates_enabled = 0,
    snapshot_required = 1,
    source_event = ' ', -- a single space
    flagged_count = 0

  2. Open the Data Replicator Manager, right-click the service in the left pane, and select Start Service.
  3. On each incremental replication group, right-click and select Properties. Under the Advanced tab, check the box marked 'Receive Change Data Notifications from Capture Agent'. Click OK.
  4. On each incremental replication group, right-click and select Resume Updates.
  5. On each incremental replication group, right-click and select Run Group.

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.

DISCLAIMER

The information in technical documents comes without any warranty. The author(s) or distributor(s) will not accept responsibility for any damage incurred directly or indirectly through use of the information contained in these documents. The information in technical documents may be gathered from various sources, including IBM, Microsoft, and other organizations.