StarQuest Technical Documents

Cloning SQDR Subscriptions

Last Update: 04 June 2008
Product: StarQuest Data Replicator
Version: 3.x
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.

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

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: Perform back ups of the SQDR control database and, optionally, the destination database.

1. Using the SQL Server Management Studio, perform a full back up of the 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 new SQDR system.
3. If the destination database will also be re-located to another system, perform a back up of it and copy it to the new destination database server.

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

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 does not need to match the control database name 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, 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. 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.

1. If necessary, create a new database on the destination database server that will serve as the destination database. Be sure to create a database that has the same name as the destination database accessed by the existing SQDR system.
2. At this point, you may optionally restore the contents of the destination database using the back up created in Step 1. If you do not restore the destination database, you will need to run all of the replication subscriptions (as part of Step 8).

Step 7: Verify the properties of the SQDR 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 for the products as provided by StarQuest.
3. Review the properties for each Source and Destination and update the User ID and Password entries, if needed.
4. Right-click the SQDR service in the left pane and select Stop Service.
5. Close the Data Replicator Manager.

Step 8: Synchronize source and destination database tables.

• For snapshot replication subscriptions:

1. Open the Data Replicator Manager, right-click the SQDR service in the left pane, and select Start Service.
2. Run a single subscription to verify connectivity between the source and destination database servers.
3. If the destination database has been re-located and you did not chose to restore the destination database, right-click each subscription (or group) and select Run.
   
   If the destination database has not been moved, you do not need to take any further action.

• For incremental replication subscriptions:

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_group set auto_snapshot_enabled = 0

2. Open the Data Replicator Manager, right-click the SQDR service in the left pane, and select Start Service.
3. For each Incremental Replication Group, right-click and select Run Group.

After the baseline replication completes SQDR will begin polling for source table changes according to the interval configured in the Group properties.