SQL RDS sync using CDC SQL Triggers interfere with sync

0

Hello all, hope you can help. We're hosting a new SQL 2016 RDS instance we'll call this the target DB. There is a 3rd party that is syncing data into the target DB on this instance using AWS's CDC mechanism from another AWS hosted SQL server(Source DB). This has been working successfully to an on-premise SQL server for many years and this new AWS deployment is also successful.

This mechanism is used in both a bulk refresh overnight and incremental changes through the business day when the 3rd Party application interface is used to make changes to the data the content is then synced to the SQL RDS Instance. This is working well.

The issue I've run into, is that I have some triggers to maintain a set of auxiliary tables. The triggers are to be applied to the tables participating in the sync. The Triggers are only enavled for the incremental updates through the day. They are disabled prior to the bulk overnight update and enabled after. These are AFTER INSERT,UPDATE triggers to be used during the day.

When these Triggers are enabled, there are no incremental CDC updates. We interactively disabled the Triggers and the CDC updates resumed and completed successfully.

Testing the Triggers with simple SQL UPDATES or INSERTS work fine but the CDC meachnism is a mystery.

I'm aware that BULK UPDATES don't fire triggers at all but this blocking was unexpected.

Any thoughts? I'm happy to approach this from a completely different angle. This was just my first thought's having used similar mechanisms with SQL Merge Replication for many years.

An Example Trigger. For those familiar with the ESRI world this is using a View as a source to populate a materialized table for inclusion in Map production. Due to the size of the View I can't just perform a bulk TRUNCATE and SELECT INTO on change. I need something nearly as fast as the CDC.

CREATE TRIGGER [dbo].[TRG_ML_label_status_tables]
ON [dbo].[Location]
AFTER INSERT,UPDATE
AS
--Update gis_pt_im_map_auth_label
DELETE from [Test].[SDE].[gis_pt_im_map_auth_label]  
WHERE PNumber IN (
	select m_Location from INSERTED)
INSERT INTO [Test].[SDE].[gis_pt_im_map_auth_label] 
	SELECT VW.PNumber, VW.GISLocation, VW.names, VW.sizeclass, VW.rotation, VW.shape.STCentroid(), VW.OBJECTID
	FROM [Test].[dbo].gis_vw_im_map_auth_label VW
		WHERE PNumber IN (
			select m_Location from INSERTED)

Your advice appreciated. regards Andrew

No Answers

You are not logged in. Log in to post an answer.

A good answer clearly answers the question and provides constructive feedback and encourages professional growth in the question asker.

Guidelines for Answering Questions