Sample - SQL Express Client Sync Sample

Question

We have just posted a new sync sample to our Code Gallery that demonstrates how to enable offline data collection applications to synchronize a SQL Express client database to another SQL Server database using the Synchronization Services for ADO.NET library.  In this scenario, SQL Express client databases can be used as client databases to a central ADO.NET enabled database.  Unlike the existing peer-to-peer synchronization SQL Express provider sample which you may have seen, this sample is optimized for a hub-and-spoke topology.

In this sample, two tables (orders and order_details) are on the server database and also on the local client database.  The sample synchronizes edits to these table to keep their data identical.
This sample demonstrates:

• Using SQL Express to cache changes for a client application.
• A customized SQLExpressClientSyncProvider class that wraps around Microsoft.Synchronization.Data.Server.DbServerSyncProvider.

I would like to point out that even though we have done a fair amount of testing in house with this sample, it is still only posted as a sample.  In the future we certainly plan of including a fully supported version of a SQL Express client provider within a future release of Sync Services for ADO.NET. 

 

With that, we certainly want to hear your feedback on this sample and please feel free to post comments to this forum.

 

Liam Cavanagh

Answer 1

 

Just great  I'll have a look...

 

Thanks a lot,

 

George B

Answer 2

Good job!!!

 

Now I load and analyze this example ...

THANKS!

Answer 3

Great job!

 

I have 23 tables with more than 10,000 records each, in server and client, and I need Bidirectional sync between them. (This is my first sync condition).

 

I'm looking for 'Batch Mode' and found a forum that shows a demo for batch server records to client. (follow).

 

http://www.syncguru.com/projects/SyncServicesDemoBatching.aspx

 

Is there a way to do that in SqlExpressClientSyncProvider to send client records to server in batch mode?

 

Thanks in advance!

 

 

Answer 4

Jaime,

 

We do not currently have any samples posted that demonstrate how one should go about extending the Express samples to support batching.  However, the patterns demonstrated in Rafik's batching demo would be the same insomuch that you would need to implement a proc that generates new anchors based on the batch size variables. Your app would then cal that proc when generating new anchors. 

 

I think that it would be nice to update the samples to demonstrate this and appreciate your feedback. 

 

Sean Kelley

Program Manager

Microsoft

 

 

Answer 5

SQL Express Client Sample seems to only work for Bidirection

I am in the process of making an engine that will sync between sql 2008 server and sql 2008 express.

The sample has worked great ... but when i do "download only" it doesn't apply the changes.

It recognizes what to change.. it just doesn't apply them.

Any ideas??

Thanks,

Roger

Answer 6

Hi Roger,

Nice catch BTW. This is indeed a sample implementation bug. Basically we use a DbServerSyncProvider internally to implement our express client sync provider. When the sync direction is set to anything but bidirectional, its reaches the server sync provider without modification. So changes that was supposed to be “applied” reaches the express ServerSyncProvider with direction set to “DownloadOnly”. Since a server cannot apply changes to itself in a DownloadOnly mode it skips the changes. This explains the hokey behavior you are seeing (ie changes are enumerated but not applied).

 

Please fix the ApplyChanges method implementation in the sampl's SqlExpressClientSyncProvider.cs file by copying the following.

 

 

public override SyncContext ApplyChanges(SyncGroupMetadata groupMetadata, DataSet dataSet, SyncSession syncSession)

{

  //Map SyncDirection from client POV to our internal server POV

  foreach (SyncTableMetadata tableMetadata in groupMetadata.TablesMetadata)

  {

    if (tableMetadata.SyncDirection == SyncDirection.DownloadOnly || tableMetadata.SyncDirection ==   SyncDirection.Snapshot)

    {

      //This SyncDirection DownloadOnly/Snapshot is from a Client point of view. But our client is inturn a Server provider.   Hence switch this to UploadOnly

      tableMetadata.SyncDirection = SyncDirection.UploadOnly;

    }

    else if (tableMetadata.SyncDirection == SyncDirection.UploadOnly)

    {

      //This SyncDirection UploadOnly is from Client POV. But our client is inturn a Server provider. Hence switch this to DownloadOnly

      tableMetadata.SyncDirection = SyncDirection.DownloadOnly;

    }

  }

  SyncContext syncContext = _dbSyncProvider.ApplyChanges(groupMetadata, dataSet, syncSession);

  foreach (SyncTableMetadata table in groupMetadata.TablesMetadata)

  {

    SetTableReceivedAnchor(table.TableName, groupMetadata.NewAnchor);

  }

  return syncContext;

}

 

 

Sample on the offlicial link would be refreshed once we rerun all our tests over this change.

Answer 7

