Debugging SQL Server CDC for Debezium

You can use Debezium and SQL Server CDC to publish data changes in a reliable and scalable way. Once its a key part of your solution, you’ll need to stay on top of any issues that you encounter.

The connector logs and the Debezium FAQ will help you solve most issues. However some issues don’t show up in the logs.

Changes Not Being Picked up After Initial Load

Before fixing this issue, check if you have lost any changes when CDC was down.

• If you have a fast and easy way to get a snapshot of the source data to the target and compare, this will be the easiest way to check for lost changes.
• If rows in your tracked table have timestamps, you can use it to identify target tables missing changes.

Changes may not be picked up after the initial load due to the following reasons.

• The SQL Server Agent is not running. If the status does not show up in SQL Server Management Studio (as with certain cloud providers), you can query the sys.dm_server_services view.

-- from https://stackoverflow.com/a/30633873
SELECT servicename, status_desc
FROM sys.dm_server_services dss
WHERE servicename LIKE N'SQL Server Agent (%';
-- must be Running