StarQuest Technical Documents

SQDR Plus: SQL Server & Change Data Capture (CDC)

Last Update: 31 August 2020
Product: SQDR Plus
Version: 4.50 and later
Article ID: SQV00PL049

Abstract

This technical document contains tips and troubleshooting hints when using SQDR Plus for incremental data replication from a Microsoft SQL Server database when using Change Data Capture (CDC) rather than Change Tracking (CT).

The following technical documents provide additional information:

• Quick Start Guide to Using SQDR Plus v4 on Windows with SQL Server
• SQDR Plus: Tips for SQL Server Data Replication
• SQDR Plus SQL Server User Authorities

Contents:

• Requirements for Change Data Capture (CDC)
• Changes to the Source Database and Tables
• Troubleshooting
• Reverting CDC properties
  
  • Scenario 1 - deleting some but not all subscriptions
    
  • Scenario 2 - disabling CDC for the entire database (deleting the agent)
    
  • Scenario 3 - disabling CDC for the entire database to disable CDC for some but not all tables
• Recovery from accidental disabling of CDC
  
  • Scenario 1 - disabling CDC for a table
    
  • Scenario 2 - disabling CDC for the database
• Increasing size of SQL Server Agent job history log
  

Requirements for 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.

System Requirements:

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),

The SQL Server Agent must be running on the source system.

CDC is available in Azure SQL Managed Instance but not Azure SQL Database.

Table Requirements: All subscribed tables must have primary keys or unique indexes.

User Authorities: The Agent user must have authority to run the following procedures in the source database:

• sys.sp_cdc_enable_db: Requires membership in the sysadmin fixed server role.
• sys.sp_cdc_enable_table: Requires membership in the db_owner fixed database role.
  

Changes to the Source Database and Tables

When you create a CDC agent to a database, SQDR Plus enables CDC for the database (if it is not already enabled) using EXEC sys.sp_cdc_enable_db.

When you create a subscription, SQDR Plus enables CDC for that table using EXEC sys.sp_cdc_enable_table.

The following commands supply information about whether the database is enabled for CDC and which tables are enabled:

select name, is_cdc_enabled from sys.databases where name ='mydb';

EXEC sys.sp_cdc_help_change_data_capture;

select name, is_tracked_by_cdc from sys.tables where is_tracked_by_cdc = 1;

In addition to the tables that you have subscribed to, you will see that SQDR Plus creates an internal subscription to its own SQ_BASELINES control table, and that table will be enabled for CDC.

You can also use SQL Server Management Studio to view whether a table is enabled for CDC by right-clicking on the table name, selecting Properties, and examining the Replication/Table is Replicated value of the General tab.

Troubleshooting

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.

Reverting CDC properties

When you delete a subscription or an SQDR Plus agent, SQDR does not change the CDC settings on the source database, as it has no way of determining whether another application is using CDC. If you no longer require CDC (e.g. you have decided to use Change Tracking instead, or you no longer need to replicate some or all of the subscribed tables), here are some techniques for changing CDC properties for a table or a database.

Scenario 1 - deleting some but not all subscriptions

• In Data Replicator Manager, delete the subscriptions.
• Create a list of tables that no longer need to be enabled for CDC. You can obtain the value for capture_instance by running
  EXEC sys.sp_cdc_help_change_data_capture.
• Run sys.sp_cdc_disable_table for each table:

EXEC sys.sp_cdc_disable_table
@source_schema = N'dbo',
@source_name = N'mytab',
@capture_instance = N'dbo_mytab';

Scenario 2 - disabling CDC for the entire database (deleting the agent)

• In Data Replicator Manager, delete the subscriptions.
• In SQDR Control Center, delete the Agent.
• Disable CDC for the entire database:

EXEC sys.sp_cdc_disable_db;

Scenario 3 - disabling CDC for the entire database to disable CDC for some but not all tables

If many tables are enabled for CDC, but you are now interested in replicating a small subset, you can temporarily disable CDC for the database to clear CDC for all tables. SQDR Plus will re-enable CDC for the few tables of interest:

• Ensure that no other application is using CDC, and that the source tables will not be changing during this process.
• In Data Replicator Manager, delete any subscriptions that no longer of interest.
• In SQDR Control Center, stop the Agent.
• Disable CDC for the database. This also disables CDC for all tables in the database.

