Question:
Being new to SQL Server 7.0 I wanted to get other opinions on this:

My company has many clients using a proprietary db schema that will replicate transactions to a central db. The central db will then be used to route these transactions to their appropriate destination.

Now, incoming transactions are placed in the central db and the clients would have to obtain their appropriate records.We are using horizontal partitioning of a single table to handle all clients.The plan is to make the central db a subscriber to the clients which would be publishers/distributors. Clients would be performing a push but they need to obtain any new data.

Could they accomplish this via stored procedure? Do I have to make the central db a publisher/distributor as well as a subscriber?Do the clients have to be subscribers also?

Answer:
I think that you need to map the ownership, the quantity and flow of the data a little more precisely.

Bidirectional replication schemes are nightmarish to maintain, so if there’s a way to establish ownership of the data, that may reveal fewer round trips to be mediated. Are the clients changing the data?

I’m also confused over what you are calling the central db because in one sense I hear you referring to it as a subscriber, and in another as a publisher/distributer. Who owns the data?Who wins on contention?

On March 15th, 2000, Anne M. Watelet responded with these comments:

I thought that the Pro could have mentioned the use of merge replication to possibly resolve the question.

We are in a similar situation, in which we have been using bi-directional replication for 3 years (with stored procs) and it is hard to maintain. The only reason we have not gone to merge on a central db is that we do not want to be reliant on a central data flow place due to our geographic nature. I think, though, from the question, that this is what the person may be looking for.