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: Insert Trigger ONLY when replicating

Hello,
I have a scenario where clients enter data into a MSDE local database on
their laptop. I would like to add a trigger on that table that fires only
on the Server (SQL2K Ent.) when they are synchronizing when a new records
has been inserted. By looking at the Merge trigger automaticcaly generated
on the table, I found something interesting:
"if sessionproperty('replication_agent') = 1 and (select
trigger_nestlevel()) = 1"
Therfore, I've created my trigger like the following:
Create Trigger tg_Inserted On tblBlaBla FOR INSERT
AS
if not sessionproperty('replication_agent') = 1
return
Insert Into tblTest (Account_Code, Product_Code, DateCreation)
Select ins.Account_code, ins.Product_Code, GetDate()
From Inserted As Ins
I've done some test and everything works fine. However, before putting this
in Production, I was wondering if I absolutely need to put the "Select
trigger_nestlevel() ..." or not. I've read in the newsgroups and some are
saying you need to, some are saying you don't need to... Since it is a very
well documented feature, can anyone confirm me the proper way of doing this?
That would be greatly appreciated. Thanks.
Nest level check allows to avoid ping-pong traffic from subscriber to
publisher and vice versa
But in your case that should matter only if you are also replicating table
tblTest
Regards,
Kestutis Adomavicius
Consultant
UAB "Baltic Software Solutions"
"Christian Hamel" <chamel@.NOSPAM.com> wrote in message
news:udebeQ7WFHA.2692@.TK2MSFTNGP15.phx.gbl...
Hello,
I have a scenario where clients enter data into a MSDE local database on
their laptop. I would like to add a trigger on that table that fires only
on the Server (SQL2K Ent.) when they are synchronizing when a new records
has been inserted. By looking at the Merge trigger automaticcaly generated
on the table, I found something interesting:
"if sessionproperty('replication_agent') = 1 and (select
trigger_nestlevel()) = 1"
Therfore, I've created my trigger like the following:
Create Trigger tg_Inserted On tblBlaBla FOR INSERT
AS
if not sessionproperty('replication_agent') = 1
return
Insert Into tblTest (Account_Code, Product_Code, DateCreation)
Select ins.Account_code, ins.Product_Code, GetDate()
From Inserted As Ins
I've done some test and everything works fine. However, before putting this
in Production, I was wondering if I absolutely need to put the "Select
trigger_nestlevel() ..." or not. I've read in the newsgroups and some are
saying you need to, some are saying you don't need to... Since it is a very
well documented feature, can anyone confirm me the proper way of doing this?
That would be greatly appreciated. Thanks.
|||Great. I'm not replicating this table. Thanks for the information.
"Kestutis Adomavicius" <kicker.lt@.noospaam_tut.by> wrote in message
news:eVz8BD8WFHA.1796@.TK2MSFTNGP15.phx.gbl...
> Nest level check allows to avoid ping-pong traffic from subscriber to
> publisher and vice versa
> But in your case that should matter only if you are also replicating table
> tblTest
> --
> Regards,
> Kestutis Adomavicius
> Consultant
> UAB "Baltic Software Solutions"
>
> "Christian Hamel" <chamel@.NOSPAM.com> wrote in message
> news:udebeQ7WFHA.2692@.TK2MSFTNGP15.phx.gbl...
> Hello,
> I have a scenario where clients enter data into a MSDE local database
on
> their laptop. I would like to add a trigger on that table that fires only
> on the Server (SQL2K Ent.) when they are synchronizing when a new records
> has been inserted. By looking at the Merge trigger automaticcaly
generated
> on the table, I found something interesting:
> "if sessionproperty('replication_agent') = 1 and (select
> trigger_nestlevel()) = 1"
> Therfore, I've created my trigger like the following:
> Create Trigger tg_Inserted On tblBlaBla FOR INSERT
> AS
> if not sessionproperty('replication_agent') = 1
> return
> Insert Into tblTest (Account_Code, Product_Code, DateCreation)
> Select ins.Account_code, ins.Product_Code, GetDate()
> From Inserted As Ins
> I've done some test and everything works fine. However, before putting
this
> in Production, I was wondering if I absolutely need to put the "Select
> trigger_nestlevel() ..." or not. I've read in the newsgroups and some are
> saying you need to, some are saying you don't need to... Since it is a
very
> well documented feature, can anyone confirm me the proper way of doing
this?
> That would be greatly appreciated. Thanks.
>
|||I think it is to prevent recursive triggers from causing duplicate entries
in msmerge_contents. I could be wrong here.
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
"Christian Hamel" <chamel@.NOSPAM.com> wrote in message
news:udebeQ7WFHA.2692@.TK2MSFTNGP15.phx.gbl...
> Hello,
> I have a scenario where clients enter data into a MSDE local database
on
> their laptop. I would like to add a trigger on that table that fires only
> on the Server (SQL2K Ent.) when they are synchronizing when a new records
> has been inserted. By looking at the Merge trigger automaticcaly
generated
> on the table, I found something interesting:
> "if sessionproperty('replication_agent') = 1 and (select
> trigger_nestlevel()) = 1"
> Therfore, I've created my trigger like the following:
> Create Trigger tg_Inserted On tblBlaBla FOR INSERT
> AS
> if not sessionproperty('replication_agent') = 1
> return
> Insert Into tblTest (Account_Code, Product_Code, DateCreation)
> Select ins.Account_code, ins.Product_Code, GetDate()
> From Inserted As Ins
> I've done some test and everything works fine. However, before putting
this
> in Production, I was wondering if I absolutely need to put the "Select
> trigger_nestlevel() ..." or not. I've read in the newsgroups and some are
> saying you need to, some are saying you don't need to... Since it is a
very
> well documented feature, can anyone confirm me the proper way of doing
this?
> That would be greatly appreciated. Thanks.
>

