Question :
One of my Silverlight applications is running. This Application is using one central database (SQL Server 2008) and three other client databases (SQL Server 2008) in server other locations.
Our requirement is that with minor changing or without changing the Silverlight application,
when we make changes in the central database (insert, update and delete in multiple tables) these changes automatically occur in all client databases. And when any change is made in any client database all client as well as central database should be updated automatically.
For this I read some about Sync Framework and SQL Server 2008 Change Tracking. But I have no idea about how will I do this. Please help me. How can we achieve this?
Answer :
The out-of-the-box solution for this in SQL Server is Peer-to-Peer Transactional Replication. This requires Enterprise edition at all nodes.
If your application truly needs this architecture, I would strongly recommend using the built-in tools rather than trying to patch something together using another technology. Peer-to-Peer replication was designed for this exact scenario.
Having said that, I would recommend that you make sure this architecture is actually necessary, as opposed to having one centralized database. I’m not saying this is the case in your scenario, but sometimes the people in suits can try to dictate a solution like you’re asking for, without fully understanding the technical complexity. Setting up and managing this type of replication can be challenging.
You can refer the below article to understand bit more on it. They have given some sample code as well.
Database Synchronization with the Microsoft Sync Framework
HTH