StarQuest Technical Documents

Installing SQL Server for a Control Database

Last Update: 26 January 2015
Product: StarQuest Data Replicator
Version: 4.50 & later
Article ID: SQV00DR002

Abstract

The StarQuest Data Replicator requires a control database in which to store definitions for sources, destinations, subscriptions, and groups of subscriptions. You can use any of the versions of SQL Server listed in System Requirements for the Data Replicator control database. You can use also use DB2 for Linux, UNIX, and Windows (DB2 for LUW).

General considerations

  • If you do not already have a SQL Server installation, you can obtain SQL Server 2008/2008R2/2012/2014 Express Edition free of charge from http://www.microsoft.com/downloads to use as a control database for the Data Replicator.

Solution

Following are some guidelines for Data Replicator customers who do not already have any edition of SQL Server installed and want to install SQL Server Express for use as a control database. Refer to the information that Microsoft provides to ensure that your hardware and software meets the minimum requirements and for the detailed installation instructions.

This document also describes how to create a SQL Server ODBC data source for use with the Data Replicator.

Step 1:Install SQL Server Express Edition.

Step 2: Create a SQL Server ODBC Data Source for Use with SQDR.

Installing SQL Server Express

  1. Log on to the computer with a user account that has local administrative permissions.
  2. Download SQL Server Express and then double-click the downloaded file to start the installation process.
  3. After the files have been extracted, accept the license agreement and click Next.
  4. Install the prerequisite components and click Next.
  5. On the welcome dialog, click Next to start the installation.
  6. After the system configuration check completes, click Next.
  7. Enter registration information and de-select (uncheck) the Hide advanced configuration options box. Click Next.
  8. On the Feature Selection dialog, expand the Client Components and choose to install the Connectivity Components. Click Next.
  9. On the Instance Name dialog, check the Named instance radio button and enter SQDR in the Named instance field. Click Next.
  10. If you are presented with the Existing components dialog, review and click Next. Otherwise, proceed to the next step.
  11. Specify the Windows service account to use for the SQL Server service and click Next.
  12. Choose the Mixed Mode authentication and enter a strong sa password. Make a note of this password as it will be required during the Data Replicator Configuration process.
  13. On the Collation Settings dialog, select the Collation designator and sort order radio button and choose the Latin1_General from the drop down list. Tick the case-sensitive checkbox and click Next. (To install SQL Server Express on a non-English system, please consult the SQL Server Books Online for the correct collation value for your environment.)
  14. Select the options on the Configuration Options dialog as desired and click Next. Repeat for the Error and Usage Report Settings dialog.
  15. Click Install and click Finish once the installation is complete.

For more information on installing SQL Server Express Edition, Refer to the "How to: Install SQL Server Express" topic in the SQL Server Books Online or MSDN website.

Verifying that SQL Server Express Is Installed and Start the Service

  1. Right-click the My Computer icon and select Manage.
  2. Double-click Services and Applications.
  3. Double-click Services.
  4. Locate the SQL Server service and start it if it is not already running.

Create a SQL Server ODBC Data Source for Use with SQDR

Beginning with SQDR 4.50, we recommend using a connection string to define a connection to the SQL Server database that the Data Replicator Service will use to access its control database.

Alternatively, you can define an ODBC System DSN. If the same SQL Server server will also serve as the destination database server, we recommend creating a separate ODBC System DSN to use as the SQDR Destination.

  1. Launch the ODBC Administrator from the SQDR program group. On SQDR 4.1x and earlier, this will ensure that you are using the 32-bit ODBC Administrator even on a 64-bit system, since SQDR 4.1x is a 32-bit application and uses only 32-bit ODBC data sources. SQDR 4.5 and later is available as a 64-bit application, and uses 64-bit ODBC DSN's.
  1. Click the System DSN tab of the ODBC Data Source Administrator window.
  2. To create a new data source for the SQL Server database, click Add. In the next dialog, select SQL Server Native Client 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.
  3. Specify a name and select which SQL Server you want the data source to connect to, and click Next to proceed.
  4. 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.

DISCLAIMER

The information in technical documents comes without any warranty or applicability for a specific purpose. 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 instructions may need to be modified to be appropriate for the hardware and software that has been installed and configured within a particular organization.  The information in technical documents should be considered only as an example and may include information from various sources, including IBM, Microsoft, and other organizations.