none
SqlSyncDescriptionBuilder - Not getting Contstraints RRS feed

  • Question

  • Hello,

            I am syncronizing the two SQL Server databases. Before initiating the sync between two databases I need to configure the Scope from one  database to the other. I am using SqlSyncDescriptionBuilder.GetDescriptionForScope() to get the ScopeDescription from first database and then using the DbSyncScopeDescription returned by SqlSyncDescriptionBuilder.GetDescriptionForScope() trying to configure the Scope on other database [using SqlSyncScopeProvisioning prov = new SqlSyncScopeProvisioning(DbSyncScopeDescription); prov.Apply(connection2);].

    It creates all the base tables in the Scope and also configures the scope. But when I look at the table definictions created in the other database, I do not see the constraints of ForeignKey.

    Note : When I look at the DbSyncScopeDescription.Tables[0].Constraints I see the count is 0.

    I need to create all the constraints those are present in the first database. Can someone please tell me what I am doing wrong? and how to get this done?

    Thanks in advance.

     

     

    -Ajinath

    Thursday, July 1, 2010 7:27 AM

Answers

  • Hi Ajinath,

    GetDescriptionForTable() or GetDescriptionForScope() do not capture any constraints except the PK constraint.  The reason was to simplify the schema description and mapping across different DB types and keep it simple as its difficult to capture parity across different DB platforms. The expectation is that customer would add the required subset of constraints after getting the DbSyncScopeDescription object.  There are two options for your scenario

    1. Add the constraints to the DbSyncScopeDescription object  before you provision the destination.

    2. Manually add constraint to the destination using sql commands.

    That being said, for future releases we are in process to expand on common set of schema metadata e.g FK to be added as part of GetDescriptionForScope(). We would love to hear your feedback and any suggestions you may have.

    thanks

    Jandeep

    MSFT


    jandeepc
    • Proposed as answer by Jandeep Tuesday, August 3, 2010 6:21 PM
    • Marked as answer by Yunwen BaiModerator Wednesday, January 26, 2011 8:34 PM
    Tuesday, August 3, 2010 6:21 PM
  • Hi,

    Sync Fx doesn't apply the fk relationships automatically.

    To apply them, you need to add the fk relationships manually.

    After calling SqlSyncDescriptionBuilder.GetDescriptionForScope(), you need to add all FK relationships manually.

    Please use DbSyncScopeDescription.Tables[0].Constraints.Add()

    In this case there is very important thing to be considered.

    Each table in DbSyncScopeDescription.Tables must be ordered according to FK relationships.

    Parent Table is first and Child Table second for any FK relationship.

    e.g. User table and UserDetail table.    Assume that there is FK constraints FK_UserDetail_User: parent table/column-User/id,  child table/column-UserDetail/userid

    In this case, you need to do as following as

    DbSyncScopeDescription.Tables.Add(User)   //parent table first

    DbSyncScopeDescription.Tables.Add(UserDetail) //child table second.

    ... ...

    And after getting scopeDescription, you need to add the FK constraints manually.

    This is correct way.

    Best Regards

     

     

     

     

    Wednesday, January 26, 2011 3:25 AM

