3

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.

Brandon Williams
  • 3,695
  • 16
  • 19
mbourgon
  • 1,286
  • 2
  • 17
  • 35
  • 1
    Why would you go transactional between Z and A, then merge between A and B? You could set a merge replication scheme Z->A->B and then just get rid of Z. – Philippe Grondier Sep 17 '14 at 06:01
  • Interesting idea, @PhilippeGrondier. Hadn't considered it, mostly because I want to make sure everything is rock-solid first, and don't want any work to accidentally go "upstream". But that's an interesting idea. – mbourgon Sep 17 '14 at 13:39
  • merge replication is indeed 'rock solid', and transactional replication makes sense only in limited cases. – Philippe Grondier Sep 17 '14 at 15:01

1 Answers1

2

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.

Brandon Williams
  • 3,695
  • 16
  • 19