I'm trying to migrate from server Z, which replicates out to a dozen subscribers in two data center, to servers A & B, one in each data center. In order to survive outages between data centers we were looking at doing Merge Replication between A & B.
My initial plan was to take Z and use transactional to go to A, which would then replicate out to B. Then, as we migrate processes to use A, we could roll one process at a time.
However, while I can make updates directly to A or B and have them go to the other server, updates to A via Transaction Replication (Z) do NOT make it to B!
Identities are set as NOT FOR REPLICATION, but the rest of it is pretty much standard.
Good question.
You need to set the Merge article property @published_in_tran_pub to true for all Merge articles participating in the Transactional publication. You can use sp_changemergearticle to do this at Server A:
EXEC sp_changemergearticle
@publication = 'MyPublication',
@article = 'MyArticle',
@property = 'published_in_tran_pub',
@value = 'true';
You can execute this change at will, without generating a new snapshot or reinitializing subscriptions.