Merge replication problem MSDE

Summary:
Failed to insert detail rows in master-detail scenario during merge replication. Not always, sometimes.
Topology:
I've got 5 servers running MSDE, one of them is central publisher/distributor for merge type of replication.
There is no row or column filtering: all subscribers have all data. Central publisher resolve conflicts with default revolvers.
Some tables have relations: master-detail (i.e. orders-ordersDetails, etc). Relations are defined in tables as FK.
My application which fills data use datasets with relations between tables defined the same way as in the sqlserver. So app first work with dataset which is unable to receive details without master record. When data updated to msde, it is done without errors, means master and details table updated correctly (tables at msde has relations too)
Applications running on 4 different locations and fill data to local db (subscriber to central publisher).
Sync occurs every 15 minutes in the following order (merge agents run at publisher/distributor):
subscriber1: every 15 minutes, starts at 00.00h
subscriber2: every 15 minutes, starts at 00.03h
subscriber3: every 15 minutes, starts at 00.06h
subscriber4: every 15 minutes, starts at 00.09h
Sync lasts for average 15 sec, never 3 min.
Problem details:
Message:
The row was inserted at 'CentPub.myDB' but could not be inserted at 'Sub2.MyDB'. INSERT statement conflicted
with COLUMN FOREIGN KEY constraint 'FK_OrdersDetails_Orders'. The conflict occurred in database 'MyDB',
table 'Orders', column 'OrderID'.
Description
CentPub is central publisher which just collecting data from subscribers. So, Order was made on one of the other subscribers different than Sub2, mean user at location3 insert order with details in local database, after a while, CentPub take this order to its database (MyDB), after that CentPub try to sync with some of the other subscribers (i.e. Sub2 == location2) and then for some unknown reason orderDeatils failed to insert in Sub2's orderDeatil table because constraint 'FK_OrdersDetails_Orders' conflict.
After that happened, thing goes like in http://support.microsoft.com/kb/307482 (generally: in next session, failed order details are deleted
from CentPub, which means deleted from all subscribers after syncs.)
The problem is that subscribers have tables with relations, so Cause isn't as describe in MS kb because my tables have relations at all servers.
After finished replications I have Orders without details records at all subscribers, so I assume that merge agents sometimes (not always) try to insert details prior to master table during the same sync session.
In resolution section of MS kb article they say 'Mark the subscriber foreign key constraints as NOT FOR REPLICATION'.
In relations definition I see 'Enforce relationship for replication) options which is enabled in my tables.
If I turn that option off, is it possible to happen that my subscribers receive details without master record? (My observation of behavior says that will not happen(that will lead to problems in my app because my datasets enforce relations too).
Deleted record I bring back to life with conflict manager in EM, but I'd like it never happen.
Is it a bug, side effect or something I do it the wrong way?

Thanks and regardsThe answer is no. When you change your constraints into "NOT FOR REPLICATION" the merge agent is the only process that will ignore the constraint as it propagates the changes amongst the subscribers. The point at which the data is entered into the tables (whether it be front-end app, sproc, etc.) will still enforce the FK constraints.|||Thanks.
I'll try to remove that option.
Anyway, what is the purpose of this option if merge agents don't care about tables with relations?
It is very strange in my situation because I'm sure that my data is in correct form (master-detail) in every point in time (first master, than details) so I don't expect problems during replication.|||Merge agents do care unless you tell them not to. In previous dealings with M$oft, it appears to be related to the number of transactions and the generations associated with them. The merge agent can be set to transmit up to 2000 generations, but will sometines still split up the parent and child transactions, especially in a heavy OLTP database.

Here is link: http://support.microsoft.com/kb/307356/en-us|||Thanks tomh53.
I'll try to increase generations to 2000 and mark not for replication (it should be enough to avoid the problem)
Regards
g.|||I've tested with max value for generations parameter (2000) and everything work fine.
Thanks and regares
G.

Merge Replication Not Accounting For Dependencies

We have a SQL Serrver 2000 database that utilizes merge replication. Every
now and then the replication process attempts to insert or update tables out
of order. For instance: We have a TransactionDocuments table that has a one
to many relationship with a TransactionLines table. Sometimes SQL Server will
attempt to instert new TransactionLines before it has inserted the
TransactionDocuments the TransactionLines depend on.
I thought merge replication was supposed to keep track of these
dependencies. If so, is there a bug that has a workaround or something else I
need to check?
Thanks,
Matt
Matt,
you could set the NOT FOR REPLICATION attribute on the FKs to true. In SQL
server 2005 it is possible to specify the merge replication order but not so
in SQL 2000. Usually the order selected in SQL Server 2000 is correct but
not always. Please check these articles:
http://www.replicationanswers.com/Me...derArticle.asp
http://support.microsoft.com/default.aspx?scid=kb;[LN];307356
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||Merge replication has a rich set of features regarding dependencies, but
these are typically for sensing join filters. As Paul has pointed out child
records can be inserted before parent records, but normally these are
resolved within a batch and if not the next time the agent runs the
dependencies will be fixed as the parent will get in before the child the
second time around.
The generic work around is to use the not for replication switch on all
constraints.
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
"Matt Sradley" <MattSradley@.discussions.microsoft.com> wrote in message
news:4C82B8F1-0176-4E6B-B0AF-D4F4F840A268@.microsoft.com...
> We have a SQL Serrver 2000 database that utilizes merge replication. Every
> now and then the replication process attempts to insert or update tables
> out
> of order. For instance: We have a TransactionDocuments table that has a
> one
> to many relationship with a TransactionLines table. Sometimes SQL Server
> will
> attempt to instert new TransactionLines before it has inserted the
> TransactionDocuments the TransactionLines depend on.
> I thought merge replication was supposed to keep track of these
> dependencies. If so, is there a bug that has a workaround or something
> else I
> need to check?
> Thanks,
> Matt
|||Thanks,
You both answered my question. It looks like the main problem are self
referencing tables in our DB. Not for replication will fix the problem. Do
you think I should do a constraint check after the replication process is
finished? It makes me nervous that data is getting put in the tables without
the checks in place.
"Hilary Cotter" wrote:

> Merge replication has a rich set of features regarding dependencies, but
> these are typically for sensing join filters. As Paul has pointed out child
> records can be inserted before parent records, but normally these are
> resolved within a batch and if not the next time the agent runs the
> dependencies will be fixed as the parent will get in before the child the
> second time around.
> The generic work around is to use the not for replication switch on all
> constraints.
> --
> 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
> "Matt Sradley" <MattSradley@.discussions.microsoft.com> wrote in message
> news:4C82B8F1-0176-4E6B-B0AF-D4F4F840A268@.microsoft.com...
>
>
|||Matt,
I suppose the best option is to upgrade to SQL 2005, but if this is not an
option currently for you, then it's entirely your decision about the
constraint checking after synchronization - I never do it FWIW.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)

