locked
is it possible to sync to a view? RRS feed

  • Question

  • We are looking for a way to implement data partitiioning when doing a sync. Our current architecture is that we have one "master" sql server 2008 store and N SQL Server CE peers. Each CE peer is configured for a specific client and we want to use filters to specify the data that they should get.

    So, if we have a Customer table, an Orders table and an OrderDetail table then is is clear how to filter the Customer and Orders table; they both would have "CustomerCode" in them. The problem is the OrderDetail table; it just has a foreign key to the Orders table in it so we cannot filter by CustomerCode.

    A first thought is that we could sync with a view of the OrderDetails table; one that included the correct customer code. When I tried to provision the Master db specifying the view as a "table" to sync, this did not work. I really don't want to denormalize all our tables to make this work, and I would much rather work with one of the out-of-the-box Sync providers rather than roll our own if possible.

    Thanks for your help!

    Dan



    • Moved by Mahesh DudgikarMicrosoft employee Wednesday, November 25, 2009 7:07 AM (From:SyncFx - Technical Discussion)
    • Moved by Max Wang_1983 Thursday, April 21, 2011 10:32 PM forum consolidation (From:SyncFx - Microsoft Sync Framework Database Providers [ReadOnly])
    Wednesday, November 18, 2009 11:01 PM

Answers

  • What I was saying was that you can filter the Customer and Orders tables with the filter on CustomerCode column. Then for the OrderDetail table, why dont you put a join to the Order table in the filter clause.
    This posting is provided AS IS with no warranties, and confers no rights
    Wednesday, November 25, 2009 6:17 PM

All replies

  • Please look at filtering for Offline and collab scenarios. I am assuming you using Microsoft Sync Framework 2.0 in this cae
    This posting is provided AS IS with no warranties, and confers no rights
    Wednesday, November 25, 2009 7:07 AM
  • Thanks for the reply Mahesh - yes, we are using MSF 2.0.

    I am aware of the filtering functionality; that is what I am trying to use. The problem as I see it is that in a normalized database, not every table contains the correct filtering criteria that is needed to filter (please read my OrderDetail example above).

    The options then seem to be that we need to denormalize our db a bit in order to have the filter column present in every table we want to filter and sync, or possibly we could achieve the same result without altering table structure by using views. This does not work when following the examples in the documentation:

     

    DbSyncScopeDescription scopeDesc = new DbSyncScopeDescription("myscope");
    DbSyncTableDescription customerDescription = SqlSyncDescriptionBuilder.GetDescriptionForTable("ViewOfTableToSync", serverConn);

    scopeDesc.Tables.Add(customerDescription);

    SqlSyncScopeProvisioning serverConfig = new SqlSyncScopeProvisioning(scopeDesc);
    serverConfig.SetCreateTableDefault(
    DbSyncCreationOption.Skip);
    serverConfig.Tables[
    "ViewOfTableToSync"].AddFilterColumn("Code");
    serverConfig.Tables[
    "ViewOfTableToSync"].FilterClause = "[side].[Code] = '2'";

    serverConfig.Apply(serverConn);

    The error happens on the Apply: "The object 'ViewOfTableToSync' does not exist or is invalid for this operation."

    So I have 2 questions:

    1) is it even possible to use views instead of tables for syncing?
    2) what is the suggested pattern to use to partition a normalized database?

    Thanks again!

    Wednesday, November 25, 2009 2:01 PM
  • I will also add that the above code works fine if "ViewOfTableToSync" is instead the real table instead of the view "TableToSync" (assuming the table in question is denormalized to have the filter column "Code"
    Wednesday, November 25, 2009 2:04 PM
  • What I was saying was that you can filter the Customer and Orders tables with the filter on CustomerCode column. Then for the OrderDetail table, why dont you put a join to the Order table in the filter clause.
    This posting is provided AS IS with no warranties, and confers no rights
    Wednesday, November 25, 2009 6:17 PM
  • Ok - i think I see what you are saying now. Thanks for your help!

    Wednesday, November 25, 2009 9:21 PM