StarQuest Technical Documents

Quick Start Guide for the StarQuest Data Replicator

Updated: 14 April 2008
Product: StarQuest Data Replicator
Version: 3.4 or later
Article ID: SQV00DR0001

Abstract

This Quick Start Guide describes how to install and configure the software you need, including the StarSQL ODBC driver for DB2, to perform replication operations using SQDR. This document describes how to prepare a Windows computer so you can define and run an SQDR replication operation. The estimated time to complete all tasks is 45 minutes, as summarized below.

Step 1: Install the StarSQL ODBC Driver. (estimated time: 5 minutes)

Step 2: License the StarQuest Products. (estimated time: 5 minutes)

Step 3: Create DBMS Data Source and Bind StarSQL Packages. (estimated time: 5 minutes)

Step 4: Start the SQL Server Service. (estimated time: less than 5 minutes)

Step 5: Install and Configure the StarQuest Data Replicator Software. (estimated time: 10 minutes)

Step 6: Start the Data Replicator Manager and Create a Subscription. (estimated time: 10 minutes)

Step 7: Review the Subscription Status. (estimated time: less than 5 minutes)

Solution

The following sections guide you through the steps that are summarized in the preceding Abstract. The steps are performed on a Windows-based computer that has TCP/IP connectivity to the target DB2 server. If you do not plan to replicate to or from a DB2 server, skip Steps 1 through 3. If you already have an ODBC driver and a different type of database server configured you can proceed directly to Step 4 to install SQDR and use it to replicate data.

For best results with SQDR, StarQuest recommends using the StarSQL ODBC driver for access to DB2. However, SQDR does support the use of the IBM DB2 Connect driver. If you plan to use the IBM DB2 Connect driver with SQDR, skip Step 1, follow Step 2 to license SQDR, and then proceed directly to Step 4.

Before You Begin

Before you begin, review the following list to ensure that you have the information and files you need, and that your computer environment provides the minimum requirements.

General Requirements

  • You have requested and downloaded evaluation copies of the SQDR software, and the StarSQL driver if you plan to use it for accessing the DB2 database. All software packages are distributed as compressed files that you download from the StarQuest Ventures Web site. If the prerequisite software has not been downloaded, from a browser enter the address http://www.starquest.com and click on Trial Download to download the required software.
  • You have the Download Instructions that were sent to the email address specified in the StarQuest Product Download Request form. You need these instructions and Internet access to license the software.
  • You have the appropriate ODBC drivers installed and configured on the Windows system in order to access the source and destination database servers. Step 1 of this guide describes how to install the StarSQL ODBC driver for access to DB2.
  • You have access to a user ID on the source database that has adequate authority to work with the tables you wish to replicate.
  • You have access to a user ID on the destination database that has sufficient authority to perform the replication operations on the database.

SQL Server (SQDR Host) Requirements

The client computer on which you will install SQDR and StarSQL must have some version of Microsoft SQL Server installed. If you do not already have a SQL Server installation, you can obtain the Microsoft SQL Server 2000 Desktop Engine (MSDE 2000) or the SQL Server 2005 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 MSDE 2000 or 2005 Express with SQDR.

  • If you are using SQDR with SQDR Plus, the collation of the SQL Server database to where data will be replicated should be case-sensitive. Verify the collation for the database by viewing the database properties in the SQL Server Enterprise Manager (SQL Server 2000) or the SQL Server Management Studio (SQL Server 2005). A case-sensitive collation will have the characters "CS" appended to the Collation Designator. For example, the collation SQL_Latin1_General_CP1_CS_AS is a case-sensitive collation for U.S. English systems. You can set the collation for a new database using the SQL Server Enterprise Manager or the SQL Server Management Studio. To change the collation for an existing database, use the ALTER DATABASE (Transact-SQL) command with the COLLATE clause. Refer to the SQL Server Books Online for more information on collation values.
  • Depending on the type of authentication that is configured for the SQL Server, you must have a SQL Server login (if using SQL Server and Windows authentication) or a Windows user ID (if using Windows-only authentication) that has permission to access the SQL Server Control Database that is used by SQDR. This login must be a member of the System Administrators (sysadmin) fixed server role.
  • Your SQL Server user ID (if using SQL Server and Windows authentication) or Windows user ID (if using Windows-only authentication) also must have sufficient permissions to perform the replication operations on the target SQL Server database. If using Windows-only authentication, ensure that the proper logins have been created for the Windows user account that will access the target SQL Server database. For more information on SQL Server Authentication, refer to the Permissions Validation section of Administering SQL Server documentation (SQL Server 2000) or refer to the Authentication Mode section in the Installing SQL Server documentation (SQL Server 2005). 
  • If you intend to configure replication subscriptions to run according to a schedule, verify that the SQL Server Agent service is available and running. (MSDE 2000 includes the SQL Server Agent. SQL Server Express does not include the SQL Server Agent component; therefore you cannot schedule subscriptions to run automatically if you use SQL Server Express for the SQDR control database.)

If you have any problem installing or using the evaluation software, please send an email to StarQuest Customer Support at contact@starquest.com or call (+1) 415-669-9619 for assistance.

Step 1: Install the StarSQL ODBC Driver

Estimated Time: 5 minutes

For best results with SQDR, StarQuest recommends using the StarSQL ODBC driver for access to DB2. The StarSQL software is distributed as a compressed file that you download from the StarQuest Ventures Web site. See the section Before You Begin if you have not already downloaded the StarSQL software.

  1. To install StarSQL, run the setup.exe program from the StarSQL installer image.
  2. Select to perform a Custom installation and, in addition to the required components, also select to install the StarAdmin component.

