none
Bi-Directional Transaction Replication?

    Question

  • 0

    hi all

    i want to know how to reslove conflicts when using Bi-directional transactional replication ,,conflicts like

    1-If you insert a record that has a key into a table on one of the servers and another record that has the same key already exists on the other servers that participate in the replication, the replication does not propagate the changes to the other servers.

    2-When you update a column in a record that is updated at the same time on another server, the data may be different on the two servers.

    3-When you update different columns in a record, simultaneous updates of different columns of a record may sometimes lead to conflicts.

    4-When you delete a row that is being deleted at the same time on another server that is participating in the replication, the replication fails because the DELETE statement does not affect any rows on some of the subscribers.

    ..i need help to know what to do to handle this conflicts and if there is any implementation or any ideas about this issue

     

    thnx

     

    Wednesday, September 29, 2010 11:29 AM

Answers

All replies

  • Areyou talking about bi-directional transactional replication or transactional replication with updateable susbcriptions ? Please confirm and I can send the details about it

    Wednesday, September 29, 2010 11:51 AM
  • i am talking about bi-directional transactional replication ,,and i need details about it..and also what is the difference between bi-directional transactional replication or transactional replication with updateable susbcriptions ?

    **i my case i have 2 servers connected through a network with leased line of 1 mega and i want to make bidirectional transactional replication between 2 databases on these servers ..and how to resolve different conflicts what i have sent

    Wednesday, September 29, 2010 5:27 PM
  • Ok so, Transactional replication with updateable subscriptions is bi-directional replication where both publisher and subscriber can make the changes and the changes are replicated from publisher to subscriber as well from subscriber to publisher

    Here are the links for Transactional replication with updateable susbcriptions and also about conflict detection and resolution in this kind of replication

    Updatable Subscriptions for Transactional Replication

    http://msdn.microsoft.com/en-us/library/ms151718.aspx

    How Updatable Subscriptions Work

    http://msdn.microsoft.com/en-us/library/ms151794.aspx

    Queued Updating Conflict Detection and Resolution

    http://msdn.microsoft.com/en-us/library/ms151177.aspx

    Please let me know if you have more queries on the above

    • Proposed as answer by Vijay SirohiEditor Thursday, September 30, 2010 4:05 AM
    • Marked as answer by hatemgamil Thursday, September 30, 2010 10:32 AM
    Wednesday, September 29, 2010 5:43 PM
  • thnx Gaurav.Mathur for ur help

    i will check these urls and i will let u know my feed back

    thnx again so much

     

    • Marked as answer by hatemgamil Thursday, September 30, 2010 10:32 AM
    • Unmarked as answer by hatemgamil Thursday, September 30, 2010 10:37 AM
    Thursday, September 30, 2010 6:25 AM
  • If a post has answered your original question, please indicate this by clicking on the "Mark as Answer" button for that thread
    Thursday, September 30, 2010 8:38 AM
  • hi Gaurav.Mathur

    thnx for ur posts it helps me alot ,,buti have a question about how does the updateble subscription Detecting conflict ,,exactly about this part

    When creating a publication and enabling queued updating, replication adds a uniqueidentifier column (msrepl_tran_version) with the default of newid() to the underlying table. When published data is changed at either the Publisher or the Subscriber, the row receives a new globally unique identifier (GUID) to indicate that a new row version exists. The Queue Reader Agent uses this column during synchronization to determine if a conflict exists.

    A transaction in a queue maintains the old and new row version values. When the transaction is applied at the Publisher, the GUIDs from the transaction and the GUID in the publication are compared. If the old GUID stored in the transaction matches the GUID in the publication, the publication is updated and the row is assigned the new GUID that was generated by the Subscriber. By updating the publication with the GUID from the transaction, you have matching row versions in the publication and in the transaction.

    If the old GUID stored in the transaction does not match the GUID in the publication, a conflict is detected. The new GUID in the publication indicates that two different row versions exist: one in the transaction being submitted by the Subscriber and a newer one that exists on the Publisher. In this case, another Subscriber or the Publisher updated the same row in the publication before this Subscriber transaction was synchronized.

    My question is if the leased line is 1 mega in the network that connects the 2 servers together that way to handle conflict will make a problem because it will be large amout of data that will be transfered through the network

    thnx

    Thursday, September 30, 2010 10:37 AM
  • You have asked a very interesting question and thats where many people get confused. So this is regarding conflict detection:

    And before I start with it. let's first understand what is a conflict in transactional replication with updateable subscriptions:
    Between two synchronizations if the publisher and subscriber make any DML change on the same row in the same table then it results in a conflict:
    so we can have such conflicts: update-update conflict, update-delete conflict etc

    Lets understand conflict detection now:

    Replication Toplology: Publication1 - Subscriber1 (Publication with only one subscriber)

    10:00 Synchronization happens and publisher and subscriber are in sync and table t1 has msrepl_tran_version value for row R1 on both pub and sub

    10:10 - Publisher makes a change in Row1 on Table t1 and updates the row. Row looks like this:
     ID Name msrepl_tran_version
     10 G1 2  (Note that version increments to 2)

    10:15 - Subscriber1 makes a change in Row1 on Table t1 and updates the row. Row looks like this:
     ID Name msrepl_tran_version
     10 G2 2  (Note that version increments to 2)
    10:30 - They synchronize and see what happens:
    At publisher when the change arrives from Publisher, ther versions are checked and since the version changed on pub and sub both, its a conflict. The conflict is detected.

    Now what would be the conflict resolution:
    The conflict resolution depends on what you choose, here are the options:
    1. Publisher wins (the default)
    2. Publisher wins and the subscription is reinitialized
    3. Subscriber wins

    And one more thing which is very important to understand, always remember that the synchronization is always between publisher and subscriber and NEVER between two subscribers. So you should not worry about understanting conflict detection when multiple susbcriptions are present. Because it is always the publisher and subscriber who syncs and the above concept of conflict detection follows. So what matters is which subscription synchronizes first (if you have set subscriber wins).

    Example: we have subscriber always wins

    Replication Toplology: Publication1 - Subscriber1 (Publication with TWO subscribers)

    10:00 Synchronization happens and publisher and subscriber are in sync and table t1 has msrepl_tran_version value for row R1 on both pub and sub

    10:10 Publisher makes a change in Row1 on Table t1 and updates the row. Row looks like this:
     ID Name msrepl_tran_version
     10 G1 2  (Note that version increments to 2)

    10:20  Subscriber1 makes a change in Row1 on Table t1 and updates the row. Row looks like this:
     ID Name msrepl_tran_version
     10 G2 2  (Note that version increments to 2)

    10:30  Subscriber2 makes a change in Row1 on Table t1 and updates the row. Row looks like this:
     ID Name msrepl_tran_version
     10 G3 2  (Note that version increments to 2)

    10:40 Subscriber2 synchronizes with Publisher, conflict is detected and the resolution is that Subscriber2 wins and the row from Susbcriber2 applies on Publisher. This is the  row on pub and sub then
     ID Name msrepl_tran_version
     10 G3 2 

    10:50 Subscriber1 synchronizes with publisher, conflict is detected and the resolution is that Subscriber1 wins and the row from Susbcriber1 applies on Publisher. This is the  row on pub and sub then
     ID Name msrepl_tran_version
     10 G2 2 

    So the final outcome does not depend on the subscriber who made the change at what time, it depends on when which subscriber synchronzes with the Publisher. The outcome would have changed if we would have set the Publisher wins. I hope you would understand what I am trying to explain.

    Please let me know if you have any queries

    Regarding your query about whetheryour environment would give good performance, for that we always suggest that you should run tests and do the benchmarking on your test servers  and check the performance

     


    Gaurav Mathur | Please mark solved if I've answered your question
    --------------------------------------------------------------------------------
    This posting is provided "AS IS" with no warranties, and confers no rights.
    --------------------------------------------------------------------------------
    Thursday, September 30, 2010 11:01 AM
  • While you can use up datable subscriptions for this, pure bi-directional transactional replication might be a better fit as there is no schema change requirement. You will find that the overhead with this is less than using updateable subscribers.

    Conflicts occur when

    1) you have a pk violation -these occur when the same identity value is inserted on both sides of your replication topology. This can be avoided by using an identity increment of 2, and a seed of 1 on one side and 2 on the other. You have to run dbcc checkident and reseed the values to the next odd or even number on each side if you have existing data in your database.

    By doing so you will never get a insert conflict.

    2) rows are updated on both sides. In this case the data will be swapped. For example - if I update col1 to 4 for pk=5 on one side, and update col1 to 7 for pk=7, I will end up swapping values - so I will end up with a value of 7 for col1 on the first node, and 4 on the other.

    3) updating a row which does not exist. To get around this you will have to do some existence check that will throw away the update.

     

    Here is a script that will show you how to set up this:

     

    if not exists (select * from sys.databases where name ='Hatemgamil')
    create database Hatemgamil
    GO
    if not exists (select * from sys.databases where name ='HatemgamilSub')
    create database HatemgamilSub
    GO
    sp_replicationdboption Hatemgamil,publish,true
    GO
    sp_replicationdboption HatemgamilSub,publish,true
    GO
    use Hatemgamil
    GO
    if not exists(select * from sys.objects where name='testme')
    create table testme(pk int identity (1, 2) not for replication primary key, charcol varchar(20))
    GO
    use HatemgamilSub
    GO
    if not exists(select * from sys.objects where name='testme')
    create table testme(pk int identity (2, 2) not for replication primary key, charcol varchar(20))
    GO
    use Hatemgamil
    GO
    sp_addpublication Hatemgamil, @status='active'
    GO
    sp_addpublication_snapshot Hatemgamil
    GO
    sp_addarticle Hatemgamil, testme, @source_object=testme, @identityrangemanagementoption =manual
    GO
    sp_addsubscription Hatemgamil, 'all', @@ServerName, HatemgamilSub, @sync_type='replication support only',@loopback_detection=true
    GO
    sp_startpushsubscription_agent Hatemgamil, @@ServerName, HatemgamilSub
    GO
    use HatemgamilSub
    GO
    sp_addpublication Hatemgamil, @status='active'
    GO
    sp_addpublication_snapshot Hatemgamil
    GO
    sp_addarticle Hatemgamil, testme, @source_object=testme, @identityrangemanagementoption =manual
    GO
    sp_addsubscription Hatemgamil, 'all', @@ServerName, Hatemgamil, @sync_type='replication support only',@loopback_detection=true
    GO
    sp_startpushsubscription_agent Hatemgamil, @@ServerName, Hatemgamil
    GO
    

     

     

     


    looking for a book on SQL Server 2008 Administration? http://www.amazon.com/Microsoft-Server-2008-Management-Administration/dp/067233044X looking for a book on SQL Server 2008 Full-Text Search? http://www.amazon.com/Pro-Full-Text-Search-Server-2008/dp/1430215941
    Thursday, September 30, 2010 12:22 PM
    Moderator
  • thnx Hilary Cottler for ur valuable post ,,the way u suggested to resolve conflict doesn't match my business needs :

    first i am not allowed to make identity on one side incremented by 1 and the other incremented by 2 as there are other applciations that runs on these data bases and they are running live,therefore the swaping solution u suggested to resolve the conflict of updating rows  on both sides will not effective as the rows will have the same pk

    secondly the whole swapping idea will not work with me as i dont want to swap the updates between subscriber and publisher,,

    then i am asking  if the leased line is of 1 mega in the network that connects the 2 servers together that way to handle conflict will make a problem because it will be large amout of data that will be transfered through the network.

     

    now i am searching on sql server service broker ,i think it helps to exchanges messages between 2 sql instances ,,if u have any useful information or data about sql server service broker please share

     

    thnx

    Sunday, October 03, 2010 12:28 PM
  • Thanks Gaurav for detailed explanation of the conflicts. It is very helpful. You can as well turn it into a blog post.


    ARASKAS
    Wednesday, October 06, 2010 5:16 AM