1

I have setup SQL Server transactional replication to run continuously for two tables (Parent and child records). At the subscriber end, I have insert and update triggers on both replicated tables. These trigger all have the same code which queries the records in the replicated tables and modifies records in other tables of the subscriber db.

My question is..Will these triggers run concurrently? My fear is that a trigger in one table will interrupt the processing work done by the trigger in the other table

MakkyNZ
  • 2,215
  • 5
  • 33
  • 53

1 Answers1

1

The triggers will run in the context of the replication agent applying the updates. The agent uses a configurable number of connections:

-SubscriptionStreams [0|1|2|...64]

Is the number of connections allowed per Distribution Agent to apply batches of changes in parallel to a Subscriber, while maintaining many of the transactional characteristics present when using a single thread. For a SQL Server Publisher, a range of values from 1 to 64 is supported. This parameter is only supported when the Publisher and Distributor are running on SQL Server 2005 or later versions. This parameter is not supported or must be 0 for non-SQL Server Subscribers or peer-to-peer subscriptions.

Since transaction semantics are properly maintained by the replication agent, if your updates have correct transaction semantics then they cannot conflict on the subscriber (since they did not conflict on the publisher). If they do, then is a problem with the design of your transaction boundaries and you need to solve accordingly, by going back to the drawing board and designing your application with correct transaction semantics.

Remus Rusanu
  • 288,378
  • 40
  • 442
  • 569