StarQuest Technical Documents

SQDR Plus: Tips for SQL Server Data Replication

Last Update: 18 July 2019
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:

Contents:

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.

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.


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.