Combining Peer-to-Peer Transactional Replication with Standard Transactional Replication

Hello,

I'm interested in combining the Peer-to-Peer Transactional Replication and Standard Transactional Replication to provide a scale out solution of SQL Server 2005. The condition is as follows:

We may have 10 SQL Server 2005 (1 Publisher + 9 Subscriber) running transactional replication in the production environment and allow updates in subscribers. To offload the loading of the publisher, we plan to have 2 Publisher (PubNode1 and PubNode2) using Peer-to-Peer Transaction Replication and the rest 8 subscribers will be divided into 2 groups. The subscribers 1-4 (SubNode1, SubNode2, SubNode3, and SubNode4) will be set to be standard transactional replication subscribers of PubNode1, and the rest 4 subscribers (SubNode5, ..., SubNode8) will be set to be standard transactional replication subscribers of PubNode2.

Is it possible to setup above 2 Publisher + 8 Subscriber topology?
Also, could we set the 8 subscribers with updatable subscriptions to achieve each node is updatable?

We do not plan to set all the 10 nodes using Peer-to-Peer Transactional Replication as it is necessary to make sure n*(n-1)/2 (i.e. 45) peer-to-peer connections is reliable. It seems that the maintenance cost is high if the servers are not in a LAN and the topology is very high coupling. So we prefer to divide the 10 nodes into 2 groups and reduce the cost of each node to maintain the connections to all other sites.

That's the scenario.

Any feedback is welcome and appreciated.

Thanks,
Terence

Peer-to-peer would be ideal for your scenario if all your subscribers work primarily on partition data, meaning the chance of two subscribers access the same piece of data is usually pretty low and stable network connection.

If you foresee lots of coflicts situation where different subscribers will access the same data, then you may want to consider merge replication. Merge replication handles conflicts better and does not require dedicated live connection from node to node.

Gary

|||If our network is not stable, we would prefer to use Merge Replication.
However, if there are at least 10 sites for replication, then is each site need to have 9 connections to the other sites?
That's our main concern.
Thanks a lot.|||

I believe the changes are replicated to publisher first then to other subscribers.

http://msdn2.microsoft.com/en-us/library/ms152746.aspx

Therefore, each site only needs to maintain one connection (to the publisher).

Gary