Merge replication is deleting records

I have a merge replication of some tables between two servers. Something
curious happens. When I insert a register in a table in one database, then I
wait and the other table in the database doesn't update the new register and
I refresh the view in my original database and the record I inserted is not
there anymore. Is like when I added the record he found that is not in the
other site so he thinks he should delete it instead of inserting in the
other site. Why this happens? Is it a bug of replication?
This is scaring me because the same problem in this lilte table is happening
in a master table where I keep inventory, by some reason sometimes (not very
often) a record is being deleted and I sopposed is for the replication
because the process inserts the record in one place and then he finds that
this record is not in the other place and instead of inserting he deletes
the record.
Please help me because I can't miss any of this records, what can I do, how
can I trace what happened? I created a trigger for delete that inserts in a
new table every time a register is being deleted in this master table and he
inserted the deleted record in this table but how can I trace how was
deleted?
Please help me with this replication problem
Thanks in advance
Jennyfer
Jennyfer ,
do you have filters on the publication?
This scenario could occur if the record doesn't satisfy the filter, so an
insert on the subscriber will be uploaded then downloaded as a delete.
HTH,
Paul Ibison
|||I don't have any filters
Thanks
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:%23e$%231fBdEHA.4092@.TK2MSFTNGP10.phx.gbl...
> Jennyfer ,
> do you have filters on the publication?
> This scenario could occur if the record doesn't satisfy the filter, so an
> insert on the subscriber will be uploaded then downloaded as a delete.
> HTH,
> Paul Ibison
>
|||Would it be the case that this insert violates some constraint (PK, FK,
check, etc) at the subscriber?
Hope that helps
--Mahesh
[ This posting is provided "as is" with no warranties and confers no
rights. ]
"Jennyfer J Barco" <pdwhitt@.nospam.wdsinc.com> wrote in message
news:urTZBYCdEHA.3728@.TK2MSFTNGP09.phx.gbl...[vbcol=seagreen]
> I don't have any filters
> Thanks
> "Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
> news:%23e$%231fBdEHA.4092@.TK2MSFTNGP10.phx.gbl...
an
>
|||To find out a bit more info you might want to...
To see who is doing the delete...select publisher_insertcount,
publisher_updatecount, publisher_deletecount,
subscriber_insertcount, subscriber_updatecount, subscriber_deletecount
from dbo.MSmerge_history
Run profiler on the publisher and subscriber to see why/how the delete is
being done.
Check the conflict viewer to see if any conflicts are registered.
HTH,
Paul Ibison
|||Thanks so much for the constraint idea (PK, FK...) I have a FOREIGN KEY in
the master table that the product register should exists in another table
wich is beign replicated too. It looks that he tryed to insert the master
register and the other table was not replicated yet so the error in the
confict_Mergename_mastertable table says that the insert in the other site
conflicted with the FOREIGN KEY and my records that were deleted are in this
table. The thing is, if there is a problem like this and SQL can't insert
the register in the other site but should not delete the record in my site
where it was originated inserted. Is there any settings where I can say that
if an insert error comes don't delete the original record just write in this
log table that the record was not replicated. Please give me an idea and
thanks so much for you help and tips
Jennyfer
"Mahesh [MSFT]" <maheshrd@.hotmail.com> wrote in message
news:eClCOeEdEHA.3300@.TK2MSFTNGP09.phx.gbl...
> Would it be the case that this insert violates some constraint (PK, FK,
> check, etc) at the subscriber?
> Hope that helps
> --Mahesh
> [ This posting is provided "as is" with no warranties and confers no
> rights. ]
> "Jennyfer J Barco" <pdwhitt@.nospam.wdsinc.com> wrote in message
> news:urTZBYCdEHA.3728@.TK2MSFTNGP09.phx.gbl...
> an
>
|||One thing that caused this was because the replication stopped wth no reason
in the weekend and then we started again. It looks thar SQL trys to insert
all the transactions that were not inserted while it was stopped and in this
there is no order or priority with the tables( maybe he sorts
alphabeticaly). May I define a priority so always when I start synchronizing
the outer reference tables comes first? May I select the order of the tables
in which I want the replication to start?
Thanks so much
"Jennyfer J Barco" <pdwhitt@.nospam.wdsinc.com> wrote in message
news:u8V6DXMdEHA.3728@.TK2MSFTNGP09.phx.gbl...
> Thanks so much for the constraint idea (PK, FK...) I have a FOREIGN KEY in
> the master table that the product register should exists in another table
> wich is beign replicated too. It looks that he tryed to insert the master
> register and the other table was not replicated yet so the error in the
> confict_Mergename_mastertable table says that the insert in the other site
> conflicted with the FOREIGN KEY and my records that were deleted are in
this
> table. The thing is, if there is a problem like this and SQL can't insert
> the register in the other site but should not delete the record in my site
> where it was originated inserted. Is there any settings where I can say
that
> if an insert error comes don't delete the original record just write in
this[vbcol=seagreen]
> log table that the record was not replicated. Please give me an idea and
> thanks so much for you help and tips
> Jennyfer
> "Mahesh [MSFT]" <maheshrd@.hotmail.com> wrote in message
> news:eClCOeEdEHA.3300@.TK2MSFTNGP09.phx.gbl...
so[vbcol=seagreen]
delete.
>
|||Basically you are running into the compensate_for_errors problem.
This problem is that if a change from publisher (say) fails to get applied
at the subscriber (for some reason, PK,FK,CHECK,etc constraints) it undoes
the change at the publisher. So a insert from publisher when fails at the
subscriber gets deleted at the publisher too. Similary a delete from
publisher when fails at the subscriber, it gets re-inserted at the
publisher.
The KB article for that is here:
http://support.microsoft.com/default...&Product=sql2k
This is fixed in a QFE and please contact PSS for the QFE.
Hope that helps
--Mahesh
[ This posting is provided "as is" with no warranties and confers no
rights. ]
"Jennyfer J Barco" <pdwhitt@.nospam.wdsinc.com> wrote in message
news:u8V6DXMdEHA.3728@.TK2MSFTNGP09.phx.gbl...
> Thanks so much for the constraint idea (PK, FK...) I have a FOREIGN KEY in
> the master table that the product register should exists in another table
> wich is beign replicated too. It looks that he tryed to insert the master
> register and the other table was not replicated yet so the error in the
> confict_Mergename_mastertable table says that the insert in the other site
> conflicted with the FOREIGN KEY and my records that were deleted are in
this
> table. The thing is, if there is a problem like this and SQL can't insert
> the register in the other site but should not delete the record in my site
> where it was originated inserted. Is there any settings where I can say
that
> if an insert error comes don't delete the original record just write in
this[vbcol=seagreen]
> log table that the record was not replicated. Please give me an idea and
> thanks so much for you help and tips
> Jennyfer
> "Mahesh [MSFT]" <maheshrd@.hotmail.com> wrote in message
> news:eClCOeEdEHA.3300@.TK2MSFTNGP09.phx.gbl...
so[vbcol=seagreen]
delete.
>
|||Don't forget this:
One thing that caused this was because the replication stopped wth no reason
in the weekend and then we started again. It looks thar SQL trys to insert
all the transactions that were not inserted while it was stopped and in this
there is no order or priority with the tables( maybe he sorts
alphabeticaly). May I define a priority so always when I start synchronizing
the outer reference tables comes first? May I select the order of the tables
in which I want the replication to start?
Thanks so much
"Mahesh [MSFT]" <maheshrd@.hotmail.com> wrote in message
news:%23Yz$O9MdEHA.1692@.tk2msftngp13.phx.gbl...
> Basically you are running into the compensate_for_errors problem.
> This problem is that if a change from publisher (say) fails to get applied
> at the subscriber (for some reason, PK,FK,CHECK,etc constraints) it undoes
> the change at the publisher. So a insert from publisher when fails at the
> subscriber gets deleted at the publisher too. Similary a delete from
> publisher when fails at the subscriber, it gets re-inserted at the
> publisher.
> The KB article for that is here:
>
http://support.microsoft.com/default...&Product=sql2k[vbcol=seagreen]
> This is fixed in a QFE and please contact PSS for the QFE.
> Hope that helps
> --Mahesh
> [ This posting is provided "as is" with no warranties and confers no
> rights. ]
> "Jennyfer J Barco" <pdwhitt@.nospam.wdsinc.com> wrote in message
> news:u8V6DXMdEHA.3728@.TK2MSFTNGP09.phx.gbl...
in[vbcol=seagreen]
table[vbcol=seagreen]
master[vbcol=seagreen]
site[vbcol=seagreen]
> this
insert[vbcol=seagreen]
site[vbcol=seagreen]
> that
> this
FK,[vbcol=seagreen]
filter,
> so
> delete.
>
|||You cannot specify the processing order in SQL Server 2000.
There is a possibility as you say that some changes could go before the
others that can cause this kind of conflicting behavior.
However if the PKs and FKs were defined as NOT FOR REPLICATION then probably
you would not have hit this error.
Or had there been merge filters between the articles, probably they would
have been enumerated in the proper order too.
So what is the current state? Has it synched up now?
If you make any inserts to tables at the publisher do you see them
propagated to the subscriber?
Hope that helps
--Mahesh
[ This posting is provided "as is" with no warranties and confers no
rights. ]
"Jennyfer J Barco" <pdwhitt@.nospam.wdsinc.com> wrote in message
news:uxUYZaOdEHA.3632@.TK2MSFTNGP09.phx.gbl...
> Don't forget this:
> One thing that caused this was because the replication stopped wth no
reason
> in the weekend and then we started again. It looks thar SQL trys to insert
> all the transactions that were not inserted while it was stopped and in
this
> there is no order or priority with the tables( maybe he sorts
> alphabeticaly). May I define a priority so always when I start
synchronizing
> the outer reference tables comes first? May I select the order of the
tables[vbcol=seagreen]
> in which I want the replication to start?
> Thanks so much
> "Mahesh [MSFT]" <maheshrd@.hotmail.com> wrote in message
> news:%23Yz$O9MdEHA.1692@.tk2msftngp13.phx.gbl...
applied[vbcol=seagreen]
undoes[vbcol=seagreen]
the
>
http://support.microsoft.com/default...&Product=sql2k[vbcol=seagreen]
KEY[vbcol=seagreen]
> in
> table
> master
the[vbcol=seagreen]
> site
in[vbcol=seagreen]
> insert
> site
say[vbcol=seagreen]
in[vbcol=seagreen]
and
> FK,
> filter,
>

