StarQuest Technical Documents

Installing SQL Server for a Control Database

Last Update: 16 November 2021
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).

In general, we recommend using Db2 LUW on a combined tier deployment (where SQDR and SQDR Plus are installed on the same machine; the SQDR Plus installer creates a Db2 database named SQDRC for use by SQDR) and SQL Server on a split tier environment. However, there are exceptions; for example, you must use Db2 LUW as the SQDR control database to use the Kakfa Producer.

General considerations

• If you do not already have a SQL Server installation, you can obtain SQL Server Express Edition free of charge from http://www.microsoft.com/downloads to use as a control database for the Data Replicator.
• We recommend installing SQL Server Management Studio (SSMS) as well.
• The SQL Server control database can be either local or remote.

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: Install ODBC Driver 17 for SQL Server

Step 3: 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. You can choose to install SQL Server as a named instance or the default instance. 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. Unless you are planning to use Integrated Security, 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.

Install ODBC Driver 17 for SQL Server

If necessary, install ODBC Driver 17 for SQL Server.

Create a SQL Server ODBC Connection String or Data Source for Use with SQDR

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. Refer to the topic Using Connection Strings (under Working with Sources) in the SQDR help file (drmgr.chm) for examples. Some typical connection strings are:

Server=(local) or Server=. or Server=127.0.0.1
(for a local default instance)

Server=.\SQLEXPRESS (for a local named instance)

Server=MYHOST (for a default instance)

Server=MYHOST\SQLEXPRESS (for a named instance)

Server=MYHOST,<port> (for a default instance listening on a port other than 1433)

Server=MYHOST,<port>\SQLEXPRESS (for a named instance if SQL Browser service is not being used)

Server=MYSERVER;Trusted_Connection=Yes;Database=MyDatabase;Encrypt=Yes; (to use integrated Windows security & specify an SSL connection)

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 64-bit ODBC Administrator from the SQDR program group or Administrative Tasks.

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 ODBC Driver 17 for SQL Server 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.