locked
What is the best way of handling schema and software changes in a hub and spoke enterprise environment? RRS feed

  • Question

  • The environment is:

    • One 'Master' server, running our software and Sql Server (either on the same box or in the same facility)
    • One or more 'Satellite' servers, running the same software and either Sql Server or Sql Express, generally the latter, connected to the Master via a WAN.

     

    All systems are using SqlSyncProvider.  Only the Satellites perform Synchronization, downloading/uploading based on custom filtering.  All of this is working as designed.  I am now looking in to the best way to handle upgrades in the future.  I am aware that schema changes are not propagated by Sync Framework ... actually, this is a good thing!  In our situation, the likelihood of a customer with this setup updating all servers the same day (let alone the same month) is nil.  It will probably happen over a period of many months.

    I had envisioned upgrading the Master server first, and then each Satellite as is convenient.  When I wrote tests to see what would happen, though, I see that schema changes can cause errors ... for example, if a new column is introduced in a table on the master, even if it is not a required column, when a Satellite without that column tries to sync with it it issues an error, specifically stating the the column does not exist.  This is not raised as a conflict, either ... it kills the Sync request.

    I should also note that the software will re-provision the sync scopes as necessary (without blowing away the tracking data, of course).

    Has anyone else developed a procedure for enterprise-wide schema/software updating?

    I would also like to know if Microsoft has any plance to change the SqlSyncProvider and/or SyncOrchestrator to be more tolerant of schema mismatches.

    Thanks in advance!


    • Edited by khauser24 Wednesday, March 10, 2010 4:54 PM Added additional detail, removed silly signature
    Wednesday, March 10, 2010 4:33 PM

Answers

  • Hi Kevin,

    "separate scope definitions for each Master<-->Satellite relationship " - i think you will still run into problem with this approach.

    What i found out when testing defining new scope is that additional scopes for the same table are dependent on the objects created by the first scope. In fact when you  provision your 2nd scope, you have to skip creating all other objects for that table (SetCreateTrackingTableDefault, SetCreateTriggersDefault,SetCreateProceduresDefault)  otherwise you get an error. Your new scope get added by specifying SetCreateProceduresForAdditionalScopeDefault which creates a new scope definition and a new SelectChanges stored procedure. Essentially, you still have the same insert, update SPs created by the first scope.

    just a wild idea, how about intercepting the changes from the master either in GetChanges/GetChangeBatch or ApplyChanges/ProcessChangeBatch and taking out the unmatched column from the server before applying to the client?


    Thursday, March 11, 2010 3:37 PM