Merge Replication increases the bulk insert time dramatically

You have triggers firing which populate the
MSmerge_contents table. You also have the merge agent
which tries to read this table. So, there are 2
additional features. You can't remove the triggers -
they're mandatory so try disabling the merge agent while
the bulk upload takes place. If this still takes a while,
try the same bulk load on an identical copy of the table
without the triggers - just to confirm that it is the
triggers and not the higher quantity of data that you now
have in the table, as placing rows according to the PK
value will by necessity take longer.
Rgds,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
I checked, and we didn't have FIRE_TRIGGERS in our bulk insert commands. The
data was still being replicated, however.
Can you programatically disable the merge agent and then re-enable it during
bulk inserts? If so, can you point me to the right commands?
When we turn off replication, the insertion times remain around 3 seconds.
"Paul Ibison" wrote:

> You have triggers firing which populate the
> MSmerge_contents table. You also have the merge agent
> which tries to read this table. So, there are 2
> additional features. You can't remove the triggers -
> they're mandatory so try disabling the merge agent while
> the bulk upload takes place. If this still takes a while,
> try the same bulk load on an identical copy of the table
> without the triggers - just to confirm that it is the
> triggers and not the higher quantity of data that you now
> have in the table, as placing rows according to the PK
> value will by necessity take longer.
> Rgds,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
>
|||There's something I don't understand here. When you did
the bulk insert without firing the triggers, how is it
possible that the data could be merged? Perhaps when you
say the data was still being replicated, you mean that
the merge agent was running? Anyway, What are the times:
(a) no replication
(b) replication and no firing of triggers and the merge
agent on a schedule which means it is not currently
running
(c) replication and firing of triggers and the merge
agent on a schedule which means it is not currently
running
(d) replication and firing of triggers and the merge
agent currently running
Rgds,
Rgds,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||Hi Paul.
I do not understand either. However, the data is on both servers.
Here are some times:
a)No replication: average 1.5 seconds daily
d)Continuous Replication: 3,4,14,29,118 (averages of daily values over 5 days)
I will have to test the other two options.
I understand that adding RGUIDs will increase the time it takes to insert
data, but is an exponential increase expected?
"Paul Ibison" wrote:

