How to update the database on a central server when changes are made on any one of the child database servers

Question

Suppose 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.

---

Niranjan

Answer 1

Hello Niranjan,

It sounds like you could take a few different approaches:

1. Transactional Replication

http://msdn.microsoft.com/en-us/library/ms151176

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.

http://msdn.microsoft.com/en-us/library/ms166043(v=SQL.90).aspx

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,

Sean

---

Sean Gallardy, MCC | Blog | Twitter