All replies

  • hi Ajinath,

    AFAIK, GetDescriptionForScope does not retrieve the ForeignKey constraints. What it does is just basically read the tables/columns based on the scope configuration stored in the scope_config table.

    Try using GetDescriptionForTable instead. The catch is you have to do it by table and populate DbSyncScopeDescription.

    hth,

     

    junet

    Thursday, July 1, 2010 11:59 AM
    Moderator
  • Hi JuneT,

                    I tried the same as

    DbSyncTableDescription orderDesc = SqlSyncDescriptionBuilder.GetDescriptionForTable("orders", Connection);

    but no luck. Still I get the orderDesc.Constraints.Count as '0'.

    I dont know if this is the problem of SqlServer or Sync framework or mine. Here is what I found :

    I have database 'Peer1' with tables 'orders' & 'order_details' . The table 'order_details' contains the ForeignKey 'order_id' referenced to table 'orders' for column 'order_id'. The name of the foreignKey constraint is 'FK_order_details_orders'.

    Now ForeignKey is one type of Constraint, so I am expecting this ForeignKey constraint 'FK_order_details_orders' to be listed under the 'Constraints' folder of table 'order_details' in the object browser of 'Microsoft Sql Server Management Studio', but it is not there :( . When I expand the 'Keys' folder of the same table, I see the 'FK_order_details_orders' is listed there.

    So I think as my ForeignKey constraint 'FK_order_details_orders' is not listed under the 'Constraints' list of table 'order_details', I am not getting it in the DbSyncTableDescription.

    I see the 'PkColumns' property to the DbSyncTableDescription but no any property or member for the ForeignKeys.

    I want to create the table on other database using SqlSyncScopeProvisioning or SqlSyncTableProvisioning, it creates the table but does not cretaes the ForeignKey :(.

    Is there any way to create ForeignKey using the SyncFramework?

    If I am wrong please correct me and advise.

     

    -Ajinath

    Wednesday, July 7, 2010 11:20 AM
  • the foreign key is on order_details right? so it will not appear on your orderDesc. have you tried the GetDescriptionForTable against the order_details and check the Constraints collection?
    Thursday, July 8, 2010 12:27 PM
    Moderator
  • I am sorry JuneT. It was typing mistake. I had checked GetDescriptionForTable against the order_details and check the Constraints collection but the Constratints count was zero.  Like

    DbSyncTableDescription orderDetDesc = SqlSyncDescriptionBuilder.GetDescriptionForTable("order_details", Connection);

    but no luck. Still I get the orderDetDesc.Constraints.Count as '0'.

    Even I tried it now and the same thing.

    Please help.

     

    -Ajinath

    Friday, July 9, 2010 3:47 AM
  • I am running into the same issue.  Have you found a solution to this problem?
    Monday, August 2, 2010 9:04 AM
  • Hi Ajinath,

    GetDescriptionForTable() or GetDescriptionForScope() do not capture any constraints except the PK constraint.  The reason was to simplify the schema description and mapping across different DB types and keep it simple as its difficult to capture parity across different DB platforms. The expectation is that customer would add the required subset of constraints after getting the DbSyncScopeDescription object.  There are two options for your scenario

    1. Add the constraints to the DbSyncScopeDescription object  before you provision the destination.

    2. Manually add constraint to the destination using sql commands.

    That being said, for future releases we are in process to expand on common set of schema metadata e.g FK to be added as part of GetDescriptionForScope(). We would love to hear your feedback and any suggestions you may have.

    thanks

    Jandeep

    MSFT


    jandeepc
    • Proposed as answer by Jandeep Tuesday, August 3, 2010 6:21 PM
    • Marked as answer by Yunwen BaiModerator Wednesday, January 26, 2011 8:34 PM
    Tuesday, August 3, 2010 6:21 PM
  • Hi,

    Sync Fx doesn't apply the fk relationships automatically.

    To apply them, you need to add the fk relationships manually.

    After calling SqlSyncDescriptionBuilder.GetDescriptionForScope(), you need to add all FK relationships manually.

    Please use DbSyncScopeDescription.Tables[0].Constraints.Add()

    In this case there is very important thing to be considered.

    Each table in DbSyncScopeDescription.Tables must be ordered according to FK relationships.

    Parent Table is first and Child Table second for any FK relationship.

    e.g. User table and UserDetail table.    Assume that there is FK constraints FK_UserDetail_User: parent table/column-User/id,  child table/column-UserDetail/userid

    In this case, you need to do as following as

    DbSyncScopeDescription.Tables.Add(User)   //parent table first

    DbSyncScopeDescription.Tables.Add(UserDetail) //child table second.

    ... ...

    And after getting scopeDescription, you need to add the FK constraints manually.

    This is correct way.

    Best Regards

     

     

     

     

    Wednesday, January 26, 2011 3:25 AM