Synchronizing WinRT/SQLite using Sync Framework Toolkit


It’s been a while since my last blog post and it’s been a while since I started to write this post and a couple of more that’s been eternally in draft mode.

So, let me tackle a common question I often see in forums and I get ask personally: Database synchronization for WinRT/SQLite based applications.

I’ve been asked many times if MS is working on porting Sync Framework to WinRT or if the Sync Framework Toolkit will be ported to WinRT as well. (or if Sync Framework is even still alive Smile ).

Robert Green did a presentation in last year’s BUILD conference (Key technologies for Windows Store business apps ) and demoed a sample application that has sync capabilities built using Sync Framework Toolkit(although i think the toolkit was still referred to as Sync Framework v4 CTP). A sample application is supposed to be forthcoming but we have yet to see one.

Thus the motivation to continue working on this post.

If you’re after the sample app that does synchronization, you can jump to bottom of this page and click on a link to download it.

But if you want to understand a bit how it’s built and how it works, allow me do indulge you with a bit of backgrounder on the Sync Framework Toolkit.

The Sync Framework Toolkit is what was supposed to be Sync Framework v4.  Microsoft released the v3 CTP which eventually become V4 CTP and then they decided to release it as a sample, open-sourced application instead. So now you know there is no V4 of Sync Framework (you may have seen many forum replies from me emphasizing this.)

Contrary to popular belief because of the huge jump in the version number from 2.1 to v4, the Sync Framework Toolkit (or V4 CTP if you insists Smile ) is not a version upgrade from v2.1. In fact, the server side component of the toolkit actually requires an installation of Sync Framework 2.1. It’s built on 2.1, not a replacement.

The toolkit (v4) actually addresses the scenarios where you can’t install the full Sync Framework SDK. These platforms include Silverlight, Windows Phone, iOS, Android or HTML5-based apps.

Instead, MS developed client components that works with the respective client platforms. If you download the toolkit and look at the codes, you will find client components that works with Silverlight/Windows Phone and Isolated Storage, Windows Mobile and SQL Compact, etc.…

These client components then communicate with a service via HTTP and it’s the service that synchronizes with a Microsoft SQL-based backend (can be SQL Server, SQL Express, LocalDB, Azure SQL Database). You will also find some Json and OData in the codes (it uses the OData protocol, but its not same OData you see in the current generation of OData interfaces from MS).

To simplify the components of the toolkit, there’s a:

  • Sync Service (uses Sync Framework 2.1, WCF-based, coupled to SQL Server/Express/LocalDB/Azure SQL)
  • Cache Controller (client-side, similar to SyncAgent/SyncOrchestrator in Sync Framework)
  • Offline Provider (a sync provider)
  • Storage Handler (the actual code that interacts with the database/ local store/cache)

Here’s a simplified view of  the Synchronization Data Flow amongst components:

(you may have to zoom in as my blog theme may not display the diagrams clearly)

image

This is how an Upload Scenario looks like:

image

 

And here’s how a Download Scenario looks like:

image

As I have mentioned, the actual client components of the toolkit are specific to the target platforms.

image

Thus the bulk of the work is on creating client specific codes for a specific development platform and store. The server side is pretty much done via ready-made utilities that simply exposes a HTTP endpoint that client can post and get updates and the service actually takes care of interacting with a SQL Server.

So for a WinRT/SQLite based client, we have to build a Cache Controller and a Storage Handler to interact with SQLite. The codes for the Offline Provider is pretty much the same for most platforms, so there’s not much to do there.

Fortunately for us, the Sync Framework Toolkit includes a sample for Windows Mobile/SQL Compact, so I just went to modify them. The bulk of the work is on getting them to work on WinRT (just moving the toolkit to 4.5 breaks it already, and WinRT even more)

To give you an idea of the stuff that needs to be written for a Cache Controller, here’s the list of files that I converted to WinRT. The bulk of the changes are on the codes that uses Reflection, the Async stuff, and HTTP request handling (specifically, HTTPCacheRequestHandler).

image

So let’s move on sync-enabling an existing sample app. I downloaded the sample from Robert Green’s post here: Blog Post: Using SQLite in Windows 8 Store Apps (Code Sample: Using SQLite in a Windows Store App)

Apart from adding a reference to the Cache Controller above, I’ve also added the SQLite specific codes in the client application.

The bulk of the code for retrieving and applying changes is inside the SqlliteStorageHandler. This is the part that is going to be very specific for every application.

