How to pass parameter to Microsoft Sync 2.1 generated Stored procedures

Question

I am using Microsoft sync framework 2.1 version

we are trying to implement database versioning i.e. if there is a table schema change in the server database, all or some client should be still able to sync their data without doing same schema changes or without taking an updated DB. At the higher level we need to maintain multiple application version for different clients with same sever DB.

I am trying to pass application version as parameter to all stored procedures so that i can handle data versions for multiple clients. I am able to pass Parameter to "select_chagnes" stored procedure .. but My question is how to pass version number to all sync generated stored procedures so that a parameter @version should be generated in all sync generated procedures.

OR

Any Suggestions to maintain client specific data are welcome our primary goal is allowing existing clients to sync their database without taking latest database changes. so that we can clients can have multiple application versions which points to same server database.

Answer 1

Remark The solution I have don't work if you delete columns or tables which are provisioned by older clients. If you want to remove columns you need to do that is multiple phases. First upgrade everyone to version 1. If all clients are upgraded you can remove columns and tables.

Possible solution

If I understand you well, you want to have one scope or template having multiple provisioned configurations.

YourScope:

• (Version1)
  • Table1(ColumnA, ColumnB)
• (Version2)
  • Table1(ColumnA, ColumnB, NewColumnC)
  • NewTable2(ColumnX, ColumnY, ColumnZ)

In my opinion it's better to use:

Version1_YourScope:

• Table1(ColumnA, ColumnB)

Version2_YourScope:

• Table1(ColumnA, ColumnB, NewColumnC)
• NewTable2(ColumnX Columny ColumnZ)

So in this case you don't have to handle the versions inside Sync Framework procedures, now you need to handle the versions outside by giving the right clients the right set of scopes.

What to do:

This change requires some changes during provisioning. Having scopes overlapping each other gives some problems:

• You must have two BulkTypes for Table1 (1 without NewColumnC and 1 type with this new column inside)
• You must have two Select Changes for Table1
• You must have two BulkInsert sp's for Table1
• You want to have just one set of triggers for Table1
• You want to have just one tracking table for Table1
• ...

During provisioning you probably uses SqlSyncScopeProvider.Apply(). There is also a function which returns the script instead of applying the script: SqlSyncScopeProvider.Script(). This return the provisioning script.

So you can do something like this:

1: Use following provisioning settings to make overlapping scopes possible:

_scopeProvisioning.SetCreateProceduresForAdditionalScopeDefault(DbSyncCreationOption.Create);
_scopeProvisioning.SetCreateTableDefault(DbSyncCreationOption.Skip);
_scopeProvisioning.SetCreateProceduresDefault(DbSyncCreationOption.CreateOrUseExisting);
_scopeProvisioning.SetCreateTrackingTableDefault(DbSyncCreationOption.CreateOrUseExisting);
_scopeProvisioning.SetCreateTriggersDefault(DbSyncCreationOption.CreateOrUseExisting);

2: Get provision script

var builder = new StringBuilder(_scopeProvisioning.Script());

3: For each table rename <tablename>_<procedure/bulktype> to <scopename>_<tablename>_<procedure/bulktype>

// Rename <tablename>_selectchanges to <scopename>_<tablename>_selectchanges and also all other stored procedures and bulk type
builder = builder.Replace(String.Format("CREATE PROCEDURE [{0}_selectchanges", table.Name), String.Format("CREATE PROCEDURE [sync].[{1}_{0}_selectchanges", table.Name, scope.Name));
builder = builder.Replace(String.Format("SelChngProc=\"[{0}_selectchanges", table.Name), String.Format("SelChngProc=\"[sync].[{1}_{0}_selectchanges", table.Name, scope.Name));
builder = builder.Replace(String.Format("[{0}_BulkType]", table.Name), String.Format("[{1}_{0}_BulkType]", table.Name, scope.Name));
builder = builder.Replace(String.Format("[{0}_selectrow]", table.Name), String.Format("[{1}_{0}_selectrow]", table.Name, scope.Name));
builder = builder.Replace(String.Format("[{0}_insert]", table.Name), String.Format("[{1}_{0}_insert]", table.Name, scope.Name));
builder = builder.Replace(String.Format("[{0}_update]", table.Name), String.Format("[{1}_{0}_update]", table.Name, scope.Name));
builder = builder.Replace(String.Format("[{0}_delete]", table.Name), String.Format("[{1}_{0}_delete]", table.Name, scope.Name));
builder = builder.Replace(String.Format("[{0}_insertmetadata]", table.Name), String.Format("[{1}_{0}_insertmetadata]", table.Name, scope.Name));
builder = builder.Replace(String.Format("[{0}_updatemetadata]", table.Name), String.Format("[{1}_{0}_updatemetadata]", table.Name, scope.Name));
builder = builder.Replace(String.Format("[{0}_deletemetadata]", table.Name), String.Format("[{1}_{0}_deletemetadata]", table.Name, scope.Name));
builder = builder.Replace(String.Format("[{0}_bulkinsert]", table.Name), String.Format("[{1}_{0}_bulkinsert]", table.Name, scope.Name));
builder = builder.Replace(String.Format("[{0}_bulkupdate]", table.Name), String.Format("[{1}_{0}_bulkupdate]", table.Name, scope.Name));
builder = builder.Replace(String.Format("[{0}_bulkdelete]", table.Name), String.Format("[{1}_{0}_bulkdelete]", table.Name, scope.Name));

4: For each table which has overlap with an already existing scope change CREATE TRIGGER to ALTER TRIGGER because they already exist in database

builder = builder.Replace(String.Format("CREATE TRIGGER [{0}_insert_trigger]", table.Name), String.Format("ALTER TRIGGER [{0}_insert_trigger]", table.Name));
builder = builder.Replace(String.Format("CREATE TRIGGER [{0}_update_trigger]", table.Name), String.Format("ALTER TRIGGER [{0}_update_trigger]", table.Name));
builder = builder.Replace(String.Format("CREATE TRIGGER [{0}_delete_trigger]", table.Name), String.Format("ALTER TRIGGER [{0}_delete_trigger]", table.Name));

5: Execute new script. Note that the script contains a lot of GO statements. You need to execute everything between two GO's in one SqlCommand.

string[] seperatedScript = GetProvisionScriptSplittedOnGOstatement(builder.ToString);
foreach(string command in seperatedScript)
{
   new SqlCommand(command, connection).ExecuteNonQuery(); 
  // make sure you dispose SqlCommand correctly. Not in this example
}

6: Make sure old clients provisions only Version1_YourScope and new clients only provisions Version2_YourScope so client side there is no overlap between multiple versions.

If you are using templates because you want to pass filter parameters you need to be aware of the following things:

• different filtered columns for a table which is mentioned in multiple scopes causes problems because the triggers are not aware of multiple scopes using multiple filtered columns
• Provisioning a new filtered column requires a new column to an already existing tracking table

Good Luck!