StarQuest Technical Documents

Troubleshooting SQDR Installation and Setup Issues

Last Update: 6 February 2015
Product: StarQuest Data Replicator
Version: v4.50 and later
Article ID: SQV00DR026

Abstract

This technical document describes some of the issues that may be encountered when installing or performing initial configuration tasks with the StarQuest Data Replicator.

The following topics are covered:

• Errors Creating or Updating the Control Database
• Removal of pre-3.75 SQL Server Agent Jobs
• Issues When Starting the SQDR Service
• Issues When Starting the SQDR Service (Windows 2008)
• Issues connecting to Source or Destination using connection strings
• Data Replicator Configuration requires .NET Framework (SQDR 4.1x & earlier)
• Using a Remote SQL Server for the Control Database requires SMO (SQDR 4.1x & earlier)
• Service Locale and Windows XP/Windows Server 2003 (SQDR 4.1x & earlier)

Errors Creating or Updating the Control Database

If you encounter an error creating or updating the control database with Data Replicator Configuration, here are some suggestions:

Examine sqdrver table

To verify that the control tables were successfully created or updated, check the contents of the table sqdrver (e.g. ControlDB.dbo.sqdrver). The version number should match the contents of the cntrldb.sql and updatedb.sql scripts located in the SQDR program directory.

Note that if the Data Replicator Service encounters a version mismatch, it will not start and it will log an error in the Application Event Log (Event ID: 2011- controlDB version mismatch - run config).

Conversion of pre-3.76 Scheduled Subscriptions and Groups

Prior to SQDR 3.76, using Data Replication Manager to schedule subscriptions and groups required SQL Server Agent.

In SQDR 3.76 and later, SQL Server Agent is no longer used for scheduling. When you run Data Replicator Configuration after updating to 3.76 and later from a pre-3.76 installation, you will be prompted with the option to convert scheduled jobs. After conversion, we recommend that you examine your scheduled subscriptions and groups to confirm that the conversion was successful.

 

Issues When Starting the SQDR Service

If the SQDR service fails to start, check the Application and System Event Logs for error messages. Some common errors are:

Application Event: SQDRSVC Error ID 3039: Failed to access SQDRSVC Registry

The service user must have read/write access to the registry key HKEY_LOCAL_MACHINE\SOFTWARE\StarQuest\SQDRSVC.

System Event: Service Control Manager Error 7000: The SQDRSVC service was unable to log on as <USER> with the currently configured password due to the following error:
Logon failure: the user has not been granted the requested logon type at this computer.

To verify whether a specific user has Logon as a Service privileges, open the Group Policy Object Editor for the Local Computer in MMC:

1. In the Run dialog box, type mmc, and then click OK. The Microsoft Management Console appears.
2. On the File menu, click Add/Remove Snap-in.
3. On the Standalone tab, click Add.
4. In the Available Standalone Snap-ins list box, click Group Policy Object Editor, and then click Add.
5. In the Select Group Policy Object dialog box, in Group Policy Object, select Local Computer, and then click Finish.
   
6. Click Close, and then click OK.
7. In the console tree, expand Computer Configuration, expand Windows Settings, expand Security Settings, expand Local Policies, and then click User Rights Assignment.
8. In the details panel, Right-click and examine the Properties for Log on as a service and Deny log on as a service.
9. Add the service user to Log on as a service if necessary.

Application Event: SQDRSVC Error ID 1010 or 2012: Access to Control Database

Application Event: SQDRSVC Error ID 1010: ODBC message: SQLSTATE 42000, native error 229, [Microsoft][SQL Server Native Client 11.0][SQL Server]The SELECT permission was denied on the object 'sqdrver', database 'ControlDB', schema 'dbo'.

Application Event: SQDRSVC Error ID 1010: ODBC message: SQLSTATE 08004, native error 916, [Microsoft][SQL Server Native Client 11.0][SQL Server]The server principal "NT AUTHORITY\SYSTEM" is not able to access the database "ControlDB" under the current security context.

Application Event: SQDRSVC Error ID 2012: Initialization of incremental support failed (retrying). Error: ODBC message: SQLSTATE 42000, native error 229, [Microsoft][SQL Server Native Client 11.0][SQL Server]The INSERT permission was denied on the object 'statistic', database 'ControlDB', schema 'dbo'.

The above errors indicate that the service user does not sufficient authority to access the control database. This situation is more likely to occur when you are using a SQL Server ODBC data source configured for integrated security. In this case, the control database is created or updated using the Data Replicator Configuration utility by one user (a foreground user who is installing and setting up SQDR) but the service is run under the authority of another user. If you accept the default of "Logon as System Account", then the service is running as NT AUTHORITY\SYSTEM.

