Greg, Looks ok to me. In step 2 I would change to LastDownloadTime and LastUploadTime so you can run the two steps separately if you need to and keep separate times. Remember, the server is always passive, it should never care what a client has or did except for statistical or reporting purposes. That means the client can simply decide to "force a full sync" in which you set your LastDownloadTime back to null and pull the complete table again. Also remember to store the times as they were when you started the sync to avoid losing data changed during the the moving window of the sync.
I would also avoid the Head Office Server Available Test check. Use the network availability to see if you have internet without pinging the network and when you have network just hit the server and try. Best case: you get data, worst case: you fail and try again in 10 minutes. Your check is useless if you do the check and that works and server goes offline 5 seconds later :) After every sync step the client will have to update the server about the success of the sync (your synchronization health). Don't do that in the server as you don't know from the server if the client actually processed the response data. Maybe the client died half way through the download or half way through saving the data in the local database. Good luck, Corneliu. On Fri, Mar 29, 2013 at 3:36 AM, Greg Harris <g...@harrisconsultinggroup.com > wrote: > 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). >
