Our company has 100 franchises organized into 10 zones, and each zone manages 10 branches. Each branch runs a booking system on SQL Server 2000, and every week, each branch must send data to its zone office and the company's head office. What's the best way to ensure that the data is synchronized and includes modifications from each branch?
If you make updates only at the branch level, you can set up a hierarchical topology using transactional replication. Set up each zone database as a central (rollup) subscriber to publications that you create at each branch. Then, republish data from the zone databases into a central subscriber at the head office.
The "Designing a Replication Topology" section in SQL Server Books Online (BOL) provides more information about using transactional replication for this kind of data transfer. If updates go from the head office to the branches as well as from the branches to the head office and could conflict, you might want to use merge replication. Merge replication provides a rich, customizable conflict-management framework.