Sync SQLite With SQL Server (Merge Replication)

JeffKanJeffKan AUMember ✭✭

Hi Guys,

I spent a couple months develop a Sync Engine which sync data between SQLite and SQL Server (2008+) and it works with Xamarin . But the project failed to go ahead, now the sync engine seems to be a waste. I am looking for help to see others can take advantage of this.

Why I built this sync engine in the first place? I worked Window Mobile (WM) platform for a long long time, the solution (Sale Force app for FMCG) I built requires app to be fully functional even there is no connection as the user needs to complete task in various places. I used Merge Replication to do the sync at database level for WM application for 500 devices. WM is dead by now, everyone wants to use iPad to be next platform. The problem I found there is virtually no good sync engine runs on iOS device can replace merge replication. After done lots of searches, I decide to write my own sync engine. In addition, I rewrote the Xamarin Field Server app to prove that the sync engine does work.

The some benefits of the Sync Engine I can think of right now.

1) Write great offline enterprise app.

2) Increase Developer Productivity as the developer only need to focus on CRUD operation, the sync engine takes care of the data synchronization.

Here are some features & architecture design:

  • It works in iOS & Android under Xamarin platform.

  • It is similar to Microsoft Merge Replication’s design.

  • It has much better performance than Merge Replication

  • It supports the row filtering (Data Partition)

    a)The device only gets the data relevant to the user’s context.

    b)When a row was updated which move the row out of partition, the replicated data on the device will be deleted automatically.

  • It supports Row Level & Column Level changing at server side as well as client side.

    a)The benefit is that it reduces the payload size.

  • It has a server component runs in the IIS (using Asp.net MVC 4)

  • It has a client component which track local changes and apply server side changes.

KJH

[email protected]

Melbourne, Australia

Answers

  • JohnMillerJohnMiller USForum Administrator, Xamarin Team Xamurai

    @KJH,

    This sounds good, but I dont see anything in your post about why you posted on these forums? Whats next? Are you planning to release this as open source? Are you going to make a component for it? Are you just sharing that you made something? I would love to see something like this made open source, as there are always questions on these forums from people looking for something similar, if not exactly, to what you created.

  • JeffKanJeffKan AUMember ✭✭
    edited July 2013

    Thanks John,

    This sounds good, but I dont see anything in your post about why you posted on these forums?

    A: I only wrote the client side sync engine using Xamarin Platform, People on this forum may find it useful as the it's very difficult to develop a sync engine at database level.

    Whats next? Are you planning to release this as open source? Are you just sharing that you made something? I would love to see something like this made open source, as there are always questions on these forums from people looking for something similar, if not exactly, to what you created.

    A: I was thinking of Open Source, but i am not prepared to do in the next 12 months. I happy to give some tips if any people has similar problems.

    Are you going to make a component for it?

    A: No in short. Sync engine requires lots of server side setup and best practice needs to be followed, just like the "Sql server merge replication". Not all existing databases are ready for mobile database replication. it usually requires minor modification & optimisations.

  • JohnMillerJohnMiller USForum Administrator, Xamarin Team Xamurai

    @KJH,

    Thanks for clarifying a little. I see you mentioned that people on this forum may find it useful. How can someone get access to what you made? Do you want us to email you or something? I was hoping for some info on how to get what you made.

  • JeffKanJeffKan AUMember ✭✭

    Thanks John,

    Surely, I can be reached on [email protected], if you found any thread regards to this topic, i can be notified to give some comments on the problems & solutions.

    After that, i am not too sure.

    If anyone is really interested, I may open source the client app (my version of Xamarin sample Field Service), and setup the testing server for people to play with it.

    BTW, one thing i was very crazy, i really hate the storyboard as the underlying file changes significantly even if you only made a tiny change on the designer. So I rewrote the UI using code ONLY.

  • JeffKanJeffKan AUMember ✭✭
    edited July 2013

    Some code which builds the UI.

  • +1 for 100% coded UI! :D

  • JeffKanJeffKan AUMember ✭✭

    @pierceboggan

    I am not alone.

  • GavinHallGavinHall AUMember

    Hi KJH
    I'm looking to use Xamarin for a mobile app that users will have to collect data whilst offline and then sync it back to the server. So far what you have created seems like the best way for the users to do this sync.
    Is there any chance that I can see what you have done ?
    Cheers
    Gavin

  • JeffKanJeffKan AUMember ✭✭

    I am planning to publish a sample app and a sample backend on amazon service, but it will take some time.

    Please send me your contact details to [email protected], i can do a demo to check whether this is what you are looking for.

  • KirkKlobeKirkKlobe USMember, Beta

    The Microsoft Sync Framework is another solution that provides cross-platform data synchronization.

  • JeffKanJeffKan AUMember ✭✭
    edited July 2013

    @kirkus

    Before I rolled up my sleeve, I did the look at the sync framework and its open source toolkit.

    Here are the reasons I did not end up using it.

    1) Row Filter support is buggy, if you have row updated which cause that row is no longer satisfying the filter, the row won't be deleted from device, it row will be kept on device forever.

    2) it requires lots of coding for large application as i got more than 80 tables need to be replicated to device.

    3) Microsoft stopped working on "Sync Framework" long long time ago.

  • GavinHallGavinHall AUMember

    Zumero has this functionality at no to very low costs.

  • StephenGrayStephenGray USUniversity ✭✭

    Hi KJH, are you opensourcing your solution as it sounds very interesting. Any links to the source so we can have a play? Thanks in advance.

  • RudaRuda BRUniversity ✭✭

    Man,

    Comes in contact with the staff of Xamarin, speaking of the solution. They will probably want something like this Xamarin.Sync
    A version opensource and support them to continue developing and improving.

  • JeffKanJeffKan AUMember ✭✭
    edited November 2013

    lots of people sent me emails regards to this sync engine, they would like to gets the bits and have a play. unfortunately, I have been extremely busy involving a start up project which heavily depends on this sync engine. this project may last a couple months. I would revisit this around mid of 2014.

  • ismaisma USMember

    Hi,
    your project is too interesting...
    Have you share it somewhere or can you share it in his actual state?
    Is there an other existing solution to do this stuff?
    Thanks

  • ShivasJayaramShivasJayaram USMember

    Great concept, any update on making this available to others?
    Thanks

  • HiroDevHiroDev USMember ✭✭

    KJH - I am also very interested in this project. I think that a lot of people would benefit from this, and if you open source it, even at its incomplete state, a lot of progress will be made to take it to the next level.

    We're all hoping to hear some good news about this project soon!

  • BrianRepettiBrianRepetti USUniversity ✭✭✭

    An old thread, but I would be interested in this.

  • manishkumarmanishkumar INMember

    There is a software available at "www.mobiledatabasesync.com" which has web based management console for publishing and synchronizing data between sqlite and sql server. when you download software, it gives you dll file which you can attach in your xamarin code and call a simple function. However this product is only built for Android platform and quite easy to use.

  • If there's still interest - I just published my synchronization framework (Xamarin - Spring Rest) here: https://github.com/gcausin/mobile-synchronization/

  • krono111krono111 CHMember

    Do you have any updates on this ? It's 2017 now :)

Sign In or Register to comment.