I've had a first attempt at playing with the new ADO.NET synchronisation services this weekend so I thought I'd share here.

ADO.NET Synchronisation Services is a technology for helping with those online/offline applications where you need to get data down to be stored on a laptop to enable mobile working (device support isn't something that it's in the first version AFAIK).

Here's some links;

Download the bits ("Orcas" beta 1)

Download the docs ("Orcas" beta 1)

Subscribe to the "Synchronizer" blog

To take my simplistic view of Synchronisation Services, it's about having a local SQL Server Compact Edition database which your application talks to and the Sync Services helps with getting the data in that database sync'd with a remote DB to which connectivity isn't always available.

I say helps because Sync Services is definitely more of a framework that a developer can use to build this kind of sync. support into their app rather than a product which does it for them.

There's no shortage of ways to do this already - SQL Server supports technologies such as Merge Replication which achieves a similar thing but there are differences in Sync Services (quite a lot, but here's a few);

1. Sync Services supports multiple back ends, not just SQL Server (anything that's OLEDB compliant, AFAIK)
2. Sync Services doesn't necessarily have to talk to a DB as a back-end at all, you can use it over N-Tier and have it talk to your own service rather than a DB.
3. Sync Services is driven by programmability rather than configuration.
4. Sync Services supports views down on the client.

It seems to me that there's quite a lot of moving parts to Sync Services and so kicking off coding against it seemed a little daunting but, actually, once you get going it's not particularly tough. I copied this picture out of the books online update (hyperlink above);

So, we have;

1. The client and server databases that we want to sync.
2. The client sync provider (this abstracts the sync agent from the details of the client DB). There's only a SQL CE one of these provided AFAIK right now.
3. A set of SyncTables that contain the details about what we want to synchronise between client and server. These can be grouped to ensure that a bunch of stuff is all sync'd at once (i.e. within the bounds of a transaction).
4. A SyncAgent that does the synchronisation for us.
5. A set of SyncAdapters. These essentially control the how in terms of how we take data in one place and get it into the other place.
6. A server sync provider (this abstracts the sync agent from the details of the server DB). There's a generic "DB" version of this right now which speaks OLEDB.

So, let's say that I've got this little table sitting in my SQL Server;

create table fruitSales
(
  id int identity not null primary key,
  fruit nchar(20) not null,
  quantity int not null,
  price money not null
)

and I want to synchronise that down to my client. I have a lot of choices about what kind of synchronisation I want to perform (e.g. snapshot from server to client, download incremental from server to client, upload incremental from client to server, bidirectional) and the simplest one is to do a snapshot.

The following code has references to Microsoft.Synchronization.Data, Microsoft.Synchronization.Data.Client, Microsoft.Synchronization.Data.Server.

So, I'll need a client provider for my local CE database;

 

      SqlCeClientSyncProvider clientProvider =
        new SqlCeClientSyncProvider(@"datasource=c:\temp\syncdb.sdf", true);

And I'll be needing a server provider for my remote SQL database;

 

     SqlConnection sourceDbCon = new SqlConnection(
       @"server=.;database=fruitData;integrated security=sspi");

      DbServerSyncProvider serverProvider = new DbServerSyncProvider()
      {
        Connection = sourceDbCon
      };
      

And I need a SyncTable specifying that I'm wanting to synchronise my fruitSales table;

 

      SyncTable syncTable = new SyncTable("fruitSales")
      {
        CreationOption = TableCreationOption.DropExistingOrCreateNewTable,
        SyncDirection = SyncDirection.Snapshot 
      };

Note that I'm asking to drop any existing table and that my direction is a snapshot synchronise from server to client.

Then I need a SyncAdapter to actually do the work on that table for me;

 

 SyncAdapter adapter = new SyncAdapter("fruitSales");

      SqlCommand selectCommand = new SqlCommand("select * from fruitSales", 
        sourceDbCon);

      selectCommand.CommandType = CommandType.Text;

      adapter.SelectIncrementalInsertsCommand = selectCommand;

 

Notice that we tell the SyncAdapter how to get data (the SelectIncrementalInsertsCommand - bit weird naming for me at first but I got it in the end) by giving it a SqlCommand but it's really an IDbCommand so any provider you've got will work there.

Now, we need to let our server provider know about our adapter;

serverProvider.SyncAdapters.Add(adapter);

and we finally just need a SyncAgent to do the work for us and we need to tell it about the client provider, the server provider and the tables that we want do sync;

    SyncAgent agent = new SyncAgent() 
      {
        ClientSyncProvider = clientProvider,
        ServerSyncProvider = serverProvider                          
      };

      agent.SyncTables.Add(syncTable);

 

With all that in place, we can do the synchronisation (there's an async version with events and so on rather than just this synchronous version);

 

agent.Synchronize();

and we have synchronisation :-) I've managed to move a single table's worth of data down from the server to my client.

I'll follow up with another post about how I took this slightly further to do incremental downloads of data from the server to the client.

---

Posted Mon, May 21 2007 6:35 AM by mtaulty