merge replication, help!

Hi,

I'm setting up a merge replication between one publisher(sql server 2005 standard sp2) and a couple of subscribers(sql server 2005 express sp2). They are connected with each other through VPN tunnels(1.5M adsl connections). I'm using pull replication, every 20 minutes. The initial snapshot replication was finished properly for each subscriber, but after several hours, the subscribers keep getting such kind of error message "Another merge agent for the subscription or subscriptions is running, or the server is working on a previous request by the same agent. (Source: MSSQLServer, Error number: 21036)". it looks like the vpn tunnel is not good sometimes, and the merge agent just sits there waiting. then another request comes in, it cannot get the handler of that agent which is held by the previous request. So how can i configure the server to release the agent when a new request comes in? or setup the timeout for each request?

Any idea would be appreciated.

Thank you very much!
Maybe you can consider -QueryTimeout parameter for the merge agent?|||thanks for reply.
but would u be able to tell me how to do that?
|||oh, sorry, i found it. the current timeout is 300, just 5 minutes. but i set the merge replication every 20 minutes. then how come this happens?
|||QueryTimeout is how long the merge agent will wait on a given query before timing out. The 20 minutes you specified sounds like the scheduled intervals at which the subscription will synchronize.sql

merge replication, help!

Hi,

I'm setting up a merge replication between one publisher(sql server 2005 standard sp2) and a couple of subscribers(sql server 2005 express sp2). They are connected with each other through VPN tunnels(1.5M adsl connections). I'm using pull replication, every 20 minutes. The initial snapshot replication was finished properly for each subscriber, but after several hours, the subscribers keep getting such kind of error message "Another merge agent for the subscription or subscriptions is running, or the server is working on a previous request by the same agent. (Source: MSSQLServer, Error number: 21036)". it looks like the vpn tunnel is not good sometimes, and the merge agent just sits there waiting. then another request comes in, it cannot get the handler of that agent which is held by the previous request. So how can i configure the server to release the agent when a new request comes in? or setup the timeout for each request?

Any idea would be appreciated.

Thank you very much!
Maybe you can consider -QueryTimeout parameter for the merge agent?|||thanks for reply.
but would u be able to tell me how to do that?
|||oh, sorry, i found it. the current timeout is 300, just 5 minutes. but i set the merge replication every 20 minutes. then how come this happens?
|||QueryTimeout is how long the merge agent will wait on a given query before timing out. The 20 minutes you specified sounds like the scheduled intervals at which the subscription will synchronize.

Merge Replication Synchronization Issue

