Results 1 to 6 of 6
  1. #1
    Join Date
    Jan 2003
    Location
    Nottinghamshire, UK
    Posts
    364

    Exclamation Unanswered: Merge Replication Referential Integrity (Failures)

    Hi

    Hope U Guys can Help with this one

    I am Using Pull Merge Replication with SQL2k UK(Publisher)<>Sri Lanka (Subscriber) via VPN

    apprx 120 Tables in 12 Publications (Hourly Merge)

    I am Getting conflicts

    a) Failure to Insert @ Subscriber Cannot Insert Duplicate Primary Key.
    b) Failure to Insert @ Subscriber Foreign Key Constraint.

    There is no one updating data at the Subscriber as I am Using a Test Subscriber DB But Live Publisher DB

    The Problem is this error cause deletions at the Live Publisher to maintain consistency between the 2 DB's - and it's Frightening

    This causes a Cascade of Conflicts which hurt my brain (GUI Conflict Resolver could do with some serious improving)

    It seems to happen completely @ Random & Occasionaly

    As far as I can tell the replication process is trying to Insert the Footer Records Before the Header even with Declared Referential Int in Place

    It lloks like I am going to have to remove Referential Int at the Subscribers - but don't like doing this.

    Has Anyone got any ideas why this might be happening ?????

    All suggestions greatly appreciated ?-)

  2. #2
    Join Date
    Jan 2003
    Location
    Nottinghamshire, UK
    Posts
    364

    Further Info

    Just in Case anyone wants a look I'm attatching the Transcript of the Conflicts

    Thanks Again

    GW
    Attached Files Attached Files

  3. #3
    Join Date
    Jan 2003
    Location
    Nottinghamshire, UK
    Posts
    364

    Red face MMMMMmmmmmm

    Guess no one one to play with this one

    Ah well Dropping Subscriber Referential Integrity it is then
    "Everything should be made as simple as possible, but not simpler." - Albert Einstein
    "Everything should be made as complex as possible, so I look Cleverer." - Application Developer

  4. #4
    Join Date
    Mar 2003
    Location
    Bucharest, Romania
    Posts
    80

    Re: Merge Replication Referential Integrity (Failures)

    Merge Replication Agent sync tables in the order you chose when you create the publication @ the Publisher. In theory Microsoft says that the only way to alter that default order is to declare referential integrity (from the properties of the tables - diagram, not with trrigers) in which case parent table will be sync first than all the child's.

    Now let's assume that MS is wrong (wouldn't be the first time, isn't it?) and even with referential integrity declared the order remains the same, from first to last. You could try to redefine the publication and insert in it tables in the order you want them to syncronise.


    But I'm not sure that this is the real problem of your database.
    My problem with that is the first error you encountered, I quote: "a) Failure to Insert @ Subscriber Cannot Insert Duplicate Primary Key."

    You said that no updates are made @ the subscriber. OK. This is difficult and I'm not sure on anything that I'm going to write bellow, But you should consider it, who knows....

    At the publisher, which is the algorithm for allocation fo the value of primarykey? Is there any chance that you've addopted the solution of re-allocating values for it? (Eg. You had a customer "BLABLA" with the ID: 4300 that you've deleted in the past hour (since the last replication proccess occured), and insert a new customer with the same ID but different name: "BLABLA 1")?

    It is possible that when the next replication occurs the order of actions performed by MS SQL should be this: first insertions then deletions (I can not bet on this, in fact this is my assuming).


    If I'm right with my presumptions, let's have a look of what is going to happen when the replication agent will run again:

    1) first tries to add the new customer in the customers table ID:4300, Name:BLABLA 1(this is the parent table so it is the first table to be updated) => ERROR (You already have a customer with that ID:4300 at the Subscriber: BLABLA => first error you mentioned).

    2) second: SQL delete the customer with ID:4300 at the Subscriber: BLABLA =>Succesfull (it also erase all the records from child table due to referential integrity decleared at the subscriber (Eg. SQL erase all invoices from customer with ID:4300)

    3) third: SQL tries to insert the records in the child table for the new customer. This means that it tries to copy all the invoices from the publisher (better said from the distribution database located at the distributor but this is another story) for the new customer with the ID:4300, name:BLABLA1 =>ERROR (at the subscriber you don't have anymore a customer with the ID:4300, it was just deleted=> the second error)


    You should also verify all posibilities, if you declared an horizontal filter in your publication (This could also be the source of your troubble, if you changed the environment at the subscriber, and from a moment the subscriber should receive other date that it has up until that precise moment. In this case the replication agent first tries to clear all the data that is no longer valid for that subscriptions, and than it tries to sync with the publiher).

    You can also try to obtain the latest service pack for MS SQL Server 2K, currently SP3.0. Maybe this is a bug that they had discovered and fixed.

    Any way, good look!

    Ionut

    ******
    Originally posted by GWilliy
    Hi

    Hope U Guys can Help with this one

    I am Using Pull Merge Replication with SQL2k UK(Publisher)<>Sri Lanka (Subscriber) via VPN

    apprx 120 Tables in 12 Publications (Hourly Merge)

    I am Getting conflicts

    a) Failure to Insert @ Subscriber Cannot Insert Duplicate Primary Key.
    b) Failure to Insert @ Subscriber Foreign Key Constraint.

    There is no one updating data at the Subscriber as I am Using a Test Subscriber DB But Live Publisher DB

    The Problem is this error cause deletions at the Live Publisher to maintain consistency between the 2 DB's - and it's Frightening

    This causes a Cascade of Conflicts which hurt my brain (GUI Conflict Resolver could do with some serious improving)

    It seems to happen completely @ Random & Occasionaly

    As far as I can tell the replication process is trying to Insert the Footer Records Before the Header even with Declared Referential Int in Place

    It lloks like I am going to have to remove Referential Int at the Subscribers - but don't like doing this.

    Has Anyone got any ideas why this might be happening ?????

    All suggestions greatly appreciated ?-)

  5. #5
    Join Date
    Jan 2003
    Location
    Nottinghamshire, UK
    Posts
    364

    Thanks M8y

    Ionut

    Many Thanks for your Input what you say makes a lot of sense !

    Maybe you are right about the Declared Ref Int not working the way that MS Say's it would certainly explain a few things - I'll try adding the tables/articles in the proper order & not rely on Declared Ref Int.

    It does look like what is happening is that a row is deleted at the publisher & then recreated with the same primary key (Identity fields are not used in these tables) before the Merge takes place - I presume this gets an entirely different GUID which the replication Agent just regards as a new Insert.

    I Just Can't believe that The Agent would do all the Inserts First then do the Deletions - That would never work in real life for anyone - maybe the other way round would stand a better chance but I can still see problems with that.

    I really don't logically see how Merge replication could ever work.

    Does anyone actually ever use it without problems - They must do somewhere - If your reading this & Use Merge Replication please just post & tell us that it can work !

    I'll do some more tests before I drop Integrity @ the subscriber - but my patience is wearing thin.

    There is No filtering in place at all & I have already applied SP3 when I upgraded from 7

    Once again Many thanks ionut

    GW
    "Everything should be made as simple as possible, but not simpler." - Albert Einstein
    "Everything should be made as complex as possible, so I look Cleverer." - Application Developer

  6. #6
    Join Date
    Mar 2003
    Location
    Bucharest, Romania
    Posts
    80
    You're right, I've already said yesterday that I'm not sure about the insert's first and the deletion's after order. If I think more about it, it's probable just the opposite.

    But still make no sense for Replication Agent to fail its job, because, of what I know about merge replication it depends entirely on rowid fileds (which are GUID fields -> and these are suppose to have unique values; from my experience with them, I have a database of nearly 200MB which relias on GUID's as primarykeys, they relly have unique values).
    The problem with GUID's is that they are not timestamp kind of data and therefore they cannot by orderred from the time point of view. So, there must be another way to sync rows (not in order of datetime they have been entered, modified or deleted). This was the motiv for my assumption with insert's and deletions order


    Anyway good look!


    Ionut


    PS I'm very intrested if you figure out this problem, because I may need it in the next months. Relly.

Posting Permissions

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