SQL Server Performance

Peer-to-peer Replication failure & restoration - Scripts

Discussion in 'SQL Server 2005 Replication' started by balu20, Jul 8, 2008.

  1. balu20 New Member

    Hi All,
    For Disaster Recovery scenario, we have two sites i.e. Site A contain primary & secondary db servers and same is the case at Site B. All DB servers are replication peer-2-peer. My application talks to the primary db servers through configuration setting in app servers (win2003).
    DR strategy: In case of any issues with the primary DB server, we modify the configuration setting in app server to point to the secondary db.
    Issue: Whenever replication fails, DB's are losing sync and requires a min of 10hrs outage to restore the business.
    I am looking fwd for workarounds to restore the situation when the application is operational. When replication fails, I am getting "Unique key constraint failed when trying to replicate...." msg in the event viewer logs. I could trace around 4-5 transactions which are currently causing this issue.
    Can I remove the failure from distributed list of commands and resolve the issue? can anyone tell me how do I go about in case this is possible?
    I guess this is highly optimistic but how can I ensure my appln availability is 100% when i come across replication failure scenario? Currently I have no clue apart from restoring the backup from stable server.
    Any inputs/scripts to address this tricky situation is highly appreciable.
    Thanks in advance.
  2. satya Moderator

    Welcome to the forums.
    Can you opt for Database mirroring to replace the replication to provide DR>?
  3. balu20 New Member

    Thanks Satya for the reply. Actually am looking fwd for any short-term fix rather than changing the architecture.
    Pls let me know in case you require any further details on the problem statement.
    Thanks in advance.
  4. colinr New Member

    Im researching Peer to Peer for implementation at a client and one of the highlighted risks so far is the lack of information on resolving any replication issues like the one you have highlighted above. during this research I came upon a suggestion to disable Constraint Checking as it is a unnecessary overhead as the data was constrained on insert at the publisher.
    Personally Im reluctant to move away from default settings without thorough testing mainly due to the lack of information on the net at the moment. but it could be worth testing

Share This Page