> There's something I don't understand here. When you did
> the bulk insert without firing the triggers, how is it
> possible that the data could be merged? Perhaps when you
> say the data was still being replicated, you mean that
> the merge agent was running? Anyway, What are the times:
> (a) no replication
> (b) replication and no firing of triggers and the merge
> agent on a schedule which means it is not currently
> running
> (c) replication and firing of triggers and the merge
> agent on a schedule which means it is not currently
> running
> (d) replication and firing of triggers and the merge
> agent currently running
> Rgds,
> Rgds,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
|||Hi Paul,
It turns out our MSmerge_contents table was over 2.5 million records.
And it's not automatically clearing up. We are inserting data (not using
bulk insert) every five minutes, so this is a critical concern for us.
Could this be the problem? How can we tell it to automatically clean up
after data is replicated?
"Paul Ibison" wrote:

> There's something I don't understand here. When you did
> the bulk insert without firing the triggers, how is it
> possible that the data could be merged? Perhaps when you
> say the data was still being replicated, you mean that
> the merge agent was running? Anyway, What are the times:
> (a) no replication
> (b) replication and no firing of triggers and the merge
> agent on a schedule which means it is not currently
> running
> (c) replication and firing of triggers and the merge
> agent on a schedule which means it is not currently
> running
> (d) replication and firing of triggers and the merge
> agent currently running
> Rgds,
> Rgds,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>

