SQL Transactional Replication - identify transaction
Transactional replication is configured and running:
SRC/DIST/TGT: SQL Server 2017 14.0.3281.6
SRC - DIST - TGT
TGT - DIST - SRC
1) I have configured transaction replication (uni-dir) for tableA SRC->DIST->TGT. Also configured another trans replication for same table "tableA" from TGT-DIST-SRC. In other words, this is bi-directional replication for tableA using trans replication technique. When I insert row for tableA in SRC, it is replicating back to SRC because log reader on TGT doesn't differentiate between user-trans & dist-agent-trans. This is causing infinite looping. We cannot use merge replication for tableA due to some issues. My suggestion would be dist agent job can run txns with specific name (MSREPLTXNAME) and when log reader for the same database picks up the txn from log file, it can read the txn name (MSREPLTXNAME) and gives user the option to skip it or read it.
2) We have another requirement where log reader should skip extracting the transactions run by Data-OPS team. Data-OPS make some data changes that dont want to get replicated to target server. This change can be considered as extension of point no. (1)