Many to Many replication in sql server 2000

Hi

I'm trying to determine if it is possible to do many to many replication in sql server 2000.

What i basically want is to have n databases share the same basedate (share a common database) and allow updates in any database to be replicated to all the other databases (with a simple conflict resolution, like last update wins).

My goal is total autonomy, without a single point of failure. If any node goes down, the other nodes will continue to work and continue to replicate their data to the remaining nodes. When a node comes back up it will catch up with the over nodes (or get reinitialized if it was a serious crash).

The amount of data i want to replicate is not that big (less than 100MB) and does not change that often. All servers are sql server 2000 instances connected by a gigabit network and the number of nodes involved is less than 10. Some latency is also acceptable.

the question is: is this at all possible? I have read i bit in 'SQL Server High Availability By Paul Bertucci' and some other resources and it looks like a multiple publishers or multiple subscribers with merge replication setup should work, but i'm not too sure if it will work for n > 2 nodes (where all nodes publish and subscribe to each other) and it also mentions constraints on which data a given node is allowed to update (i hope this could be handled by simple conflict resolution).

And if it is not possible in 2000, could it be accomplished en 2005?

TIA Jens

Unfortunately you cannot do peer to peer replication you must have a publisher / subscriber model. You can have a cluster of servers at the center for availablilty in SQL 2005 i believe.

However one server (or cluster of servers) needs to be the master (publisher) and all the others subscribers.

Martin

|||

Martin_McNally wrote:

Unfortunately you cannot do peer to peer replication you must have a publisher / subscriber model. You can have a cluster of servers at the center for availablilty in SQL 2005 i believe.

However one server (or cluster of servers) needs to be the master (publisher) and all the others subscribers.

Martin

This was what i thought, but in the book i mention there is examples of a Multiple Publishers/Multiple Subscribers setups, where each node is both subscriber, publisher and distributer, to quote from the book (full book and figures available here):

Microsoft? SQL Server High Availability By Paul Bertucci wrote:

In the multiple publishers or multiple subscribers scenario, as shown in Figure 7.15, a common table (such as the Customers table) is maintained on every server participating in the scenario. Each server publishes a particular set of rows that pertain to it—usually via filtering on something that identifies that site to the data rows it owns—and subscribes to the rows that all the other servers are publishing. The result is that each server has all the data at all times, and can make changes to its data only. You must be careful when implementing this scenario to ensure that all sites remain synchronized. The most frequently used applications of this configuration are regional order processing systems and reservation tracking systems. When setting up this configuration, make sure that only local users update local data. This check can be implemented through the use of stored procedures, restrictive views, or a check constraint.

But i guess setting up multiple servers as both subscriber, publisher and distributer and letting them all subscribe to each other will cause havoc without the above mentioned constraints.

But i guess i'm not the first person to want this kind of setup and if it was possible i would have found some references somewhere on the net (i just did another search and found this article which describes what i'm looking for, but it is only for 2005 and when looking further into it, it does not support conflict resolution, which i think is required, guess we will have to build it ourselves if we really want it).

Best regards Jens