Results 1 to 6 of 6
  1. #1
    Join Date
    Jan 2007
    Posts
    4

    Unanswered: Merge replication problem MSDE

    Summary:
    Failed to insert detail rows in master-detail scenario during merge replication. Not always, sometimes.
    Topology:
    I've got 5 servers running MSDE, one of them is central publisher/distributor for merge type of replication.
    There is no row or column filtering: all subscribers have all data. Central publisher resolve conflicts with default revolvers.
    Some tables have relations: master-detail (i.e. orders-ordersDetails, etc). Relations are defined in tables as FK.
    My application which fills data use datasets with relations between tables defined the same way as in the sqlserver. So app first work with dataset which is unable to receive details without master record. When data updated to msde, it is done without errors, means master and details table updated correctly (tables at msde has relations too)
    Applications running on 4 different locations and fill data to local db (subscriber to central publisher).
    Sync occurs every 15 minutes in the following order (merge agents run at publisher/distributor):
    subscriber1: every 15 minutes, starts at 00.00h
    subscriber2: every 15 minutes, starts at 00.03h
    subscriber3: every 15 minutes, starts at 00.06h
    subscriber4: every 15 minutes, starts at 00.09h
    Sync lasts for average 15 sec, never 3 min.
    Problem details:
    Message:
    The row was inserted at 'CentPub.myDB' but could not be inserted at 'Sub2.MyDB'. INSERT statement conflicted
    with COLUMN FOREIGN KEY constraint 'FK_OrdersDetails_Orders'. The conflict occurred in database 'MyDB',
    table 'Orders', column 'OrderID'.
    Description
    CentPub is central publisher which just collecting data from subscribers. So, Order was made on one of the other subscribers different than Sub2, mean user at location3 insert order with details in local database, after a while, CentPub take this order to its database (MyDB), after that CentPub try to sync with some of the other subscribers (i.e. Sub2 == location2) and then for some unknown reason orderDeatils failed to insert in Sub2's orderDeatil table because constraint 'FK_OrdersDetails_Orders' conflict.
    After that happened, thing goes like in http://support.microsoft.com/kb/307482 (generally: in next session, failed order details are deleted
    from CentPub, which means deleted from all subscribers after syncs.)
    The problem is that subscribers have tables with relations, so Cause isn't as describe in MS kb because my tables have relations at all servers.
    After finished replications I have Orders without details records at all subscribers, so I assume that merge agents sometimes (not always) try to insert details prior to master table during the same sync session.
    In resolution section of MS kb article they say 'Mark the subscriber foreign key constraints as NOT FOR REPLICATION'.
    In relations definition I see 'Enforce relationship for replication) options which is enabled in my tables.
    If I turn that option off, is it possible to happen that my subscribers receive details without master record? (My observation of behavior says that will not happen(that will lead to problems in my app because my datasets enforce relations too).
    Deleted record I bring back to life with conflict manager in EM, but I'd like it never happen.
    Is it a bug, side effect or something I do it the wrong way?

    Thanks and regards

  2. #2
    Join Date
    Jan 2004
    Location
    In a large office with bad lighting
    Posts
    1,040
    The answer is no. When you change your constraints into "NOT FOR REPLICATION" the merge agent is the only process that will ignore the constraint as it propagates the changes amongst the subscribers. The point at which the data is entered into the tables (whether it be front-end app, sproc, etc.) will still enforce the FK constraints.

    -- This is all just a Figment of my Imagination --

  3. #3
    Join Date
    Jan 2007
    Posts
    4
    Thanks.
    I'll try to remove that option.
    Anyway, what is the purpose of this option if merge agents don't care about tables with relations?
    It is very strange in my situation because I'm sure that my data is in correct form (master-detail) in every point in time (first master, than details) so I don't expect problems during replication.

  4. #4
    Join Date
    Jan 2004
    Location
    In a large office with bad lighting
    Posts
    1,040
    Merge agents do care unless you tell them not to. In previous dealings with M$oft, it appears to be related to the number of transactions and the generations associated with them. The merge agent can be set to transmit up to 2000 generations, but will sometines still split up the parent and child transactions, especially in a heavy OLTP database.

    Here is link: http://support.microsoft.com/kb/307356/en-us

    -- This is all just a Figment of my Imagination --

  5. #5
    Join Date
    Jan 2007
    Posts
    4
    Thanks tomh53.
    I'll try to increase generations to 2000 and mark not for replication (it should be enough to avoid the problem)
    Regards
    g.

  6. #6
    Join Date
    Jan 2007
    Posts
    4
    I've tested with max value for generations parameter (2000) and everything work fine.
    Thanks and regares
    G.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •