Sunday, June 6, 2010

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.

6 comments:

  1. Thank you, this is very useful.
    Can you explain one thing? As I see you take config_data from "old" scope and copy it to the "new". I suppose that if you have made some changes to tables, so config_data must change too. Just cann`t find where changes in config_data take place?

    ReplyDelete
  2. I`ve found that config_data changes if "serverConfig.SetCreateTrackingTableDefault(DbSyncCreationOption.Create);" command was running. So you need to recreate all _tracking tables to have a valid config_data. Or to make changes by hands. Am I right?

    ReplyDelete
  3. Hello...

    Does your method here, require a full redownload of the sync metadata ?

    i'm facing to redownload 4 GB of syncdata if the metadate is recreated...

    ReplyDelete
  4. I'am testing this function and it works but after I change this

    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.sync_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.sync_scope_name = @scope_name

    Delete [scope_config] WHERE config_id=@config_id;
    Delete [scope_info] WHERE scope_config_id=@config_id;
    ", conn))



    There is a problem with the sync_scope_name

    If I find how not to send all data again I will use this for my project. Thanks (Using sync framework 2.1)

    ReplyDelete
  5. What difference does these 2 functions accomplish than just deprovisioning and provisioning scope again?

    It appears that the data does a full pull with an additional full dataset comparison with both approach.

    Or am I doing something incorrectly?

    ReplyDelete
  6. Thank you very much this was much useful in my application.
    I am using such method and sync function is working with ease.
    I have one question.
    When I am scanning my dll with CAT .NET it is showing me SQL injection vulnerability on the line where we are passing tablename.
    Can you please suggest some way so that I can remove this SQL injection issue.
    Thanks

    ReplyDelete