Replication: Many Clients, One Central DB

Replication: Many Clients, One Central DB

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?

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.

Share the Post:
Heading photo, Metadata.

What is Metadata?

What is metadata? Well, It’s an odd concept to wrap your head around. Metadata is essentially the secondary layer of data that tracks details about the “regular” data. The regular

XDR solutions

The Benefits of Using XDR Solutions

Cybercriminals constantly adapt their strategies, developing newer, more powerful, and intelligent ways to attack your network. Since security professionals must innovate as well, more conventional endpoint detection solutions have evolved

AI is revolutionizing fraud detection

How AI is Revolutionizing Fraud Detection

Artificial intelligence – commonly known as AI – means a form of technology with multiple uses. As a result, it has become extremely valuable to a number of businesses across

AI innovation

Companies Leading AI Innovation in 2023

Artificial intelligence (AI) has been transforming industries and revolutionizing business operations. AI’s potential to enhance efficiency and productivity has become crucial to many businesses. As we move into 2023, several

data fivetran pricing

Fivetran Pricing Explained

One of the biggest trends of the 21st century is the massive surge in analytics. Analytics is the process of utilizing data to drive future decision-making. With so much of

kubernetes logging

Kubernetes Logging: What You Need to Know

Kubernetes from Google is one of the most popular open-source and free container management solutions made to make managing and deploying applications easier. It has a solid architecture that makes