SQL Server Transactional Replication Distributor on Basic Availability Group

Question

I am trying to create a transactional replication distributor environment on a 2 node Basic Availability Group (SQL Server 2016 SP2 CU15). The doc configure-distribution-availability-group

states that:
All secondary replicas in a distribution database AG should be readable. If a secondary replica is not readable, distributor properties in SQL Server Management Studio on the particular secondary replica can not be accessed, however replication will continue to work correctly.

When I try to setup the secondary node with the SP_ADDDISTPUBLISHER command I get the following error:

Msg 25036, Level 16, State 1, Procedure sp_adddistpublisher, Line 51 [Batch Start Line 46]
Distribution database needs to be in readable state on secondary replica, when distribution database is part of availability group.

So is what I am trying to achieve actually possible with a basic availability group and if so, how do I get around this ?

Answer 1

Hi @England, SCOTT (Secure Solutions & Services) ,

According to MS document, If a replica is not readable as a secondary, perform failover such that the replica becomes the primary, and run

sp_adddistpublisher @publisher= 'PUB', @distribution_db= 'distribution', @working_directory= '<network path>'  

This is an old thread, check if it could help you.

Best regards,
Cathy

---

If the response is helpful, please click "Accept Answer" and upvote it, thank you.

Answer 2

Basic Availability Groups do not allow the replicas to be readable and also only allow 1 database per availability group, so you won't be able do have the distribution database in an AG that is Basic.

https://learn.microsoft.com/en-us/sql/database-engine/availability-groups/windows/basic-availability-groups-always-on-availability-groups?view=sql-server-ver15#:~:text=A%20basic%20availability%20group%20supports%20a%20failover%20environment,basic%20availability%20groups%20are%20summarized%20in%20this%20document.