StarQuest Technical Documents

SQDR Plus: Tips for SQL Server Data Replication

Last Update: 07 December 2022
Product: SQDR Plus
Version: 4.50 and later
Article ID: SQV00PL041

Abstract

This technical document contains tips and troubleshooting hints when using SQDR Plus for incremental data replication from a Microsoft SQL Server database.

The following technical documents provide additional information:

• Quick Start Guide to Using SQDR Plus on Windows with SQL Server
• SQDR Plus: SQL Server & Change Data Capture (CDC)
• SQDR Plus SQL Server User Authorities
• SQDR: SQL Server Performance Tips

Contents:

• Snapshot could not be started
• Change Tracking (CT) vs Change Data Capture (CDC)
• Adding a Primary Key to a Source Table
• Incremental changes not processed (CDC)
• Avoiding Locks for SQL Server Destination
• Re-synch SQL Server logins after restoring a database from backup
• SQL Server Always On (Cluster)
• SQL Server 2008R2 as an incremental source

Snapshot could not be started (SQL Server source)

Symptom: The following error was encountered when creating an incremental subscription from a SQL Server source:

Failed to add incremental subscription at Capture Agent for source table 'dbo.mytable'.
Snapshot could not be started.
Error: Stored procedure SQDR.ADDSUBSCRIPTION 04.78.20161220 returned error 11.
RemoteException occurred in server thread; nested exception is:
java.rmi.RemoteException: Failed to add subscription: DB2 SQL Error: SQLCODE=-206, SQLSTATE=42703, SQLERRMC=MYCOLUMN, DRIVER=3.69.66 SQLSTATE=42703

Solution: The Subscription Wizard panel of Service Properties for SQDR had been configured with Quote SQL Identifiers turned off. This setting must be enabled when creating SQL Server subscriptions. Recreate the subscription after enabling the setting.

 

Change Tracking (CT) vs Change Data Capture (CDC)

SQDR Plus supports incremental replication from SQL Server using either Change Tracking (CT) or Change Data Capture (CDC). This choice is made when creating the SQDR Plus Agent, and can be changed later by modifying the agent configuration. This section explains the advantages and disadvantages of each method. In general, CDC is the recommended choice when available.

System Requirements: CT is available on all editions of SQL Server, including Express. To use CDC, the source system must be running SQL Server Enterprise or Developer Edition (SQL Server 2008R2 or later) or Enterprise, Developer, or Standard Edition (SQL Server 2016sp1 or later), and the SQL Server Agent must be running.

CT is available in both Azure SQL Database and Azure SQL Managed Instance. CDC is available in Azure SQL Managed Instance.

Table requirements: To use Change Tracking, all subscribed tables must have have primary keys. To use Change Data Capture, all subscribed tables must have primary keys or unique indexes.

User Authorities: There are no significant differences in user authorities required to use CT vs CDC; see SQDR Plus SQL Server User Authorities for details.

CDC provides a Before Image, which allows the use of the compareChangeData property, which instructs the Agent to compares before and after images of an update and then minimize staged data. See the Configuration Reference section of the SQDR Control Center Help for details.

Adding a Primary Key to a Source Table

Incremental replication from SQL Server requires that all subscribed tables have primary keys (Change Tracking or Change Data Capture) or unique indexes (Change Data Capture).

If needed, you can add a primary key (data type timestamp) to a source table and include it in the replication:

alter table [dbo].[Table_1] add key1 timestamp not null Primary Key

NOTE: Do not confuse the timestamp data type with the datetime data type. The timestamp data type is an auto-generated binary value used for "row-versioning." It does not actually preserve the date or time and thus is better suited than the datetime data type for incremental replication because it is guaranteed to be a unique value regardless of system date/time changes on the SQL Server system.

Incremental Changes Not Processed (CDC)

Symptom: Incremental changes are not being processed by the agent or applied to the destination, and the status of the incremental group is Synchronizing the baseline(s) (the icon is a green circle with a black dot).

Solution: Confirm that the SQL Server Agent is running on the source system.

Avoiding Locks for SQL Server Destination

When a SQL Server destination is to be used by extract processing that is based upon READ_COMMITTED (as opposed to performing "ad-hoc" querying), or is itself the source for another replication, configure the destination database as follows:

ALTER DATABASE [mydb] SET allow_snapshot_isolation ON
ALTER DATABASE [mydb] SET read_committed_snapshot ON

This reduces deadlock and blocking scenarios in a busy system, and insures reads are consistent in multiple passes over the same data.

This is the default for newly-created Azure SQL databases.

Re-synch SQL Server logins after restoring a database from backup

Symptom:

After restoring a SQL Server destination database from a backup from a different (but identically configured) system, replications are failing with this error from the SQL Server ODBC driver:

The server principal "MySQLUser" is not able to access the database "MySQLDB" under the current security context.

This symptom may also appear for other SQDR uses of SQL Server (as a source or a control database).

Solution:

When a database is restored from a backup (.bak), the user within the database may be out of synch with the login for that instance of SQL Server.

• Ensure the SQL Server login associated with the database user has been added to the instance under Security > Logins.
• Open a new query in SQL Server Management Studio and execute the following command:

use MySQLDB
go
ALTER USER MySQLUser WITH login = MySQLUser
go

The following queries can be run to compare the syslogins table in the master database and the sysusers table within the database. To verify if the SID columns match, use the following query:

use master
go
select * from syslogins where name='MySQLUser'
go

use MySQLDB
go
select * from sysusers where name='MySQLUser'
go

 

SQL Server Always On

Question: Does SQDR support SQL Server Always On?

Answer: StarQuest supports SQL Always On.
No special configuration is required othan than using the proper “listener” port for the cluster.

SQL Server 2008R2 as an incremental source

SQDR Plus 5.22 & later support incremental replication from SQL Server 2012 & later. Existing users of SQL Server 2008R2 may be able to update to new versions of SQDR Plus by using an older version of the SQL Server JDBC driver supplied by Stelo Support.

1. Examine C:\Program Files\StarQuest\sqdrplus\capagent\wrapper\conf\wrapper.conf and identify the line that references mssql-jdbc.jar .e.g

wrapper.java.classpath.5=C:\Program Files\StarQuest\sqdrplus\/capagent/mssql-jdbc.jar

2. Place the older JDBC driver (sqljdbc4.jar) in a location such as C:\ProgramData\StarQuest\sqdrplus\.
3. Create a text file C:\ProgramData\StarQuest\sqdrplus\conf\wrapper-local.conf (/var/sqdrplus/conf/wrapper-local.conf on Linux) containing the following line, which will override the value in wrapper.conf:

wrapper.java.classpath.5=C:\ProgramData\StarQuest\sqdrplus\sqljdbc4.jar

Note: the name wrapper.java.classpath.5 may change in future versions of SQDR Plus.

4. Restart the Capture Agent service.
5. Examine the agent log - the version of the SQL Server JDBC driver will be displayed shortly after the agent starts.

Note that this will allow existing agents for SQL Server 2008r2 to continue to function after updating SQDR Plus. Creating a new agent for SQL Server 2008r2 is not supported.