Complicated Merge Replication/Trigger problem

Hi all, we have a complicated merge replication/trigger problem.
Ill try to explain the setup first...
We have 2 SQL Server 2005 servers (called Server1 and Server2 for this
example)
On each Server there is an identical database (called SalesData for
this example) which contains 2 related tables (called A and B for this
example).
Tables A and B are related in that when a record is updated in table A,
a trigger (called trUpdateB) fires which updates a related record in
table B.
The trigger trUpdateB is marked as 'Not For Replication'.
Now we have Merge replication set up between Server1.SalesData
(Publisher) and Server2.SalesData (Subscriber) which replicates changes
back and forth between the 2 databases.
When we update a record in table Server1.SalesData.A the trigger fires
and the replication takes place, so we get changes in both A and B
replicated to Server2.SalesData.
And vice versa, when we update a record in table Server2.SalesData.A
the trigger fires and the replication takes place, so we get changes in
both A and B replicated to Server1.SalesData.
So far, all works as expected.
The problem is this...
We create a conflict - updates are made to Server1.SalesData.A and
Server2.SalesData.A at about the same time.
We are using the default conflict resolver so the Publisher (Server1)
should win.
However when I look at the Replication Monitor it says that the there
was 1 change downloaded to the subscriber (table A's update) and 1
change uploaded to the publisher (table B's update - created by the
trUpdateB trigger).
I expected to see both the 2 changes downloaded to the subscriber.
The Replication Conflict Viewer only reports 1 conflict (for table A)
which was resolved as expected (Publisher wins).
I expected to see 2 conflicts (one for each table) both resolved in
favour of the Publisher.
Can anyone explain where I am going wrong here, either in my
implementation of merge replication or in my expectation of how it
should work?
Thanks in advance, any help is greatly appreciated.
Bazza
This is hard to figure out. I take it you have column level tracking -
otherwise you would have no conflict.
I would do the following. On the Subscriber update A. See the update in B.
Verify that there are corresponding entries in msmerge_contents for both the
updates.
Repeat this on the publisher. Verify that everything is in place. Now do the
sync.
There should be 2 downloads. Both from the publisher to the subscriber.
There should be between one and two conflicts depending on the columns
changed.
Run profiler on the subscriber to see what it is doing.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"bazza" <bazzaontour2002@.hotmail.com> wrote in message
news:1164110590.894357.308440@.m73g2000cwd.googlegr oups.com...
> Hi all, we have a complicated merge replication/trigger problem.
> Ill try to explain the setup first...
> We have 2 SQL Server 2005 servers (called Server1 and Server2 for this
> example)
> On each Server there is an identical database (called SalesData for
> this example) which contains 2 related tables (called A and B for this
> example).
> Tables A and B are related in that when a record is updated in table A,
> a trigger (called trUpdateB) fires which updates a related record in
> table B.
> The trigger trUpdateB is marked as 'Not For Replication'.
> Now we have Merge replication set up between Server1.SalesData
> (Publisher) and Server2.SalesData (Subscriber) which replicates changes
> back and forth between the 2 databases.
> When we update a record in table Server1.SalesData.A the trigger fires
> and the replication takes place, so we get changes in both A and B
> replicated to Server2.SalesData.
> And vice versa, when we update a record in table Server2.SalesData.A
> the trigger fires and the replication takes place, so we get changes in
> both A and B replicated to Server1.SalesData.
> So far, all works as expected.
> The problem is this...
> We create a conflict - updates are made to Server1.SalesData.A and
> Server2.SalesData.A at about the same time.
> We are using the default conflict resolver so the Publisher (Server1)
> should win.
> However when I look at the Replication Monitor it says that the there
> was 1 change downloaded to the subscriber (table A's update) and 1
> change uploaded to the publisher (table B's update - created by the
> trUpdateB trigger).
> I expected to see both the 2 changes downloaded to the subscriber.
> The Replication Conflict Viewer only reports 1 conflict (for table A)
> which was resolved as expected (Publisher wins).
> I expected to see 2 conflicts (one for each table) both resolved in
> favour of the Publisher.
> Can anyone explain where I am going wrong here, either in my
> implementation of merge replication or in my expectation of how it
> should work?
> Thanks in advance, any help is greatly appreciated.
> Bazza
>
|||Thanks for your reply Hilary
No, we are using Row Level tracking. However Im changing the same
column on the same record in table A on both servers so it shouldnt
make any difference should it? Whether Im using Row or Column level
tracking Im going to cause a conflict as far as I understand it? Please
correct me if Im wrong.
Ok, I tried your suggestion. After making the changes on the publisher
and the subscriber there were 2 entries in the msmerge_contents in each
database. The 'rowguid' column shows the correct value for the 2
records (one from A and one from B). So everything seems to be in place
at this point.
Then I started the sync and got the same result as before! 1 download
(table A) and 1 upload (B) is showing in replication monitor. There is
1 conflict (as before) showing in replication conflict viewer which was
resolved in favour of the Publisher (as before)
Something very strange is going on but I cant quite figure it out.
Curiously when I disabled the trigger (on both servers) and made the
change to table A (on both) then simulated the trigger firing by
manually changing the record in table B (on both), it all works as
expected. 2 downloads, 0 uploads, 2 conflicts resolved in favour of the
publisher (1 on each table).
It appears to be something to do with the trigger - is it conflicting
with the Merge triggers in some way, do you think?
Bazza
Hilary Cotter wrote:[vbcol=seagreen]
> This is hard to figure out. I take it you have column level tracking -
> otherwise you would have no conflict.
> I would do the following. On the Subscriber update A. See the update in B.
> Verify that there are corresponding entries in msmerge_contents for both the
> updates.
> Repeat this on the publisher. Verify that everything is in place. Now do the
> sync.
> There should be 2 downloads. Both from the publisher to the subscriber.
> There should be between one and two conflicts depending on the columns
> changed.
> Run profiler on the subscriber to see what it is doing.
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
>
> "bazza" <bazzaontour2002@.hotmail.com> wrote in message
> news:1164110590.894357.308440@.m73g2000cwd.googlegr oups.com...
|||Just to make a wild guess. ensure that you are using the custom
resolver (publisher wins) on both the tables A and B.
On Nov 22, 10:57 pm, "bazza" <bazzaontour2...@.hotmail.com> wrote:[vbcol=seagreen]
> Thanks for your reply Hilary
> No, we are using Row Level tracking. However Im changing the same
> column on the same record in table A on both servers so it shouldnt
> make any difference should it? Whether Im using Row or Column level
> tracking Im going to cause a conflict as far as I understand it? Please
> correct me if Im wrong.
> Ok, I tried your suggestion. After making the changes on the publisher
> and the subscriber there were 2 entries in the msmerge_contents in each
> database. The 'rowguid' column shows the correct value for the 2
> records (one from A and one from B). So everything seems to be in place
> at this point.
> Then I started the sync and got the same result as before! 1 download
> (table A) and 1 upload (B) is showing in replication monitor. There is
> 1 conflict (as before) showing in replication conflict viewer which was
> resolved in favour of the Publisher (as before)
> Something very strange is going on but I cant quite figure it out.
> Curiously when I disabled the trigger (on both servers) and made the
> change to table A (on both) then simulated the trigger firing by
> manually changing the record in table B (on both), it all works as
> expected. 2 downloads, 0 uploads, 2 conflicts resolved in favour of the
> publisher (1 on each table).
> It appears to be something to do with the trigger - is it conflicting
> with the Merge triggers in some way, do you think?
> Bazza
> Hilary Cotter wrote:
>
>
>
>
>
>
>
>
>
|||Thanks Udit
However, we are using the default resolver (and the subscriber priority
is set to 75.00) so the Publisher should win all conflicts as I
understand it - correct?
Ok, well Ive moved on slightly with the problem.
Ive realized that although the trigger is firing as expected, it doesnt
actually make any changes to the record in table B - the column value
is updated to the same as the existing value.
Therefore I can now understand why there is only 1 conflict.
However I still dont understand why there is 1 upload and 1 download -
I would now expect there just to be the 1 download and 0 uploads.
Any ideas?
Udit Ghai wrote:[vbcol=seagreen]
> Just to make a wild guess. ensure that you are using the custom
> resolver (publisher wins) on both the tables A and B.
>
> On Nov 22, 10:57 pm, "bazza" <bazzaontour2...@.hotmail.com> wrote:
|||You are right that there is one upload when actually you would expect 0
uploads. However even an update to the same value is an update. So I would
say that one would expect 1 upload attempted which should result in a
conflict and 1 download with the publisher update winning (even though there
is no real update).
However since there are no real updates, this may seem cosmetic. But if you
had real updates then I do see the expected behavior -- 2 updates attempted
which will resut in 2 conflicts and the 2 publisher rows in A and B
downloaded.
Note that you dont need to have 2 tables and triggers to see this behavior.
Just a simple table and try updating c1=c1 on both publisher and subscriber.
At this point, I would say that if this doesnt cause you pain in your logic,
its livable. We can address this in the next release of SQL Server.
Hope that helps
--Mahesh
[ This posting is provided "as is" with no warranties and confers no
rights. ]
"bazza" <bazzaontour2002@.hotmail.com> wrote in message
news:1164290161.387477.142820@.l39g2000cwd.googlegr oups.com...
> Thanks Udit
> However, we are using the default resolver (and the subscriber priority
> is set to 75.00) so the Publisher should win all conflicts as I
> understand it - correct?
> Ok, well Ive moved on slightly with the problem.
> Ive realized that although the trigger is firing as expected, it doesnt
> actually make any changes to the record in table B - the column value
> is updated to the same as the existing value.
> Therefore I can now understand why there is only 1 conflict.
> However I still dont understand why there is 1 upload and 1 download -
> I would now expect there just to be the 1 download and 0 uploads.
> Any ideas?
>
> Udit Ghai wrote:
>
|||Thanks very much Mahesh.
It doesnt cause any pain, but Im really just trying to satisfy myself
that Merge replication works as I understand it, as we are about to
deploy this a large, muti user system. What seem like little
inconveniences like this, might cause bigger problems in the live
system.
Anyway, Im still not completely happy I understand. So you are saying
that because I am doing an UPDATE in the trigger, even if I just set
the value to be the same as existing, this will still result in a merge
action?
If so, then I would still expect 2 downloads and 0 uploads, with 2
conflicts resolved in favour of the publisher, wouldnt I?
I am still getting, 1 download (table A), 1 upload (table B) and 1
conflict resolved in favour of the publisher (for the table A update).
I dont believe I should be getting the 1 upload at all.
When the merge engine tries to upload the update to table A it gets a
conflict and the publisher wins so the upload doesnt happen - thats as
I expect.
However when the merge engine tries to upload the update to table B
(created by the trigger) it doesnt appear to get a conflict and the
upload DOES happen. This is not right in my opinion.
If you like I can script out the tables with the trigger?
Thanks for your time
Bazza
Mahesh [MSFT] wrote:[vbcol=seagreen]
> You are right that there is one upload when actually you would expect 0
> uploads. However even an update to the same value is an update. So I would
> say that one would expect 1 upload attempted which should result in a
> conflict and 1 download with the publisher update winning (even though there
> is no real update).
> However since there are no real updates, this may seem cosmetic. But if you
> had real updates then I do see the expected behavior -- 2 updates attempted
> which will resut in 2 conflicts and the 2 publisher rows in A and B
> downloaded.
> Note that you dont need to have 2 tables and triggers to see this behavior.
> Just a simple table and try updating c1=c1 on both publisher and subscriber.
> At this point, I would say that if this doesnt cause you pain in your logic,
> its livable. We can address this in the next release of SQL Server.
> Hope that helps
> --Mahesh
> [ This posting is provided "as is" with no warranties and confers no
> rights. ]
> "bazza" <bazzaontour2002@.hotmail.com> wrote in message
> news:1164290161.387477.142820@.l39g2000cwd.googlegr oups.com...
|||Bazza,
Yes, I agree that update to B should conflict and you should not see an
update.
However if you make a real update (instead of updating c1=c1 or updating to
the same value, as you were doing previously), you should see a conflict and
should not see an upload happen.
Can you try that and let me know if you are still seeing an upload. Then it
can become an issue. Otherwise it doesnt matter because you are updating the
value to the same value at both sides and so essentially there is no
difference in data whether the upload happens or not.
Hope that helps
--Mahesh
[ This posting is provided "as is" with no warranties and confers no
rights. ]
"bazza" <bazzaontour2002@.hotmail.com> wrote in message
news:1164362204.094165.260040@.l12g2000cwl.googlegr oups.com...
> Thanks very much Mahesh.
> It doesnt cause any pain, but Im really just trying to satisfy myself
> that Merge replication works as I understand it, as we are about to
> deploy this a large, muti user system. What seem like little
> inconveniences like this, might cause bigger problems in the live
> system.
> Anyway, Im still not completely happy I understand. So you are saying
> that because I am doing an UPDATE in the trigger, even if I just set
> the value to be the same as existing, this will still result in a merge
> action?
> If so, then I would still expect 2 downloads and 0 uploads, with 2
> conflicts resolved in favour of the publisher, wouldnt I?
> I am still getting, 1 download (table A), 1 upload (table B) and 1
> conflict resolved in favour of the publisher (for the table A update).
> I dont believe I should be getting the 1 upload at all.
> When the merge engine tries to upload the update to table A it gets a
> conflict and the publisher wins so the upload doesnt happen - thats as
> I expect.
> However when the merge engine tries to upload the update to table B
> (created by the trigger) it doesnt appear to get a conflict and the
> upload DOES happen. This is not right in my opinion.
> If you like I can script out the tables with the trigger?
> Thanks for your time
> Bazza
> Mahesh [MSFT] wrote:
>