Good afternoon,
I am trying to perform a transactional replication in sql server 2016, and I have the following problem.
The scenario is as follows:
A publisher server and a subscriber server, the transactional replication process runs successfully,
the problem comes when, a failover occurs, the application goes from writing from the publisher server to
write to the subscriber server.
My question is, how should the subscriber server be configured so that when a failover of the publishing node occurs, the data written in the subscriber is replicated later to the publisher.
Should I return to the publisher subscriber, and subscriber to the publisher for this process?
Thank you for reading.
I will be attentive to your opinions.
Best Answer
Transactional Replication is not intended as a High Availability/Disaster Recovery solution. There is no client connectivity support for failover, and it's operationally complex.
You should usually use AlwaysOn Availability Groups instead.
Either remove replication and re-create it with the subscriber as the new publisher, or use Bi-Directional Transactional Replication, or Peer-to-Peer Transactional Replication.