merge replication corruption (system triggers and views)

All of a sudden none of our merge replications are working. In fact you can't even insert, update or delete and data from the tables in the merge publication. When trying that, we get an error stating:

Msg 550, Level 16, State 1, Procedure MSmerge_ins_E3F43EF8B259476099BBB194A2E1708C, Line 42
The attempted insert or update failed because the target view either specifies WITH CHECK OPTION or spans a view that specifies WITH CHECK OPTION and one or more rows resulting from the operation did not qualify under the CHECK OPTION constraint.
The statement has been terminated.

Currently, the only solution I've found is to delete the publication and recreate it. I'm trying to figure out why this happened. It happened on a development server that to my knowledge, hasn't been changed in a week or so outside of changing the server's IP address. Would that cause such an error to occur?

-mikeI found another change.. we added a linked server using sp_addlinkedserver.. Any thoughts?|||Does your merge subset filter clauses or join filter clauses contain views that contain WITH CHECK OPTION, pointing to remote table?|||No filters are set for the publication.|||

ok, then you have to trace your steps to see exactly what changes were made that would cause this, and see if you can back them out one by one.

any idea what the linked server would have to do with regards to the views, triggers, or any of the published tables? are you making changes from a remote machine?

Merge replication broke after SQL 2005 Evaluation upgrade to Enterprise

