StarQuest Technical Documents

Quick Start Guide for the StarQuest Data Replicator v4.1x

Updated: 30 April 2015
Product: StarQuest Data Replicator
Version: 4.1x
Article ID: SQV00DR001

Abstract

Note: if you are using SQDR for incremental replication from a DB2 database (on iSeries or Windows/Unix/Linux), SQL Server, or Oracle, refer to the SQDR Plus Quick Start Guide

Note: This Quick Start Guide describes installing and configuring SQDR v4.1x and is available here for historical interest. For the current Quick Start Guide for using SQDR 4.5 and later for snapshot replication, please see Quick Start Guide for the StarQuest Data Replicator.

This Quick Start Guide describes how to install and configure the software you need, including the StarSQL ODBC driver for DB2, to perform snapshot replication operations using the StarQuest Data Replicator (SQDR) v4.1x. Read the complete document before starting your installation.

This document is intended for first-time users of SQDR. Users who are upgrading an existing installation should refer to the SQDR Release Notes and the SQDR online help for upgrade instructions.

The estimated time to complete all tasks is 1 hour.

SQDR Quick Start Guide Outline

The same individual or a combination of individuals must be capable of performing in the following three roles in order to successfully complete the installation.

• DB2 Administrator (required only if replicating to/from DB2) is someone with user credentials suitable for creating collections and binding packages on the database host. The DB2 Database Administrator runs the standalone GUI application StarAdmin to bind StarSQL packages on the database host.
• SQL Server Administrator is someone with user credentials belonging to the sysadmin fixed server role. The SQL Server Database Administrator runs the Data Replicator Configuration utility to create the SQDR control database and database objects on the local SQL Server.
• Client-Platform Administrator is someone with administrative authority to install SQDR (and StarSQL, if needed) on a Windows computer. This person must also have a set of user credentials on the source and destination database hosts that can be used to test database connectivity and that have sufficient read/write privileges to perform data replication.

General considerations

The Windows platform that will run SQDR must have:

• TCP/IP access to the source and destination database hosts.
• .NET Framework 2.0 or later (3.5sp1 or later recommended)
• A version of Microsoft SQL Server installed. SQDR uses a SQL Server database to store replication subscription information. If you do not already have a SQL Server installation, you can obtain the Microsoft Express Edition free of charge from Microsoft to use as a control database for the Data Replicator. The StarQuest Technical Document Installing SQL Server for a Control Database provides details for using SQL Server Express with SQDR.
• ODBC drivers that can access the source and destination database servers (if replicating to/from non-DB2 databases).

SQL Server considerations

1. Depending on the type of authentication that is configured for the SQL Server, you must have a SQL Server user ID (if using SQL Server and Windows authentication) or a Windows user ID (if using Windows-only authentication) that is a member of the System Administrators (sysadmin) server role.

Summary of tasks

Perform the tasks in the order described, noting that some of the steps provide information used in subsequent steps. The detailed instructions for each task follow this summary. If you do not plan to use the StarSQL ODBC Driver for DB2, skip step 3.

1. Request to download SQDR.
2. Receive the e-mail with a Registration Key and download links for SQDR and StarAdmin.
3. DB2 Administrator: Follow the instructions in the StarQuest Technical Document Binding StarSQL Packages Using StarAdmin to install StarAdmin and bind StarSQL host packages. Provide database connectivity information to the Client-Platform Administrator to be used in step 6e. Skip this step if not using StarSQL.
4. If not using StarSQL, install the ODBC driver(s) needed to connect to your source and destination database hosts.
5. SQL Server Administrator: Start the SQL Server service and configure ODBC DSN for the control database.
6. Client-Platform Administrator:
   1. Download SQDR using the download link from step 2 and install the software.
   2. Run the SQDR configuration, connecting to the local SQL Server using the DSN created in step 4.
   3. License the StarQuest software using the Registration Key from step 2.
   4. Configure a StarSQL ODBC System Data Source Name (DSN) using the information provided in step 3, above. Or configure ODBC System DSNs using ODBC drivers appropriate for your source and destination database hosts.
   5. Configure an SQDR replication subscription.
   6. Review the subscription status.

Step 1: Request SQDR Software

Estimated Time: 5 minutes

All software packages are distributed as compressed files that you download from the StarQuest Ventures Web site. From a web browser enter the address http://www.starquest.com/ and click on Download for Trial under the Products menu. Request to download SQDR. When the request is fulfilled, you will also receive StarSQL and StarAdmin.

Step 2: Receive the Download Confirmation E-mail from StarQuest

Estimated Time: Less than 5 minutes

