SQL Server - Basic Availability Groups used for Publisher, Distributor and Subscriber databases?

Question

I see recently support has been added so that the Distributer can be hosted with AlwaysOn AG.

• https://blogs.msdn.microsoft.com/sql_server_team/replication-enhancement-distribution-database-in-availability-group/
• https://learn.microsoft.com/en-us/sql/relational-databases/replication/configure-distribution-availability-group?view=sql-server-2017

Can the Publisher, Distributor and Subscriber databases be hosted in a High Availability configuration using Basic Availability Groups?

Given that basic availability groups only support a single database, if BAG can support this, I assume it would require three sets/clusters of two nodes ~ 2 publishers, 2 distributors and 2 subscribers.

Answer 1

The short answer is Yes.

If you can do it in an AG, and you can do it in SQL Server standard - you should be able to do it in a Basic Availability Group. The only gotcha was the distributor which wasn't able to be on an AG - the Replication couldn't work with the listener right - until the releases described in your links.

So you would basically have three AGs. Each with one DB. The distributor would be in one. The Subscriber in another. The Publisher in another.

You have to have your distributor on a separate SQL Server instance. So this would be mean two instances participating in a Basic Availability Group for the distributor, and then two for the Publisher and, most likely, two for the subscriber. Because of the limitations in the Basic Availability Group.

You could have more than on AG on an instance and have one DB in each and that would be good. but your limit here is 1.) You are doing replication, likely for a reason so your publisher and subscriber would likely be on different instances. 2.) The distributor has to be separate from publisher and subscriber.

Answer 2

I'm reading the documentation and it says that in order to add the Distribution database to an AG you need readable secondaries, which is not possible with Basic AGs. See https://learn.microsoft.com/en-us/sql/relational-databases/replication/configure-distribution-availability-group?view=sql-server-2017#limitations-or-exclusions