Replication Strategy

[Mich]

Hi all,

I have 100 facility databases spread across the country. I've been charged with implementing the following solution:

- Each facility db will be replicated to a master db (warehouse of sorts). All facilities have the same table structure and data will be "merged" into one master table.
- Changes will be made at master.
- Data will be populated back to the facility dbs.

I'm looking for a strategy here. I first thought about merge replication, but I do not want the changes made at the master to be replicated to all dbs. For example, changes made to the data from facility 23 should only replicate to facility 23's db. If I understand correctly, merge replication will update all dbs.

I next thought about seperating the facility db's on the master, but the business requirement dictates they be merged together.

Can a key be added to each facilities db data upon replication then, using that key, direct the replication back to the necessary facility db?

What are your thoughts?

Thanks in advance.

-If it ain't broke, break it and make it better.

 

[JayKusch]

how large are these DBs? In saying that, what kind of data is being kept, how large are the tables, what type of volume of data do you foresee that you will be replicating/pushing each night? MBs, GBs or TBs?

Thanks

J. Kusch

 

[Mich]

They really aren't that big. Probably 2 to 5 GB each, but I won't be replicating all tables. I'm going to say 3GB per night, but if I can use some sort of transactional or merge replication then I'm looking at 50-100MB per db per day being transferred since it would be incremental.

-If it ain't broke, break it and make it better.