1) I have 2 node which node 1 is publisher and distributor in same server and subscriber in node 2. In case publisher is down for few hours and i need to bring up the subscriber for read and write by disabling replication, will the transaction and commands in node 1 distribution database will be rolled back or i will lose certain data?
2) I have node 1 is publisher and node 2 have distributor and subscriber, in case publisher is down for few hours and i need to bring up subscriber for read and write. What happen to transaction marked for replication in publisher but not has been sent to distributor, will it be rolled back or i will lose certain data?
Thank you
Best Answer
First, you shouldn't use Replication for High-Availability or Disaster Recovery. It was never designed for that, and you'll see below that is not a very good solution
Instead you should really use AlwaysOn Availibility Groups instead of Replication if you plan on using the solution for High-Availability or Disaster Recovery.
No. The old Publisher/Distributor will come back at the point-in-time it went down.
You will have data in each database that isn't present in the other. You can either discard the data in one or the other, or manually add changes from one to the other, perhaps using the tablediff utility.
The publisher will come back online, and any transactions committed, but not distributed, will be present in the Publisher, but the old Distributor is no longer reading its log file for transactions. Again the previous subscriber now has data not present in the old publisher, and you have to decide what to do.
The typical operational response here is to save a copy of the old publisher, and then to re-create and re-initialize replication using the data from the old subscriber. You do this either by configuring the old subscriber as the new publisher, or by restoring a backup of the old subscriber over the old publisher and re-creating the old replication topology.