Hi, guys...

While you're at it, you may want to consider supporting non-timestamp (i.e. datetime) anchors (I believe the "ordinary" sync providers do this). I modified a couple of lines in the original source and it seems to work. This is what I did:

 

public override SyncAnchor GetTableReceivedAnchor(string tableName)

{

........

if (anchorVal == null || anchorVal == System.DBNull.Value)

return new SyncAnchor();

 

--- BEGIN MODIFICATION ---

else

{

return new SyncAnchor(SerializeAnchorValue(anchorVal));

}

}

private byte[] SerializeAnchorValue(object anchorVal)

{

MemoryStream serializationStream = new MemoryStream();

new System.Runtime.Serialization.Formatters.Binary.BinaryFormatter().Serialize(serializationStream, anchorVal);

byte[] ret = serializationStream.ToArray();

serializationStream.Dispose();

return ret;

 

}

private object DeserializeAnchorValue(byte[] anchor)

{

MemoryStream serializationStream = new MemoryStream(anchor);

object ret = new System.Runtime.Serialization.Formatters.Binary.BinaryFormatter().Deserialize(serializationStream);

serializationStream.Dispose();

return ret;

}

--- END MODIFICATION ---

 

public override SyncAnchor GetTableSentAnchor(string tableName)

{

........

if (anchorVal == System.DBNull.Value)

return new SyncAnchor();

 

--- BEGIN MODIFICATION ---

else

return new SyncAnchor(SerializeAnchorValue(anchorVal));

--- END MODIFICATION ---

 

}

 

 

public override void SetTableReceivedAnchor(string tableName, SyncAnchor anchor)

{

string queryStr = "UPDATE " + AnchorTableName +

" SET ReceivedAnchor = @anchor WHERE TableName = '" + tableName + "'";

SqlCommand anchorCom = new SqlCommand(queryStr);

 

--- BEGIN MODIFICATION ---

anchorCom.Parameters.AddWithValue("@anchor", DeserializeAnchorValue(anchor.Anchor));

--- END MODIFICATION ---

 

...

}

 

public override void SetTableSentAnchor(string tableName, SyncAnchor anchor)

{

string queryStr = "UPDATE " + AnchorTableName +

" SET SentAnchor = @anchor WHERE TableName = '" + tableName + "'";

SqlCommand anchorCom = new SqlCommand(queryStr);

 

--- BEGIN MODIFICATION ---

anchorCom.Parameters.AddWithValue("@anchor", DeserializeAnchorValue(anchor.Anchor));

--- END MODIFICATION ---

...

}

 

BTW, I believe the db peer sync provider also needs this kind of modification (at least it did in the CTP)?

Answer 8

Thank you so much for this sample.  Using this as a starting point, I have been able to get synchronization working between SQL Express 2005 on both client and server for all our databases (at least as a prototype). 

 

We have now decided to upgrade to SQL Server Express 2008 and all still works as expected.  However, reading about "Integrated Change Tracking" makes me wonder if that is the way we should be heading, that is, possibly removing the need for triggers and improving performance.  I don't expect you to write a new sample showing this (though that would be awesome), but I just wanted to know your thoughts:

• Is "Integrated Change Tracking" a good approach or is this only useful for the Compact Edition?
• Would we be able to eliminate triggers on both the client and the server, or just the server?
• Would we still need the "guid" and "anchor" tables on the client database, or is there a way around this?

Thank you,

 

Kacee

Answer 9

Hello experts,

 

has someone any idea why i get as System.InvalidCastException at the code line

Code Snippet

 

syncAgent.LocalProvider = clientSyncProvider;

 

in the file SyncEngine.cs?

 

Full error description:

System.InvalidCastException: Microsoft.Synchronization.Data.ClientSyncProvider

   at Microsoft.Synchronization.SyncAgent.set_LocalProvider(SyncProvider value)

   at SQLExpressProviderSample.SyncEngine.Synchronize() in C:\Documents and Settings\Sample\SyncEngine.cs:line 73

   at SQLExpressProviderSample.SyncForm.buttonSynchronize_Click(Object sender, EventArgs e) in C:\Documents and Settings\Sample\SyncForm.cs:line 110

 

To add and delete random items at client and server going well.

I work with SQL Server 2005 and SQL Express 2005.

 

Thanks,

Alex

Answer 10

 

Alex,

I already replied to this question on another thread. Basically it could be that you havea  corrupt installation of SynServices or you are referencing wrong dlls.

Answer 11

Hi Kacee,

Answers to your questions.

 

• Is "Integrated Change Tracking" a good approach or is this only useful for the Compact Edition?
  • Integrated change tracking will negate the need for all the triggers to track changes so yes its a good approach. We havent published our recommended way, aka sample, demonstrating how you can use CT yet.