Follow these steps to grant read/write access to the service user:

1. Open SQL Server Studio or SQL Server Studio Express and connect to the SQL Server instance.
2. Under Security/Logins, select the user (e.g. NT AUTHORITY\SYSTEM). You may need to add it if it is not already listed.
3. Right-click and select Properties.
4. Choose User Mappings.
5. Select the checkbox next to the database (e.g. ControlDB)
6. in the lower part of the dialog, there are checkboxes for Database role membership. Public is selected by default.
7. For read/write access to the database, select db_datareader & db_datawriter.
8. If you plan to use a SQL Server ODBC DSN configured for integrated security as an SQDR destination, also select db_ddladmin (for authority to create new tables).

Issues When Starting the SQDR Service (Windows 2008)

The following has been observed only when running the SQDR Service on Windows Server 2008; it does not appear to be an issue on earlier (e.g. Windows Server 2003) or later (e.g. Windows Server 2008R2) operating systems.

Application Event: Application Error ID 1000
Faulting application sqdrsvc.exe, version 3.76.4.13, time stamp 0x4f88a727, faulting module unknown, version 0.0.0.0, time stamp 0x00000000, exception code 0xc0000005, fault offset 0x00000000, process id 0x454, application start time 0x01cd454da2c9844c.

System Event: System Service Control Manager Event ID 7009
A timeout was reached (30000 milliseconds) while waiting for the StarQuest Data Replicator service to connect.

System Event: System Service Control Manager Event ID 7000
The StarQuest Data Replicator service failed to start due to the following error:
The service did not respond to the start or control request in a timely fashion.

This error occurs when the service user lacks sufficient authority to start the service.

Solution:

Run the service as the built-in local administrator account.

OR

Create a local user account, designate it as a member of Distributed COM group and "run as a service", grant it full access to SQDRSVC registry key ( HKEY_LOCAL_MACHINE\SOFTWARE\StarQuest\SQDRSVC\), and logon at least once as that user.

Issues connecting to Source or Destination using connection strings

Trusted_Connection=Yes:

If you are using Integrated Security when connecting to SQL Server with a connection string, use the parameter Trusted_Connection=Yes. The SQDR Help file incorrectly shows this as TrustedConnection or UseTrustedConnection.

Modifications not recognized immediate:

There is a known problem with modifications to source or destination objects that use connection string. When you make a change to the connection string and click OK, the change is not recognized immediately and you may get a failure message.

Solution: create a new source or destination object with the correct connection string, or open the source or destination object again and click OK; the change will be recognized this time.

Confirm that SQL Server is configured correctly for TCP/IP:

If a connection to SQL Server fails with the error Could not open a connection to SQL Server, verify that SQL Server is configured properly for TCP/IP communication. In SQL Server Configuration Manager, confirm that TCP/IP protocol is enabled, and that the IP addresses displayed on the IP Addresses panel of TCP/IP properties are correct (especially if the SQL Server machine is configured with DHCP or has otherwise experienced a change of IP address). You may need to start the SQL Server Browser service on the SQL Server machine if you are using a named instance or have more than one instance of SQL Server installed.

SQDR 4.1x and Earlier

The following items apply only to SQDR 4.1x and earlier.

Data Replicator Configuration requires .NET Framework 3.5 (SQDR 4.1x & earlier)

If the Data Replicator Configuration utility (sqdrconf.exe) fails to run with the error "The application failed to initialize properly (0xc0000135)", be sure that .NET Framework 2.0 or later is installed (3.5sp1 recommended). This error is likely to only occur when using SQL Server 2000 as a control database, as .NET Framework is part of the installation of SQL Server 2005 and later.

If the Data Replicator Configuration utility (sqdrconf.exe) fails silently on Windows Server 2012 or Windows 8, enable .NET Framework 3.5.

On Windows Server 2012:

1. Start Server Manager
   
2. Select Local Server
   
3. Go to Roles and Features (at bottom of the window)
   
4. Click TASKS and select Add Roles and Features
   
5. Click through several screens of the Add Roles and Features Wizard
   
6. On the Features screen, select .NET Framework 3.5 Features

On Windows 8:

1. Go to Settings
   
2. Choose Control Panel then choose Programs
   
3. Click Turn Windows features on or off
   
4. Select .NET Framework 3.5 (include .NET 2.0 and 3.0) and click OK

Install the .NET Framework 3.5 before installing any Windows language packs. You will need either an Internet connection or a Windows DVD or mount ISO image.

