StarQuest Technical Documents

Cloning SQDR Subscriptions

Last Update: 04 June 2010
Product: StarQuest Data Replicator
Version: 3.63 or later
Article ID: SQV00DR011

Abstract

This document provides instructions for cloning subscriptions from one SQDR 3.x system to an SQDR 3.x installation on a different computer. This document assumes that the source database remains the same, but that the SQDR system will be moved to another computer. The destination database may or may not be relocated.

For SQDR Plus users: these instructions assume that the SQDR Plus software will remain on the same source database system. If you need to migrate SQDR Plus subscriptions from one source database environment to another, please use the instructions in the technical document titled "Migrating SQDR Plus Subscriptions from a Test Environment to a Production Environment."

Solution

These instructions use the term "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 existing SQDR system:

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

Step 2: Record the ODBC data source names and configuration information.

On the new 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 restore objects, if necessary.

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

Step 8: Synchronize source and destination database tables.

On the existing SQDR system:

Step 1: Record SQDR service settings and Pause Updates for all incremental replication groups.

1. Launch the Data Replicator Manager.
2. Right-click the SQDR service node in the left pane and select Properties. Record all of the settings under each tab.
3. If you have incremental replication groups, right-click each and select Pause Updates. Right-click again, select Properties, and set the Schedule option to "On Demand". Click OK to save the group properties.

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 new SQDR system.
3. If the destination database will also be re-located to another system, perform a backup of it and copy it to the new destination database server.

Step 3: Record the ODBC data source names and configuration information.

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.
3. Launch the ODBC Data Source Administrator which is typically found in the menu path Settings -> Control Panel -> Administrative Tools -> Data Sources (ODBC).
4. Open the data source configuration for each data source identified as a Source or Destination and record the configuration settings.

On the new SQDR system:

Step 3: Create the ODBC data sources.

The ODBC data sources on the new SQDR system must be created with the exact same names as those on the existing 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 source database. Be sure that the Data Source Name of each matches exactly the name of the ODBC Source DSN recorded in Step 3. Use the same data source configuration settings as those on the existing SQDR system.
3. Create one or more data sources that connect to the destination database. Be sure that the Data Source Name of each matches exactly the name of the ODBC Destination DSN recorded in Step 3. If the destination database is now located on a different server (i.e. the new SQDR system), configure the data source to connect to that database server. Otherwise, use the same data source configuration settings as those on the existing SQDR system.

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 existing 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. 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.
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 be 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. After the database is restored, verify that the new control database contains the same tables as the existing control database.

Step 6: Create destination database.

If the location of the destination database will not change, skip this step.

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 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 existing destination database or restore the contents of the existing destination database from a backup.
   • If the SQDR subscriptions on the test SQDR system use the Destination option "Create every time baseline is Run", or "Re-create object on each replication", 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 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 existing 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 (for incremental replication only) 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 source and destination database tables.

For snapshot replication subscriptions:

1. Open the Data Replicator Manager.
2. If the destination database has been re-located and you opted not to restore the destination database objects, open each subscription properties, verify that the subscription is configured to "Create object when subscription is saved" and click OK to save the subscription.
3. Right-click on the subscription and select Run.
   

For incremental replication subscriptions:

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