Hello,
I have a merge replication problem has been driving me nuts for the last couple of days and I haven't been able to find any information on it from other posts in this group. The problem is that extra uploads (as UPDATEs) are being sent to the publisher w
hen the subscriber sync's.
First off our setup info:
Server:
- SQL Server 2000 Enterprise (SP3).
- Running on a clustered Win 2000 server (Active/Passive).
- Publisher and Distributor are located on the same virtual Sql Server instance.
- Merge publication using dynamic horizontal data filters with join filters off of filtered tables.
- Dynamic snapshots used for each subscribers data initialization.
Client: MSDE (SP3)
- Running Win XP Tablet Edition.
- Anonymous on demand subscription.
Table Info:
Primary Table: Retailers (PK ApplicationNumber char(7))
Sub Table: RetailerInformation (PK ApplicationNumber char(7), which is also a FK to Retailers.ApplicationNumber)
Replication Info Table: ReplicationRetailers (PK ApplicationNumber char(7), UserLogin varchar(20) (This is the windows login returned by the SUSER_SNAME() function during the filtering during the sync))
Publication Info:
ReplicationRetailers table has a dynamic filter of 'WHERE UserLogin = SUSER_SNAME()'
ReplicationRetailers Join Filters Retailers on ApplicationNumber
Retailers Join Filters RetailerInformation on ApplicationNumber
Our replication is working great; the correct data is being sent down to the subscribers database, speeds are excellent, etc... However we noticed a strange behavior while testing yesterday. If I assign a new retailer to a user (by adding a row to the Re
plicationRetailers table) the first sync down to the subscription works fine; all the applicable Retailer records are INSERTed into the subscriber. However, when the subscriber sync's again, an almost identical number of UPDATEs are sent back up to the s
erver. The number of UPDATEs never quite equals the number of INSERTs but is usually 2-4 less. The data that gets UPDATEd up to the publisher is not different from the data that was INSERTed to the subscriber and I know that there are no subscriber tabl
e triggers firing that update any data.
I've traced the merge process to see what tables are sending data back up to the publisher and confirmed that it is the Retailers table and its associated sub table that sends the updates. However I cannot tell exactly which records are being sent.
I know this is not the normal behavior during a sync as I have other tables that have an identical table filter that only send data when there are changes (in the same publication). So I guess I have a couple questions:
1) Has anyone else seen this behavior?
2) Is so, what did you do to fix it?
3) Is there some way to use the MSmerge_contents and MSmerge_genhistory tables to figure out what rows are going to be sent durning the next sync BEFORE the sync occurs?
Thank you very much for your help.
Wesley Brown
Each row in a merge published table has a GUID column which is used to uniquely identify each row.
Each row in MSmerge_contents corresponds to changes which have happened locally on the database.
Each row in MSmerge_contents will contain an generation number.
When the merge agent runs compares the generation numbers in in MSmerge_contents and the msmerge_replinfo table between the publisher and subscriebr to determine which GUID's have incremented their generation number.
Then depending on whether the publisher or subscriber has the higher generation number a stored procedure is constructed with parameters based on the values in either the publisher or subscriber published tables and executed on the subscriber or publisher
|||Thanks for the response Hilary.
I've used the information you gave me and created a query to show me the state of the subscriber and the publisher after
the 1st sync. Interestingly I noticed that there are no rows in the publishers MSmerge_contents table for some of the
replicated data. I think the problem relates to the fact that most of the records in our database have never been
replicated (which makes sense given that we are using filters and have only been testing with a couple user logins). When
a database is turned into a publisher none of the existing rows are added to the MSmerge_contents table. When the sync
occurs the data gets added to the subscribers MSmerge_contents table but NOT the publishers MSmerge_contents table. I
think this may be a bug as all the required information is known when this sync is being performed. When the next Sync is
started the publisher correctly determines that the subscriber has new rows that it needs to upload for the
MSmerge_contents table. As for why the inserts to the publisher show up as updates I’m not sure; it could be that the
publisher adds the rows itself at the start of the 2nd sync and then checks the values it has for the rowguid column against
those in the subscriber database, however I’m really not sure.
Does anyone know if this behavior (the lazy load into the MSmerge_contents table) is by design?
sql

Merge Replication stopping

Hi,
We have Merge Continuous replication. Every couple of days the Merge
agent stops with an error:
The process could not enumerate changes at the 'Publisher'. Source:
Merge Replication Provider (Agent); Error number: -2147200999
We are running on SQL2000 Standard, SP4. The OS is Windows 2000 Server
SP4
Has anyone seen this?
Thanks,
Katrin
Yep. The merge agent (replmerg.exe) was either excessively blocked or
deadlocked with another process. How many rows do you have in your
MSmerge_contents and MSmerge_genhistory tables on both publisher and
subscriber?
Mike
http://www.solidqualitylearning.com
Disclaimer: This communication is an original work and represents my sole
views on the subject. It does not represent the views of any other person
or entity either by inference or direct reference.
"katrin" <katrinkump@.gmail.com> wrote in message
news:1143825489.438770.13630@.i39g2000cwa.googlegro ups.com...
> Hi,
> We have Merge Continuous replication. Every couple of days the Merge
> agent stops with an error:
> The process could not enumerate changes at the 'Publisher'. Source:
> Merge Replication Provider (Agent); Error number: -2147200999
> We are running on SQL2000 Standard, SP4. The OS is Windows 2000 Server
> SP4
> Has anyone seen this?
>
> Thanks,
> Katrin
>

Merge Replication stopping

Hi,
We have Merge Continuous replication. Every couple of days the Merge
agent stops with an error:
The process could not enumerate changes at the 'Publisher'. Source:
Merge Replication Provider (Agent); Error number: -2147200999
We are running on SQL2000 Standard, SP4. The OS is Windows 2000 Server
SP4
Has anyone seen this?
Thanks,
Katrin
normally this error is transitory and will go away on subsequent syncs. If
it does not enable logging to determine if there is another error which is
masked by this error.
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
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
<katrinkump@.gmail.com> wrote in message
news:1143825341.447084.133130@.j33g2000cwa.googlegr oups.com...
> Hi,
> We have Merge Continuous replication. Every couple of days the Merge
> agent stops with an error:
> The process could not enumerate changes at the 'Publisher'. Source:
> Merge Replication Provider (Agent); Error number: -2147200999
> We are running on SQL2000 Standard, SP4. The OS is Windows 2000 Server
> SP4
> Has anyone seen this?
>
> Thanks,
> Katrin
>

