Alex Duggleby’s Palace of Words

This morning I finally got my authors copy of the dot.net Magazin that I wrote an article on Microsoft Sync Framework. It’s in german, 7 pages long and you can buy it tomorrow.

Anyhow, I got an email this morning asking about one of the code listings and asking if there was anything different to do in VB.net. It’s been a while since I last did VB.net (I actually did more VB6 than VB.net) but I converted the sample from the magazine to VB.net. Just remember to reference Microsoft.Synchronization and Microsoft.Synchronization.Files.

(By the way I still haven’t got round to looking at CTP2 of the Sync Framework. All this stuff (and the article) applies to CTP1. Maybe there was a breaking change in CTP2 with regard to the FileSyncProvider. I must get round to looking at CTP2 a.s.a.p.)

Here is the code:

Imports Microsoft.Synchronization
Imports Microsoft.Synchronization.Files
Imports System.IO

Module Module1

    Sub Main()

        'Define directories
        Dim _sourceDir As String = "c:\temp\1"
        Dim _destDir As String = "c:\temp\2"

        'Define system ids
        Dim _sourceId As SyncId = New SyncId(New Guid("A4715EAF-341E-4ebf-90C3-71E8644CA6E8"))
        Dim _destId As SyncId = New SyncId(New Guid("FF766014-0DA4-401d-BDA1-0679326ECF96"))

        'Exclude some patterns
        Dim _syncFilter As FileSyncScopeFilter = New FileSyncScopeFilter()
        _syncFilter.AttributeExcludeMask = FileAttributes.Hidden Or FileAttributes.System
        _syncFilter.FileNameExcludes.Add("DoNotCopy.txt")
        _syncFilter.SubdirectoryExcludes.Add("DoNotCopy")

        'Setup options (makes sure files aren't deleted for ever)
        Dim _syncOptions As FileSyncOptions = FileSyncOptions.RecycleDeletes Or FileSyncOptions.RecycleOverwrites

        'Do the sync
        Using _sourceProvider As FileSyncProvider = New FileSyncProvider(_sourceId, _sourceDir, _syncFilter, _syncOptions)
            Using _destinationProvider As FileSyncProvider = New FileSyncProvider(_destId, _destDir, _syncFilter, _syncOptions)

                Dim _syncAgent As SyncAgent = New SyncAgent()
                _syncAgent.LocalProvider = _sourceProvider
                _syncAgent.RemoteProvider = _destinationProvider
                _syncAgent.Direction = SyncDirection.UploadAndDownload
                _syncAgent.Synchronize()

            End Using
        End Using

    End Sub

End Module

Tomorrow I’m off to the academic community launch and will have to wait until next week to take a look at it, but it’s available for download:

http://www.microsoft.com/downloads/details.aspx?FamilyId=C88BA2D1-CEF3-4149-B301-9B056E7FB1E6&displaylang=en

Remember to uninstall all previous editions.

