How to sync online & offline databases

Question

I have a web application which provide some information for my customers. I have another version (windows) that exactly work same as web application.

This is because Web connection may lost for some hours and in this time user is going to use the application.

I'm wonder how to sync these SQL Server databases.

Note that web application is using from 3 different cities and all of them have a windows based application too. What should I do?

NoteL windows verision is exactly web application which installed in the Local Web Server in 3 different cities and users have access to them via their LAN.

Answer 1

Your choices could be the use of a database backup to synchronize (probably pretty slow and impractical). After that you must use ETL. Pick your favorite tool. You could use either sql server CDC or I would recommend Change tracking to identify your changes and load just those. Then use merge to synchronize your changes. Granted these solutions will require you to set up linked servers or use a third party to temporarily hold the dml changes.

http://technet.microsoft.com/en-us/library/bb510625.aspx

http://msdn.microsoft.com/en-us/library/cc305322.aspx

I thought I would add one non microsoft solution http://www.red-gate.com/products/sql-development/sql-data-compare/ its not free but does exactly what you need.

Answer 2

All updates in data to from/to web/windows would originate from the windows application. But the problem is that the windows app will run when there is no internet connection.

So you will have to use a windows service which will call a webservice for local and remote database updates. The windows can wake up every x mins and update the remote and local databases.

The webservice will have two methods:

GetData(DateTime getRecordsFromThisDate) - Windows service should call this on regular intervals and update the local database.

UploadData(dataRows/collection) -  Windows service should call this on regular intervals and update the remote database.

Each record in database will have a timestamp. For local update, get the largest timestamp and send it as parameter to GetData(). The webservice will return the records created after this time.

For upload data, you will have to store the last time when an successful upload operation was run. Get the records(inserted and updated) after this time and send them to UploadData().