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:
>

completed successfully, but not sending and no error.

hi, i have a message queue system using sql 2005 service broker.

the code and setup is the same on both dev and live database. but

soon after i restored a live backup to dev. the queue stopped

working on dev, live is ok thou. after some trouble shooting, i

found that the server is not sending the message at all, but it says

"Command(s) completed successfully" without any error messages.

setup:

--

create message type TestQueryMessage validation = none

create contract TestQueryContract (TestQueryMessage sent by initiator)

create queue TestSenderQueue

create service TestSenderService on queue TestSenderQueue

create queue TestQueueReceiver

create service TestServiceReceiver on queue TestQueueReceiver (TestQueryContract)

send message:

-

declare @.conversationhandle uniqueidentifier;

begin dialog @.conversationhandle

from service [TestSenderService]

to service 'TestServiceReceiver'

on contract [TestQueryContract]

with encryption = off;

send on conversation @.conversationhandle

message type [TestQueryMessage] ('blah blah blah');

result:

-

Command(s) completed successfully.

but when i do "select * from TestQueueReceiver", there's nothing. and i sure nothing else had picked up the messages.

please advise. thanks a lot.

Look in sys.transmission_queue on the sender's database. The transmission_status column should indicate the problem. Most likely the broker was not enabled back after the restore operation.

HTH,
~ Remus

Complete newbie to replication, have a question

