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