merge replications causes insert timeout

Hi All,

i need help. i have a merge replication running every 10 minutes. the replication is for 1 database consisting around 50 tables and all 50 tables are marked as article to be published. let's say the publisher is server A and the subscriber is server B. for explanation purpose, let's pick 1 table from the database, let's say table X

in server A, there are applications running that insert and update table X. in server B, there are applications running that select and delete table X. table X at average, has tens of thousands rows.

now comes the problem, every 10 minutes whenever the sql merge agent is running, sometimes whenan application insert a row to table X in server A, it returns an error : "Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding.". when i stop the replication (delete the subscriber), the row insertion to table X in server A seems fine. then i come to a doubtful conclusion, that the timeout error happened because when the insert statement executes, the replication locks the table for merging.

could somebody verify this? if my conclusion is true, then how am i suppose to do this? (see bold text above). really2 appreciate the help

Yes, merge agent can fail due to deadlocking. WHat you need to ensure is that your application is taking the minimum locks necessary. This is no different than two applications accessing/modifying the same data simultaneously, consider merge replication a second application.

Review your queries, insert/update/delete statement and see how transactions are being used, what locking behavior, queries are written optimally, etc.

Merge Replication does not apply changes in original order!

Has anyone noticed that Merge Replication does not apply changes in original
order?
This causes foreign keys errors but I fixed that by making the foreign keys
'not for replication'.
It also causes unique index errors if I am manipulating the unique values in
an exact sequence - anyone have a solution to this one?
That's correct!
I'm not exactly sure what order it will apply the changes in however. But
what it does is gathers up the changes, figures out what to apply where, and
then applies it one by one. If there is a pk/fk/GodKnowsWhat violation in
the initial attempt, the modification goes into a queue and when the merge
agent has completed the initial pass, it will retry the failures another
time.
For the majority of the cases, this second (or third, forth, etc) retry will
succeed. Sometimes you need to bump up the generationsperbatch switches to
2000 for this to succeed.
Merge replication in SQL 2005 will apply changes which belong to related
tables together in the same batch boundary so you don't have to worry about
this anymore.
Also becareful that you don't enforce cascading updates and deletes for
replication as this can get you into trouble.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"Jim Breffni" <Jim Breffni@.discussions.microsoft.com> wrote in message
news:F55EE9FB-7733-4EDD-8E2B-A439978D3924@.microsoft.com...
> Has anyone noticed that Merge Replication does not apply changes in
original
> order?
> This causes foreign keys errors but I fixed that by making the foreign
keys
> 'not for replication'.
> It also causes unique index errors if I am manipulating the unique values
in
> an exact sequence - anyone have a solution to this one?
|||Thanks Hilary - where do I set the checkSortOrder generationsperbatch switch?
Jim.
"Hilary Cotter" wrote:

> That's correct!
> I'm not exactly sure what order it will apply the changes in however. But
> what it does is gathers up the changes, figures out what to apply where, and
> then applies it one by one. If there is a pk/fk/GodKnowsWhat violation in
> the initial attempt, the modification goes into a queue and when the merge
> agent has completed the initial pass, it will retry the failures another
> time.
> For the majority of the cases, this second (or third, forth, etc) retry will
> succeed. Sometimes you need to bump up the generationsperbatch switches to
> 2000 for this to succeed.
> Merge replication in SQL 2005 will apply changes which belong to related
> tables together in the same batch boundary so you don't have to worry about
> this anymore.
> Also becareful that you don't enforce cascading updates and deletes for
> replication as this can get you into trouble.
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
>
> "Jim Breffni" <Jim Breffni@.discussions.microsoft.com> wrote in message
> news:F55EE9FB-7733-4EDD-8E2B-A439978D3924@.microsoft.com...
> original
> keys
> in
>
>
|||you don't. What you do is make the generationsperbatch switches very large
in hopes that the pk fk modifications will occur in the same batch.
You should set
MaxDownloadChanges
MaxUploadChanges
UploadGenerationsPerBatch
DownloadGenerationsPerBatch
UploadReadChangesPerBatch
DownloadReadChangesPerBatch
UploadWriteChangesPerBatch
DownloadWriteChangesPerBatch
to 2000


Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"Jim Breffni" <JimBreffni@.discussions.microsoft.com> wrote in message
news:BD122CA6-5721-428B-8F1E-791E99CE1E72@.microsoft.com...
> Thanks Hilary - where do I set the checkSortOrder generationsperbatch
switch?[vbcol=seagreen]
>
> Jim.
>
> "Hilary Cotter" wrote:
But[vbcol=seagreen]
and[vbcol=seagreen]
in[vbcol=seagreen]
merge[vbcol=seagreen]
will[vbcol=seagreen]
to[vbcol=seagreen]
about[vbcol=seagreen]
values[vbcol=seagreen]