image

To give you an idea, here’s some of the codes that you need to write.

Code for retrieving changes that need to be uploaded:

image

Permanently deleting rows marked with IsTombstone and clearing IsDirty flags, after the rows has been uploaded:

image

Code for applying downloaded changes from the server:

image

So let’s start sync-enabling the sample app.

To sync enable the app, we have to change the way we do CRUDs so we can keep track of the changes happening (change tracking). So far this is the most intrusive change we have to include in the client app’s logic since the change tracking becomes a responsibility of the client. In the older full database sync providers for SQL Server and SQL Compact, this is done via triggers and the SQL Compact Change Tracking  API.

First we need to mark the entities that we want to include in the synchronization by having them inherit from SqliteOfflineEntity.

image

The toolkit reflects on these entities and this adds a property for the sync metadata. We have to tell SQLite to ignore the OfflineEntityMetadata property as otherwise it will try treat it like a normal column and attempt to persist it resulting to an error.

We also need to mark with the toolkit’s Key attribute the property that represents the Primary Key.

Then we have to explicitly declare the change tracking properties as well since the WinRT SQLite can’t handle the inherited ServiceMetadata property above.

image

The IsDirty flag is used to identify changes (inserts and updates).

The IsTombstone is flag that’s set when deleting rows. Instead of physically deleting the row, we just set the IsTombstone flag to identify that it’s been deleted. The sync will eventually delete the row once its been synched. Since we’re only doing logical deletes, we have to make sure we exclude all IsTombstone flagged rows when querying the tables.

Also, make sure that the namespaces in the client entities and the code-generated service entities are the same.

This is a sample snippet for the a sync tool generate entity on the server side. We’ll have a look at how to generate one later.

image

I’ve also added another entity SyncAnchor to represent the entity that holds the sync knowledge (holds the anchor and other info for what was synched). Makes it much easier to save the byte array than having to construct SQL Command text.

image

Here’s how we build the tables in SQLite.

image

Then we modify how we do inserts and updates by adding the setting of the IsDirty flag when doing an Insert or Update.

image

For deletes, we set the IsTombstone flag instead of doing a physical delete

image

Since were not physically deleting rows, we have to exclude the rows that has IsTombstone set from Selects.

image

We then set the url where the sync service the client needs to interact with is deployed and specify the corresponding scope name. (A scope in Sync Framework is the definition of what will be synched in terms of tables, columns, and filters if any).

image

Here’s the code where we actually instantiate the Cache Controller, the providers (represented as the service and the offline provider) and initiate the sync.

image

But before we can sync, let’s add a Sync button on the App Bar.

image

image

And here’s the event handler for the Sync button that includes the call to do the sync and displaying a corresponding Toasts with sync statistics when sync is done.

image

Now that we’re done on the client side, let’s go build the server side.

I’ve included in the project the corresponding SQL Scripts for creating the SQL Server database and the two tables we want to sync. I’ve also zipped the Sync Service utilities just in case you have issues downloading and compiling the Sync Framework Toolkit.

image

Here’s the two tables that get’s created with the SQL Script.

image

image

We then have to define the scope or the tables, columns and filter configuration for what we want to sync. The SyncSvcUtil.zip file included in the project contains the SyncSvcUtilUI app below:

image

We create a new sync configuration file.

image

Specify database connection information.

image

Add a scope.

image

Add Tables

image

Select Tables and Columns and optionally specify filters.

image

This then generates a config file that we can use to provision our database to participate in synchronization (see: Sync Framework Provisioning on what happens during provisioning)

image

We then open up a Command Prompt window and execute the following to provision the database (you can also do this in the UI).

syncsvcutil.exe /mode:provision /scopeconfig:SqliteDemo.config

You then see additional database objects created by the Sync Provisioning step.

image

Next, we generate the corresponding WCF service by executing the following:

syncsvcutil /mode:codegen /target:server /scopeconfig:SqliteDemo.config

The above command will generate two files: xxxScopeEntities and xxxSyncService.

Next create a Web Application project to host the service and add the two files above.

We also need to add a reference to the toolkit’s Microsoft.Synchronization.Services assembly (i’ve included as well in the file download).

image

Make sure to modify the xxxSyncService file to specify the connection string and other settings.

image

If you need to test on IIS or Azure, the documentation that comes with the Sync Framework Toolkit includes information on how to deploy the service in IIS and in Azure.