We have had a server in LocationA for quite some time. The need arose
recently to have a server setup in LocationB due to some bandwidth issues.
LocationB was given a snapshot of the DB @. LocationA when it was first
setup. However, both machines will need to have the same data as both are
used in a production environment. I will probably want to do this nightly.
I have read a lot of posts and webistes, but still am semi in the dark.
About the only thing i've figured out (i think) is that I will need to do
some sort of merge replicaiton.
What do I need to do to get started, a link to a HOW TO for a smiliar setup
would be great. I have read a bit and some mention having a 3rd server, do
i need a 3rd server as the final merged server? I guess more importantly is
a 3rd server an optimal solution?
Any help would be greatly appreciated.
Forgot to mention, I am running SQL Server 2000 on both servers.
"Lucas Graf" <lgraf2000@.comcast.net> wrote in message
news:OjZ0E0bQGHA.5296@.TK2MSFTNGP09.phx.gbl...
> We have had a server in LocationA for quite some time. The need arose
> recently to have a server setup in LocationB due to some bandwidth issues.
> LocationB was given a snapshot of the DB @. LocationA when it was first
> setup. However, both machines will need to have the same data as both are
> used in a production environment. I will probably want to do this
> nightly.
> I have read a lot of posts and webistes, but still am semi in the dark.
> About the only thing i've figured out (i think) is that I will need to do
> some sort of merge replicaiton.
> What do I need to do to get started, a link to a HOW TO for a smiliar
> setup would be great. I have read a bit and some mention having a 3rd
> server, do i need a 3rd server as the final merged server? I guess more
> importantly is a 3rd server an optimal solution?
> Any help would be greatly appreciated.
>
|||In merge replication you will have Distributor, Publisher and Subscriber.
They are different roles for a replication setup but they can be on the same
physical machine. I think the 3rd server you mentioned is distributor. You
can setup the distributor on the same server with the publisher, so you
don't need a third machine.
Yi Chen
"Lucas Graf" <lgraf2000@.comcast.net> wrote in message
news:OjZ0E0bQGHA.5296@.TK2MSFTNGP09.phx.gbl...
> We have had a server in LocationA for quite some time. The need arose
> recently to have a server setup in LocationB due to some bandwidth issues.
> LocationB was given a snapshot of the DB @. LocationA when it was first
> setup. However, both machines will need to have the same data as both are
> used in a production environment. I will probably want to do this
> nightly.
> I have read a lot of posts and webistes, but still am semi in the dark.
> About the only thing i've figured out (i think) is that I will need to do
> some sort of merge replicaiton.
> What do I need to do to get started, a link to a HOW TO for a smiliar
> setup would be great. I have read a bit and some mention having a 3rd
> server, do i need a 3rd server as the final merged server? I guess more
> importantly is a 3rd server an optimal solution?
> Any help would be greatly appreciated.
>
|||Ok. That makes sense.
I have been messing around trying to set it up today, and am still lost.
Currently the 2 servers are out of sync. Both have data the other doesn't.
What do I need to do to get them both to have the same data now, and then
keep them replciating from here on out?
"Yi Chen [MSFT]" <yiche@.online.microsoft.com> wrote in message
news:%23o7jSucQGHA.1204@.TK2MSFTNGP12.phx.gbl...
> In merge replication you will have Distributor, Publisher and Subscriber.
> They are different roles for a replication setup but they can be on the
> same physical machine. I think the 3rd server you mentioned is
> distributor. You can setup the distributor on the same server with the
> publisher, so you don't need a third machine.
> Yi Chen
> "Lucas Graf" <lgraf2000@.comcast.net> wrote in message
> news:OjZ0E0bQGHA.5296@.TK2MSFTNGP09.phx.gbl...
>
|||Lucas Graf wrote:
> We have had a server in LocationA for quite some time. The need arose
> recently to have a server setup in LocationB due to some bandwidth issues.
> LocationB was given a snapshot of the DB @. LocationA when it was first
> setup. However, both machines will need to have the same data as both are
> used in a production environment. I will probably want to do this nightly.
> I have read a lot of posts and webistes, but still am semi in the dark.
> About the only thing i've figured out (i think) is that I will need to do
> some sort of merge replicaiton.
> What do I need to do to get started, a link to a HOW TO for a smiliar setup
> would be great. I have read a bit and some mention having a 3rd server, do
> i need a 3rd server as the final merged server? I guess more importantly is
> a 3rd server an optimal solution?
> Any help would be greatly appreciated.
|||Lucas,
presumably you have initialized the subscriber and are using merge
replication? If so, you just need to synchronize the subscriber to have the
data merged. You'll need to consider who should win conflicts (publisher by
default) and if conflicts should be allowed (filtering).
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||Ok.
I got this far and was tentative on going further. I will keep trucking and
see what else may come up that causes issues. Thanks for the help so far, I
hope to report back a positive experience.
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:%23ogHO4uQGHA.2628@.TK2MSFTNGP15.phx.gbl...
> Lucas,
> presumably you have initialized the subscriber and are using merge
> replication? If so, you just need to synchronize the subscriber to have
> the data merged. You'll need to consider who should win conflicts
> (publisher by default) and if conflicts should be allowed (filtering).
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
|||I guess I am still confused w/the whole publisher/distributor thing.
Since I want both servers to merge both of their data are they both set to
publishers and distributors? Or is only 1 a publisher and both distributors
and subscribers?
So confused..
"Lucas Graf" <lgraf2000@.comcast.net> wrote in message
news:Ob$pMuzQGHA.2436@.TK2MSFTNGP11.phx.gbl...
> Ok.
> I got this far and was tentative on going further. I will keep trucking
> and see what else may come up that causes issues. Thanks for the help so
> far, I hope to report back a positive experience.
>
> "Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
> news:%23ogHO4uQGHA.2628@.TK2MSFTNGP15.phx.gbl...
>
|||Ok.
ServerA
I have set him to a Distributor, Publisher and set the Database i want to
publish to the other server as the "push" datatabase. All seems good on
ServerA.
ServerB
Not sure what to do here so its data gets pushed to ServerA to be merged
there as well. Everything I try i end up getting the error "You cannot
create a merge replicaion in database xxxc. The database contains one or
more merge subscritptions that are anonymous or that use the Priority of the
Publisher when resolving conflicts."
"Lucas Graf" <lgraf@.nvidia.com> wrote in message
news:uSQuaI8QGHA.5552@.TK2MSFTNGP14.phx.gbl...
>I guess I am still confused w/the whole publisher/distributor thing.
> Since I want both servers to merge both of their data are they both set to
> publishers and distributors? Or is only 1 a publisher and both
> distributors and subscribers?
> So confused..
>
> "Lucas Graf" <lgraf2000@.comcast.net> wrote in message
> news:Ob$pMuzQGHA.2436@.TK2MSFTNGP11.phx.gbl...
>
|||Maybe Bidirectional Transactional Replication is more what I am looking for?
"Lucas Graf" <lgraf@.nvidia.com> wrote in message
news:uSQuaI8QGHA.5552@.TK2MSFTNGP14.phx.gbl...
>I guess I am still confused w/the whole publisher/distributor thing.
> Since I want both servers to merge both of their data are they both set to
> publishers and distributors? Or is only 1 a publisher and both
> distributors and subscribers?
> So confused..
>
> "Lucas Graf" <lgraf2000@.comcast.net> wrote in message
> news:Ob$pMuzQGHA.2436@.TK2MSFTNGP11.phx.gbl...
>

Compatibility_52_409_30003

Hi
Sorry to those caught in this cross post, but the setup group is a bit
dead...you guys are usually spot on.
I am trying to set up a hot spare server. The production database is
set up with server collation Compatibility_52_409_30003 (and for teh
database too).
I am having a problem with trying to create a hot spare server with
this collation, or even to get a master rebuild to use it. Its not in
the options given.
Anyone have any idea how I can get this working?To let anyone who has this problem know: record an unattended install
file. Modify this to use the collation you need, and then run it.
Check BOL for syntax.