SQL Server Performance


Discussion in 'ALL SQL SERVER QUESTIONS' started by nadeem.shaaz, Sep 1, 2013.

  1. nadeem.shaaz New Member

    We've merge replication configured in our environment(1 Publisher, 3 Subscribers) and We know by default merge replication is bi-directional. Its working fine in case of inserts i.e, if a row is inserted at publisher then it's getting updated at subscriber and vice versa. The real issue is, if row gets deleted at publisher then its getting deleted at subscriber but, if a row gets deleted in any of 3 subscribers then its not getting deleted at publisher.

    When a row is deleted at publisher then it's getting updated in MSmerge_tombstone(Publisher). But, in case of subscriber, the corresponding row(rowguid) is not getting updated in MSmerge_tombstone(Subscriber). I think that might be the issue, even merge delete trigger exist on these tables(Subscriber). Please help me out, as it is a production issue.

    Publisher(2008R2 sp1-Enterprise), Subscribers(2008R2 sp1-Standard)
  2. Shehap MVP, MCTS, MCITP SQL Server

    Abstractly this is working perfectly between a publisher and a single subscriber but it sounds weird your case , anyways can you check conflicts exists there between the publishers and 3 subscribers, also are you using an equivalent replication priority if so try to resolve conflicts exists there and adjust properly your replication priorities in a way not to assure one will be the winner and the other will be the looser ..

    If still persist , please let me know
  3. nadeem.shaaz New Member

    Thanks for the reply,

    As you've said, We've large number of conflicts exist between publisher and subscribers. Most of the conflicts are due to violation of constraints(primary key), I think we can avoid these conflicts in two ways.
    1. Remove the primary key from replicated tables, Would you please let me know is it a good practice?
    2. EXEC sp_changemergearticle
    @publication = 'Publication_Name',
    @article = 'Article_Name',
    @property = 'compensate_for_errors',
    @value = 'true'
    (If 'compensate_for_errors' is set to true then what I've observed is subscriber's record is deleted and the publisher's record is inserted at the subscriber)

    Please suggest which one is better option? and
    Is it a good idea to have primary key on tables which are taking part in merge replication(because we've rowguid column)?

    We've Subscription type for all the subscribers is 'Client'

    Say suppose I've resolved all the conflicts(either by removing PK or by using 'compensate_for_errors'), Please let me know what all steps I need to take to fix it?

    (When I delete a row at publisher then it's getting updated(rowguid) in publisher's MSmerge_tombstone as well as subscriber's MSmerge_tombstone, but when I delete at subscriber then it's not getting updated(rowguid) in subscriber's MSmerge_tombstone.)

    Best Regards,
  4. Shehap MVP, MCTS, MCITP SQL Server

    I can see now , let me answer your questions one by one:

    · Removing PK is not recommended at all because it will generate heap tables once removing merge replication and moreover it will impact on performance of queries relying on PK columns in joins

    · Using 'compensate_for_errors' can be useful for little bit of cases on production because it might delete records on subscriber for the reason of any PK violation and this usually will lead to data inconsistency table particularly fir star-schema DBs where PK.FK relationships are much between tables and might be impacts negatively

    · For such cases , I am urging to use some schema design changes like adding identify columns or inspiring new jobs to detect such PK violations between subscribers and publishers in order to handle them properly accordingly to business needs

    Let me know if any furhter help is needed
  5. nadeem.shaaz New Member

    Thanks for your help,
    I've changed compensate_for_errors from 0 to 1, as we don't have PK-FK relationship.
    I've created a subscriber in test environment for the publication but, the same problem(row not being deleted from publisher when it's deleted from subscriber) got repeated again for this subscriber. Shall I drop all the subscribers(3) and recreate them again?
    Please let me know.
  6. Shehap MVP, MCTS, MCITP SQL Server

    No need to drop them and create them again, just try to re-initialize these subscribers again and let me know your feedback
  7. nadeem.shaaz New Member

    I truly appreciate....your help in resolving the issue, Thank you very much......

Share This Page