All replies

  • hi kevin,

    did you modify the SyncFx stored procedures as well or simply added a column on the table when you got the error?

    Since SyncFx doesnt support modifying or dropping an existing scope definition (you cant add a new scope with different column count as well), you'll have to roll your own workaround.

    When you add a new column, you'll have to modify the SelectChanges, SelectRow, Insert, Update statements for that table. Likewise, you'll have to modify the config_data column (an xml  column) in the scope_config table (chk out how to retrieve sync scope definition here: http://jtabadero.spaces.live.com/blog/cns!BF49A449953D0591!1185.entry)

    cheers

    Wednesday, March 10, 2010 4:47 PM
  • Hi June,
    Thanks for the suggestions.  I did forget to note that the software re-provisions the metadata, and have added that as well (it doesn't do it unless it is told to, but it can do it).  In fact, adding columns to the tables does not itself cause a problem.  Sync is willing to essentially ignore those columns ... it isn't until the metadata is modified (either by hand or via re-provisioning in code) that sync then fails.

    I looked at your blog entry.  I agree that the ability to enumerate scopes is a missing, and nice to have feature.  I chose to view scope_info and scope_config as tables that I did not want to 'muck' with, hence the decision to reprovision via their API instead.  I did initially in a test environment do exactly what you suggested, and found the errors that I am writing about. 
    To send email, replace firstname and lastname in my email with my actual first and last name.
    Wednesday, March 10, 2010 4:58 PM
  • when you say "re-provision", do you mean clearing out all existing objects created by the sync scope provisioning or simply adding a new scope? I've had success simply modifying the sync objects to include a new column.

    cheers
    Wednesday, March 10, 2010 5:03 PM
  • Clearing out existing objects except the tracking table and then re-creating the same scope.

    But as I said, I also tried modifying the existing objects as a manual test.  Either way, once one side of a sync relationship becomes aware of a new column that the otherside doesn't yet have, it breaks for me.


    To send email, replace firstname and lastname in my email with my actual first and last name.
    Wednesday, March 10, 2010 6:19 PM
  • yes, it would break. if one side selects one more column and passes that to the other side, the other side doesnt know what to do with that column. If you look at  config_data in the scope_config table, you'll see mappings between stored procedure parameters and actual column names

    e.g.,  <Col name="CustomerId" type="uniqueidentifier" param="@P_1" pk="true" />

    also, if the new column you added is a filter column, the filter column is also added as a column in the tracking table (filtering is done on the tracking table instead of table being synched)


    Thursday, March 11, 2010 1:23 AM
  • Hi Kevin,

    Like JuneT mentioned, clearing out tracking objects for one side will not work as both sides must agree on same schema for sync to work.

    You would need to perform the same operation (drop tracking objects *** i.e. tracking tables, scope info & config tables, triggers, and stored procs *** and  reprovisioning) on the other side as well.

    We are currently considering a feature to dynamically modify schemas, but until this feature is released,

    the cleaner work around would be to drop and reprovision. JuneT seems to have figured out how to modify provisioned stored procedures such that adding a new

    column would not break sync, although I would recommend reprovisioning as the cleaner workaround.

    Thanks,

    Patrick

    Thursday, March 11, 2010 7:49 AM
  • Thanks Patrick and June,
    We're back to square one, as the context of my original question goes, how do you handle this in an enterprise environment that can't upgrade both sides of the synchronization partnership at the same time?

    A more real world example would be a master hub, and many (lets say 10) satellites (spokes).  These satellites are all over the world.  A coordinated upgrade isn't reasonable, and in fact for logistical reasons some sites will take months to perform the upgrade (we know this from a prior product that had a form of synchronization for just a few tables and had this very problem with our customers).  Of course they don't want to lose synchronization over this period of time.

    So I am looking for a process to handle this.  Any ideas?  Patrick, you mentioned that you are working on a feature to dynamically modify schemas, but if I understand that correctly you're still wanting both schemas to match.  I am looking for, essentially, schema versioning.

    One concept that I've recently toyed with is to have separate scope definitions for each Master<-->Satellite relationship rather than a single scope definition for all of them.  Thus, each scope could be upgraded when the Satellite is upgraded.  This still has a number of problems around the fact that the Master database schema changes, but at least the synchronization problem is somewhat mitigated.  Any comments on this idea?
    Thursday, March 11, 2010 1:41 PM
  • Hi Kevin,

    "separate scope definitions for each Master<-->Satellite relationship " - i think you will still run into problem with this approach.

    What i found out when testing defining new scope is that additional scopes for the same table are dependent on the objects created by the first scope. In fact when you  provision your 2nd scope, you have to skip creating all other objects for that table (SetCreateTrackingTableDefault, SetCreateTriggersDefault,SetCreateProceduresDefault)  otherwise you get an error. Your new scope get added by specifying SetCreateProceduresForAdditionalScopeDefault which creates a new scope definition and a new SelectChanges stored procedure. Essentially, you still have the same insert, update SPs created by the first scope.

    just a wild idea, how about intercepting the changes from the master either in GetChanges/GetChangeBatch or ApplyChanges/ProcessChangeBatch and taking out the unmatched column from the server before applying to the client?


    Thursday, March 11, 2010 3:37 PM
  • Hello Kevin,

    " for example, if a new column is introduced in a table on the master, even if it is not a required column, when a Satellite without that column tries to sync with it it issues an error, specifically stating the the column does not exist.  "

    So, if I am not mistaken, you want to add a new column to master database only. I wonder if this means you don't want to sync that column with satellite clients.

    Then, you should not need to reprovision your scope. In fact, reprovisioning it will cause error because client will not understand that extra column.

    What you would do is, not touch the metadata tracking objects and sync should work.

    However, if you do other upgrade schema tasks such as removing / modifying an existing column that clients understand, that would cause problem.

    Hope this helps,

    Patrick
    Thursday, March 11, 2010 9:35 PM
  • Hi June,

    Thanks ... you are correct.  I remember that now and I'm glad you brought it to my attention before I got anywhere with it.

    "just a wild idea, how about intercepting the changes from the master either in GetChanges/GetChangeBatch or ApplyChanges/ProcessChangeBatch and taking out the unmatched column from the server before applying to the client?"

    I'll have to explore this one a bit more.  In my design, all synchronization starts from the Satellite ... the Master never performs Synchronization (it sends a message via WCF to the Satellite that synchronization is necessary). 

    Anyway, thanks for the pointer!  I'll try to update you on my success.

    Thursday, March 11, 2010 9:37 PM
  • you can try to intercept in your client's WCF proxy call to GetChangeBatch

    Thursday, March 11, 2010 11:07 PM
  • So, if I am not mistaken, you want to add a new column to master database only. I wonder if this means you don't want to sync that column with satellite clients.

    Then, you should not need to reprovision your scope. In fact, reprovisioning it will cause error because client will not understand that extra column.

    What you would do is, not touch the metadata tracking objects and sync should work.

    Hi Patrick,
    In essence, yes, you have the problem, or part of it.  The complexity is that SOME satellites would have the new column and therefore would want to sync it, while others do not yet have the column and therefore don't yet want to sync it.  This is why I believe June is suggesting I explore the GetChanges and ApplyChanges events ... to see if it is possible for me to remove the offending column during sync with those systems that don't yet have the new column.

    -Kevin

    Friday, March 12, 2010 2:14 PM
  • Hi June,
    I've run a test, and it looks like it'll work if I intercept ApplyChanges and:
    - if Sync'ing from old schema to new schema, I actually have to ADD the column, because the sprocs expect it to be there.
    - if Sync'ing from new schema to old schema, I have to remove the column, because the sprocs don't expect it.

    It's not a simple solution at all, but it is a solution.  Again, thanks!!
    Friday, March 12, 2010 3:39 PM
  • glad to hear it worked.

    cheers,
    Saturday, March 13, 2010 6:53 AM
  • Kevin,

    Since you say you need to add the column, there is another way of intercepting the calls. The answer is to use a DbSyncProvider for the sync on the server. This means that you tell the provider exactly what the SP calls should be on a per-sync session basis. That means that, for the old-schema client, you can add the missing column (say by specifying a default value, or even null) since you control the setting of the command's parameters of which the value of every column is defined. You can subclass this and add your own ObjectPrefix and ObjectSchema properties if you were using them in SqlSyncProvider. You can still use SqlSyncProvider-related functions such as (iirc) SqlSyncProviderScopeProvisioning for provisioning, just use the DbSyncServer for the actual sync. An added advantage of going this way is that you can actually do dynamic filtering with this if that is also something you need in your hub/spoke environment.

    June has posted about creating such a DbSyncProvider and there is more info, plus that link, on this post: http://social.microsoft.com/Forums/en/syncdevdiscussions/thread/4af321b6-4678-4620-af46-98c560cc2bc6

    HTH

    Steve
    Tuesday, March 16, 2010 3:00 AM