Thank you people, specifically Corneliu and Dr Greg, I think that I have decided to use either Microsoft Sync Framework ( http://msdn.microsoft.com/sync) or a “roll my own” solution.
My initial gut is feeling is that with the Microsoft Sync Framework I would be using a steam hammer to crack a nut. Have people used it, did they like it? I need to do a bit more reading on it. Thanks Greg Harris Based on your feedback, I have completed a design for a “roll my own” synchronisation job, this is what I have come up with: 1. Every database table has the additional columns: a. *LastUpdateUtcTime*: UTC time when row is created or updated set value to current UTC time (use SQL Server system function SysUTCDateTime). b. *LastSynchronisationUtcTime*: When row is created, set to '1753-01-01', when row synchronisation is acknowledged, set to current UTC time. c. *OwnerSite*. Which site “owns” the data (see notes section below). 2. Add new SynchronisationManagement table in the remote branch office servers with the synchronisation status of each table which stores: a. TableName b. LastSynchronisationTime 3. Add new SynchronisationHistory table in the head office server with the history of each synchronisation step which stores: a. SiteCode Primary key Code for remote branch office b. TableName Primary key Table being synchronised c. LastSynchronisationTime Primary key Time of synchronisation (UTC) d. RemoteTime Time on the remote server e. UploadCount Number of rows uploaded f. DownLoadCount Number of rows downloaded g. Result Code indicating how far process completed 4. Have a background job running on the head office server that is responsible to keep the database synchronisation up to date. The job of this program is to sit and wait for update requests from the remote sites. Provides functions: a. *GetUtcTime*: Returns current UTC time on the server b. *UpdateTable*: Updates a single row in a single table c. *GetUpdatesForRemoteSite*: Returns updates for remote site since selected time d. *Complete*: Tell head office the branch has finished the update 5. Have a background job running on each of the remote branch office servers that is responsible to keep the database synchronisation up to date. This job will start up every X minutes and: a. Look to see if the head office server is available, If not give up and try again in X minutes b. For each table i. Look to see if any data needs to be uploaded to the head office server (LastUpdatedTime >= LastSynchronisationTime) if found then: 1. Upload row to server 2. On acknowledgement, set LastSynchronisationTime to current UTC time 3. Repeat until no more data to upload or timeout ii. Ask the head office server for any rows to download for this table since last synchronisation iii. Set SynchronisationManagement.LastSynchronisationTime = current UTC time minus 1 minute for the given table. *Notes: * * * *Correct Date/Time Settings*: Assume that each server has the correct date/time set plus or minus five minutes, so the synchronisation has to run ten minutes behind to make sure that the errors in the time settings do not affect the synchronisation. *Head Office Server Available Test*: The test to see if the head office server is available is to call the GetUtcTime function on the head office server. If the request fails, the head office server is not available. This test has to know more than just if the internet is connected, it has to know that the head office server is contactable over the internet and ready to take requests. If the time returned is more than 10 minutes from the current time on the local server, send an error message and abort the process. *Primary Key*: Rows are identified by a GUID primary key. *Foreign Keys*: Foreign Keys are not enforced on the head office server, because the referenced rows may not yet be available in the database. The application has to be able to deal with this. *Row Owner*: Each database row can only be owned by a single site. The row owner is identified by the field OwnerSite. The valid values are codes for each site (including head office) plus “All” in which case the data is owned at the head office and will get propagated out to all sites. It is the job of the application to manage the values in this field. Data rows can only be first created in the database that “owns” the row, data is then migrated out to other databases as needed. All remote branch office server databases will be fully replicated back into the head office database, only rows owned by “All” sites will be propagated back to the remote branch office server databases. This will typically only be reference data. *Synchronisation Health*: The health of the synchronisation process can be reviewed by looking at the data in the SynchronisationHistory table, this will point out any potential problems that need to be reviewed. Will have queries for: · Sites that have not synchronised in the last X minutes, · Sites that have had more than X% failures in the last X minutes. · Sites that have their clocks out by more than X minutes. · Total number of rows transferred in last X minutes. This information will display on a central (head office) management dashboard along with other application data. *Timing*: I expect I will have the job run every five minutes on the remote servers so that they do not have too much data to upload at any one time this way I can say to management that the head office server will have all remote data that is older than 15 minutes so long as the infrastructure is up. *Unit Tests*: My unit tests are going to be a loop back test on the same machine with two databases, the time margin of error settings will be reduced to a few seconds so that the tests can complete in a reasonable time. *SQL Replication and Message Queues*: The reason I rejected SQL Replication and Message Queues is because it is something that has to be setup on the remote servers, if the MVC application is up and running, it is nothing more to have my rest API running out of the same process. If the application goes down, they will quickly tell us/fix it, if the SQL Replication or Message Queues goes down it is not important to the guys in the field. *RavenDB*: Interesting but not appropriate for this application, this fits real well into rows and columns, so no noSQL today. *Open issues*: Deleted rows will need to be propagated, probably just mark rows as deleted and have the synchronisation process do the actual deletion. Will need another column RowStatus. The application at this stage has no plans to delete any rows, so this may not be a problem. Archive: In the future, we may have to deal with how to archive data out of the database and propagate the archive (future problem, worry later).