Mike Taulty's Blog
Bits and Bytes from Microsoft UK
First Steps with ADO.NET Synchronisation Services

Blogs

Mike Taulty's Blog

Elsewhere

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

Comments

Mike Taulty's Blog wrote Second Steps with ADO.NET Synchronisation Services
on Mon, May 21 2007 6:56 AM
Following up on this post where I just got started with Sync Services, if I want to do more than just...
help.net wrote First Steps with ADO.NET Synchronisation Services
on Tue, May 22 2007 2:01 PM
ADO.NET Synchronisation Services is a technology for helping with those online/offline applications where
Christopher Steen wrote Link Listing - May 24, 2007
on Thu, May 24 2007 8:00 PM
WCF Security References [Via: nluria ]  Smart Client Software Factory v2 is now available [Via: Public...
Christopher Steen wrote Link Listing - May 24, 2007
on Thu, May 24 2007 8:02 PM
WCF Security References [Via: nluria ] Smart Client Software Factory v2 is now available [Via: Public...
Wordpress Tips » Wordpress Plugins wrote Wordpress Tips » Wordpress Plugins
on Sat, May 26 2007 12:33 AM
Wordpress Tips » Wordpress Plugin wrote Wordpress Tips » Wordpress Plugin
on Sat, May 26 2007 1:22 AM
Wordpress Tips » Link Listing - May 24, 2007 wrote Wordpress Tips » Link Listing - May 24, 2007
on Sat, May 26 2007 4:12 AM
Wordpress Tips » Link Listing - May 24, 2007 wrote Wordpress Tips » Link Listing - May 24, 2007
on Sat, May 26 2007 5:59 AM
Wordpress Tips » Link Listing - May 24, 2007 wrote Wordpress Tips » Link Listing - May 24, 2007
on Sat, May 26 2007 1:41 PM
Wordpress Tips » Quick custom text ad placement in WordPress blog categories wrote Wordpress Tips » Quick custom text ad placement in WordPress blog categories
on Sat, May 26 2007 8:57 PM
Wordpress Tips » Wordpress Plugin wrote Wordpress Tips » Wordpress Plugin
on Sat, May 26 2007 9:14 PM
Wordpress Tips » Link Listing - May 24, 2007 wrote Wordpress Tips » Link Listing - May 24, 2007
on Sat, May 26 2007 10:36 PM
Wordpress Tips » Wordpress Plugins wrote Wordpress Tips » Wordpress Plugins
on Sat, May 26 2007 10:38 PM
Wordpress Tips » Quick custom text ad placement in WordPress blog categories wrote Wordpress Tips » Quick custom text ad placement in WordPress blog categories
on Sun, May 27 2007 12:07 AM
Wordpress Tips » Link Listing - May 24, 2007 wrote Wordpress Tips » Link Listing - May 24, 2007
on Sun, May 27 2007 1:15 AM
Wordpress Tips » Quick custom text ad placement in WordPress blog categories wrote Wordpress Tips » Quick custom text ad placement in WordPress blog categories
on Sun, May 27 2007 1:39 AM
Wordpress Tips » Wordpress Plugin wrote Wordpress Tips » Wordpress Plugin
on Sun, May 27 2007 1:39 AM
Wordpress Tips » Link Listing - May 24, 2007 wrote Wordpress Tips » Link Listing - May 24, 2007
on Sun, May 27 2007 2:14 AM
Wordpress Tips » Wordpress Plugins wrote Wordpress Tips » Wordpress Plugins
on Sun, May 27 2007 2:25 AM
Wordpress Tips » Link Listing - May 24, 2007 wrote Wordpress Tips » Link Listing - May 24, 2007
on Sun, May 27 2007 2:39 AM
Wordpress Tips » Quick custom text ad placement in WordPress blog categories wrote Wordpress Tips » Quick custom text ad placement in WordPress blog categories
on Sun, May 27 2007 3:20 AM
Wordpress Tips » Quick custom text ad placement in WordPress blog categories wrote Wordpress Tips » Quick custom text ad placement in WordPress blog categories
on Sun, May 27 2007 3:38 AM
Wordpress Tips » Wordpress Plugin wrote Wordpress Tips » Wordpress Plugin
on Sun, May 27 2007 7:17 AM
Wordpress Tips » Link Listing - May 24, 2007 wrote Wordpress Tips » Link Listing - May 24, 2007
on Sun, May 27 2007 7:18 AM
Wordpress Tips » Quick custom text ad placement in WordPress blog categories wrote Wordpress Tips » Quick custom text ad placement in WordPress blog categories
on Sun, May 27 2007 7:31 AM
Wordpress Tips » Wordpress Plugin wrote Wordpress Tips » Wordpress Plugin
on Sun, May 27 2007 7:31 AM
Wordpress Tips » Link Listing - May 24, 2007 wrote Wordpress Tips » Link Listing - May 24, 2007
on Sun, May 27 2007 7:40 AM
Wordpress Tips » Link Listing - May 24, 2007 wrote Wordpress Tips » Link Listing - May 24, 2007
on Sun, May 27 2007 7:51 AM
Wordpress Tips » Link Listing - May 24, 2007 wrote Wordpress Tips » Link Listing - May 24, 2007
on Sun, May 27 2007 11:06 AM
Wordpress Tips » Wordpress Plugins wrote Wordpress Tips » Wordpress Plugins
on Sun, May 27 2007 11:10 AM
Wordpress Tips » Wordpress Plugin wrote Wordpress Tips » Wordpress Plugin
on Sun, May 27 2007 11:22 AM
Wordpress Tips » Link Listing - May 24, 2007 wrote Wordpress Tips » Link Listing - May 24, 2007
on Sun, May 27 2007 11:33 AM
Wordpress Tips » Link Listing - May 24, 2007 wrote Wordpress Tips » Link Listing - May 24, 2007
on Sun, May 27 2007 11:54 AM
Wordpress Tips » Quick custom text ad placement in WordPress blog categories wrote Wordpress Tips » Quick custom text ad placement in WordPress blog categories
on Sun, May 27 2007 3:03 PM
Wordpress Tips » Quick custom text ad placement in WordPress blog categories wrote Wordpress Tips » Quick custom text ad placement in WordPress blog categories
on Sun, May 27 2007 7:06 PM
Wordpress Tips » Wordpress Plugin wrote Wordpress Tips » Wordpress Plugin
on Sun, May 27 2007 7:46 PM
Wordpress Tips » Link Listing - May 24, 2007 wrote Wordpress Tips » Link Listing - May 24, 2007
on Sun, May 27 2007 7:48 PM
Wordpress Tips » Wordpress Plugins wrote Wordpress Tips » Wordpress Plugins
on Sun, May 27 2007 11:06 PM
Wordpress Tips » Link Listing - May 24, 2007 wrote Wordpress Tips » Link Listing - May 24, 2007
on Sun, May 27 2007 11:13 PM
Wordpress Tips » Link Listing - May 24, 2007 wrote Wordpress Tips » Link Listing - May 24, 2007
on Mon, May 28 2007 3:26 AM
Wordpress Tips » Wordpress Plugin wrote Wordpress Tips » Wordpress Plugin
on Mon, May 28 2007 3:49 AM
Wordpress Tips » Top Wordpress Plugins for the Smart Blogger wrote Wordpress Tips » Top Wordpress Plugins for the Smart Blogger
on Mon, May 28 2007 3:55 AM
Wordpress Tips » Link Listing - May 24, 2007 wrote Wordpress Tips » Link Listing - May 24, 2007
on Mon, May 28 2007 8:08 AM
Wordpress Tips » Top Wordpress Plugins for the Smart Blogger wrote Wordpress Tips » Top Wordpress Plugins for the Smart Blogger
on Mon, May 28 2007 8:47 AM
Wordpress Tips » Top Wordpress Plugins for the Smart Blogger wrote Wordpress Tips » Top Wordpress Plugins for the Smart Blogger
on Mon, May 28 2007 8:58 AM
Wordpress Tips » Quick custom text ad placement in WordPress blog categories wrote Wordpress Tips » Quick custom text ad placement in WordPress blog categories
on Mon, May 28 2007 9:01 AM
Wordpress Tips » Top Wordpress Plugins for the Smart Blogger wrote Wordpress Tips » Top Wordpress Plugins for the Smart Blogger
on Wed, May 30 2007 8:16 AM
Wordpress Tips » Link Listing - May 24, 2007 wrote Wordpress Tips » Link Listing - May 24, 2007
on Wed, May 30 2007 9:09 AM
Womens Discount Perfume » Not vera wang princess perfume but still interesting… wrote Womens Discount Perfume » Not vera wang princess perfume but still interesting…
on Tue, Oct 30 2007 2:39 PM
Womens Discount Perfume » Not miniature perfume but still interesting… wrote Womens Discount Perfume » Not miniature perfume but still interesting…
on Wed, Oct 31 2007 6:51 AM