CODEWITHSUNDEEP
web-servicessql-server-2005database-replication
Sunil
Sunil

Reputation: 2905

Synchronize changes in SQL Server database with its remote client databases

Which is the best way to synchronize a SQL Server database with its remote client databases? Web services/any other built-in features available in SQL Server?

Application details:- Wpf desktop with SQL Server 2005.

The scenario is client’s main office having stock management database. Same database structure implemented in client’s remote outlets. New stocks are adding in main office database. Whenever a new row is added to head-office database, need to inform all remote client SQL Server databases. And whenever a change happens in remote client outlet, need to send back the changes to main office database.

Thanks in advance.

Upvotes: 2

Views: 2804

Answers (1)

Sunil
Sunil

Reputation: 2905

After a lot of research I ended up with followings
1. Sql Server Replication
2. SymmetricDS
3. Microsoft Sync Framework
4. Customized WCF service.(An Example)

I chose customized WCF service for synchronizing DBs. The steps adopted are mentioned here.

1. Created an Index Table. This table holds the transaction history and signature of client.

2. Created a service layer (class library) which receives and returns table rows as stateless DTO objects. This service layer referencing a Data Access Layer which is responsible for communicating with database.
This service layer & Data Access Layer are referenced by WCF service and windows service.

3. Created one WCF service and hosted it in IIS of web server, This service offers:
a). Download(DTOClass dto)
b). Upload(DTOClass dto) service contracts.

4. Created one Windows service and deployed it in multiple client locations. This service consumes the WCF service hosted in Webserver IIS.

Windows services from client location communicate with WCF service at specific intervals, if a new update available in main server database(this information available in IndexTables), windows service downloads the update (using Download(DTOClass dto) contract) and update local database.

Likewise, if any changes happen in local database, windows service handover the changes to WCF service(using Upload(DTOClass dto) contract). WCF service then update master database.

Since both services accessing Database servers locally, this result in better performance.

Upvotes: 3

Related Questions