Using a Remote SQL Server for the Control Database requires SMO (SQDR 4.1x & earlier)

When using a remote SQL Server for the control database, the Data Replicator Configuration program must use SQL-SMO (SQL Server Management Objects) to create and update control databases. See the useSMO registry entry section below for requirements.

useSMO registry entry (SQDR 4.1x & earlier)

The Data Replicator Configuration program can use either SQL-SMO (SQL Server Management Objects) or the osql utility to create and update control databases. Using SQL-SMO is preferable, as it supplies better error feedback, it can be used in scenarios involving a remote SQL Server instance when SQL Server is not installed locally, and it can be used to convert existing schedules (based on SQL Server Agent jobs) when upgrading from a version of SQDR prior to 3.76.

The requirements for using SQL-SMO are either SQL Server 2008 or later, or a system with the following components from the SQL Server 2008R2 (sp2 or later recommended) Feature Pack.

• Microsoft® Core XML Services (MSXML) 6.0 SP 1 (not required on newer systems such as Windows 2012)
  
• Microsoft® System CLR Types for SQL Server® 2008 R2
  
• Microsoft® SQL Server® 2008 R2 Shared Management Objects
  
• Microsoft® SQL Server® 2008 R2 Native Client
  

Note that these components can be installed on a system with an existing installation of an earlier version of SQL Server (SQL Server 2000 or 2005), or on a system with no version of SQL Server at all. They can be used to connect to any version of SQL Server (2000 through 2012).

When using SQDR on a 64-bit Windows system, install the 64-bit version of the Native Client and the 32-bit versions of CLR Types and Shared Management Objects.

If the remote SQL Server is running SQL Server 2012, you can install the SQL Server 2012 (sp1 or later recommended) version in place of the 2008R2 Native Client.

During installation, the SQDR installer checks whether the system meets the requirements for using SQL-SMO and if so, sets the contents of the registry value HKEY_LOCAL_MACHINE\SOFTWARE\StarQuest\SQDRSVC\useSMO to 1. This registry setting instructs the Data Replicator Configuration program to use SQL-SMO when creating and updating the control database. You will need to set this registry value with regedit if you install the above pre-requisities after installing SQDR.

If you encounter an error when the Data Replicator Configuration program is using SQL-SMO, verify that the server name stored in the SQL Server instance is correct. If you have changed the Windows system name after installing SQL Server, failing to update this value may cause problems using SQL-SMO, even though SQL Server may appear to operate correctly otherwise.

Use the following SQL to determine the internal server name:

select @@servername

The result will be something like:

SYSNAME\INSTANCE

If the value is incorrect, use the following SQL to update it, and then restart SQL Server.

sp_dropserver 'OLDSYSNAME\INSTANCE'
GO
sp_addserver 'NEWSYSNAME\INSTANCE', local
GO

If you continue to have problems using SQL-SMO, here are some alternative methods of creating and updating the control database:

• Set the registry value HKEY_LOCAL_MACHINE\SOFTWARE\StarQuest\SQDRSVC\useSMO to 0 and restart the Data Replicator Configuration program. It will now use the osql utility instead of SQL-SMO. Note that this method only works if the osql utility (supplied with SQL Server) is present on your machine. To verify that osql is present and in your PATH, type "osql" at a Windows command prompt.
• Using SQL Server Management Studio, open the file (CNTLDB.SQL or UPDATEDB.SQL). If necessary add: "use database <ControlDB>; GO" at the top, and then execute the query.

Service Locale and Windows XP/Windows Server 2003 (SQR 4.1x & earlier)

If you select a locale in Data Replication Configuration, you may see the following warning in the Application Event log when running the SQDR service on Windows XP or Windows Server 2003:

setlocale(English (United States)) Failed. The value specified by the registry key SQDRSVC\svcLocale is not valid, locale is unchanged. Run Data Replicator Configuration to set service locale.

This warning occurs because the locale names being used are only valid for Windows Vista and later. The warning is displayed if Windows event logging (in Service Properties) is set to Expanded warning logging or Maximum level of warning and information logging.

Solution: Use regedit to open the registry key HKEY_LOCAL_MACHINE\SOFTWARE\StarQuest\SQDRSVC and modify the REG_SZ value svcLocale to an acceptable value. For instance, you may substitute English_United States.1252 in place of English (United States).

If the configured locale is valid, you should see this informational message in the Application Event log:

Event Type: Information
Event Source: SQDRSVC
Event Category: Notification
Event ID: 3037

setlocale Succeeded. The value specified by the registry key SQDRSVC\svcLocale is valid, locale is changed. New value is (English_United States.1252).