locked
MSFx 2.0 : Reprovisioning script RRS feed

  • General discussion

  • If you want to add or remove a column, you'll need to Re-Provision the two SQL Servers...

    If you don't want to re-download all the sync metadata... then you need a H4ck0r script... 

    Wednesday, October 20, 2010 1:42 PM

All replies

  • Just like this one:

     

    private void ReProvisionServer(SqlConnection Conn)
    {
      try
      {
        SqlSyncScopeProvisioning serverConfig = SetupConfiguration(true, Conn);
        var script = serverConfig.Script(Conn.Database);
    
        foreach (var table in _settings.TablesToSync)
        {
          SqlCommand dropStoredProc = new SqlCommand(@"
          IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[" + table.Key + @"_delete]') AND type in (N'P', N'PC'))
          DROP PROCEDURE [dbo].[" + table.Key + @"_delete];
    
          IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[" + table.Key + @"_deletemetadata]') AND type in (N'P', N'PC'))
          DROP PROCEDURE [dbo].[" + table.Key + @"_deletemetadata];
    
          IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[" + table.Key + @"_insert]') AND type in (N'P', N'PC'))
          DROP PROCEDURE [dbo].[" + table.Key + @"_insert];
    
          IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[" + table.Key + @"_insertmetadata]') AND type in (N'P', N'PC'))
          DROP PROCEDURE [dbo].[" + table.Key + @"_insertmetadata];
    
          IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[" + table.Key + @"_selectchanges]') AND type in (N'P', N'PC'))
          DROP PROCEDURE [dbo].[" + table.Key + @"_selectchanges];
    
          IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[" + table.Key + @"_selectrow]') AND type in (N'P', N'PC'))
          DROP PROCEDURE [dbo].[" + table.Key + @"_selectrow];
    
          IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[" + table.Key + @"_update]') AND type in (N'P', N'PC'))
          DROP PROCEDURE [dbo].[" + table.Key + @"_update];
    
          IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[" + table.Key + @"_updatemetadata]') AND type in (N'P', N'PC'))
          DROP PROCEDURE [dbo].[" + table.Key + @"_updatemetadata];", Conn);
    
          dropStoredProc.ExecuteNonQuery();
        }
    
        foreach (Match item in Regex.Matches(script, @"(CREATE PROCEDURE[\s\S]+?GO)"))
        {
          SqlCommand createStoredProc = new SqlCommand(item.Value.Substring(0, item.Value.Length - 2), Conn);
          createStoredProc.ExecuteNonQuery();
        }
    
        SqlCommand getScopeGuid = new SqlCommand("SELECT scope_config_id FROM scope_info WHERE scope_name = '" + _settings.ScopeName + "'", Conn);
    
        var reader = getScopeGuid.ExecuteReader();
        if (reader.HasRows)
        {
          reader.Read();
          var id = reader.GetGuid(0);
          reader.Close();
    
          SqlCommand updateScope = new SqlCommand("UPDATE scope_config SET config_data = " + Regex.Match(script, @"(\[config_data\]\) VALUES \([^,]+,)([\s\S]+?)(\))").Groups[2].Value + " WHERE config_id = '" + id + @"';", Conn);
    
          updateScope.ExecuteNonQuery();
        }
      }
      catch (Exception ex)
      {
        lblStatus.Text = ex.Message;
        lblStatus.ToolTip = ex.Message + ((ex.InnerException != null) ? "\n\n" + ex.InnerException.Message : "");
      }
    }
    
    private SqlSyncScopeProvisioning SetupConfiguration(bool setPopulate, SqlConnection Conn)
    {
      DbSyncScopeDescription scopeDesc = new DbSyncScopeDescription(_settings.ScopeName);
    
      foreach (var TBL in CurrentSettings.TablesToSync)
      {
        Collection<string> columnsToInclude = new Collection<string>();
        foreach (var Col in TBL.Value)
        {
          columnsToInclude.Add(Col);
        }
    
        if (columnsToInclude.Count > 0)
        {
          DbSyncTableDescription customerContactDescription = SqlSyncDescriptionBuilder.GetDescriptionForTable(TBL.Key, columnsToInclude, Conn);
          scopeDesc.Tables.Add(customerContactDescription);
        }
        else
        {
          DbSyncTableDescription customerDescription = SqlSyncDescriptionBuilder.GetDescriptionForTable(TBL.Key, Conn);
          scopeDesc.Tables.Add(customerDescription);
        }
      }
    
      //-------
      SqlSyncScopeProvisioning serverConfig = new SqlSyncScopeProvisioning(scopeDesc);
      serverConfig.SetCreateTableDefault(DbSyncCreationOption.Skip);
      if (setPopulate) serverConfig.SetPopulateTrackingTableDefault(DbSyncCreationOption.Create);
      else serverConfig.SetPopulateTrackingTableDefault(DbSyncCreationOption.Skip);
      serverConfig.ObjectSchema = "dbo";
    
      foreach (var TBL in CurrentSettings.TablesToSync)
      {
        try
        {
          SqlCommand comm = new SqlCommand("select top 1 * from " + TBL.Key + "_tracking");
          comm.Connection = Conn;
          comm.ExecuteNonQuery();
    
          serverConfig.Tables[TBL.Key].CreateTrackingTable = DbSyncCreationOption.Skip;
        }
        catch (Exception)
        {
          serverConfig.Tables[TBL.Key].CreateTrackingTable = DbSyncCreationOption.Create;
        }
      }
    
      // Specify which column(s) in the Customer table to use for filtering data, 
      // and the filtering clause to use against the tracking table.
      // "[side]" is an alias for the tracking table.
      //serverConfig.Tables["Sales.Customer"].AddFilterColumn("CustomerType");
      //serverConfig.Tables["Sales.Customer"].FilterClause = "[side].[CustomerType] = 'Retail'";
    
      return serverConfig;
    }
    

    Wednesday, October 20, 2010 1:43 PM