Now, to test the app, we just need one more step since WinRT apps can’t access our local website hosting the service. Download Fiddler and configure exemption for the app.

image

Before you add any row in the client, make sure you sync once first to get the anchor initialized. Otherwise, the service will fire an exception that the ServerBlob is empty.

If you insert rows in the server database and initiate sync, you should see them downloaded in the app. You can then insert/update/delete on either side and the changes should sync.

And here’s how the app looks like with the Sync button visible at the app bar.

image

Sample screen shot showing toast notification when sync is done.

image

You can download the full source from my SkyDrive. I’ll  publish it somewhere else once am done refining the sample app.

If you run into issues, need some clarification or has suggestions, please feel free to  contact me either via the comment section or here: Contact

Cheers and looking forward to your feedback!

JuneT

47 comments

  1. Eliot Rayner · · Reply

    Hi JuneT

    Great article, thanks so much. Do you have any more detail on changing the sync toolkit target to 4.5 ? Is it just a case of fixing the reference errors or is there more to it?
    Regards
    Eliot

    1. it’s more than just project references…there some stuff that got deprecated in 4.5, changes in namespaces, etc… I tried moving the entire toolkit actually but just opening the solution and compiling has hundreds of errors :)

    2. hello Eliot have you ever success to sync data from win 8 app with SQLite to SQL Server ? i am trying to do this but not able to do this even with JuneT code not working

  2. Hi JuneT, you mentioned ‘I’ve also zipped the Sync Service utilities just in case you have issues downloading and compiling the Sync Framework Toolkit…’ however its not included in the download :-)

    1. sorry about that…I’ve re-uploaded the file…that should have the utilities now…

  3. Hi JuneT, I’m getting a compile error on the SyncService project .. ‘The type or namespace name ‘SQLiteDemo’ could not be found (are you missing a using directive or an assembly reference?) WinRTSync\SyncService\SqliteDemoScopeSyncService.svc.cs’ caused by ‘using SQLiteDemo.Models;’ on Line 11. Not sure if I’ve done something wrong. thanks Eliot

    1. not sure about that error…the SqliteDemoScopeEntities.cs in the same project has that namespace, so that should resolve to it…
      this is the line that makes a reference to that namespace:
      public class SqliteDemoScopeSyncService : Microsoft.Synchronization.Services.SyncService {

      try removing the using statement and specify the SQLiteDemo.Models.SqliteDemoScopeOfflineEntities instead.

  4. Hi June, I commented out the using SQLiteDemo.Models; and the app compiles. However I’m now getting a System.Net.Webexception of ‘The remote server returned an error: (500) Internal Server Error.’ from a RepsonseURI of http://localhost.fiddler:3412/SqliteDemoScopeSyncService.svc/SqliteDemoScope/UploadChanges. I think this has something to do with the using i’ve cut out but not to sure.

    1. that’s because the service is trying to find the entities in a different namespace.

  5. This article is AWESOME. First I want to thank you for this article. I was able to create full application + Azure Service with it and it works like charm. :)

    But when I deploy the web service, I had few difficulties. So, I came up with few steps to deploy Sync Service in Azure.

    1. Remote log in to virtual machine (Azure)

    2. Install Syn Framework 2.1 in VM

    3. If you get following error from service.

    Retrieving the COM class factory for component with CLSID {EC413D66-6221-4EBB-AC55-4900FB321011} failed due to the following error: 80040154 Class not registered (Exception from HRESULT: 0×80040154 (REGDB_E_CLASSNOTREG)).

    Run this command:
    regsvr32.exe /s “C:\Program Files (x86)\Microsoft SDKs\Microsoft Sync Framework\2.1\Runtime\x64\Synchronization21.dll”

  6. Hi June, If I want to Sync specific rows from database, how am I sync only that relevant data. For example, I want to sync data which related to given user. In my main database I have data for all users but when it sync to device, I want to sync only user specific data. Can you please suggest me a way to do it?

    1. you should be able to specify a filter when you create the scope on the server. if you download the Sync Framework Toolkit, there are samples there that actually includes a filter by user.

  7. I did a conversion of the Sync FT to WinRT last fall and it appears to be working fine. I removed ATOM and encryption support in doing so to minimize the refactoring.

    There was tremendous pain in getting it to perform properly on an ARM processor. We sync about 600 rows of data with each new session created (about 1MB of JSON data). The background cleanup process was a hog as it consolidated change/save files into the archive.

    Having said that, the main bottle neck appears to be the processing of entities in the IsolatedStorageOfflineEntity class:

    internal static PropertyInfo[] GetEntityKeyProperties(Type t)
    {
    return (from p in GetEntityProperties(t)
    where p.GetCustomAttributes(typeof(KeyAttribute), false).Count() != 0
    select p).ToArray();
    }

    Have you performance tested this version on a Surface RT or ARM based device? Would you anticipate that the I/O is much faster using SQLLite? ARM based File I/O is orders of magnitude slower than Intel based.

    I may consider migrating if it’s performant.

    1. haven’t really done a benchmark, so i cant categorically confirm if its better than plain Isolated Storage. might try to do one when i find time.

  8. Hi, great article. What is the best way to sync in background without button? Thanks.

    1. you can invoke the sync in background task or timer…
      for info on background tasks, see: http://blogs.msdn.com/b/windowsappdev/archive/2012/05/24/being-productive-in-the-background-background-tasks.aspx

  9. Please can you tell me, how can i enable gzip for sync service ? Thanks.

    1. to be honest with you, i haven’t really thought about it so am not sure which code part you could plug that in.

      for the client, since its doing a HTTP request, am assuming you can put it in the request header and on the server, maybe just enable compression in IIS.

  10. iTrout · · Reply

    Great Article…Thanks! Our scenario is a “Hub and Spoke Sync” model where we have 3 Win 8 Tablets in the field that we’d like to keep in Sync with our main SQL Server. Any advice/best practices on handling conflicts if more than one of the tablets edits the same row in a table?

    1. the sample should work in hub-spoke. the thing is when you sync, your clients will sync at different times. so you can’t really cherry-pick which change among the three you want to keep. the conflict is between your client and whatever is in the the server. you can write some code on the server side to put some custom logic around how you want to resolve conflicts (e.g., based on last update time, based on which user, etc…)

    2. if you’re not bandwidth constrained or battery constrained, frequent sync should help reduce conflicts. sync is like source control, the longer before you check-in or get latest, the more chances for conflicts :)

  11. iTrout · · Reply

    Thanks June – figured that’s what we’d need to do. Again, GREAT article….thanks!

  12. i am getting error while it exucutes

    There was an error downloading ‘http://localhost:20621/SqliteDemoScopeSyncService.svc/$syncscopes/_vti_bin/ListData.svc/$metadata’.
    The request failed with HTTP status 400: Bad Request.
    Metadata contains a reference that cannot be resolved: ‘http://localhost:20621/SqliteDemoScopeSyncService.svc/$syncscopes’.
    The remote server returned an unexpected response: (405) Method Not Allowed.
    The remote server returned an error: (405) Method Not Allowed.
    If the service is defined in the current solution, try building the solution and adding the service reference again.

    1. is that error from the project in this blog post? because i dont remember having a ListData.svc in there.

      1. actually i have changed server here it is error which i am getting from blog project while opening in WCF test client

        Error: Cannot obtain Metadata from http://localhost:3412/SqliteDemoScopeSyncService.svc If this is a Windows (R) Communication Foundation service to which you have access, please check that you have enabled metadata publishing at the specified address. For help enabling metadata publishing, please refer to the MSDN documentation at http://go.microsoft.com/fwlink/?LinkId=65455.WS-Metadata Exchange Error URI: http://localhost:3412/SqliteDemoScopeSyncService.svc Metadata contains a reference that cannot be resolved: ‘http://localhost:3412/SqliteDemoScopeSyncService.svc’. The remote server returned an unexpected response: (400) Bad Request. The remote server returned an error: (400) Bad Request.HTTP GET Error URI: http://localhost:3412/SqliteDemoScopeSyncService.svc The document at the url http://localhost:3412/SqliteDemoScopeSyncService.svc/$syncscopes was not recognized as a known document type.The error message from each known type may help you fix the problem:- Report from ‘XML Schema’ is ‘The root element of a W3C XML Schema should be and its namespace should be ‘http://www.w3.org/2001/XMLSchema’.’.- Report from ‘DISCO Document’ is ‘Discovery document at the URL http://localhost:3412/SqliteDemoScopeSyncService.svc/$syncscopes could not be found.’. – The document format is not recognized.- Report from ‘WSDL Document’ is ‘There is an error in XML document (1, 2).’. – was not expected.

      2. don’t use the WCF Test client for it. there’s an instruction on the sync toolkit documentation on how to verify that the service is up.

  13. Hi,

    Thank you for sharing your sample.

    I’m a bit in the same situation as iTrout. (http://jtabadero.wordpress.com/2013/01/09/synchronizing-winrtsqlite-using-sync-framework-toolkit/#comment-765)

    I was wondering if I could add my own conflict resolution policy and if so, where could I do it? (Server side (where on the server ? There are only 2 classes) // client side)
    Currently, if I make changes with instance #1, upload them to the database and make changes with instance #2 there will be conflicts between server data and local ones. I would like to be able to “catch” these conflicts and treat them instead of leaving them to the application (where the server data always wins).

    I looked on the side of config.SetConflictResolutionPolicy(Microsoft.Synchronization.Services.ConflictResolutionPolicy.ServerWins); but I’m a little bit lost.

    The application will be installed on multiple tablets and I’m not sure how to handle all conflicts.

    Thanks !

    Regards

    1. have a look at SyncConflictInterceptor in the Sync Toolkit documentation (this is almost equivalent to ApplyChangeFailed event of a sync provider)

      1. Thanks ! I’ll have a look

        Regards

  14. Hi June,

    I am facing the same problem as Yagnesh with The request failed with HTTP status 404: Bad Request.

    Could you please help.

    1. use Fiddler to have a look at a more detailed insight on what the error is.

  15. Any chance you’ve done the equivalent effort for a WinJS app? :)

    1. unfortunately, none at this time. :)

  16. For WP8 ?
    thanks

  17. Viresh bhatt · · Reply

    Hi JuneT, I want to sync my local sqlite database with another sqlite database which is on skydrive. I need some start for doing this. Please guide me how can I do this ?

    1. Sync Fx doesn’t have built in sync providers for SQLite and you can’t use the Sync Toolkit either as the server side uses SqlSyncProvider.

  18. Nik Makris · · Reply

    Hi JuneT, Thanks for sharing this. I got your demo working, but having an issue integrating into my project. I haven’t found the solution yet. This is the error I get: “Unable to find a matching type for entry ‘TraderSync.CUST1′ in list of KnownTypes”.

    TraderSync is the scope name and CUST1 is my entity and table name.

    It seems to be breaking inside GetObjectForType():

    entityType = knownTypes.FirstOrDefault(e => e.FullName.Equals(wrapper.TypeName, StringComparison.Ordinal));

    Its is trying to compare MyProjectNamespace.Models.CUST1 == TraderSync.CUST1

    Which obviously not equal. Any ideas why it this is throwing an exception?

    Thanks

  19. Nik Makris · · Reply

    I’ve just found the solution to my query. The server provisioning code that is generated by SyncSvcUtilUI, specifically the entities c# class needs to use the same namespace as the entities you are using in WinRT entities.
    Makes sense really…

    1. have a look at this project as well: https://syncwinrt.codeplex.com/

  20. Nik Makris · · Reply

    Hi JuneT, any ideas where to start with creating an initial SQLite snapshot? I know you can do this with SQL CE.

    Cheers

  21. Luis Barbier · · Reply

    Thank you June for this excellent tutorial and sample implementation of the Microsoft Sync Framework Toolkit.

    You made it as easy as can be. I’ve implemented using Xamarin.iOS, Xamarin.Android, and WPF using portable class libraries.

  22. Luis Barbier · · Reply

    June,

    Do you have a sample like this one with multiple scopes?

    I’m currently running into a problem where the server sent to the first sync gets back a server blob and then the second sync doesn’t work because the server assumes that it knows the client due to the first sync.

    Can you please point me in the right direction?

    Regards,

    Luis

    1. Luis Barbier · · Reply

      The issue is in the storage handler GetAnchor() method. On the second sync it finds the syncAnchor of the first sync.

      How do you suggest handling this?

      1. Luis Barbier · ·

        Just updating the GetAnchor() method to GetAnchor(string scope) and the SaveAnchor(byte[] anchor) to SaveAnchor(byte[] anchor, string scope) will be the best approach.

        Of course, the scope will be needed in the syncAnchor object to store in the local SQLite database.

        Does this seem like a good way of handling this situation?

      2. are you trying to have multiple scopes use the same client database?

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

Join 186 other followers

%d bloggers like this: