none
How to handle data spread across multiple tables in standard custom provider RRS feed

  • Question

  • Hi Guys,

    I need to handle the following scenario. I have a database. For simplicity with two tables Person with PersonId key and referenced Address with it's own AdressID and PersonId foreign key (1:1 relation). All kesy are int currently and are not changeable. Now on the other side of sync I have Ad with Guids as a key and replica info stored in Metadata Storage Services.  Main sync entity object (let say it's AD account)is created partially form Person table and partially from Address table. I need an idea how to handle this scenario in sync framework. My first idea is to create third table which Id is of guid type and reference ids from Person (or perhaps Address) and store metadata for this table. What do you think of it? Is there any other approach to handle this kind of scenario? I thought of DbSynCProvider in first place but it seems SyncItemIds is variable lenght with maximum of 10240 but metadata storeage services cannot handle such big page size and throws an error. Any suggestions are much appreciated. I just tsick to the fact that I'll need Standard Custom Providers on both sides but not sure how to handle data spread on Db side. Shoudl I create business layer object like Account to be base sync object in session and if so where to construct and track this object, on db level, on sync provider level, ...?

    Cheers,
    Eryk


    Eryk
    Sunday, October 3, 2010 6:03 PM

All replies

  • Hi Eryk,

     

    One way or another you will need to do some sort of ID mapping.  In this particular case I think that the easier path for you will be to create a custom DbSyncProvider for AD and in that provider do the ID Mapping as you suggest by creating a third table that will map from the AD ID’s to your database IDs.

     

    Cheers,

    -Mike

     

    Monday, October 4, 2010 11:34 PM
    Moderator
  • Hi Eryk,

     

    Actually, I just realized that this request is the continuation of a longer thread and got some more context.  In fact the direction that you are going is fine.  The alternative option that you have to integrate with the standard database providers is to simply limit the size of the fields that you allow for the primary key in the database.  This should essentially give you the ability limit the size of the SyncId’s you are creating in the database provider.  At that point the simple providers will essentially take care of the ID Mapping for you.  The key is that you will need to use the incoming id to specify the ID used by the simple provider framework when GetNewItemId is called.

     

    I hope that helps.

     

    Cheers,

    Mike Clark

    Group Manager

    Microsoft Sync Framework

     

     

    Tuesday, October 5, 2010 12:34 AM
    Moderator
  • Hi,

    Thanks for your answer.

    1.Simple provider is not an option as I'm going to sync different types of objects. Please note I specified only one compound table but in fact I need more than one let's call it sync entity (whatever it would be). Moreover I expect to have tree structure in entities like School to Students assotiation where school as well as students entities is compound from multiple tables. Additionally Address table is related to different tables e.g. student, Teacher, etc. so really when tracking items only on Address table level I'm not able to say which object this address belongs to (e.g. which student or teacher).

    2. Primary key size is not issue as i talked about items SyncId presented by SyncGroupId. If you look what is used in DbSyncProvider you will notice that it is variable length and size is 10240 so it's nothing to do with DB structure. When I try to inherit from DbSyncProvider I get this value from parent class but at the other side of sync session seats metadata storage which cannot handle such large field structure but sync agent first checks if SyncGroupIds are the same and if e.g. lenght of ReplicaId or item id is different it sync framework throws an exception.

    Unfortunatelly most of your scenarios and examples sync data between the same type of sync ends in 1:1 (e.g. sql to sql express syncing few tables but both have the same schema at both sides of sync sessions)but I did not found any example which e.g. doing sync of relational database with e.g. flat file, Active Directory, Sharepoint where not only storage is different but also destination schema is different.

     

    Cheers,
    Eryk


    Eryk
    Wednesday, October 6, 2010 11:16 AM