locked
Provisioning and modifying database after production RRS feed

  • Question

  • Hi,

    I am using SFX 2.1 server DB is SQL Server 2008 and client is Compact 3.5.  I have been using sample code from Microsoft to provision the database (I just get all tables) This works fine but I am concerned what happens after the app goes live and I add a new column or delete an exisiting column.  How is this dealt with?

    Also I see that to provision/deprovision one can use

    SqlSyncScopeDeprovisioning or SqlCeSyncScopeDeprovisioning

    What is the difference? Does the latter just provision the client DB?

     

    Monday, October 17, 2011 10:21 AM

All replies

  • which provider are you using?

    Sync Framework dont do schema syncs. So you cant simply dynamically add or remove columns.

    SqlSyncScopeDeprovisioning is used for Sql Server, Sql Express or Sql Azure

    SqlCeSyncScopeDeprovisioning is for Sql CE

    Monday, October 17, 2011 10:53 AM
  • Hi June,

     

    I was hoping that it is you who answers this as I have been reading a lot of your articles.  I am at an early stage opf the app development and not sure what to use or what works in what scenario and would love your input. 

     

    Basically, we are going to have less than 20 users with laptops using the client app.  Database server is SQL Sqrver 2008 and the DB already exists.  I have an option to use SQL Server Compact or Express (I have decided Compact for ease of deployment but might need to use stored procedures, not sure I made right choice yet)

    Data exchange is from server to client.  The clients are read only and don't change anything.

    I have started using SqlSyncProvider and SqlCeSyncProvider The code works but I maybe using the wrong providers.

    I have also been trying to sync Foreign keys for days and even tried to add them manually but not worked so far.  Please could you let me know what the best providers are in my scenario, how to get foreign keys into the client DB and finally how to work around when data changes (my app will be self updating for new versions of the app but I need to know what code I need to run to decouple clkient from the old schema and pick up the new schema)

    Many thanks and looking forward to your response

    Cheers
    H

     

    Monday, October 17, 2011 12:20 PM
  • how big are your client databases in terms of size and number of rows? do you have filters for each client?
    • Edited by JuneT Monday, October 17, 2011 12:43 PM
    Monday, October 17, 2011 12:42 PM
  • Thankls June,

    The database revolves round a document table which will have max 10,000 records (will never grow more than that) currently it has 4000 records and is 200MB, so half a gig is smore than enough.  The document table contains file path and we keep the files on the server and these I will sync too using file sync

    No I cannot use filters.  All client apps receive the exact data from the server (the data in teh server is modified by a web app and so the users want to get the latest data before they do document search on teh client application)

    Cheers
    H

     

     

    Monday, October 17, 2011 1:32 PM
  • Hi June,

     

    Have you missed my last comment? 

    Monday, October 17, 2011 3:18 PM
  • both offline providers (SqlCeClientSyncProvider/DbServerSyncProvider/SyncAgent) or the collaboration/peer-to-peer providers (SqlSyncProvider/SqlCeSyncProvider/SyncOrchestrator) require provisioning to prepare the databases to participate in synchronization. both dont support schema synchronization as well. so if you change schema, these changes will not be propagated and may even result to sync errors. during provisioning neither providers support creating indeces, stored procedures, triggers and other constraints such as FKs and Defaults.

    to create the indeces, FKs and defaults, you can either pre-create the table with all the objects you require and simply configure the provisioning to skip creating the table objects since they already exists.

    if you choose Sync Fx to create the tables itself, you will have to write some post-provisioning code to create the additional database objects and these codes will run outside the context of Sync Framework (it doesnt know about these objects).

    the reason i ask how big your client databases is that the only supported option for handling schema changes is to reinitialize the client databases.

    if your clients connect via LAN, the you can continue using the current providers youre using.

    when the schema changes, you will have to deprovision the scope on your server and create a new one. then create a new client scope in SQL Ce. After creating this first Sql Ce client SDF, use the generate snapshot option to create another SDF that you will use to initialize all other clients. Once you have this snapshot SDF, you would then distribute this to the clients, replacing the old SDFs.

    If you have been to my blog, you would have probably seen some workarounds on handling schema changes.

    Tuesday, October 18, 2011 1:40 AM
  • Fantastic resposne June.  Thank you so much.

    Please bear with me, I'll ask one final question just to make sure I do everything correctly.

    1.  Since my DB is a little too large for my liking to distribute, I am wondering if it is possible to do this in code (application will run this code every time it tries to sync)

    If(Server schema version doesn't match client schema version)
    {
         Deprovision server();
         Deprovision client();
         Delete client database();
         Reprovision server();
         Reprovision client();
         Sync();
    }

    That would be ideal because changes will not happen often when they do that will clear the client and start from scratch.  It will avoid errors.

     

    Tuesday, October 18, 2011 7:56 AM
  • that should work. in addition, i would suggest you enable batching if you have large number of rows.
    Tuesday, October 18, 2011 11:52 PM
  • Thanks June.

    How do In test for this in code
          If(Server schema version doesn't match client schema version)

    Any pointers on how to enable batching, links, code etc.

     

    Thank you again

    H

    Wednesday, October 19, 2011 10:52 AM
  • the documentation that comes with framework contains steps to enable batching (lookup How to: Deliver Changes in Batches (SQL Server). its basically just setting the memorydatacachesize property.

     

    as for schema versioning, you can simply create a table that records a schema version. everytime you upgrade the schema, you would increment the version. use this version number to compare with what's in the server.

    Wednesday, October 19, 2011 12:28 PM
  • It is a shame schema versioning is not built in but SFX is fairly new technology so hopefully one day.

    May I shamelessly ask another question.  I am using 2 tier and cannot afford the complixity of N tier.  I need some sort of progress indicator (selecting changes to table XXXX, applying insrts to table yyyy etc woudl suffice) Can you point me to any sample/code.

    Thank you so much June.

     

    Thursday, October 20, 2011 2:49 PM
  • subscribe to the SyncProgress event of each provider, then report the progress from there.
    Thursday, October 20, 2011 4:05 PM
  • Thank you June
    Friday, October 21, 2011 8:14 AM