• Would we be able to eliminate triggers on both the client and the server, or just the server?
  • If you use CT on both server and client then yes you can negate triggers on both.
• Would we still need the "guid" and "anchor" tables on the client database, or is there a way around this?
  • You will need them. The client is the one which tracks what it has sent/received to the server for each table. This is just to hold the sync "anchors" which CT knows nothing about.

Please note that since we havent tested all possible combinations with CT alone, there could be some issues that might change the above answers esp ans #1.

Answer 12

We have been mostly successful in getting the synchronization to work with integrated change tracking (so, no more triggers, tombstone tables, or extra fields per table).  However, updates on the server often do not synchronize to the client, but after researching it, we found that it also does not always work in your sample.

 

To repro the bug:

1. Start with new databases (both ProviderSample_Local and ProviderSample_Remote)
2. Insert an item on the client, update that same item on the client, then synchronize.
3. Update the item on the server and then synchronize.

Now, the client table will not have the update from the server (though the progress form stated that it was successful and no conflicts were detected).  Note that I think there are other ways to get this to happen, but this is the most straight forward.  Also note that we've seen this with SQL Server 2005 and SQL Server 2008 as well as when the server is local and when it is on a different machine.

 

Thanks,

 

Kacee

Answer 13

Hi Kacee,

Good to hear that SQLExpressProviderSample is working for you between SQL Express 2005 on both client and server.

We tried to execute the same but we are facing issues on that, I have started a new thread for my issue. Please give me your suggestions for this or send me a working copy of SQLExpressProviderSample to my mail id([e-mail]).

My thread Link:

http://forums.microsoft.com/sync/ShowPost.aspx?PostID=4092167&SiteID=75

Thanks in Advance....

Answer 14

Hi Kacee,

 

Could you please update me with the sample which is working for you with SQL Express/Server 2005 on both ends.

 

The sample which I have downloded from:

 

http://code.msdn.microsoft.com/Release/ProjectReleases.aspx?ProjectName=sync&ReleaseId=1200

 

doesn't work in the scenario which you have mentioned above:

 

1. Start with new databases (both ProviderSample_Local and ProviderSample_Remote)
2. Insert an item on the client, update that same item on the client, then synchronize.
3. Update the item on the server and then synchronize.

Now, the client table will not have the update from the server (though the progress form stated that it was successful and no conflicts were detected).

 

Answer 15

Hi Maha,

 

 

We have been facing an issue with client not being updated back to the server. We have used the sql express sync provider which was available along with the sample. We have developed our project half way through based on the sql express sync sample but getting stuck with some problem issues which I am sure would get resolved with the final release from MS for sql express sync support.

 

Pls do give an update as to when the acutal release from MS for the sql express sync provider would be available so that we could plan accordingly.

 

many thanks

Answer 16

mahjayar,

In regard to your reply stating "We havent published our recommended way, aka sample, demonstrating how you can use CT yet." on 9/4/2008 - has this changed yet?
Do you have an example w/ SQL Express 2008 client using "Integrated Change Tracking"?

If not, could you outline some guidelines on modifying existing example or implementing it in a different manner alltogether?

Thanks:
G. Stoynev

Answer 17

Hi mahjayar.

I was did your example, but when i do "download only" it doesn't apply the changes.

What am I doing error? Can you show the example?

Thanks.

---

Marcos Antonio Aguiar Jr

Answer 18

Hi ,

Please add this section in SQLExpressClientSyncProvider.cs file,

 

public override SyncContext ApplyChanges(SyncGroupMetadata groupMetadata, DataSet dataSet, SyncSession syncSession)

{

  //Map SyncDirection from client POV to our internal server POV

  foreach (SyncTableMetadata tableMetadata in groupMetadata.TablesMetadata)

  {

    if (tableMetadata.SyncDirection == SyncDirection.DownloadOnly || tableMetadata.SyncDirection ==   SyncDirection.Snapshot)

    {

      //This SyncDirection DownloadOnly/Snapshot is from a Client point of view. But our client is inturn a Server provider.   Hence switch this to UploadOnly

      tableMetadata.SyncDirection = SyncDirection.UploadOnly;

    }

    else if (tableMetadata.SyncDirection == SyncDirection.UploadOnly)

    {

      //This SyncDirection UploadOnly is from Client POV. But our client is inturn a Server provider. Hence switch this to DownloadOnly

      tableMetadata.SyncDirection = SyncDirection.DownloadOnly;

    }

  }

  SyncContext syncContext = _dbSyncProvider.ApplyChanges(groupMetadata, dataSet, syncSession);

  foreach (SyncTableMetadata table in groupMetadata.TablesMetadata)

  {

    SetTableReceivedAnchor(table.TableName, groupMetadata.NewAnchor);

  }

  return syncContext;

}

