StarQuest: keeping data in motion

 

StarQuest Technical Documents

Configuring SQDR with an Existing Control Database

Last Update: 9 November 2017
Product: StarQuest Data Replicator
Version: v3.75 and later
Article ID: SQV00DR028

Abstract

The typical procedure for setting up SQDR uses the SQDR Configuration program to create the SQL Server control database and control tables and assumes that the user configuring SQDR has certain privileges to the SQL Server instance.

However, in the cases where company policy restricts SQL Server authorities, the control database and tables can be created by the SQL Server database administrator; SQDR can then be configured to use the existing database.

This technical document describes the steps involved. It assumes that SQDR and SQL Server are running on different machines, though the same principles apply if they are running on the same machine. It also assumes that the SQDR service will be running as a domain user rather than Local System Account, and that Windows authentication will be used for SQL Server access.

Solution

On the domain controller:

  1. Create a domain account for use by the SQDR service e.g. mydomain\sqdrsvc. This user does not need any special authorities other than those granted below.

On the machine running SQDR:

  1. Install the SQL Server Native Client 11 or SQL Server ODBC 13 driver. If you are using a 64-bit operating system, install the 64-bit version of the Native client.
  2. Install SQDR, but do not run the Configuration utility or start the SQDRSVC service yet.
  3. Locate the CNTRLDB.SQL script in Program Files\StarQuest\SQDR and supply it to the SQL Server database administrator.

On the SQL Server machine:

  1. Create a new database named ControlDB (or a name of your choosing).
  2. Specify Use ControlDB and then execute the CNTRLDB.SQL script to create the control tables.
  3. Grant read, write, and database owner authorities to the SQDR service user created above:
    1. Open SQL Server Studio or SQL Server Studio Express and connect to the SQL Server instance.
    2. Under Security/Logins, add the user.
    3. Right-click and select Properties.
    4. Choose User Mappings.
    5. Select the checkbox next to the database ControlDB.
    6. In the lower part of the dialog, there are checkboxes for Database role membership; public is selected by default. Also select db_datareader, db_datawriter, and db_owner.

On the SQDR machine:

  1. Create a SQL Server Native 11 or ODBC 13 system data source and verify connectivity to the SQL Server hosting the control database.
  2. Run the SQDR Configuration utlity to create sqdr.properties in C:\ProgramData\StarQuest\SQDR; this file contains connectivity information. Specify the ODBC data source (or a connection string). When prompted, select "Use existing control tables".
  1. In the Services control panel, modify the StarQuest Data Replicator service to run as the domain user (this grants Logon as Service authority) and verify the service starts. Also confirm that the Logon as Service authority will not revoked by Group Policy.
  2. Start Data Replicator Manager from the SQDR Program Group.

 

 

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.