none
Handle Database Schema Change

    Pertanyaan

  • Hi All,

    After the solution with Sync Framework is deployed and used in business operation, if there is a change to database schema (new entity, new columns, removed columns, new relatioship etc), what is the best approach to manage the change. Does anyone have any suggestions?

    Thanks

    25 Maret 2012 2:05

Semua Balasan

  • Sync Framework doesnt support changing scope definition to match schema changes.

    you can hack your way to the scope definition but the preferred way is to deprovision and re-provision the scope.

    25 Maret 2012 10:56
    Moderator
  • Thanks, JuneT. Really apprecaite your prompt response.

    After reading many posts here, I understand the limitation of Sync Framework and some workaround. I think after a solution is deployed for production use, sooner or later there will be a need to change the schema. If there are many synced clients out there, deprovision is not ideal approach, unless all clients can be updated and all the data have to be synced from scratch. Given the consideration of data volume in some applications, does Microsoft never considered the real world use cases?

    In my case I don't want to deprovision the database after initial deploymnet (this will disturb the deployed applications at client side), so the only way is to hack the synced database and manually generate/update SQL objects and make them look like being created with the initial provision.

    Do you know if MS has any plan to solve this problem? If not, this is pretty much the same to ask people to drop database, change schema and created the database again, then reload the data if the initial database needs to add a new column to one of the tables.

    Do you have any SQL scripts to partially automate this task?

    Once again, thank you for your help and great contribution to the comunity here.

    PZhuang

     

    ZHUANG

    25 Maret 2012 12:10
  • afaik, Sync Framework has always been clear it doesnt do schema syncs, just data sync. and that should be one item that should be taken into consideration when choosing whether to use Sync Framework or not.

    if Microsoft never considers real world cases, you will not have a Microsoft that it is now :)

    now, let's walk thru a couple of scenarios for schema changes.

    suppose you add a column to a scope, given that Sync Framework tracks changes at row level, how do you send that specific column only?

    if you have two scopes, where one has an extra column over the other, how do you decide whether to drop the column from one scope or add it on another?

    assuming you drop a column that is part of a filter, how do you suppose it should clean up that column from the base table and the tracking table?

    anyway, have a look at this link on pointers what it takes to modify the scope definition: Modifying Sync Framework Scope Definition – Part 1 – Introduction

    you should be able to find the subsequent post on that topic on the blog site.

    25 Maret 2012 15:18
    Moderator