How to update the database on a central server when changes are made on any one of the child database servers
22 Agustus 2012 14:12Suppose we have 4 different zone servers connected to one centralized server. Each zone servers are having the same table names with same table structure(column names) but data is unique. And the same table names with the same table structure is present in the centralized server. When ever there is any update or new data is inserted that needs to be synchronized in the centralize Server. That means all the zonal server data should be present in the centralized server. And this should happen only at the schedule time. Please suggest some ways to do this.
22 Agustus 2012 15:01Penjawab Pertanyaan
It sounds like you could take a few different approaches:
1. Transactional Replication
2. SSIS packages to copy data (this would be your scheduled time to update)
3. Service Broker
Queue the changes through service broker and at specific times, turn the queue on so that it can be processed or let the information rickle down throughout the day. This would probably be the most resource intensive to create.
4. Distributed Views
Have the linked servers on your central server to all of your zone servers. Create a view with check constraints for each type of zone, create teh view using union all between all of zone tables.
Hope this helps,