Sign in
Microsoft.com
 
Microsoft
 
 
 

locked
Confused about local schema creation RRS feed

 > 

  • Question

  • Question
    Sign in to vote
    0
    Sign in to vote

    I have a client-server style sync requirement, where 10 clients need to sync to a centralized server.  No peer-to-peer.

    I've followed several examples and tutorials on Sync Framework 2.1.  My current implementation is using the following approach:

    1. Create a DbSyncScope with all the tables to sync
    2. Provision the Server using SqlSyncScopeProvisioning
    3. using SetCreateTableDefault(DbSyncCreationOption.Skip) as I have already built the schema on the server
    4. Then I provision the client SQL CE using a SqlCeSyncScopeProvisioning with SetCreateTableDefault(DbSyncCreationOption.CreateOrUseExisting) because I do NOT have the schema built in the CE instance
    5. Now that this is done I sync with SyncOrchestrator and SqlSyncProvider (server) and SqlCeSyncProvider (client)

    First of all, I think I may be doing this incorrectly, I suspect that what I've done above is for a peer-to-peer setup.  I've seen other examples that don't seem to provision the client, they just do a sync.

    The above scenario is working, that is it sync changes back and forth.  The problem I'm having is that the scheme on the CE client is not being created properly, or more accurately I need to make some changes (set defaults, add constraints, etc)

    While searching for solutions I have across this article:

    http://msdn.microsoft.com/en-us/library/microsoft.synchronization.data.sqlserverce.sqlceclientsyncprovider.aspx

    I was VERY happy to see that you can hook into the schema creation and change table and column properties, for example:

    e.Schema.Tables["Customer"].Columns["CustomerId"].RowGuid = true;


    PERFECT!  But wait.... schema creation during sync?  I thought I already created the schema when I provisioned the client??

    At this point I'm confused about how to proceed.  It seems that I'm missing a fundamental concept here and likely doing something wrong.

    I could really use a little guidance here.  I'm forced to work fast and skim through things in order to hit a deadline for a tradeshow, otherwise I would take more time and learn more about syncfx, but right now I just need to get past this step so I can have my CE instance properly setup for synching.

    Hopefully someone can help me out.

    Friday, October 14, 2011 7:44 PM

Answers

  • Question
    Sign in to vote
    0
    Sign in to vote

    you cant mix and match offline provider and collaboration provider.

    if you're using SqlSyncProvider on the server, you have to use either another SqlSyncProvider on the client or SqlCeSyncProvider.

    the SqlSyncProvider/SqlCeSyncProvider requires explicitly provisioning the databases. so you have to use SqlSyncScopeProvisioning/SqlCeSyncScopeProvisioning and you have to do post-provisioning to apply the FKs and Defaults outside of the framework. after provisioning, you can write some extra code to add the other schema stuff you need. provisioning need only to happen once. provisioning is just needed to prepare the database for synchronization. once you've provisioned, you can start synching and subsequent synching dont require provisioning again.

    the SchemaCreated event only applies to SqlCeClientSyncProvider. in the offline provider, the SyncAgent can detect if the client has not been initialized/provisioned and will create the schema depending on the TableCreation options set for the table (UseExistingTableOrFail, CreateNewTableOrFail, etc...). If schema creation is required, it will fire the SchemaCreated event after it does its provisioning. any code you put in the SchemaCreated event is  post-provisioning. Sync framework has done its stuff and is just informing you its done via the SchemaCreated event. whatever you write in the SchemaCreated event, it doesnt know.


    Saturday, October 15, 2011 1:32 AM

All replies

  • Question
    Sign in to vote
    0
    Sign in to vote

    SqlCeSyncProvider and SqlCeClientSyncProvider are different.

    The latter is what is commonly referred to as the offline provider and this is the provider used by the Local Database Cache project item in Visual Studio. This provider works with the DbServerSyncProvider and SyncAgent and is used in hub-spoke topologies.

    The one you're using is referred to as a collaboration provider or peer-to-peer provider (which also works in a hub-spoke scenario). SqlCeSyncProvider works with SqlSyncProvider and SyncOrchestrator and has no corresponding Visual Studio tooling support.

    both providers requires provisioning the participating databases.

    The two types of providers provisions the sync objects required to track and apply changes differently. The SchemaCreated event applies to the offline provider only. This get's fired the first time a sync is initiated and when the framework detects that the client database has not been provisioned (create user tables and the corresponding sync framework objects).

    the scope provisioning used by the other provider dont apply constraints other than the PK. so you will have to do a post-provisioning step to apply the defaults and constraints yourself outside of the framework.

    Saturday, October 15, 2011 12:48 AM
  • Question
    Sign in to vote
    0
    Sign in to vote

    I'm so glad you answered me, I've just finished going through your blog for the 3rd time!  :)

    I've read your response a couple times and am unclear on a few points:

    "both providers requires provisioning the participating databases.
    ...
    This get's fired the first time a sync is initiated and when the framework detects that the client database has not been provisioned (create user tables and the corresponding sync framework objects)."

    Are you saying that I need to use SqlCeSyncScopeProvisioning to provision thet tracking data on the offline DB BEFORE I try to sync?  Or are you saying that the first time I sync it will handle this for me?  Additionally are you saying that the first time it syncs it will create the user schema ("my tables") and raise the CreatingSchema and SchemaCreated evented?

    "the scope provisioning used by the other provider dont apply constraints other than the PK. so you will have to do a post-provisioning step to apply the defaults and constraints yourself outside of the framework."

    Are you saying that if I use the collaboration objects I will need to update the schema outside the framework or are you saying that I will need to do this no matter WHICH approach I take?  I'm confused because I've seen examples that use the CreatingSchema event to modify the schema.

    Is this overview of steps accureate:

    1. Design server user schema
    2. Create scope configuration and provision server with SqlSyncScopeProvisioning
    3. Use SyncAgent to sync the client SQLce
    4. SyncFX detects first sync and builds user schema and provisions tracking artifacts
    5. I subscribe to schema creation events and make changes to local schema
    6. Done, data is syncing and life is good

    Do I have that correct?

    Thank you so much for your reply, I was losing hope of ever getting this to work.

    Saturday, October 15, 2011 1:06 AM
  • Question
    Sign in to vote
    0
    Sign in to vote

    you cant mix and match offline provider and collaboration provider.

    if you're using SqlSyncProvider on the server, you have to use either another SqlSyncProvider on the client or SqlCeSyncProvider.

    the SqlSyncProvider/SqlCeSyncProvider requires explicitly provisioning the databases. so you have to use SqlSyncScopeProvisioning/SqlCeSyncScopeProvisioning and you have to do post-provisioning to apply the FKs and Defaults outside of the framework. after provisioning, you can write some extra code to add the other schema stuff you need. provisioning need only to happen once. provisioning is just needed to prepare the database for synchronization. once you've provisioned, you can start synching and subsequent synching dont require provisioning again.

    the SchemaCreated event only applies to SqlCeClientSyncProvider. in the offline provider, the SyncAgent can detect if the client has not been initialized/provisioned and will create the schema depending on the TableCreation options set for the table (UseExistingTableOrFail, CreateNewTableOrFail, etc...). If schema creation is required, it will fire the SchemaCreated event after it does its provisioning. any code you put in the SchemaCreated event is  post-provisioning. Sync framework has done its stuff and is just informing you its done via the SchemaCreated event. whatever you write in the SchemaCreated event, it doesnt know.


    Saturday, October 15, 2011 1:32 AM
  • Question
    Sign in to vote
    0
    Sign in to vote

    Great, thank you for the clarification.  I had come across SyncAgent several times but I read somewhere that "SyncAgent" became "SyncOrchestrator" with a version increase so I dismissed it as "old stuff".  It sounds like SyncAgent is exactly what I need.

    Thanks again.

    Saturday, October 15, 2011 1:42 AM