none
Can you figure out the output in this Scenario on replication? RRS feed

  • 问题

  • I have two Server A, B.  one database on A named DB1, so we replicate DB1 to DB2 on Server B.  this is an trasaction replication.

    suppose all the setting in replication is in default


    In DB1,i have two tables table1 and table2, they have same schema and data, the table1 is in replication, was replicated to table3 on server B.

    so i will execute two different update on table1 from table2, so what we will see if you start the sql profile on Sever B?

     

    columns in table1 and table2
    (PartNumber primary key, CodeName nvarchar(20),TypeCode nvarchar(20))

     

    1. first update

        update a set a.CodeName=b.CodeName,a.TypeCode=b.TypeCode  from table1 a
        inner join table2 on a.PartNumber=b.PartNumber

     

        in this case, will you see any operation was replicated to table3 on Server B from the sql profile?


    2. Second Update

      
        update a set a.PartNumber=b.PartNumber,a.CodeName=b.CodeName,a.TypeCode=b.TypeCode  from table1 a
      inner join table2 on a.PartNumber=b.PartNumber

     

        in this case, what operations will you see were replicated to table3 on Server B from the sql profile?


    2013年5月25日 3:09

全部回复

  • No difference in replication point of view. If statement updated any row in table a, that change is replicated. To confirm, just run profiler on server b. 
    2013年5月25日 3:38
  • hi Rimao, thanks you reply. i just found it is difference if you update the primary key.

    when the primary key is updated, even the table a and table b is the same. you will can see

    batch delete/insert on server B by checking the sql profile.

    but if the primary key is not updated, in this case, we will see no DML on server B

    this is very intersting.

    thanks

    kevin


    2013年5月25日 4:26