I was preparing some demos for the upcoming Academic Community Launch Tour that we - a bunch of Microsoft Student Partners in Austria - are organizing (see http://anuga.at for details). I had just finished a wonderful little app using SQL Compact Edition, Sync Framework, ASP.net MVC and all the funky stuff. Just before I wanted to call it a night I noticed the sync portion of my app suddenly wasn’t working anymore. Somehow I had done something bad and the sync framework wasn’t finding any rows that had changed. This morning I got up and took a closer look underneath the covers of SqlCeClientSyncProvider. This is what I found out.

A word of warning: The information here only applies to Sync Services for ADO.net and is based on Version 2.0 with some help of Reflector. Use the information and my interpretation with caution. Especially beware of the fact that Sync Services for ADO.net in the Sync Framework are handled completely differently than Sync Services for File Systems and Custom Sync Providers.

The sync framework uses anchors to determine what rows have changed. These anchors are compared to the metadata in the table. When creating the tables on the server you probably inserted columns such as InsertTimestamp or UpdateTimestamp. That’s the metadata. Fine, but what happens on the client side in a SQLce database? At first I thought the metadata columns are transferred to the client in the schema (remember the SqlCeClientSyncProvider retrieves the schema to create on the table from the server) and then used for the change tracking there too.

Well not quite. In the app I’m working on I created the SQLce database first (and used used SQLMetal to generate my Linq2Sql classes by the way which is really easy. I’ll do another post in a moment). SQLce doesn’t support triggers so I only created a timestamp column called ‘TS’. When it came to sync’ing the client with a server database, that looked similar but had more metadata columns, I noticed I had no idea how the metadata part worked on the client. (It’s actually quite nice too know that for standard use cases it just works out of the book.)

Enough bla, let’s get to the details. Thanks to a couple of hours with reflector and running queries against my SQLce database I found out where the metadata goes.

Preparing my custom tables for the meta data

When you configure your SqlCeClientSyncProvider you add SyncTables with a couple of options:

foreach (SyncTable _syncTable in syncTables)
{
_syncTable.CreationOption = TableCreationOption.UploadExistingOrCreateNewTable;
_syncTable.SyncTableTransferOption = SyncTableTransferOption.Bidirectional;
m_clientProv.Configuration.SyncTables.Add(_syncTable);
//
// This would recreate the schema for each table on each sync, which resets the timestamps to 0.
// m_clientProv.CreateSchema(_syncTable, null);
}

You can see a line at the bottom that I commented out. I figured that since my client database was in place and I specified UploadExistingOrCreateNewTable that my client database needed to be adapted to include the sync metadata. So I called CreateSchema on the provider which does just that. But you don’t want to create the schema every time, so I should actually move the call into my database initialization code, but I didn’t want to mix my sync code with my DAL code and I didn’t need to. CreateSchema is called at the beginning of the sync session only if the metadata is not found in the table.

Once that method has run all sync tables now have two extra columns: __sysChangeTxBsn and __sysInsertTxBsn. These are initialized with 0 and contain a binary timestamp.

CreateSchema also creates some more tables. First of all it creates a sync subscription table called ‘__sysSyncSubscriptions’. This contains the client’s id and it’s pairing with any server sync providers. Next up is an article table called ‘__sysSyncArticles’. An article is just a sync table, so it contains the table name, the last sent and last received anchors for a specific table.

Once it has done that it just switched on the built-in SQLce change tracking which in turn updates the metadata columns when ever a change occurs.

But wait there is one thing missing. What happens to deleted rows? So far we don’t have any tombstone or archive tables. Actually we do, a table called ‘__sysOcsDeletedRows’ was created that is used by the SQLce change tracking to store deleted rows. It has columns ‘__sysTName’ (the table name), ‘__sysRK’ (the reference key = primary key that was deleted) and the timestamp value when it was deleted and inserted.

(shameless plug at the beginning. Who am I? A Microsoft Student Partner from Austria on his quest to keep up with an ever expanding universe of must-know technologies and working on taking the next step as an intern at Microsoft… A friendly hello to any Microsoft team members reading here, check out the about page. Sorry, for that - life’s all about marketing - now back to the content…)

In the last part (part 1, part 2, part 3, part 4) we saw how to setup the synchronization between a local SQLce database and a server database.

The SyncAgent was setup with two providers, one for the server which had SyncAdapters to specify which tables on the server side are used, which columns hold the metadata. Another for the client that used a local compact edition database and a number of SyncTable objects to setup the sync.

Upon Synchronization the SyncAgent downloads the schema from the server provider, creates the tables on the client if required and transfers changes between the databases since the last synchronization.

And it works, but you will soon notice something is missing.

The client schema

The server provides a schema so that the client can create an identical set of tables to use on the client. But if you attempt to insert a row on the client you will notice that the default values are missing. The primary keys are not specified and foreign key constraints on the client are not available.

This is not required but it would be nice to have them anyway. But there is only one way to do it. You have to manually alter the tables after they have been created by the client sync provider.

Adapting the client schema after it has been automatically created

The SqlCeClientSyncProvider provides a number of events, two of which are: CreatingSchema and SchemaCreated.

In the CreatingSchema handler you should set the primary key for each table using the CreatingSchemaEventArgs:

e.Schema.Tables[_mySchema.TableName].Columns[_mySchema.PK].RowGuid = true;

And you will also want to add foreign key constraints:

e.Schema.Tables[_mySchema.TableName].ForeignKeys.
                        Add(
                            _fkName,
                            _fkSchema.ParentTable,
                            _fkSchema.ParentColumn,
                            _fkSchema.ChildTable,
                            _fkSchema.ChildColumn);

The second event handler for SchemaCreated is used to run custom sql commands inside the same transaction used by the sync provider for creating the tables ensuring data integrity:

SqlCeCommand _cmdAlterTableDef = new SqlCeCommand();

_cmdAlterTableDef.Connection = (SqlCeConnection)e.Connection;

_cmdAlterTableDef.Transaction = (SqlCeTransaction)e.Transaction;

_cmdAlterTableDef.CommandText = _alterCmdString;

_cmdAlterTableDef.ExecuteNonQuery();

Summary

So far we have created an application that synchronizes data between a client database and a server database and done taken a look at some nice-to-have schema changes on the client.

Next-up is the distributed scenario where the server provider is called via a WCF proxy. Hopefully I can post that before Christmas.

(shameless plug at the beginning. Who am I? A Microsoft Student Partner from Austria on his quest to keep up with an ever expanding universe of must-know technologies and working on taking the next step as an intern at Microsoft… A friendly hello to any Microsoft team members reading here, check out the about page. Sorry, for that - life’s all about marketing - now back to the content…)

In this part of the tutorial (part 1, part 2, part 3) we will take a look at a fully functional client application that uses the sync framework to synchronize to the server database. It requires the client application to directly connect to the database. In the next part we will see how to connect via a service layer.

The solution

Any sync framework solution will need at least a sync agent and two sync providers. The sync agent takes care of setting up the providers with the sync agent and configures the options. The providers are database providers. The client is a special SQLce version and the Server provider is the standard DbProvider. When the application wishes to synchronize its data, it calls the Synchronize method on the SyncAgent and it will take care of the rest.

Our goal is to synchronize data changes between server and client regardless of who makes the changes. This functionality is invoked manually from the client.

ActionSyncAgent.Setup()

// Providers

ActionClientSyncProvider _clientSyncProv = ActionClientSyncProvider.Setup();

ActionServerSyncProvider _serverSyncProv = ActionServerSyncProvider.Setup();

// Sync Agent

this.LocalProvider = _clientSyncProv;

this.RemoteProvider = _serverSyncProv;

// Tables

SyncTable _syncTableAction;

SyncTable _syncTableProject;

SyncTable _syncTableUser;

SyncGroup _syncGroupMain = new SyncGroup("AllSync");

_syncTableAction = CreateStandardSyncTable("Action", _syncGroupMain);

_syncTableProject = CreateStandardSyncTable("Project", _syncGroupMain);

_syncTableUser = CreateStandardSyncTable("User",_syncGroupMain);

_clientSyncProv.Configuration.SyncTables.Add(_syncTableAction);

_clientSyncProv.Configuration.SyncTables.Add(_syncTableProject);

_clientSyncProv.Configuration.SyncTables.Add(_syncTableUser);

_serverSyncProv.SetupSyncTableAdapters(

  new[] { _syncTableAction, _syncTableProject, _syncTableUser }); 

Start by creating an instance on ActionClientSyncProvider and ActionServerSyncProvider which have both been setup (details on those later). Both are attached to the respective provider properties on the sync agent.

Next up we specify which tables we want to synchronize. We create a sync table for each table and attach them to the main sync group (to ensure foreign key constraint are handled correctly) using the CreateStandardSyncTable method:

SyncTable _syncTable;

_syncTable = new SyncTable(tableName);

_syncTable.CreationOption = 

   TableCreationOption.DropExistingOrCreateNewTable;

_syncTable.SyncTableTransferOption = 

   SyncTableTransferOption.Bidirectional;

_syncTable.SyncGroup = _syncGroupMain;

return _syncTable;

We specify the CreationOption property as DropExistingOrCreateNewTable from the TableCreationOption enumeration.

The available options are:

CreateNewTableOrFail: creates the table on the client and throws an exception if it exists.

DropExistingOrCreateNewTable: creates the table but drops any existing tables with the same name.

TruncateExistingOrCreateNewTable: creates the table if it doesn’t exist. If it exists is removes all rows on the client table.

UploadExistingOrCreateNewTable: creates the table if it doesn’t exist. If it exists it will upload all rows from the table.

UseExistingTableOrFail: Throws an exception it the client table does not exist.

The SyncTableTransferOption specifies in which direction data can flow. There are four options: UploadOnly, DownloadOnly and Bidirectional are similar. They download a schema and then submit changed data in one or both directions. The fourth option is Snapshot which downloads the set from the server on every synchronization and replaces the client data.

The last call in the SyncAgent’s Setup method is a call to the server provider to set up it’s SyncAdapters using the SyncTables. SyncTable objects are not directly related to SyncAdapter objects but in this case the SyncAdapters use tables that share the same name as the sync tables. For this reason they are passed along:

Setting up SyncAdapters

public void SetupSyncTableAdapters(SyncTable[] syncTables)

{

    foreach (SyncTable _syncTable in syncTables)

    {

        this.SyncAdapters.Add(

              SyncUtil.CreateStandardSyncAdatper(this, _syncTable));

    }

}

The SyncUtil.CreateStandardSyncAdapter method handles the details. They are similar for all my tables therefore extracted into a utility method.

public static SyncAdapter CreateStandardSyncAdatper

  (ActionServerSyncProvider prov, SyncTable syncTable)

{

    SyncAdapter _syncAdapterAction = 

      SyncUtil.CreateStandardBuilder(prov, syncTable.TableName).ToSyncAdapter();

    _syncAdapterAction.TableName = syncTable.TableName;

    return _syncAdapterAction;

}

public static SqlSyncAdapterBuilder CreateStandardBuilder

  (ActionServerSyncProvider prov, string tableName)

{

    SqlSyncAdapterBuilder _builder = 

       new SqlSyncAdapterBuilder(prov.Connection as SqlConnection); 

    _builder.TableName = tableName;

    _builder.TombstoneTableName = _builder.TableName + "_Archive";

    _builder.SyncTableTransferOption = SyncTableTransferOption.Bidirectional;

    _builder.CreationTrackingColumn = "InsertTimestamp";

    _builder.UpdateTrackingColumn = "UpdateTimestamp";

    _builder.DeletionTrackingColumn = "DeleteTimestamp";

    _builder.CreationOriginatorIdColumn = "InsertID";

    _builder.UpdateOriginatorIdColumn = "UpdateID";

    _builder.DeletionOriginatorIdColumn = "DeleteID";

    return _builder;

}

The SyncAdapter is created using a builder class. The class (SqlSyncAdapterBuilder) requires the connection to the database.

Be aware: The connection property on the provider is inherited from the base class (DbServerSyncProvider) and is of type IDbConnection. But the SqlSyncAdapterBuilder expects a SqlConnection. We are inferring that the property will return a SqlConnection but I consider this bad style and usually this would be a warning light for a design problem. But since this is only an example I’ll just continue.

The builder properties specify the names of the columns, of the table and the option for transferring which needs to correspond to the SyncTables.

The client provider

The client provider basically just inherits from the SqlCeClientSyncProvider and provides a connection string to the local SQLce database file. Well not quite, but that’s a part of the next part of the tutorial.

(shameless plug at the beginning. Who am I? A Microsoft Student Partner from Austria on his quest to keep up with an ever expanding universe of must-know technologies and working on taking the next step as an intern at Microsoft… A friendly hello to any Microsoft team members reading here, check out the about page. Sorry, for that - life’s all about marketing - now back to the content…)

In the first and second part of the tutorial I gave an overview on the Microsoft Sync Framework and the Sync Services for ADO.net. What it can do for you, what tools you are given and now we are going to take a look at the first set of providers that Microsoft has written.

The Sync Services for ADO.net let you synchronize data between databases. It’s a common requirement for online/offline scenarios. I’m sure a lot of people have used RDA in the past. Now SSA provides a simple way to achieve such scenarios.

Prerequisites

You need to install the the Sync Framework (as of writing available in CTP1 Refresh) and the Sync Services for ADO.net. Both are available here.

Additionally you need the SQL Server Compact Edition (formerly Everywhere Edition, current Version 3.5). If you have Visual Studio 2008 installed the libraries required are installed as well.

For the server part of this tutorial I have used SQL Server Express Edition.

The demo project

As a demo for the first part of the tutorial I have created a small time tracking application. You can track user’s time spent on different projects.

The project has a simple database schema, shown above. It’s the normal way I would design such a database, except for two things. The primary key chosen here is a Guid. In such simple examples I would usually use an integer, but since I knew it was going to be a replication scenario I chose a unique identifier (see below for some explanation) and secondly I usually automatically include a timestamp column (called timestamp) in addition to the datetime columns STC, STM (system-time created, modified). Read on to see why I left it out.

The client is a simple GUI for entering and viewing data.

Preparing the database for synchronization.

(A lot of more detailed information on the topics covered here can be found in the Sync BOL.)

I will go for a maximum of flexibility in this tutorial and will enable bidirectional inserts, updates, and deletes with conflict detection. To do this your database needs to fulfill a few properties:

Primary Key of unique column: This sounds fairly normal. Any good database design has a unique PK, but you may have encountered a lot of integer primary keys. These are great for local systems, easy to use and great for writing manual sql statements. But for a replication scenario you can’t really sync a sequential integer key very well because it has a state (the sequence). You need a globally unique ID. SQL provides the type uniqueidentifier which is ideal for this.

Side-note: A lot of people might argue against using Guids, the main reasons usually being a performance hit (Guids are not sequential, so the row may not be inserted  at the end of the table page), a maintenance hit (ever tried writing a lot of sql statements that lookup guid columns) and other impacts.

My point of view is that you can counteract a lot of the impacts and personally the prime example for synchronizing data is the Active Directory, which uses a Guid as it’s object Guid. But check out the BOLs for a number of alternatives.

In the example db, the primary keys are all unique identifiers. I have chosen to use NEWSEQUENTIALID() as a default - instead of NEWID() - to ensure a sequence when inserting rows locally (it has a privacy trade-off though,  read about it here and it will not help us when the client syncs it’s of out sequence data to the server).

Side-note: What if you already have data in your database? That’s a tedious task, but it can be done. I once did it similar to the way described here.

Track update time and track insert time: In SQL you can add a timestamp column to each row which will update on every change. Great, so this is your update time. But what about the insert time? Well add another timestamp. Wait, SQL won’t let you do this (and you can’t actually tell SQL not to update the timestamp field - I don’t actually know why). So the BOLs suggest adding a Binary(8) column and put a timestamp field in there. Can’t think of anything that’s wrong with that, so we’ll just go ahead an do that.

Track delete time: Sounds similar to the above, but once the row is deleted you can’t really use the deleted column of the table. So we create something Microsoft calls a tombstone table. I personally have seen the word archive more often but they basically talk about the same thing. You have a live table with data that is still alive and then you have a dead table where all you deleted table lives. (Some people might actually go so far as to include a pregnant or premature table, to include data that is not alive yet - tax rates for some future time - but others opt for specifying a validity time range in the live table for this case. But this is a topic for another post.)

In our example we create an archive table for each table that we would like to sync. They are almost identical to the live table. BOLs suggests removing the insert and update time columns and adding a delete column. I personally wouldn’t do that because you are losing information that you may need in an enterprise scenario (i.e. auditing). Keep the insert and update columns, just turn the update column into a bigint column like the insert, and add a timestamp column as the delete time column.

Then you need a trigger to catch deletes to the live table and write them to the tombstone table. Nothing spectacular here, check the BOLs if you need advice.

CREATE TRIGGER [dbo].[TRI_Action_Delete] ON [dbo].[Action] FOR DELETE
AS
BEGIN
    SET NOCOUNT ON
    DELETE FROM [Action_Archive]
        WHERE [ActionID] IN (SELECT [ActionID] FROM deleted)
    INSERT INTO [Actions].[dbo].[Action_Archive]
           ( [...] all columns [...] )
     SELECT
           [...] all columns [...]
     FROM deleted
    SET NOCOUNT OFF
END

Track client ID for inserts & Track client ID for deletes: As to not piggy-back changes that a client made back to the client you need to keep track of who made the change. Each client in the sync framework has a client id and you can track this by adding a InsertID, UpdateID and DeleteID to the respective tables.

In an enterprise database you may already have similar columns to comply with auditing rules and may be able to re-use those columns.

Indexes

The BOLs include some advice on creating indexes on the tables. For now you can ignore them but in a production environment you will speed up your data synchronization considerably by adding the appropriate indexes.

Summary

So far we have (only) prepared the server-side database for synchronizing with another database. This is probably about 50% of the work need to be done. In the next part we will take a look at the code that uses the Sync Framework.

(shameless plug at the beginning. Who am I? A Microsoft Student Partner from Austria on his quest to keep up with an ever expanding universe of must-know technologies and working on taking the next step as an intern at Microsoft… A friendly hello to any Microsoft team members reading here, check out the about page. Sorry, for that - life’s all about marketing - now back to the content…)

The first parts of the tutorial will cover Sync Services for ADO.net. They work similar to but they extend the sync runtime and provide some providers for databases. Read part 1 for an introduction to Sync Framework.

The basics

The sync agent is the runtime which takes the two providers. The client provider has a set of sync tables that specify which tables on the server it would like to synchronize. You specify if the table is created on the client and how, the type of synchronization (e.g. download only, bidirectional…) and wether or not it belongs to a sync group.

(Sync groups specify groups of tables that are analyzed and synchronized in accordance to foreign key constraints.)

The sync adapters specify which columns on the server database tables are used for tracking changes and the sync options. They describe how data is synced.

The server provider also needs a set of commands, one of them being the new anchor command which specifies how to retrieve a new anchor on the server side.

The database

In the diagram you can see that there the data store and metadata are one components (instead of two as shown in the last part). This means you are tracking the metadata (change tracking) in the data store itself. In this concrete scenario there are additional columns in the tables that specify when something was added, changed or deleted and who did it.

This is next step in the tutorial. Preparing the server database for synchronization.

Technorati Tags: Microsoft Sync Framework,SQL Server Compact Edition

At Tech-Ed in Barcelona this year Soma Somasegar announced the Microsoft Sync Framework. It provides a set of tools and best practices for enabling online/offline and sharing applications with any kind of data store and over any kind of transport channel.

At the core the SF has a synchronization runtime that takes care of the main sync’ing algorithm and provides a number of hooks to allow system-specific providers and custom conflict handling.

The sync providers enumerate or apply changes for their specific client system data store and can be built to transform between different schemas of the source and destination systems. In addition to the data store they also need some place to store the metadata required for synchronization.

Three providers are currently available from Microsoft:

- Sync Services for ADO.net: Similar to RDA (Remote Data Access) this provider will allow synchronizing databases. On the client side the SQL Server Compact Edition is utilized.

- Sync Services for File Systems: Check out the new version of the SyncToy (currently beta) which allows easy change tracking between folders.

- Sync Service for FeedSync: formerly known as SSE - Simple Sharing Extensions, this provider allows the communication of sync changes between hosts using RSS/ATOM feeds.

The SF targets a number of participants: full (Anywhere you can run your own custom sync executables - PCs…), partial (dumb devices without custom code but they can store metadata - USB drives…) and simple (consume changes but cannot sync own changes back to the partner - RSS readers).

The runtime itself is simple. It runs on one host and talks to two providers. The runtime itself does not work in a distributed environment. If you want to synchronize across a communication channel of some kind you will have to do it yourself. For example by writing a proxy provider that communicates with the actual provider (implemented as a WCF service).

In the next few weeks I will be posting a set of walk-through articles on the Sync Framework, so check back for more.

More information about the sync framework at http://msdn.microsoft.com/sync.