After submitting a request to download SQDR, you will receive an e-mail containing a Registration Key and download links for the StarAdmin and SQDR software. The registration key will be used in step 6c to obtain temporary licenses valid for 15 days and the download links will be used in steps 3 and 6a.

Step 3: (DB2 Database Administrator task) Bind Host Packages Using StarAdmin

Estimated Time: 15 minutes

If you are not using the StarSQL ODBC Driver for DB2, skip this section and proceed to step 4.

The user who installs StarAdmin must be an administrator on the Windows platform. The database connectivity information collected in this section will be provided to the SQDR Client-Platform Administrator for use in step 6d. Follow the instructions in the StarQuest Technical Document Binding StarSQL Packages Using StarAdmin to install and bind StarSQL host packages.

Step 4:

If not using StarSQL, install the ODBC driver(s) needed to connect to your source and destination database hosts.

Step 5: (SQL Server Administrator task) Start the SQL Server Service and Configure ODBC DSN

Estimated Time: 5 minutes

The client computer on which you will install SQDR must have a supported version of Microsoft SQL Server installed. If you do not already have a SQL Server installation, you can obtain the Microsoft SQL Server Express Edition free of charge from Microsoft to use as a control database for the Data Replicator. Review the SQL Server considerations above for more information.

Start SQL Server Service

The SQL Server service must be running for the Data Replicator Service to access its control database. Start the SQL Server and service from Windows Services, typically found under Control Panel—> Administrative Tools—> Services.

Create a SQL Server ODBC DSN for the Control Database

Define the ODBC DSN for the SQL Server database that the Data Replicator Service will use as a control database. If the destination database is also SQL Server, create a new DSN instead of using the one previously created in step 4b.

1. Launch the ODBC Administrator from the Windows Control Panel. For most versions of Windows this is located in the menu path Control Panel—>Administrative Tools—>Data Sources (ODBC).

NOTE	If using a 64-bit SQL Server, you will need to create a 32-bit ODBC DSN for the SQL Server database using the 32-bit ODBC Data Source Administrator (odbcad32.exe), typically located in \WINDOWS\SysWOW64 directory, or use the ODBC Administrator shortcut in the SQDR Program Group.

2. Click the System DSN tab of the ODBC Data Source Administrator window.
3. To create a new data source for the SQL Server database, click Add. In the next dialog, select SQL Server Native for the driver and click Finish.  If you already have a data source defined for the SQL Server database you want to use for the Data Replicator control tables, select it and click Configure to verify the properties.
4. Specify a name and select which SQL Server you want the data source to connect to, and click Next to proceed.
5. Continue through the remainder of the wizard and click Test Data Source to ensure the DSN can connect to the specified SQL Server. If you do not see TESTS COMPLETED SUCCESSFULLY, modify the data source configuration until it can successfully connect to the server.

Step 6a: (Client-Platform Administrator) Install the StarQuest Data Replicator Software

Estimated Time: Less than 5 minutes

The StarQuest Data Replicator software is distributed as a compressed file that you download from the StarQuest Ventures Web site.

1. Log on as an Administrator.
2. Download SQDR using the download link provided in step 2 and uncompress the file into a temporary directory.
3. Run the setup.exe program from the SQDR installer image.
4. Respond to the prompts accordingly to complete the installation.

At the end of the installation, the installer prompts you to start the ODBC Administrator and/or the SQDR Configuration. You do not need to launch the ODBC Administrator, as ODBC DSNs were already created in steps 4b and 5. Select the option to launch the Configuration after the installation completes and proceed to the next step.

Step 6b: (Client-Platform Administrator) Configure the StarQuest Data Replicator Service

Estimated Time: Less than 5 minutes

You must configure the Data Replicator service to specify which SQL Server data source to use for the control tables and how the Data Replicator service should log in to the SQL Server.

1. In the first pane of the Configuration Wizard, select the SQL Server data source that you configured (in step 5) to access SQDR's control database and enter the SQL Server credentials for a user who is a member of the sysadmin server role.
   Click Next to proceed.

NOTE

If you receive an error that the data source cannot be found, launch the ODBC Data Source Administrator from Control Panel--> Administrative Tools-->Data Sources (ODBC), double-click the SQL Server System DSN, and review the configuration. At the configuration summary verify that the Test Connection succeeds. Exit the ODBC Administrator and return to the SQDR Configuration Wizard.

2. Select the default option Create a new control database and click Next.
3. Leave the default database name of ControlDB or enter an alternative name. Leave the schema name field blank, as it will default to "dbo". Click Next.
4. 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.
5. Review the configuration summary, choose to start the Data Replicator Service and Manager and click Finish to save the Data Replicator configuration.