USE mydb;
EXEC sys.sp_cdc_disable_db;

• Enable CDC for the database:

EXEC sys.sp_cdc_enable_db;

• In Center Center, stop the Agent and use the menu item Database/Recovery/Set Startup Mode.. to set Startup Mode to Warm Start (Restored).
• Start the Agent. The Agent will automatically enable CDC for the tables of interest.
• Unless you are confident that no changes were made to the source tables during the time that CDC was disabled, run new baselines for the subscribed tables.

See Recovery: Scenario 2 - disabling CDC for the database for details.

Recovery from accidental disabling of CDC

If CDC is unintentionally disabled for a table or for the database, here are the typical behavior and some suggested recovery techniques. This information is current as of SQDR and SQDR Plus 5.19; behavior and recovery recommendations may change in future versions.

Scenario 1 - disabling CDC for a table

If CDC is disabled for a table that SQDR is subscribed to:

• You will stop receiving any incremental changes
• In Data Replicator Manager, the icon for the I/R group and the subscription will indicate a warning (yellow exclamation) with the error message Source table was altered. Use the Merge with Source function in the subscription properties to automatically update column information.
• The following appears in the Agent diagnostics:

MsSqlCdcChangePropagator.tableAltered: Change Data Capture is disabled for dbo.tab1
MsSqlCdcChangePropagator.getAgent:Got reference to capture agent
TargetsTable.resetTargets:
Subscription.subscriptionFailed:tab1

Recovery:

Examine the subscription and click OK. This will recreate the subscription. SQDR Plus will re-enable CDC on the table and a new baseline will be run.

Scenario 2 - disabling CDC for the database

If CDC is disabled for the database, the following symptoms are observed:

• You will stop receiving any incremental changes, but there will be no error indications in Data Replicator Manager.
• In SQL Server Management Studio, property of the subscribed tables will appear as Replication/Table is Replicated = false.
• You will see the following in Control Center:
  • The icon of the Agent will change to a yellow exclamation mark
  • The following messages will appear in the Agent diagnostics. cdc.lsn_time_mapping is a system table that only exists when CDC is enabled for the database.

MsSqlCdcLogReader.next:
com.microsoft.sqlserver.jdbc.SQLServerException: Invalid object name 'cdc.lsn_time_mapping'.

ReplicationWorkerFactory.performWorkerHealthCheck:
com.starquest.sqdr.capture.ReplicationException: Unexpected native error: Invalid object name 'cdc.lsn_time_mapping'.

Recovery:

• Enable CDC for the database. Note that the cdc.* tables will appear under System Tables.

EXEC sys.sp_cdc_enable_db;

The following message now appears in the Agent diagnostics:

com.starquest.sqdr.capture.ReplicationException: Invalid journaling/logging options: Change Data Capture is not active. Is the SQL Server Agent running?

• In Center Center, stop the Agent and use the menu item Database/Recovery/Set Startup Mode.. to set Startup Mode to Warm Start (Restored).
• Start the Agent. The agent should now appear with a green icon.

You may ignore these transient error messages that might appear in Diagnostics immediately after starting the agent:

SubscriptionMonitor.checkForUpdates:Failed to call getchanges procedure, errcode=11. Connection refused to host: 127.0.0.1; nested
java.net.ConnectException: Connection refused: connect
SubscriptionMonitor.run:Error in monitor thread, will retry
com.starquest.sqdr.capture.ReplicationException: Unexpected native error: Connection refused to host: 127.0.0.1; nested exception is:
java.net.ConnectException: Connection refused: connect

In Data Replicator Manager, you may see this temporary error:

Stored procedure SQDR.GETCHANGE3 05.20.20200810 returned error 11. Connection refused to host: 127.0.0.1; nested exception is:
java.net.ConnectException: Connection refused: connect

As mentioned in the symptoms, you will stop receiving any incremental changes, but there will be no error indications in Data Replicator Manager. Unless you are confident that no changes were made to the source tables during the time that CDC was disabled, run new baselines for the subscribed tables.

Increasing size of SQL Server Agent history log

The use of CDC will cause additional entries to be added to the SQL Server Agent history log. If necessary, you can increase the maximum size of the history log in SQL Server Management Studio by selecting properties of SQL Server Agent and then the History page: