How to change client schema during provisioning?

I'm rushing (never a good thing) to get Sync Framework up and running for a "offline support" deadline on my project. We have a SQL Express 2008 instance on our server and then will deploy SQLCE to the clients. Clients will only sync with server, no peer-to-peer.

So far I have the following working:

1. Server schema setup
2. Scope created and tested
3. Server provisioned
4. Client provisioned w/ table creation

I've been very impressed with the relative simplicity of all of this. Then I realized the following:

• Schema created through client provisioning to SQLCE does not setup default values for uniqueidentifier types.
• FK constraints are not created on client

Here is the code that is being used to create the client schema (pulled from an example I found somewhere online)

static void Provision()
{
    SqlConnection serverConn = new SqlConnection(
        "Data Source=xxxxx, xxxx; Database=xxxxxx; " +
            "Integrated Security=False; Password=xxxxxx; User ID=xxxxx;");

    // create a connection to the SyncCompactDB database
    SqlCeConnection clientConn = new SqlCeConnec开发者_C百科tion(
        @"Data Source='C:\SyncSQLServerAndSQLCompact\xxxxx.sdf'");

    // get the description of the scope from the SyncDB server database
    DbSyncScopeDescription scopeDesc = SqlSyncDescriptionBuilder.GetDescriptionForScope(
        ScopeNames.Main, serverConn);

    // create CE provisioning object based on the scope
    SqlCeSyncScopeProvisioning clientProvision = new SqlCeSyncScopeProvisioning(clientConn, scopeDesc);
    clientProvision.SetCreateTableDefault(DbSyncCreationOption.CreateOrUseExisting);

    // starts the provisioning process
    clientProvision.Apply();
}

When Sync Framework creates the schema on the client I need to make the additional changes listed earlier (default values, constraints, etc.).

This is where I'm getting confused (and frustrated): I came across a code example that shows a SqlCeClientSyncProvider that has a CreatingSchema event. This code example actually shows setting the RowGuid property on a column which is EXACTLY what I need to do. However, what is a SqlCeClientSyncProvider?! This whole time (4 days now) I've been working with SqlCeSyncProvider in my sync code. So there is a SqlCeSyncProvider and a SqlCeClientSyncProvider?

The documentation on MSDN is not very good in explaining what either of these.

I've further confused whether I should make schema changes at provision time or at sync time?

How would you all suggest that I make schema changes to the client CE schema during provisioning?

---

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.

---

While researching solutions without using SyncAgent I found that the following would also work (in addition to my commented solution above):

1. Provision the client and let the framework create the client [user] schema. Now you have your tables.
2. Deprovision - this removes the restrictions on editing the tables/columns
3. Make your changes (in my case setting up Is RowGuid on PK columns and adding FK constraints) - this actually required me to drop and add a column as you can't change the "Is RowGuid" property an existing columns
4. Provision again using DbSyncCreationOption.CreateOrUseExisting