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)
- (Version2)
- Table1(ColumnA, ColumnB, NewColumnC)
- NewTable2(ColumnX, ColumnY, ColumnZ)
In my opinion it's better to use:
Version1_YourScope:
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!
