Post reply

AAG and replication setup

  • March 30, 2022 at 6:00 am

    #4008023

    I have a general question, hopefully I explain it properly.

    I have 4 SQL 2019 servers Server1 is the primary, Server2 is the secondary with AAG synchronously, Server3 is also with AAG in Asynchronous mode and the Server4 is the BI server with replication.

    My question is: I've seen best practice is to have separate servers for the Publication, Distribution and Subscription. Which server would you recommend they sit on. My first thought was publication on Server2, Distribution on Server3 and the Subscriber on Server4.

    Can any one help out with what would be the best practice.

    Thanks,

    Kris

  • March 30, 2022 at 2:48 pm

    #4008266

    So, you have 3 machines in the AOAG, Server 1, 2 & 3.   Server 4 is completely stand alone, not in the AG or anything?

    So the publication will be on Server 1, 2 & 3 as you have to have it on all replicas of the AOAG for when it fails over.

    The Subscriber will be Server 4.

    As for distribution, well that is up to you where to put it.  What is the throughput you are anticipated to put into replication.

    If the load is going to be high, then you may need a Server 5 for distribution, otherwise, you can put distribution on 1,2&3 inside the AOAG, or put it on Server 4.

  • March 30, 2022 at 10:08 pm

    #4008479

    Thanks for that, however I do have a question. If Server3 is only an asynchronous read-only environment, would it not be best to make it the publisher and distributor as it's doing the least amount of work.

    In the event of fail over and the secondary becoming the primary, replication will still work from Server3.

    Thanks,

    Kris

  • March 31, 2022 at 7:08 am

    #4008661

    No, publisher must be the AOAG not an individual replica.

    The log reader technology needs the active log and can only run from the primary replica, it cannot run from a secondary replica.

    https://docs.microsoft.com/en-us/sql/database-engine/availability-groups/windows/configure-replication-for-always-on-availability-groups-sql-server?view=sql-server-ver15

    https://docs.microsoft.com/en-us/sql/database-engine/availability-groups/windows/replicate-track-change-data-capture-always-on-availability?view=sql-server-ver15

    In an availability group, a secondary database cannot be a publisher. Republishing is only supported when transactional replication is combined with Always On availability groups.

    If a published database is a member of an availability group and the publisher is redirected, it must be redirected to an availability group listener name associated with the availability group. It may not be redirected to an explicit node.

     

    It’s the same with CDC as that uses the same technology, you can only read the CDC data from the primary.

  • March 31, 2022 at 5:49 pm

    #4008944

    I have something similar setup, I would recommend to put distributor on it's own set of nodes with AG if you want HA. As @Ant-Green mentioned, replication can read ONLY from primary replica's and you can create publications ONLY from primary replica's since it makes changes to metadata on the database which can happen only on primary.

  • April 4, 2022 at 5:05 am

    #4010281

    Thanks everyone for your help

    Thanks,

    Kris

  • April 5, 2022 at 7:32 am

    #4010879

    Thanks for the great information

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply