how to set up replication having an availability group as publisher and the subscriber and distributor on the same server?

Question

I have a situation that is similar to this picture:

2 servers sql1 and sql2 that are in an availability group and a third server that is basically a reporting server.

I need to create a transactional replication, the publisher is in the availability group, the subscriber in the client server.

what I was planning doing is:

1. client is also a distributor
2. the publication lives in a listener so the distributor does not know whether it is sql1 or sql2 that is active
3. in case of a failover, or failback, the replication jobs are active only in the primary node.

Ideally I would like another server or availability group just to be the distributor but that is not possible.

can this potentially work, or there are any bridges I cannot cross in this topology?

after enabling TF 1448 ?

Answer 1

can this potentially work, or there are any bridges I cannot cross in this topology?

Yes, so long as you follow the proper workflow for replication in AGs. I personally wouldn't want to support Replication in AGs, though. You'll also have to following proper management tasks, should the need arise. These setups aren't trivial, make sure you're extremely familiar with the entire process, topology, and handling common issues before using it in production. I have personally found these to not be worth the hassle.

of course after enabling TF 1448.

I would absolutely not enable this if you care about your replication setup and data integrity. It allows for uncommitted transactions to be sent to subscribers which can lead to data incorrectness in the event of a failure. You'll have to go manually fix your data in the distributor/subscriber at that point.