locked
Subscriber(part of AlwaysOn Primary Replica) and three publisher in T-replication RRS feed

  • Question

  • AlwaysOn Availability Group primary replica as a Replication Subscriber, and it is running successfully with one publisher (central subscriber replication topology).
    Subscriber is also work as a publisher for some master table.it means all server work as publisher as well as subscriber.

    Now I want to add second publisher from different data center.
    currently distribution database running on node-3 which is also a publisher server now i am looking to add another publisher server (node-4).publisher-2 is also use same distribution database(node-3)...i am fine with this but my concern is if node-3 is down therefor publisher-2(node-4) is also stopped replicating data.so i want to create distribution database on both server node-3 and node-4.

    Problem- So..Is this right decision to create distribution database on both publisher ?


    Note:-

    node-1 (alwayson primary node and replication subscriber)

    node-2 (secondary replica and report server)

    node-3 (publisher 1)/yocc_pub1 (work as distributor)

    node-4 (publisher 2)/yocc_pub2

    Note- node-1,2,3 are placed in same data centre and node-4(publisher 2) in different data centre.

    listener name - repl_agl

    database - yoccdb

    os-  Window Server 2012 R2

    sql server 2014
    Wednesday, September 6, 2017 1:02 PM

Answers

  • If your publisher server is part of an always on topology you will need to use a remote distributor. From what I read your subscriber server is part of the always on topology.

    In this case if node 3 and node 4 are not part of your always on topology, you can have a local distributor. If your published databases on node 3 and node 4 are part of an AG you will need to use remote distributors and you will be unable to use a local distributor.

    Its not totally clear to me how you have deployed this from your description.

    • Marked as answer by Gulrez Khan Thursday, September 7, 2017 5:51 AM
    Wednesday, September 6, 2017 1:53 PM
    Answerer

All replies

  • If your publisher server is part of an always on topology you will need to use a remote distributor. From what I read your subscriber server is part of the always on topology.

    In this case if node 3 and node 4 are not part of your always on topology, you can have a local distributor. If your published databases on node 3 and node 4 are part of an AG you will need to use remote distributors and you will be unable to use a local distributor.

    Its not totally clear to me how you have deployed this from your description.

    • Marked as answer by Gulrez Khan Thursday, September 7, 2017 5:51 AM
    Wednesday, September 6, 2017 1:53 PM
    Answerer
  • If your publisher server is part of an always on topology you will need to use a remote distributor. From what I read your subscriber server is part of the always on topology.

    In this case if node 3 and node 4 are not part of your always on topology, you can have a local distributor. If your published databases on node 3 and node 4 are part of an AG you will need to use remote distributors and you will be unable to use a local distributor.

    Its not totally clear to me how you have deployed this from your description.

    I add two link which is exactly i am deployed...i merged both topology because i want to replicate database 

       node-3(publisher)    to    node-1(subscriber)

       node-1(publisher)    to    node-3(subscriber)

    Actually i categorised my database in two part first one is behave like master table that is replicating same row to all publisher(use no. 1 model as below).

    second model is some of table is bahave detail table with composite primary key(horizontal partitioned) passing serverid=1,2,3...(use no.2 model as below).

    1. Replication on a database that is part of an AlwaysOn Availability Group...

    https://blogs.msdn.microsoft.com/alwaysonpro/2014/01/30/setting-up-replication-on-a-database-that-is-part-of-an-alwayson-availability-group/

    2. Central Subscriber Model (multiple publishers replicating to one subscriber which is part of AG)

    http://www.sqlrepl.com/sql-server/central-subscriber-model-explained/

    Note- Distribution database should not reside on the servers that are part of AlwaysOn Availability...

    hope this time you will understand my problem...i always follow you for replication problem



    • Edited by Gulrez Khan Thursday, September 7, 2017 6:07 AM
    Thursday, September 7, 2017 5:52 AM