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.

Solution 1

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().

Solution 2

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.

Comments

[Nasser Hadjloo]

Isn't it better to sync 3 databases together first and then sync thatwith the server? or I should sync all of them with server and then use synced version of server and send it's backup for clients?

[JStead]

@Aseem Are these all inserts or do you have updates you have to worry about as well? You are most likely going to run into contention with update where multiple clients updated the same record if it is the case. Then you have to determine the right one. If it is only inserts order does not matter.

[Sam Johnson]

This solution will lose some changes in an unpredictable way - if two users both update the same field while both (or one) is offline, when the sync occurs one of their changes will be overwritten. Use a syncing library such as Microsoft Sync Framework instead of writing your own.