none
Sync 2.0 RTM Conditional Synchronization RRS feed

  • Question

  • Hi,

     

    I am using Sync 2.0 RTM for Hub-n-spoke scenario and I am using SQL ent as server and sql express as my client DB. I am using SqlSyncProvider for client as well as sever side provider. I want to restrict the synchronization data with a conditional parameter through the framework created sprocs like xxx_selectchanges. How can I pass this parameter to framework specific sprocs?

    Monday, January 4, 2010 11:13 AM

Answers

  • If I understand right, you want to get only a subset of data during the sync.
    Please look at the example in: How to: Configure and Execute Collaborative Synchronization (SQL Server) that describes how you filter.What it is doing is creating a scope with the filter you are specifying and the procs will be generated accordingly.
    However if you have a different filter, then you need to create another scope for it.

    Also remember that if your client was synching with one filter (scope) and then thereafter wants to change the filter and sync again, your client would need to be reinitialized and sync with this other scope which has the new filter.
    <!---->

    What Rudi described is that after provisioning the database, you cannot conditionally send paramters to the procs so as to achieve filtered rows to flow down to the client.
    This posting is provided AS IS with no warranties, and confers no rights
    Wednesday, January 6, 2010 9:24 PM

All replies

  • Hi,

    I do not think that is supported.
    In theory you could set the SyncParameters, these will be copied over into your SQL parameters:
    http://msdn.microsoft.com/en-us/library/cc761645(SQL.105).aspx

    However, I think the SqlSyncProvider does not allow you to access these parameters. From what I can gather this has all been closed down in the RelationalSyncProvider. Well, let say: I can't find a documented way how to set these parameters.

    • Proposed as answer by Rudi - Euricom Wednesday, January 6, 2010 1:14 PM
    Monday, January 4, 2010 4:08 PM
  • If I understand right, you want to get only a subset of data during the sync.
    Please look at the example in: How to: Configure and Execute Collaborative Synchronization (SQL Server) that describes how you filter.What it is doing is creating a scope with the filter you are specifying and the procs will be generated accordingly.
    However if you have a different filter, then you need to create another scope for it.

    Also remember that if your client was synching with one filter (scope) and then thereafter wants to change the filter and sync again, your client would need to be reinitialized and sync with this other scope which has the new filter.
    <!---->

    What Rudi described is that after provisioning the database, you cannot conditionally send paramters to the procs so as to achieve filtered rows to flow down to the client.
    This posting is provided AS IS with no warranties, and confers no rights
    Wednesday, January 6, 2010 9:24 PM
  • If I understand right, you want to get only a subset of data during the sync.
    Please look at the example in: How to: Configure and Execute Collaborative Synchronization (SQL Server) that describes how you filter.What it is doing is creating a scope with the filter you are specifying and the procs will be generated accordingly.
    However if you have a different filter, then you need to create another scope for it.
    I have exactly the scenario described as scenario 1 at the referenced link.

    Scenario 1:

    • Scope 1 is sales-WA. This scope includes: products; orders, with a filter of state=WA; and order_details, with a filter of state=WA.

    • Scope 2 is sales-OR. This scope includes: products; orders, with a filter of state=OR; and order_details, with a filter of state=OR.

    In this scenario, the entire products table is shared by both scopes. The orders and order_details tables are in both scopes, but the filters do not overlap; therefore the scopes do not share rows from these tables.

    However, the example code does not actually show you how to achieve this.

    When I have a scope, say scope-WA, and I add a filter to the order table and the order_details like:

    SqlSyncProvider provider = this.CreateLocalProvider("scope-WA");
    
    SqlSyncScopeProvisioning serverConfig = CreateLocalSyncScopeProvisioning(provider);
                    
    if (serverConfig.ScopeExists(provider.ScopeName, (System.Data.SqlClient.SqlConnection)provider.Connection))
        return;
    
    DbSyncScopeDescription scopeDesc = new DbSyncScopeDescription(provider.ScopeName);
    
    scopeDesc.Tables.Add(SqlSyncDescriptionBuilder.GetDescriptionForTable("orders", (System.Data.SqlClient.SqlConnection)provider.Connection));
    
    scopeDesc.Tables.Add(SqlSyncDescriptionBuilder.GetDescriptionForTable("order_details", (System.Data.SqlClient.SqlConnection)provider.Connection));
    
    serverConfig.PopulateFromScopeDescription(scopeDesc);
    
    serverConfig.Tables["orders"].AddFilterColumn("State");
    serverConfig.Tables["orders"].FilterClause = "[side].[State] = 'WA'";
    serverConfig.Tables["order_details"].AddFilterColumn("State");
    serverConfig.Tables["order_details"].FilterClause = "[side].[State] = 'WA'";
    
    serverConfig.SetCreateTableDefault(DbSyncCreationOption.Skip);
    
    serverConfig.Apply(provider.Connection as SqlConnection);
    

    then I get tracking tables added (order_tracking, order_details_tracking and I get stored procedures named (amongst others):

    orders_selectchanges
    order_details_selectchanges

    both of which contain a conditional WHERE that includes ...[side].[State] = 'WA'.

    So that's all well and good for the state of WA. But how do you add another scope named "scope-CA"? which is going to fail with an error that the tracking table already exists (just as stated by the OP) and if that was overcome (as suggested by another poster) by the fact that the stored procedures already exist.

    Put much more simply, Microsoft, please show me example code that meets your Scenario 1 with two scopes.

    Thank you.

    • Edited by Speedware Thursday, February 18, 2010 2:07 PM Fixed mistake in example code
    Thursday, February 18, 2010 2:05 PM
  • hi speedware,

    try this when adding new scope. on your 2nd and subsequent scopes, make sure to skip creating most of the objects:

    //skip creating the user tables
     serverConfig.SetCreateTableDefault(DbSyncCreationOption.Skip);
    
    //skip creating the change tracking tables
    serverConfig.SetCreateTrackingTableDefault(DbSyncCreationOption.Skip);
    
    //skip creating the change tracking triggers
    serverConfig.SetCreateTriggersDefault(DbSyncCreationOption.Skip);
    
    //skip creating the insert/update/delete/selectrow SPs including those for metadata
    serverConfig.SetCreateProceduresDefault(DbSyncCreationOption.Skip);
    
    //create new SelectChanges SPs for selecting changes for the new scope
    //the new SelectChanges SPs will have a guid suffix
    serverConfig.SetCreateProceduresForAdditionalScopeDefault(DbSyncCreationOption.Create); 
                
    
    serverConfig.Apply(serverConn);
    Friday, February 19, 2010 4:47 PM
    Moderator
  • hi speedware,

    try this when adding new scope. on your 2nd and subsequent scopes, make sure to skip creating most of the objects:

    <snipped>
    serverConfig.SetCreateProceduresForAdditionalScopeDefault(DbSyncCreationOption.Create);



    JuneT - thanks. Yes that did it.

    Shame, though, that you don't have control over the suffix added in the tablename_select_changes_suffix SP. For example, continuing my example from their documentation above, if I know that something will maintain a unique name, then I should be able to specify it. No reason I cannot have "-WA" or "-CA" as the suffices.

    Now, even so, this could be achieved programatically by deserializing the scope_config.config_data into SqlSyncProviderScopeConfiguration, renaming the SPs in it, rewriting it and then renaming the actual SPs that were changed. Nevertheless, all it needed to be was a parameter for method or a property to SqlSyncProviderScopeProvisioning.

    Well, maybe next release :-)

    Sunday, February 28, 2010 8:41 PM
  • funny you mention the SqlSyncProviderConfiguration, such trouble you have to go thru deserializing a column just to populate it. And I agree, it could have been just one get/set property :) but there must be a reason for it from the SyncFx team :)

    yeah, maybe in next release, you could list scopes, modify them, delete/clean up its objects, reinitialize, change schema, etc, etc... :)
    Monday, March 1, 2010 3:25 AM
    Moderator


  • Creating hundreds of scopes just because there is no way to pass a parameter seems silly to me. This must be a demand from most users. Microsft claims that the collaborative sync is the way of the future, and with richer functionality.

    We are planning to have more than 2500 scopes just because of this. That means 2500 stored procedures instead of 1.

    There must be a way to do this in this release?

    Kay

    Wednesday, March 3, 2010 12:12 PM
  • I think there is a way if you don't mind not using the built-in collaboration sync providers and building the adapters similar to the offline scenario. Instead, follow the steps in Synchronizing Other ADO.NET Compatible Databases (http://msdn.microsoft.com/en-us/library/cc807291(SQL.105).aspx).

    If you look at the sample (http://msdn.microsoft.com/en-us/library/dd918709(SQL.105).aspx, check ConfigureCeSyncProvider and ConfigureDbSyncProvider), you may find that the SyncProviders are in code. The SyncProviders contains a collection of Commands (SQLCommands if your using SQL) and these commands are built via code including the parameters. Since the adapters are built in your code, you can pass parameters to the SQL commands for the filtering before invoking Synchronize.

    hth,

    JuneT

    Wednesday, March 3, 2010 1:48 PM
    Moderator
  • Thanks again,

    I'm using SqlAzureSyncScopeProvisioning to create the procedures, triggers, tracking_tables and so on, in my SQL Azure database.

    The only Procedure which require a parameter is the _selectchanges procedure.

    Is it possible use the procedures, triggers and tracking_tables created by the SqlAzureSyncScopeProvisioning or do I have to create a script manually for this?

    Otherwise I could simply change the _selectchanges procedure and use the offline scenario with adapters to hook up with the procedures created by the SqlAzureSyncScopeProvisioning.

    Kay

    Wednesday, March 3, 2010 6:13 PM
  • i think you can re-use the the triggers and tracking tables as is. Same for the stored procedures, just specify their names in the SyncAdapter commands and their corresponding parameters. You don't have to switch to the offline scenario.

    I'll play around with it when i find time later.

    cheers,

    junet
    • Proposed as answer by KayWessel Thursday, March 4, 2010 7:30 PM
    Thursday, March 4, 2010 2:55 AM
    Moderator
  • Here you go KayWessel. It's a bit long so i decided to post it as a blog instead (found an excuse to start blogging :) )

    http://jtabadero.spaces.live.com/blog/cns!BF49A449953D0591!1187.entry?&_c02_vws=1

    I manage to get it to sync several times, so i think it should work.

    The sample reuses all the objects created by sync provisioning with changes made on the SelectChanges stored procedure to accept an extra parameter for the filter. Also created a custom adapter to be able to pass a filter value for the parameter.

    would love to hear your feedback if you get it working in your scenario.

    cheers,

    junet
    Thursday, March 4, 2010 6:16 PM
    Moderator
  • Thanks again JuneT,

    I think this is the perfect solution to this problem with passing parameters.

    I like the triggers, stored procedures and tracking tables created by SqlAzureSyncScopeProvisioning much better than the scripts I found in the documentation of the sync framework 2.0. With SqlAzureSyncScopeProvisioning you don't get ekstra columns in the base table which is much cleaner. That is why I wanted to use the new SqlAzureSyncScopeProvisioning.

    I am impressed how fast you made this blogpost.

    Thank you very much !!!

    Kay

    Thursday, March 4, 2010 7:30 PM
  • According JuneT's blog, I made some change for common case, it read scope config info from table and fill provider command automaticlly.

    I don't if there's other better solution.


            private SqlSyncProviderScopeConfiguration GetScopeConfig(string scopeName, SqlConnection conn)
            {
                SqlSyncProviderScopeConfiguration scopeConfig = null;

                SqlCommand command = new SqlCommand(string.Format(
                    "SELECT SC.config_data FROM scope_config SC join scope_info SI on SC.config_id = SI.scope_config_id where SI.scope_name= N'{0}'",
                    scopeName), conn);

                try
                {
                    conn.Open();
                    SqlDataReader reader = command.ExecuteReader();

                    if (reader.Read())
                    {
                        SqlXml xml = reader.GetSqlXml(0);
                        XmlSerializer serializer = new XmlSerializer(typeof(SqlSyncProviderScopeConfiguration));
                        object obj = serializer.Deserialize(xml.CreateReader());
                        scopeConfig = (SqlSyncProviderScopeConfiguration)obj;
                    }
                    reader.Close();
                }
                finally
                {
                    if (conn.State != ConnectionState.Closed)
                    {
                        conn.Close();
                    }
                }

                return scopeConfig;
            }

            /// <summary>
            ///
            /// </summary>
            /// <param name="connString"></param>
            /// <param name="scopeName"></param>
            /// <param name="filterList">like ["@param", "paramValue"]</param>
            /// <returns></returns>
            public DbSyncProvider ConfigureDbSyncProvider(string connString, string scopeName, params object[] filterList)
            {
                DbSyncProvider dbProvider = new DbSyncProvider();

                SqlConnection peerConnection = new SqlConnection(connString);
                dbProvider.Connection = peerConnection;
                dbProvider.ScopeName = scopeName;

                SqlSyncProviderScopeConfiguration scopeConfig = GetScopeConfig(scopeName, peerConnection);
                if (scopeConfig == null)
                    throw new Exception(string.Format("Scope {0} is not exists.", scopeName));

                if (peerConnection.State == ConnectionState.Closed)
                    peerConnection.Open();

                foreach (SqlSyncProviderAdapterConfiguration adapterConfig in scopeConfig.AdapterConfigurations)
                {
                    DbSyncAdapter dbSyncAdapter = adapterConfig.BuildAdapter(peerConnection);
                    SqlCommand selChgsCmd = (SqlCommand)dbSyncAdapter.SelectIncrementalChangesCommand;              
                     selChgsCmd.Parameters[filterList[0].ToString()].Value = filterList[1];

                    //Add the adapter to the provider
                    dbProvider.SyncAdapters.Add(dbSyncAdapter);
                }
                try
                {
                    if (peerConnection.State != ConnectionState.Closed)
                    {
                        peerConnection.Close();
                    }
                }
                catch
                { }

                // Configure commands that relate to the provider itself rather
                // than the DbSyncAdapter object for each table:
                // * SelectNewTimestampCommand: Returns the new high watermark for
                //   the current synchronization session.
                // * SelectScopeInfoCommand: Returns sync knowledge, cleanup knowledge,
                //   and a scope version (timestamp).
                // * UpdateScopeInfoCommand: Sets new values for sync knowledge and cleanup knowledge.           
                // * SelectTableMaxTimestampsCommand (optional): Returns the maximum timestamp from each base table
                //   or tracking table, to determine whether for each table the destination already
                //   has all of the changes from the source. If a destination table has all the changes,
                //   SelectIncrementalChangesCommand is not called for that table.
                // There are additional commands related to metadata cleanup that are not
                // included in this application.


                //Select a new timestamp.
                //During each synchronization, the new value and
                //the last value from the previous synchronization
                //are used: the set of changes between these upper and
                //lower bounds is synchronized.
                SqlCommand selectNewTimestampCommand = new SqlCommand();
                string newTimestampVariable = "@" + DbSyncSession.SyncNewTimestamp;
                selectNewTimestampCommand.CommandText = "SELECT " + newTimestampVariable + " = min_active_rowversion() - 1";
                selectNewTimestampCommand.Parameters.Add(newTimestampVariable, SqlDbType.Timestamp);
                selectNewTimestampCommand.Parameters[newTimestampVariable].Direction = ParameterDirection.Output;

                dbProvider.SelectNewTimestampCommand = selectNewTimestampCommand;

                //Specify the command to select local replica metadata.
                SqlCommand selReplicaInfoCmd = new SqlCommand();
                selReplicaInfoCmd.CommandType = CommandType.Text;
                selReplicaInfoCmd.CommandText = "SELECT " +
                                                "scope_id, " +
                                                "scope_local_id, " +
                                                "scope_sync_knowledge, " +
                                                "scope_tombstone_cleanup_knowledge, " +
                                                "scope_timestamp " +
                                                "FROM Scope_Info " +
                                                "WHERE scope_name = @" + DbSyncSession.SyncScopeName;
                selReplicaInfoCmd.Parameters.Add("@" + DbSyncSession.SyncScopeName, SqlDbType.NVarChar, 100);

                dbProvider.SelectScopeInfoCommand = selReplicaInfoCmd;


                //Specify the command to update local replica metadata.
                SqlCommand updReplicaInfoCmd = new SqlCommand();
                updReplicaInfoCmd.CommandType = CommandType.Text;
                updReplicaInfoCmd.CommandText = "UPDATE  Scope_Info SET " +
                                                "scope_sync_knowledge = @" + DbSyncSession.SyncScopeKnowledge + ", " +
                                                "scope_id = @" + DbSyncSession.SyncScopeId + ", " +
                                                "scope_tombstone_cleanup_knowledge = @" + DbSyncSession.SyncScopeCleanupKnowledge + " " +
                                                "WHERE scope_name = @" + DbSyncSession.SyncScopeName + " AND " +
                                                " ( @" + DbSyncSession.SyncCheckConcurrency + " = 0 OR scope_timestamp = @" + DbSyncSession.SyncScopeTimestamp + "); " +
                                                "SET @" + DbSyncSession.SyncRowCount + " = @@rowcount";
                updReplicaInfoCmd.Parameters.Add("@" + DbSyncSession.SyncScopeKnowledge, SqlDbType.VarBinary, 10000);
                updReplicaInfoCmd.Parameters.Add("@" + DbSyncSession.SyncScopeCleanupKnowledge, SqlDbType.VarBinary, 10000);
                updReplicaInfoCmd.Parameters.Add("@" + DbSyncSession.SyncScopeName, SqlDbType.NVarChar, 100);
                updReplicaInfoCmd.Parameters.Add("@" + DbSyncSession.SyncCheckConcurrency, SqlDbType.Int);
                updReplicaInfoCmd.Parameters.Add("@" + DbSyncSession.SyncScopeId, SqlDbType.UniqueIdentifier);
                updReplicaInfoCmd.Parameters.Add("@" + DbSyncSession.SyncScopeTimestamp, SqlDbType.BigInt);
                updReplicaInfoCmd.Parameters.Add("@" + DbSyncSession.SyncRowCount, SqlDbType.Int).Direction = ParameterDirection.Output;

                dbProvider.UpdateScopeInfoCommand = updReplicaInfoCmd;

                return dbProvider;
            }        

    Thursday, May 13, 2010 5:07 AM