Merge Replication Slow after period of inactivity

Hi Guys,
I don't know if you can help, I have a couple of issue with Merge
replication running on SQL Server 2000 SP3a.
I install merge replication and build all the snapshot for the pull
subscriptions and create the subscriptions running on the local machines
(MSDE 2000). This particular installation has 70 local machines and 140
subscriptions.
The problem I am seeing is that if the machines are not communicated for a
period of time, even though no data has changed on the local machines or
server, it can take ages for the communications process to complete,
sometimes in excess of 3 hours.
If anyone could shed some light on this issue I would be greatly
appreciated.
Thanks Tim.
Have a look at this white paper for hints on what causes this and how to
improve its performance.
http://msdn.microsoft.com/library/de...artitioned.asp
"Tim Ford" <timford@.removethisin2focus.com> wrote in message
news:#iNZR#ptEHA.2452@.TK2MSFTNGP10.phx.gbl...
> Hi Guys,
> I don't know if you can help, I have a couple of issue with Merge
> replication running on SQL Server 2000 SP3a.
> I install merge replication and build all the snapshot for the pull
> subscriptions and create the subscriptions running on the local machines
> (MSDE 2000). This particular installation has 70 local machines and 140
> subscriptions.
> The problem I am seeing is that if the machines are not communicated for a
> period of time, even though no data has changed on the local machines or
> server, it can take ages for the communications process to complete,
> sometimes in excess of 3 hours.
> If anyone could shed some light on this issue I would be greatly
> appreciated.
> Thanks Tim.
>

merge replication error

After running a merge replication consistantly for a couple of months, I
needed to remove a table from replication so that I could modify the datatype
on one of the fields (This table was really unnecessary on the subscribed
db). I removed the table from replication by
sp_MSunmarkreplinfo 'TableName'
After doing this, I started getting errors:
Error Details:
The merge process could not retrieve article information for publication
'db2'.
(Source: Merge Replication Provider (Agent); Error number: -2147201017)
------
Cannot insert the value NULL into column 'tablename', table '@.tmp_table';
column does not allow nulls. INSERT fails.
(Source: db1(Data source); Error number: 515)
------
Failed to allocate new identity range.
(Source: db1 (Data source); Error number: 21197)
------
Could i get some help on this please?
Here are details of how to achieve this:
http://www.replicationanswers.com/AddColumn.asp
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .
|||Basically you will need to hack your way through this one. You should have
used sp_dropmergearticle. I think your best bet is to drop the
subscriptions, drop the article and then reinitialize.
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
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
"Don Deskins" <Don Deskins@.discussions.microsoft.com> wrote in message
news:13D5377F-8635-4F04-9191-D4626618A2E3@.microsoft.com...
> After running a merge replication consistantly for a couple of months, I
> needed to remove a table from replication so that I could modify the
> datatype
> on one of the fields (This table was really unnecessary on the subscribed
> db). I removed the table from replication by
> sp_MSunmarkreplinfo 'TableName'
> After doing this, I started getting errors:
> Error Details:
> The merge process could not retrieve article information for publication
> 'db2'.
> (Source: Merge Replication Provider (Agent); Error number: -2147201017)
> ------
> Cannot insert the value NULL into column 'tablename', table '@.tmp_table';
> column does not allow nulls. INSERT fails.
> (Source: db1(Data source); Error number: 515)
> ------
> Failed to allocate new identity range.
> (Source: db1 (Data source); Error number: 21197)
> ------
> Could i get some help on this please?
|||Hillary and Paul,
Thank you for your answers.

>I think your best bet is to drop the
>subscriptions, drop the article and then reinitialize.
If I drop the subscription, then drop the article and reinitialize, will the
data in db1 and db2 be preserved? Data is being added to each db on a daily
basis.
thanks again
|||No - after dropping the subscription, you'll need to synchronize the data in
the 2 tables and then do a nosync initialization.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .
|||OK, thanks Paul.
To synchronize the data, do I have to run a script on each table and compare
rows or is there a store procedure that will do this for me? It is not just
2 tables. There is one table in particular in which the data has been
altered on both of the hosts (although it is entirely different fields). But
there are also tables in which the data has only been altered on one (or the
other) of the hosts.
Thanks again,
Don
"Paul Ibison" wrote:

> No - after dropping the subscription, you'll need to synchronize the data in
> the 2 tables and then do a nosync initialization.
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com .
>
>
|||There is TABLEDIFF in SQL Server 2005, but the easiest way is using
DataCompare from Redgate.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
|||Paul (and Hillary),
Thank you for your help.
Paul, I took your advice and downloaded DataCompare (will get the full
version soon). I have my dbs now replicating properly.
"Paul Ibison" wrote:

> There is TABLEDIFF in SQL Server 2005, but the easiest way is using
> DataCompare from Redgate.
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
>
>
|||Hi I am creating a three level merge replication. I mean I have a main
publisher with its distributor, a subscriber/publisher and also with
it's own distributor and lastly a subscriber.
I start by creating the relation between subscriber/Publisher and the
subscriber and than i move up to the main Publisher and
Subscriber\Publisher relation. After this second merge I get the
following errors and the articles in my Subscriber\Publisher are
lost...
The merge process could not retrieve article information for
publication 'I-Test-Publisher'.
(Source: Merge Replication Provider (Agent); Error number: -2147201017)
------
Cannot insert the value NULL into column 'tablename', table
'@.tmp_table'; column does not allow nulls. INSERT fails.
(Source: DEVSERVER3\MSDE (Data source); Error number: 515)
------
Failed to allocate new identity range.
(Source: DEVSERVER3\MSDE (Data source); Error number: 21197)
------

Merge replication data loss

I have serious problem regarding merge replication. After couple of pull
subscription synchronizations, data loss occurred in several tables (both
publisher and subscriber databases).
Publication has 62 articles, but only few are affected. Agents reports several
errors (including really nasty "general network failure").
I think problem is not in dynamic or join filters, because some of those
tables are not filtered at all.
Example: with tables T1 and T2 (FK constraint) users confirmed data loss in
child table T2 "right after synchronization" (sometimes couple of hundreds of
records).
So, question is how this could possibly happen (FK constraint maybe or general
network failure)?
Any help is appreciated,
Tom
general network error means your network hic cupped.
Merge replication refreshes the data on the subscriber when you send the
snapshot. This could account for the data loss. Also check the conflict
viewer to see if there is a record of conflicts which also might account for
it.
Make sure if you have any cascading deletes that they are disabled on the
subscriber.
"Tom" <tomislav.hrvoic.rimuv.dis@.inet.hr> wrote in message
news:uM5NnqIGEHA.4084@.TK2MSFTNGP11.phx.gbl...
> I have serious problem regarding merge replication. After couple of pull
> subscription synchronizations, data loss occurred in several tables (both
> publisher and subscriber databases).
> Publication has 62 articles, but only few are affected. Agents reports
several
> errors (including really nasty "general network failure").
> I think problem is not in dynamic or join filters, because some of those
> tables are not filtered at all.
> Example: with tables T1 and T2 (FK constraint) users confirmed data loss
in
> child table T2 "right after synchronization" (sometimes couple of hundreds
of
> records).
> So, question is how this could possibly happen (FK constraint maybe or
general
> network failure)?
> Any help is appreciated,
> Tom
>
>
|||what type of connectivity you are using?
those records might be on the publisher, you just need to
update the child records and replicate them to subscriber.

