How To Re-Provision a scope in Microsoft Sync Framework

B"H

What happens when you are using Sync Framework and you need to make a change to the schema/structure of the database?

Whether you are just adding a column, or you are changing the primary key on a table in the scope or you need to add a table to the scope. If you change a table in the scope, you invalidate the scope and it will no longer sync with its peers. Even if you update all of the peers so that their schemas all match, the scope config data will still be out of sync as will the stored procedures and maybe even the triggers and the _tracking tables.

So here is my proposal. I have written a function in c# to drop the tracking infrastructure for the tables that have changed. Then a second function that uses the framework to create a new scope but only letting it create the infrastructure for the new tables. Ten it copies the scope config data to the old schema and manually deletes the new schema info.

Before you run this function you need to apply the schema changes to all the peers. What I do is create a script of the changes I want to make and then run them on every peer. You need to run the function ReProvision once for every peer.

Here they are:

     public void ReProvision(SqlConnection conn, string scopeName, IEnumerable<string> tablesThatChanged)  
     {  
       var serverConfig = new SqlSyncScopeProvisioning();  
       var scopeDesc = SqlSyncDescriptionBuilder.GetDescriptionForScope(scopeName, conn);  
       scopeDesc.ScopeName += "_temp";  
   
       foreach (var tableName in tablesThatChanged)  
       {  
         DropTrackingForTable(conn, tableName);  
   
         var bracketedName = string.Format("[{0}]", tableName);  
         scopeDesc.Tables.Remove(scopeDesc.Tables[bracketedName]);  
   
         var tableDescription = SqlSyncDescriptionBuilder.GetDescriptionForTable(bracketedName, conn);  
         scopeDesc.Tables.Add(tableDescription);  
       }  
         
       serverConfig.PopulateFromScopeDescription(scopeDesc);  
       serverConfig.SetCreateProceduresDefault(DbSyncCreationOption.Skip);  
       serverConfig.SetCreateTableDefault(DbSyncCreationOption.Skip);  
       serverConfig.SetCreateTrackingTableDefault(DbSyncCreationOption.Skip);  
       serverConfig.SetCreateTriggersDefault(DbSyncCreationOption.Skip);  
       serverConfig.SetPopulateTrackingTableDefault(DbSyncCreationOption.Skip);  
       serverConfig.SetCreateProceduresForAdditionalScopeDefault(DbSyncCreationOption.Skip);  
   
       foreach (var tableName in tablesThatChanged)  
       {  
         var bracketedName = string.Format("[{0}]", tableName);  
   
         serverConfig.Tables[bracketedName].CreateProcedures = DbSyncCreationOption.Create;  
         serverConfig.Tables[bracketedName].CreateTrackingTable = DbSyncCreationOption.Create;  
         serverConfig.Tables[bracketedName].PopulateTrackingTable = DbSyncCreationOption.Create;  
         serverConfig.Tables[bracketedName].CreateTriggers = DbSyncCreationOption.Create;  
       }  
       serverConfig.Apply(conn);  
   
       using (SqlCommand comm1 = new SqlCommand(@"  
         declare @config_id uniqueidentifier, @config_data xml  
         SELECT @config_id=sc.config_id, @config_data=sc.config_data  
         From scope_config sc Join [scope_info] si on si.scope_config_id=sc.config_id  
         WHERE si.scope_name = @scope_name + '_temp'  
   
         Update [scope_config] Set config_data=@config_data  
         From scope_config sc Join [scope_info] si on si.scope_config_id=sc.config_id  
         WHERE si.scope_name = @scope_name  
   
         Delete [scope_config] WHERE config_id=@config_id;  
         Delete [scope_info] WHERE scope_config_id=@config_id;  
       ", conn))  
       {  
         conn.Open();  
         comm1.Parameters.AddWithValue("@scope_name", scopeName);  
         comm1.ExecuteNonQuery();  
         conn.Close();  
       }  
     }  
   

1:      public void DropTrackingForTable(SqlConnection conn ,string tableName)
2:      {
3:        SqlCommand comm;
4:        StringBuilder sb = new StringBuilder();
5:        //Drop tracking table & triggers
6:        sb.AppendFormat(@"
7:                IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[{0}_tracking]') AND type in (N'U'))
8:                DROP TABLE [dbo].[{0}_tracking]
9:                IF EXISTS (SELECT * FROM sys.triggers WHERE object_id = OBJECT_ID(N'[dbo].[{0}_insert_trigger]'))
10:                DROP TRIGGER [dbo].[{0}_insert_trigger]
11:                IF EXISTS (SELECT * FROM sys.triggers WHERE object_id = OBJECT_ID(N'[dbo].[{0}_delete_trigger]'))
12:                DROP TRIGGER [dbo].[{0}_delete_trigger]
13:                IF EXISTS (SELECT * FROM sys.triggers WHERE object_id = OBJECT_ID(N'[dbo].[{0}_update_trigger]'))
14:                DROP TRIGGER [dbo].[{0}_update_trigger]
15:                ",tableName);
16:        //Drop Procedures
17:        foreach (string procName in new string[] { "delete", "deletemetadata", "insert", "insertmetadata", "update", "updatemetadata", "selectrow", "selectchanges" })
18:        {
19:          sb.AppendFormat(@"
20:                  IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[{0}_{1}]') AND type in (N'P', N'PC'))
21:                  DROP PROCEDURE [dbo].[{0}_{1}]
22:                  ", tableName, procName);
23:        }
24:        using (comm = new SqlCommand(sb.ToString(), conn))
25:        {
26:          conn.Open();
27:          comm.ExecuteNonQuery();
28:          conn.Close();
29:        }
30:      }
31:

If anyone would like me to explain what is going on in these functions, just post a comment or send me an email. If I know someone is actually reading this thing I will put more effort into it.