I'm try to upgrade SQL Server 2005 Evaluation with tuned up and worked merge replication.

- I'm insert disk with licensed SQL Server 2005 Enterprise, start installation - in process, indicate instance for installing, in list of options appears I see option "ugrade"
- All passes fine and go on:

After upgarde I'm get completely broken merge replication!

Moreover, such feeling that certain strange troubles with SQL Agent:
all points context menu in Replication Monotor - disabled and if I try to start replication manually through jobs - too failed - with some error "agent shutdown" !

I'm try to build "clear" situation: install in Virtual PC Windows 2003 Ent SP1, SQL 2005 Eval and setup my test replication and upgrade SQL Server to Enterprise version - and get same trouble too!!!

What the best way to uprade my evaluations sql servers with replication to licensed version SQL Ent?

I've moved this thread to the replication forum, where you're more likely to get an answer.

Paul

|||

Hi, Nikolai,

What error message did you observe when you say merge replication is broken? Remember after you upgrade to SQL Server 2005 RTM (either from SQL 2000 or SQL 2005 Eval), you need to re-run snapshot job for merge replication upgrade to complete, from then you can run normal merge agent job, otherwise, you will hit error like "You must rerun snapshot because current snapshot files are obsolete.". Let me know if you're seeing a different error.

Thanks,

Zhiqiang Feng

This posting is provided "AS IS" with no warranties, and confers no rights.

Merge Replication and publisher deleting subscriber inserts.

The row was inserted at 'Subscriber Laptop' but could not be inserted at
'Publisher server'. INSERT statement conflicted with COLUMN FOREIGN KEY
constraint 'FK1'. The conflict occurred in database 'Tst', table 'Report',
column 'ReportNo'.
http://support.microsoft.com/default...b;en-us;307356
We are using Merge Replication. Sql Server 2000 SP4 on the server and MSDE
SP4 on the laptops.
We've been testing for a few months and just starting to trickle into
production. This is the first time we've seen this error. From what I could
google, the KB I refer to above says we should have NOT FOR REPLICATION on
all FK constraints.
Now, all of my hits on this subject seem to be 2003 and earlier. So before I
go ahead and make this huge change, I want to confirm that yes, infact, I
should be doing this.
Should NFR be put on Primary Keys as well?
Can this NFR be specified through VISIO as that is where we maintain the
data model. If not, is there an alter statement we can apply to constraints
to turn this option on?
Buzz.
NFR can't be put onto PKs but definitely you should have it on the FKs (see
http://www.replicationanswers.com/Me...derArticle.asp)
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)