When the Data Replicator Manager launches for the first time, a License Configuration dialog prompts you to enter your license keys. Keep this dialog open and request the license keys for all the StarQuest products you want to use, as described in the next step.

Step 6c: (Client-Platform Administrator task) License the StarQuest Products

Estimated Time: 5 minutes

StarQuest products require a valid set of licenses for evaluation purposes. You can request license keys for multiple products at once, as described below. If the computer running the StarQuest software has access to the Internet, follow the Online Licensing Instructions below. Otherwise, follow the Alternate Licensing Instructions.

Online Licensing Instructions

1. If the SQDR License Configuration utility is not already running, open the StarLicense Configuration utility from Start --> Programs --> StarQuest Data Replicator--> License Configuration.
2. Click on the License Online tab. Select a License Lock Type, enter in the Registration Key provided in the download confirmation email from step 2, and click Get License.

When the request successfully completes, the license(s) for the software you are registered to use appear in the License Keys list of the Licenses tab. The Registration Key may produce several License Keys, depending on the products you are registered to use. Subsequent attempts to use the same Registration Key will result in the identical License Key(s) being retrieved.

Alternate Licensing Instructions

1. On the computer where the StarQuest software is installed, open the StarLicense Configuration utility from Start --> Programs --> StarQuest Data Replicator --> License Configuration. Record the Host ID displayed on the License Online tab.
2. From a computer that has access to the Internet, click on or browse to the following URL:

   http://starcust.starquest.com/Registration/index.html#license

3. On the StarQuest Online Licensing Form web page, enter in the email address used for the original download request and the Registration Key you received in the download confirmation email.
4. Enter the Host ID value recorded previously and select the "Node-locked" license option. Click Next.
5. Review the information provided. If any changes are required, click Previous and modify the values as needed. Otherwise, click the Accept button.
6. Copy the license key(s) displayed on the web page. You will also receive an e-mail with the license key(s).
7. On the computer running the StarQuest software, open the StarLicense Configuration utility from Start --> Programs --> StarQuest Data Replicator --> License Configuration.
8. Under the Licenses tab, click the Add button and enter in the license key. Repeat until all of the license keys have been entered.

Step 6d: (Client-Platform Administrator task) Create ODBC DBMS Data Sources

Estimated Time: Less than 5 minutes

An ODBC data source name (DSN) defines the information that a driver needs to access a specific instance of data in a DBMS. You must define an ODBC System DSN for each host DBMS that you want to replicate to and from.

Create ODBC DSN for the DB2 source database

The following steps describe how to create a StarSQL ODBC DSN to connect to a DB2 source database. If not using StarSQL, refer to the documentation for your ODBC drivers for instructions on creating an ODBC DSN.

1. Launch the ODBC Administrator from the Windows Control Panel. For most versions of Windows the ODBC Administrator is typically found in the menu path Settings—>Control Panel —>Administrative Tools—>Data Sources (ODBC).
2. Click the System DSN tab of the ODBC Data Source Administrator window. Click Add and, in the next dialog, select StarSQL 32 for the driver and click Finish.
3. In the first dialog of the StarSQL Data Source Wizard, enter a name for the data source and, optionally, a description. Click Next to step through the Wizard panes, specifying the database connectivity parameter values obtained by the DB2 Administrator in step 3.
4. From the Network pane of the StarSQL Data Source Wizard, click the Test Connection button to ensure you can connect to the host database. On the next pane, enter a valid AS/400 user ID and password so that the data source can connect to the database and set default values, and click Summary.  If the connection fails, modify the data source configuration until the ODBC DSN can successfully connect to the host.
5. Click the Expert Page item in the left pane of the StarSQL Data Source Wizard. Select IsolationLevel in the Data Source Entry list box, and select Read Committed in the Values list box. Click Next to display the summary page and click OK to save the DSN.

Create ODBC DSN for the destination database

The following steps describe how to create a SQL Server ODBC data source that connects to the SQL Server destination database. If your destination database is not SQL Server, refer to the documentation for the ODBC driver specific for your database for instructions on creating ODBC DSNs.

1. Launch the ODBC Administrator from the Windows Control Panel. For most versions of Windows this is located in the menu path Control Panel—>Administrative Tools—>Data Sources (ODBC). If you are using a Windows 64-bit operating system, create a 32-bit ODBC DSN using the 32-bit ODBC Data Source Administrator (odbcad32.exe) found in the \WINDOWS\SysWOW64 directory, or use the ODBC Administrator shortcut in the SQDR Program Group.
2. Click the System DSN tab of the ODBC Data Source Administrator window.
3. To create a new data source for the SQL Server database, click Add. In the next dialog, select SQL Server Native for the driver and click Finish. 
4. Specify a name and select which SQL Server you want the data source to connect to, and click Next to proceed.
5. Continue through the remainder of the wizard and click Test Data Source to ensure the DSN can connect to the specified SQL Server. If you do not see TESTS COMPLETED SUCCESSFULLY, modify the data source configuration until it can successfully connect to the server.

