Clever Geek Handbook

.Net Synchronization of data between multiple clients and a central server

My client, a financial services provider, has their software deployed to 500+ clients.

Their application is written in C # and uses SQL Server as a database. They would like to collect data from all of their customer databases, and also send less data back to customers. There are several requirements:

  • Data sync against 500+ clients with 7 slightly different database schemas in 7 different versions of their software.

  • Over time, new versions of their software may appear with different schemas that cannot be delayed to await updates to this data synchronization mechanism. Basically, data sync needs to be flexible to support any schema and be managed from a central location, rather than changing its binding to the release of your application.

  • Need to write in .Net since all their software runs on Windows

  • There may be a dedicated data sync client deployed together that has access to every SQL Server database for clients, but the client is not guaranteed to be updated frequently.

Is there already a framework (other than the Microsoft Sync Framework, which uses triggers on the database) that will help in the design of this data synchronization system?

Are there any good books / articles for reading on multiuser systems or data synchronization that give some good points on how to create such a system?

+3


source to share


1 answer


Not sure if there are existing frameworks other than sync, I had a similar requirement a while ago, but I couldn't use MS Sync as I didn't use mssql.

This is what I did, some custom code,

To make some assumptions about changing the schema,

  • can add new fields / attribute
  • existing fields will not be removed (i.e. you don't need to migrate existing data)
  • the clocks on the client and server use UTC and are synchronized.

You can create a schema history table with the following attributes,

  • table name
  • version
  • last sync date


Then with the whole table requiring each value to be synchronized has the date change and the last changed user attribute like

  • item1, 1/1/13, server
  • item2, 2/2/13, client1

When synchronizing,

  • check schema table if client version <server version ==> upgrade schema (run upgrade schema scripts)
  • Get server updates, new data will be transferred to client where owner = server and datechanged> clientchangedate

  • compare each table to sync the last modified date according to the schema last sync date table for each table

    if modified date> sync date and user = client => sync with server (update or insert)

also check ms sync concept http://msdn.microsoft.com/en-us/sync/bb821992.aspx if you want to replicate it.

+3


source






More articles:


All Articles