Can replication create one master DB out of many? -- sql server replication

Laurence Neville
8/28/2003 5:16:12 AM

I am new to replication and I would appreciate knowing if replication can
handle this business problem before setting up test systems to work out the
details.

We have one customer sales application in many offices worldwide. The
application manages customer registrations into the seminars our company
offers. The seminars are scheduled in our corporate HQ for all offices. All
offices have SQL 2000 SP3 and the database structures are identical. The
offices and corporate HQ are connected via high speed DSL/T1 connections. We
want to solve three problems:

1. In the corporate HQ we have a Schedule database that holds the seminar
schedule. We want inserts and updates to the Seminar table to be pushed out
to all sales databases. There are two complications: (i) the local offices
get to set the Tuition field in the Seminar table so this data must be
preserved as updates to other fields come in from the Schedule DB. Apart
from updating this one field the local offices don't make any other changes
to this table (ii) the Schedule database has a different structure from the
sales database - the only way to create an object with the same structure as
the sales DB's Seminar table would be to create a view (that would join at
least 2 tables that have a 1 to many relationship).

2. In the corporate HQ we want to create a 'World' database that contains
all the data from all the sales databases. The data in the sales databases
could be merged into one database because the primary keys do not conflict.
This data flow is one way - from local sales DBs to World.

3. Lastly, in the corporate HQ we have a 'Model' database. The purpose of
the model database is to maintain a master copy of the data in various
tables that control the business rules for the application. This data is
relatively static and when it changes must be pushed out to all local sales
databases. This data flow is one way - from Model to local sales DB.

What replication methods could be used to handle each of the above?

Thanks

Laurence Neville
8/28/2003 6:43:24 AM

Another thing that I need to know, for problems 1 and 3 below, is how to get
the replication running when the publisher and subscriber databases start
off with data already in the replicated tables. In other words, I would
rather not empty the tables on the subscriber before initializing the
replication (but if I had to I could).

[quoted text, click to view]

sql server replication : Can replication create one master DB out of many?