Additional References

StarSQL Quick Start Guide
StarSQL User’s Guide
StarSQL Online Help

Step 6e: (Client-Platform Administrator) Start the Data Replicator Manager and Create a Subscription

Estimated Time: 10 minutes

You use the Data Replicator Manager to set up and administer replication operations.

1. Select Data Replicator Manager from the StarQuest Data Replicator program group if it is not already running.
2. In the License Configuration dialog, verify that you have a license key entered with a PROD ID of "DR" and click OK. See step 6c if you have not entered license keys for using the evaluation software.
3. Stop and restart the SQDR service in order for the license key to take effect. In the left pane, right-click the Service node for your computer and Stop the service. Right-click again and choose Start.

Define a Source

To define the source data that you want to replicate:

1. In the main Data Replicator Manager window, right-click the Sources folder and select the Insert Source command.
2. Select the DSN that connects to the source database that contains the data you want to replicate, and enter a user ID and password that has permission to access that database.

3. On the Advanced dialog, set the default values for Database and/or Object Schema, as applicable by the database. These values will be used by the SQDR subscription wizard to populate configuration fields. Click OK to create the source.
   

Define a Destination

Now define the destination that will receive the replicated data.

1. Right-click the Destinations folder and select the Insert Destination command.
2. Select the DSN that connects to the database to which you want to replicate the data, and enter a user ID and password that has permission to access that database. (Note that this screenshot is inaccurate; the DSN should be using the SQL Server Native driver.)

3. On the Advanced dialog, set the default value for the Database and/or Object Schema. These values will be used by the SQDR subscription wizard to populate configuration fields. Click OK to create the destination.

Create a Subscription

The Replicator Manager provides a Subscription Wizard to help you define the specific source data and how you want to replicate it.

1. Right-click the Subscriptions folder beneath the source that you added and select the Insert Subscription command. The Add Subscription Wizard appears.
2. In the Select the Source pane, enter a schema or object name if you want to filter the source objects list. Click the Refresh button to display the available source objects in the right pane and select the source object you want to replicate. Click Next to proceed.

3. In the Select a Destination pane, select the DSN that connects to the destination you added to the Replicator Manager. Review the Destination Options and Replication Options, and click Next to proceed.
4. In the Select Group pane, optionally select to add the subscription to an existing group or to a new group. Click Next to continue.
5. The Define Criteria pane allows you to specify a SQL WHERE clause in order to horizontally partition the data to be replicated. Enter a valid WHERE clause and click the Verify Criteria button, or leave the field blank. Click Next to continue.
6. The Define Destination Columns pane allows you to review and change the data type mappings for the source and destination objects. Modify the destination columns if desired and click Next.
   
   
7. Click Next in the Create Table Statement pane. The CREATE TABLE statement will not appear until the subscription has been saved.
8. On the Define Processing pane, enter optional host commands to be executed before and/or after the data is replicated. Click Next to continue.
9. On the Indexes/Constraints pane, click Refresh to display any indexes or constraints defined for the source. Select any that you want to replicate to the destination and click Next.
10. On the Define Replication Schedule pane, configure a schedule for the subscription or leave the Scheduled box unchecked. Click Next to continue.
11. Enter a name for the subscription and click Finish.

The subscription is added beneath the Subscriptions folder of the defined source and to the corresponding group.

Run the Subscription

Right click the newly created subscription and choose Run to start the replication.

Step 6f: (Client-Platform Administrator) Review the Subscription Status

Estimated Time: less than 5 minutes

1. Review the status of the subscription by highlighting the subscription in the left pane of the Replicator Manager.
   
   
2. Double-click the replication event, indicated by the icon, to view the statistics of the replication. If the data did not replicate successfully, review the error(s) and take the necessary steps to address the problem.
   
   

---

Continuing Your Software Evaluation

This Quick Start Guide is intended to help you install the StarQuest software and perform a data replication operation as quickly as possible. Please continue to explore the capabilities of SQDR by adding additional subscriptions, scheduling replication operations, and performing SQL operations on data before and/or after it is replicated.

As you use the SQDR software, refer to the product documentation for more information. The Data Replicator Manager provides an online help system that provides more information about defining and running replication operations. Press F1 or click a Help button from the Data Replicator Manager to display the online help system.

If you encounter any problems while using the evaluation software, please open a problem report with StarQuest Customer Support at http://support.starquest.com or call +1 415.669.9619 for assistance.