>--Original Message--
>general network error means your network hic cupped.
>Merge replication refreshes the data on the subscriber
when you send the
>snapshot. This could account for the data loss. Also
check the conflict
>viewer to see if there is a record of conflicts which
also might account for
>it.
>Make sure if you have any cascading deletes that they are
disabled on the
>subscriber.
>"Tom" <tomislav.hrvoic.rimuv.dis@.inet.hr> wrote in message
>news:uM5NnqIGEHA.4084@.TK2MSFTNGP11.phx.gbl...
After couple of pull
several tables (both
Agents reports
>several
failure").
because some of those
confirmed data loss
>in
couple of hundreds
>of
constraint maybe or
>general
>
>.
>
|||"Kiran" <anonymous@.discussions.microsoft.com> wrote in message
news:175be01c418c4$e0f0f180$a301280a@.phx.gbl...
> what type of connectivity you are using?
Subscribers use 56K modem or ISDN adapter to dial-in to company domain.
Network protocol is TCP/IP, DNS works fine, I did notice some lost packets
when pinging publisher from subsribers. However, that was before system
administrators replaced dial-in server and reconfigured network (replication
data loss problem is still unsolved though).

> those records might be on the publisher, you just need to
> update the child records and replicate them to subscriber.
Nope, older database backup is the only place where I can find those
records. Publisher is also missing those records and that makes it very
tricky.
Regards,
Tom

Merge Replication Between two Existing Databases?

Hi gang,

We have a couple of databases on separate servers that have exactly the same schema, and we would like to set up merge replication between them.

Is that possible? The few times we've experimented on test databases, the subscriber database has been trashed and rebuilt using data from the publisher, rather than preserving data from both databases.

I'm really new to replication in general, so any kind of advice would be helpful.

Cheers,
Matt

Yes it's possible, see topic in Books Online "Initializing a Merge Subscription Without a Snapshot".

|||Thanks Greg. I guess sometimes the help really is helpful!

Merge Replication and Keys

A couple of questions about Merge Replication (involving two 2000 servers):
1. From what I read Merge Replication requires a uniqueidentifier column
and will create one if you do not already have one. Correct?
2. If #1 is true, then do you have to have primary keys for every table
involved in Merge Replication?
Thx.1. Yes
2. Not for merge since it will use rowguid columns, but you really should
have PK on every table in your database.
MC
"CLM" <CLM@.discussions.microsoft.com> wrote in message
news:F06DE00E-F49B-49A9-90BA-7415204A4E99@.microsoft.com...
>A couple of questions about Merge Replication (involving two 2000 servers):
> 1. From what I read Merge Replication requires a uniqueidentifier column
> and will create one if you do not already have one. Correct?
> 2. If #1 is true, then do you have to have primary keys for every table
> involved in Merge Replication?
> Thx.

Merge Replication and Keys

A couple of questions about Merge Replication (involving two 2000 servers):
1. From what I read Merge Replication requires a uniqueidentifier column
and will create one if you do not already have one. Correct?
2. If #1 is true, then do you have to have primary keys for every table
involved in Merge Replication?
Thx.
1. Yes
2. Not for merge since it will use rowguid columns, but you really should
have PK on every table in your database.
MC
"CLM" <CLM@.discussions.microsoft.com> wrote in message
news:F06DE00E-F49B-49A9-90BA-7415204A4E99@.microsoft.com...
>A couple of questions about Merge Replication (involving two 2000 servers):
> 1. From what I read Merge Replication requires a uniqueidentifier column
> and will create one if you do not already have one. Correct?
> 2. If #1 is true, then do you have to have primary keys for every table
> involved in Merge Replication?
> Thx.

Merge Replication and Keys

A couple of questions about Merge Replication (involving two 2000 servers):
1. From what I read Merge Replication requires a uniqueidentifier column
and will create one if you do not already have one. Correct?
2. If #1 is true, then do you have to have primary keys for every table
involved in Merge Replication?
Thx.1. Yes
2. Not for merge since it will use rowguid columns, but you really should
have PK on every table in your database.
MC
"CLM" <CLM@.discussions.microsoft.com> wrote in message
news:F06DE00E-F49B-49A9-90BA-7415204A4E99@.microsoft.com...
>A couple of questions about Merge Replication (involving two 2000 servers):
> 1. From what I read Merge Replication requires a uniqueidentifier column
> and will create one if you do not already have one. Correct?
> 2. If #1 is true, then do you have to have primary keys for every table
> involved in Merge Replication?
> Thx.

Merge Replication and altering tables

I have a couple of SQL 2000 servers that are using Merge replication and
need to alter a table. Can I alter a table on one and have that alteration
replicate, for example, if I change the field length or add an index?
If I add a column to a table, I assume that I would have to add that column
to the subscription, correct?
Thanks
Robert,
if you use sp_repladdcolumn on the publisher, it will propagate to the
subscriber.
Regards,
Paul Ibison

Merge Replication and altering tables

I have a couple of SQL 2000 servers that are using Merge replication and
need to alter a table. Can I alter a table on one and have that alteration
replicate, for example, if I change the field length or add an index?
If I add a column to a table, I assume that I would have to add that column
to the subscription, correct?
ThanksRobert,
if you use sp_repladdcolumn on the publisher, it will propagate to the
subscriber.
Regards,
Paul Ibison

Merge Replication and altering tables

I have a couple of SQL 2000 servers that are using Merge replication and
need to alter a table. Can I alter a table on one and have that alteration
replicate, for example, if I change the field length or add an index?
If I add a column to a table, I assume that I would have to add that column
to the subscription, correct?
ThanksRobert,
if you use sp_repladdcolumn on the publisher, it will propagate to the
subscriber.
Regards,
Paul Ibison