Replication SQL Server 2016

Question

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.

Answer 1

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.

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.

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.