Sql server Service Broker

  • hatemgamil

    Newbie

    Points: 6

    Greetings,,

    i want to make bi-diretctional replication between 2 databases each is one sql server instance,,after searching for the best way to do bi-directional replication i found that i can use service broker to exchange messages between 2 databases ,,but i have a question about conflict detecting and resolution and i need some help,,my question is how can i detect these type of conflicts that can be occured during exchanging messages ,,for examples,,

    1-updating the same rows,,if each message containg the rows that are updated on each database .

    2-updating rows on 1 database that are deleted on the other,,so when the message from data base 1 will contain an update statement for the rows that have been updated but when it arrives @ database 2 and executed the result will be 0 rows affected because those rows are deleted on database 2..

    and there are other conflict,,i need some help or any ideas to know how can resolve those conflict ,,,

    any suggestion will be highly apperciated

    thnx all

  • sturner

    SSC-Insane

    Points: 22411

    These situations are similar to what you would encounter when multiple users attempt update to the same row on the same table on the same server except you have the added dimension of the replication latency.

    What it means is that all the updates to the tables have to go through your replication mechanism which would have to insure that updates to both servers were within a transaction and that deadlocks do not occur or are handled properly.

    As you have surmised this is not a trivial exercise.

    The probability of survival is inversely proportional to the angle of arrival.

  • Oliiii

    SSCertifiable

    Points: 5328

    You could use the Merge replication.

    Doing all this by hand is not really the easiest thing in the world 🙂

  • cliffb

    SSCarpal Tunnel

    Points: 4547

    Oli is correct. You want to use Merge Replication. no reason to reinvent the wheel when SQL Server has something already available that will do what you need.

  • hatemgamil

    Newbie

    Points: 6

    thnx Oliiiii for ur post,,

    can u give me more details on how can implemenet conflict resolver when using merge replication??

  • hatemgamil

    Newbie

    Points: 6

    thnx stuner

    i wana ask u how can i handle this conflict when updating same rows on the same table,,please give me more details ,,what u mean by replication mechanism

Viewing 6 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply