Trying to determine Distributor setup for publishing a database that is in a Distributed AG

Question

I'm using SQL Server 2019 on Windows 2019. I have a Distributed AG, consisting of 2 underlying AGs, one in each of 2 sites. Each site AG consists of 2 Replicas (primary and secondary). I wish to Publish one of the AG databases so that it can be Replicated to AWS RDS. I'm not sure if the Distribution database can be in the same AG as the database being published, so that it fails over with the published database, or does it have to be on a server that is not part of the AG containing the Published Database? I'm also not sure if the "Distributed AG" versus just a single local AG affect the setup options. I would like to know what steps to take to define it all properly.

Thanks,

Sheila

Answer 1

After re-reading the following info multiple times, I'm pretty sure it is telling me that I cannot set it up the way that I want to.

https://learn.microsoft.com/en-us/sql/relational-databases/replication/configure-distribution-availability-group?view=sql-server-ver15

Limitations or exclusions
Local Distributor (where the Publisher server is also the Distributor) is not supported. The Publisher and Distributor must be separate SQL Server instances. These instances can be hosted on the same sets of nodes. A local Distributor is not supported for the following reasons:
If the Distributor is configured locally, you can't use the availability group listener to route traffic to the Distributor, which causes replication agents to fail after failover.
If a local Distributor is configured and then the Distributor availability group fails over to the original secondary, the Publisher connection to the Distributor changes from local to remote, which causes replication stored procedures and agents to fail.

However, I'm still not sure how to configure the Distributor, even with it being on another SQL Server Instance. The Distributor uses the Listener, but in a "distributed AG", there are 2 listeners.

Answer 2

I can't say that I know anything about this. Nor do I have much practical experience of neither AGs nor Replication. But I can't see why you could not include the distribution database in the AG.

If you put the distribution database on a separate server, and you fail over to the other site, will you have connectivity to the distribution database that is still on Site1? And the chief reason you would fail over is that there have been a major disaster on the first site. So you may have to set up replication again.

If you include distribution in the AG, you need to make sure that you have connectivity to AWS from Site2 as well, but that should not be too difficult.

No matter where you place distribution, you need to cater for all the Agent jobs that Replication creates. Exactly how that is done, I don't know, but if you don't care of the job, Replication will stop working once there is a failover.