none
Batching - looking for some general advice RRS feed

  • Question

  • For my App (windows application using SQL Ce, that syncs over IIS hosted WCF with central db, n-tier, using SQL server 2008 R2 with integrated change tracking on the server) I want to enable batching, as the database is about 300 MB and I don't want to run into time outs (from the wcf) or out of memory errors from the clients (especially for the first sync, were all data has to be downloaded). 

    If I got this right, there are two ways (or more?) to implement batching:

    1) one general, that supports all providers (MSDN: http://msdn.microsoft.com/en-us/library/bb902828.aspx#). There I would have to create a stored proc to handle the number of changes that I want to process in one batch unit. This looks a bit complicated.

    2) The other option is specific for the scenario SQLServer and SQLCe (MSDN: http://msdn.microsoft.com/en-us/library/dd918908%28SQL.105%29.aspx), so only if you use  SqlSyncProvider, SqlCeSyncProvider, or DbSyncProvider, that will work. The support for batching is kind of "built in, into the providers". Basically a MemoryDataCacheSize is specified, that sets the maximum amount of memory, which is used, before files are spooled to disk.

    For my app I want to go with option two. Regarding this I have some questions.

    MSDN does not provide VB code for this, does that mean VB is not supported ? ("MSDN:No code example is currently available or this language may not be supported.")

    I am looking for some (preferable VB) code examples to enable batching in my scenario.

    I would like to show the user the progress of the sync process at the batch level. At the moment I show the progress, by using SessionStateChangedEventArgs and SessionProgressEventArgs, handling syncAgent.StateChanged and syncAgent.SessionProgress. But if I would like to show info about current tables number of rows etc. (Like the LocalDatabaseCache Wizard does). I think I must then use DbBatchAppliedEventArgs or DbBatchSpooledEventArgs and add a handler to the current batch processing. Are there any code examples?

    Any other advice, useful links etc. is very much appreciated.

    Thanks

    Christian

    Thursday, September 1, 2011 2:58 PM

Answers

  • a) yes, you cannot use Local Database Cache Wizard as it uses the SqlCeClientSyncProvider/DbServerSyncProvider/SyncAgent

    SqlSyncProvider/SqlCeSyncProvider/SyncOrchestrator has a provisioning API to create the change tracking mechanism.

     

    b) yes, however you will have to write different query than the one on the sample because you're using Sql Server Change tracking.

    • Marked as answer by chl-h Monday, September 5, 2011 4:06 PM
    Friday, September 2, 2011 11:37 AM
    Moderator

All replies

  • VB is supported. its just that some of the samples by MS are sometimes written just for C#. If you check the documentation under Synchronizing Databases->Database Synchronization Scenarios -> Synchronizing Sql Server and Sql Server Compact, you will find that the tutorials has codes for both C# and VB.

    as for your batching requirements, i would suggest you go with the SqlCeSyncProvider/SqlSyncProvider combo and just use the built-in batching support.

    Please note thought that you cannot directly migrate or upgrade your existing SqlCeClientSyncProvider/DbServerSyncProvider with Sql Change Tracking to the newer providers.

    the newer providers also uses their own change tracking and does not work with Sql Server Change Tracking.

    Friday, September 2, 2011 1:26 AM
    Moderator
  • Hi June,

    OK, if I got this right for my current setup with SqlCeClientSyncProvider and DbServerSyncProvider there is no built-in batch-supprt, because this are the "old providers". Built-in batch support is for the "newer providers" SqlSyncProvider and SqlCeSyncProvider.

    So my alternatives are:

    a) for SqlSyncProvider and SqlCeSyncProvider:  As I can not migrate/update I will have to setup synchronisation for the project again. I cannot use the LocalDataCache Wizard?

    > the newer providers also uses their own change tracking and does not work with Sql Server Change Tracking.
    So I will have to use custom change tracking?

    b) If I stay with my Setup using SqlCeClientSyncProvider and DbServerSyncProvider, I will have to implement custom batch handling as described in http://msdn.microsoft.com/en-us/library/bb902828.aspx#

    thanks
    christian
    Friday, September 2, 2011 10:34 AM
  • a) yes, you cannot use Local Database Cache Wizard as it uses the SqlCeClientSyncProvider/DbServerSyncProvider/SyncAgent

    SqlSyncProvider/SqlCeSyncProvider/SyncOrchestrator has a provisioning API to create the change tracking mechanism.

     

    b) yes, however you will have to write different query than the one on the sample because you're using Sql Server Change tracking.

    • Marked as answer by chl-h Monday, September 5, 2011 4:06 PM
    Friday, September 2, 2011 11:37 AM
    Moderator