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

  • July 20, 2022 at 4:52 am

    #4065681

    I just wanted to add to this another question. I'm getting conflicting information on the web about this topic. Can you have the distributor in an AAG group? For example Server1 (primary) Server2 (sync secondary) and Server3 (async secondary) and have the distributor on Server3

    Thanks,

    Kris

  • July 20, 2022 at 7:39 am

    #4065763

    Yes. You can put the distribution database on whatever server you wish.

    However you need to make sure it is highly available.  If you lose server 3 then your replication stops working.

    Its always a balance of functionality vs availability.

    As your running 2019, and if the replication throughput is small, just put the distribution database in the original AG or on a subscriber.

  • July 20, 2022 at 8:34 am

    #4065801

    Ok. What if I put the distributor on Server2 and the subscriber is Server3

    Thanks,

    Kris

  • July 20, 2022 at 12:05 pm

    #4065887

    It all depends on your setup and what your trying the achieve along with your HA requirements.

    Putting distribution on a standalone machine you introduce a single point of failure, but that may be acceptable for you.

    I would strip your requirements back to absolute basics and see what is actually needed and what the topology should actually be to give you the right HA and DR pieces for what you’re trying to build.

  • August 3, 2022 at 4:36 am

    #4071703

    I've tried to change the server that is the distributor. When I run  the below on the new distributor:

    EXEC sp_adddistributor

    @distributor = 'Server2',

    @password = 'password'

    I get the following error:

    The server 'Server4' is already defined as a Distributor. To reconfigure the server as a Distributor, you must first uninstall the exisiting Distributor. Use the stored procedure sp_dropdistributor, or use the Disable Publishing and Distribution Wizard.

    But when I go to seerver4 and run EXEC sp_dropdistributor @no_checks = 1. I get "The Distributor is not installed."

    Am I running it on the wrong server. Server3 is now meant to be the new distributor and obviously server1 is the publisher.

    Thanks,

    Kris

Viewing 12 posts - 1 through 11 (of 11 total)

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