How to setup a SQL Server Replication between three servers in a typical scenario..

  • Article

Three Servers (Top, middle and Bottom) Data will flow from Top to the Bottom Server.
Top to Middle Server (one way Replication) and Middle to Last Server (two ways Replication).

=================================================

                                                ---->
Top Server----> Middle Server Last Server

                                                 <----

==================================================

In order to get this replication topology done, we can use either of below ways:

A.) One way Trans Repl between Top and Middle Server + Updatable Subscriptions (again Trans) between Middle and Bottom Server.

B.) One way Trans Repl between Top and Middle Server + Merge between the Middle and the Bottom Server.

C.) Merge between the Top and Middle Server (with -ExchangeType 2) + Normal Merge between the Middle and the last Server.

The A and B are simple methods therefore we will discuss the third method here.

About ExchangeType [1|2|3]: Specify the type of exchange. A value of 1 indicates a push exchange. A value of 2 indicates a pull exchange. A value of 3 indicates a bidirectional exchange (default).

Please note that before implementing this, we need to understand as to what we are doing and what will be the end goal?

If the changes from Middle Server are not Replicating to the Top Server, we should be aware that in case we need to re-initialize the Middle Server, we do not drop the tables at the Middle Server but choose the option of manual sync so that we do not lose the Data that is on the Middle Server but not on the Top Server.

In short this is very much possible but again we need to understand the topology we are implementing (including any conflicts that can occur at various sites) and what could be the results in case of re-initialization. Thus, we would recommend an in-depth test in a test environment before implementing this in production.

References,
How to manually synchronize replication subscriptions by using backup or restore

https://support.microsoft.com/default.aspx?scid=kb;EN-US;320499

Raman Sharma

SE, Microsoft SQL Server