CODEWITHSUNDEEP
c#.netsql-servermulti-tenant
user2023749
user2023749

Reputation: 75

.Net Data synchronization between multiple clients and a central server

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

Their application is written in C# and uses SQL Server as the database backend. They would like to collect data from all their clients databases and also send back a smaller amount of data back to the clients. There are a couple of requirements:

  1. Data synchronization is against 500+ clients with 7 slightly different database schemas across 7 different versions of their software

  2. There can be new versions of their software with different schemas over time which cannot be delayed for waiting to update this data sync mechanism. Basically the data sync needs to be flexible to support any schema and controlled from a central location instead of changes to it being tied to the release of their application

  3. Needs to be written in .Net since all their software runs on Windows

  4. There can be a special data sync client deployed together which has access to each clients SQL Server database - but updating the client is not guaranteed to happen often.

Is there already a framework ( other than Microsoft Sync Framework which uses triggers on the database ) which would help with developing this data synchronization system?

Are there any good books/articles to read on multi-tenant systems or data synchronization which provide some good points on how to create a system like this?

Upvotes: 3

Views: 7552

Answers (1)

john
john

Reputation: 581

Not sure if there are existing framework other than sync, I had similar requirement a while back but couldn't use MS Sync as I wasn't using mssql.

This is what I did, some custom code,

Going to make some assumptions on schema change means

  • new fields/attribute can be added
  • existing fields will not be deleted (i.e. you don't have to migrate existing data)
  • the clock on client and server are using UTC and synced.

You can create a schema history table with following attributes,

  • table name
  • version
  • last sync date

Then with all the table requiring syncing each value has a date change and last changed user attribute, e.g.

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

When you sync,

  1. check the schema table, if the client's version < server version ==> update schema (running update schema scripts)
  2. Get the server updates, new data to be pushed to client, where owner = server and datechanged > clientchangedate

  3. compare each table for sync's last change date against schema table's last sync date for each table

    if change date > sync date and user = client => sync to server (either update or insert)

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

Upvotes: 3

Related Questions