Replication with two publishers [duplicate]

Question

I have three servers with own databases, and database schemas are same. Let's call them servers A, B and C.

All three servers will be operational, which means that all three servers will serve for its region.

Data from servers B and C need to be replicated on server A, so that server A will contains data from all servers, but other two servers contain just own data.

I need to implement some replication solution to achieve this goal, and I am asking if it is possible to have two publishers (server B and C) and one server with two subscription (server A)? Is there any better solution? Which replication model will be the best?

Answer 1

You can use Transaction Replication, if you can fulfill its considerations regarding:

• Transaction log space.
• Disk space for the distribution database.
• Primary keys for each published table.
• Triggers.
• Large object (LOB) data types.

Transaction Replication allows data modified at Publisher to be delivered to the Subscriber as they occur (in near real time). The data changes are applied to the Subscriber in the same order and within the same transaction boundaries as they occurred at the Publisher; therefore, within a publication, transactional consistency is guaranteed.

You can also look at this using transaction replication or this using merge replication.

Answer 2

Merge replication supports partitioning data, with each subscriber working on its own set of data.

In this case, A would be the publisher and B and C would be subscribers, using the @@SERVERNAME to match the region.

Merge replication has a considerable overhead (each article has a system trigger to capure changes), so make sure you test carefully.

Transactional replication does not support JOIN filters.