At the end of the installation, a License Configuration dialog prompts you to enter your license key for using StarSQL. You can enter the license keys for both StarSQL and SQDR, as described in the If you have not yet received a temporary license key, click OK to close the License Configuration dialog. You can access the License Configuration at a later time from the StarSQL or SQDR program group.

Step 2: License the StarQuest Products

Estimated Time: 5 minutes

If you have not already received temporary license keys for the evaluation software, refer to the Download Instructions that were sent via email in response to the software evaluation request. The Download Instructions provide specific instructions for obtaining a license key that allows you to use the software product. You can add the license key for multiple products at once, as described below for adding the StarSQL and SQDR license keys.

  1. Launch the License Configuration dialog from Start—>Programs—>StarQuest Data Replicator.
  2. From the Licenses tab of the License Configuration dialog, click Add to display the Add License dialog.
  3. In the Add License dialog enter the license key for SQDR and click the Add button. If necessary, repeat this step to enter the license key for StarSQL. After you have added the license key for each of the products, click OK in the License Configuration dialog.

Step 3: Create DBMS Data Source and Bind StarSQL Packages

Estimated Time: 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 DSN for each host DBMS that you want to replicate to or from.

  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). If you are running StarSQL on a Windows 64-bit operating system, use the 32-bit ODBC Data Source Administrator (typically located in \WINDOWS\SysWOW64\odbcad32.exe) to create a new data source.
  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 values appropriate for your environment. A typical data source has the following configuration values:

Database Server Name

Relational Database Name (OS/400), DDF Location Name (OS/390 or z/OS), Database Name (UDB on Windows/Unix)

Package Collection Name

STARSQL (OS/400 users must create an empty library called STARSQL)

Host Name

Network name or IP address of the DB2 server

Port

446, unless configured differently on the DB2 server
  1. From the Network pane of the StarSQL Data Source Wizard, click the Test Connection button to ensure you can connect to the host database. If the connection fails, verify the data source configuration and/or contact a network administrator if you are uncertain about the connection parameters.
  2. After the data source has been created, open StarAdmin from the StarSQL program group and select Open Database Connection from the File menu. Choose the StarSQL data source and connect with a user ID and password that has authority to create and bind packages in the STARSQL library.
  3. Once connected, click the BIND ALL button to create and bind the StarSQL packages.
  4. Select YES when StarAdmin asks if you wish to grant EXECUTE authority to PUBLIC so that all StarSQL users will have access to the packages.

Additional References

StarSQL Quick Start Guide, StarSQL User's GuideAcrobat PDF document , StarSQL Online Help, StarSQL Technical Document: How to Bind packages with StarAdmin

Step 4: Start the SQL Server Service

Estimated Time: less than 5 minutes

The StarQuest Data Replicator requires a control database in which to store definitions for sources, destinations, subscriptions, and groups of subscriptions. If you do not already have a SQL Server installation, you can obtain the Microsoft SQL Server 2000 Desktop Engine (MSDE 2000) or SQL Server 2005 Express Edition free of charge from Microsoft, as further described in the StarQuest Technical Document "Installing SQL Server for a Control Database."

The SQL Server service must be running for the Replicator Service to access its control database. If you intend to configure replication subscriptions to run according to a schedule, also start the SQL Server Agent service. Start the SQL Server and SQL Server Agent services from Windows Services, typically found under Control Panel—> Administrative Tools—> Services.

NOTE

The SQL Server 2005 Express Edition does not include the SQL Server Agent component, which the Data Replicator Manager uses to schedule subscriptions to run automatically. If you use the free SQL Server 2005 Express Edition as a control database for Data Replicator, you can still use the Data Replicator Manager to run subscriptions manually, and you can use other methods, such as Windows Scheduler, to schedule them to run automatically.

Additional References

StarQuest Technical Document " Installing SQL Server for a Control Database"

Step 5: Install and Configure the StarQuest Data Replicator Software

Estimated Time: 10 minutes

The StarQuest Data Replicator software is distributed as a compressed file that you download from the StarQuest Ventures Web site. See the section Before You Begin if you have not already downloaded the SQDR software.

  1. Run the setup.exe program from the SQDR installer image.
  2. At the end of the installation, with the Run Configuration Wizard option enabled, click Finish.

Configure the Data Replicator Service

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 for the Data Replicator control tables and enter the user ID required to log in to the SQL Server.
    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 Tool—>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.
  1. Select the option Create a New Control Database and click Next.
  2. 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. Click Finish to save the Data Replicator configuration.

Step 6: 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 Product ID of DR and click OK. If you have not performed Step 2: License the StarQuest Products, click Add to display the Add License dialog, enter the license key for SQDR, and click the Add button. Click OK in the License Configuration dialog to save the license keys.

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. Click OK to add 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. Click OK to add 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.

  1. 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.
  2. In the Select Group pane, select optionally to add the subscription to an existing group or to a new group. Click Next to continue.
  3. 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.
  4. 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.

  5. Click Next in the Create Table Statement pane. The CREATE TABLE statement will not appear until the subscription has been saved.
  6. On the Define Processing pane, enter optional host commands to be executed before and/or after the data is replicated. Click Next to continue.
  7. 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.
  8. On the Define Replication Schedule pane, configure a schedule for the subscription or leave the Scheduled box unchecked. Click Next to continue.
  9. Enter a name for the subscription and click Finish.
  10. 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 7: 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 send an email to StarQuest Customer Support at contact@starquest.com or call (+1) 415-669-9619 for assistance.

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.