Please let me know if it works or send me your query in detail so that i can assist you in this further.

Answer 19

 Hi.

thanks Jawahar,  functioning properly

---

Marcos Antonio Aguiar Jr

Answer 20

Hi
Can any one please suggest if there is a way to insert random test data in the tables used by this sample
say for e.g. i want to create 10000 rows in JobList and PropertyDetails is there a way to do that through some option in SQL Server or Visual STudio ?
(except for writing manual tSQL code)
thanks

Answer 21

Liam,

A few quick questions:

- Does MSF V2CTP2 provide a Microsoft solution for the SQLExpressClientSyncProvider sample solution ?  (see notes below)
- Any idea when the CTP2 might be released?
- If you want to give me (us) an early Christmas present ... create an sample that Sync(s) a SQL2008 database to a SQL2008Express database using change tracking (on both sides) dynamically creating syncTableAdapters?  (ok ... this really a statement, not a question)

Meanwhile, I will explore the new CTP2... the batching and conflict management look very interesting.  I also like that I can install it side by side with MSF V1.

FYI, we are going live with the a MSF V1 and modified version of the SQLExpressClientSyncProvider solution this month.  Wish us luck.  Nothing like the real deal.

Thanks,

Roger

(source: http://www.microsoft.com/downloads/details.aspx?FamilyID=89adbb1e-53ff-41b5-ba17-8e43a2e66254&displaylang=en )

Microsoft Sync Framework 2.0 CTP2

(clip)

The major new features included in this CTP (CTP2) are:

• New database providers (SqlSyncProvider and SqlCeSyncProvider) : Enable hub-and-spoke and peer-to-peer synchronization for SQL Server, SQL Server Express, and SQL Server Compact. Sync Framework automatically creates all of the commands that are required to communicate with each database. You do not have to write synchronization queries as you do with other providers. The providers support: flexible initialization options; batching of changes based on data size; and maintenance tasks, such as metadata cleanup and server database restore.

Answer 22

Hi,

I would like to implement a simple resolver conflict for the sample : https://code.msdn.microsoft.com/Release/ProjectReleases.aspx?ProjectName=sync&ReleaseId=1200

When i subscribe to ApplyChangeFailed event on client and server providers and set ApplyChangeFailedEventArgs Action to specified states.

But i don't have any effect when i use  ApplyAction.RetryWithForceWrite (the server and the client keeps their values) and loop when i use ApplyAction.Continue.

The Command
// update row command
SqlCommand updOrderDetailsCmd = new SqlCommand();
updOrderDetailsCmd.CommandType = CommandType.StoredProcedure;
updOrderDetailsCmd.CommandText = "sp_order_details_applyupdate";
updOrderDetailsCmd.Parameters.Add("@order_id", SqlDbType.Int);
updOrderDetailsCmd.Parameters.Add("@order_details_id", SqlDbType.Int);
updOrderDetailsCmd.Parameters.Add("@product", SqlDbType.VarChar, 100);
updOrderDetailsCmd.Parameters.Add("@quantity", SqlDbType.Int);
updOrderDetailsCmd.Parameters.Add("@" + SyncSession.SyncClientIdHash, SqlDbType.Int);
updOrderDetailsCmd.Parameters.Add("@" + SyncSession.SyncLastReceivedAnchor, SqlDbType.Binary, 8);
updOrderDetailsCmd.Parameters.Add("@" + SyncSession.SyncRowCount, SqlDbType.Int).Direction = ParameterDirection.Output;
updOrderDetailsCmd.Parameters.Add("@" + SyncSession.SyncForceWrite, SqlDbType.Bit);
adapterOrderDetails.UpdateCommand = updOrderDetailsCmd;

The stored procedure
[dbo].[sp_order_details_applyupdate] ( 
  @sync_last_received_anchor binary(8) ,
  @sync_client_id_hash int ,
  @sync_row_count int out,
        @order_id int,
        @order_details_id int = NULL ,
        @product varchar(100) = NULL ,
        @quantity int = NULL,
        @sync_force_write bit )       
as
    update [order_details]
        set [order_details_id] = @order_details_id,
            [product] = @product,
         [quantity] = @quantity,
         [update_originator_id] = @sync_client_id_hash
     where (@sync_force_write = 1 OR (update_timestamp <= @sync_last_received_anchor or update_originator_id =  @sync_client_id_hash)) and [order_id] = @order_id 
    set @sync_row_count = @@rowcount

What's wrong?

Thanks