Unanswered: Can replication create one master DB out of many?
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?
Another thing that I need to know, for problems 1 and 3, 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).