Merge replication intermittent error.

Windows 2003 Server Standard, SQL Server 2000. Two identical machines.
Primary (P) and Secondary (S). I have set up merge replication with P as
the publisher and S as the distributor and an updating pull subscriber. The
machines are installed at a customer site and were successfully put into the
domain and all the replication is working. I have the merge and snapshot
agents impersonating the SQL Server Agent login.
They are still working on the power, networking and other parts of the new
building so the machines lose connectivity to the domain and login servers
peridically and sometimes power down. At times when I connect to the system
to check the replication monitor is showing an error. I had originally set
all agents to start with SQL Server and run continously. I saw that the
merge agent would encounter an error and stop. To avoid this I scheduled
the merge agent to run every day, once a minute to avoid intermittent
network errors causing it to stop and not restart. However, I still
periodically get an error that the merge agent logs every time it runs. I
can clear the error up by manually running the snapshot agent. I need to
figure out what is causing the error and have some way automatically restore
the replication.
The error reported by the merge agent is this:
The merge agent could retrieve article information for publication 'xxx'.
(Source:Merge Replication Provider (Agent); Error: -2147201017
SQL Server does not exist or access denied.
(Source: Primary (Data SOurce); Error Number: 17
I know it is not an access permission because it works most of the time. I
am wondering if it is a timing issue when a network error occurs or the
machines reboot and the primary cannot access the distributor in the
secondary. I am not sure why this would cause the merge agent to fail. It
seems to be that the snapshot agent may be failing during a reboot or during
a network failure and the subscription is marked as "bad" or something. I
am thinking of making the primary the distributor to see if that helps.
Any suggestions?
TIA
I think you need to set LoginTimeout to something large - like 60.
By default LoginTimeout is 15s.
"Mike M." <nospam@.someplace.com> wrote in message
news:%230FRneD6EHA.2192@.TK2MSFTNGP14.phx.gbl...
> Windows 2003 Server Standard, SQL Server 2000. Two identical machines.
> Primary (P) and Secondary (S). I have set up merge replication with P as
> the publisher and S as the distributor and an updating pull subscriber.
The
> machines are installed at a customer site and were successfully put into
the
> domain and all the replication is working. I have the merge and snapshot
> agents impersonating the SQL Server Agent login.
> They are still working on the power, networking and other parts of the new
> building so the machines lose connectivity to the domain and login servers
> peridically and sometimes power down. At times when I connect to the
system
> to check the replication monitor is showing an error. I had originally
set
> all agents to start with SQL Server and run continously. I saw that the
> merge agent would encounter an error and stop. To avoid this I scheduled
> the merge agent to run every day, once a minute to avoid intermittent
> network errors causing it to stop and not restart. However, I still
> periodically get an error that the merge agent logs every time it runs. I
> can clear the error up by manually running the snapshot agent. I need to
> figure out what is causing the error and have some way automatically
restore
> the replication.
> The error reported by the merge agent is this:
> The merge agent could retrieve article information for publication 'xxx'.
> (Source:Merge Replication Provider (Agent); Error: -2147201017
> SQL Server does not exist or access denied.
> (Source: Primary (Data SOurce); Error Number: 17
> I know it is not an access permission because it works most of the time.
I
> am wondering if it is a timing issue when a network error occurs or the
> machines reboot and the primary cannot access the distributor in the
> secondary. I am not sure why this would cause the merge agent to fail.
It
> seems to be that the snapshot agent may be failing during a reboot or
during
> a network failure and the subscription is marked as "bad" or something. I
> am thinking of making the primary the distributor to see if that helps.
> Any suggestions?
>
> TIA
>
|||I read somewhere you can't do this from Enterprise Manager. Is that true?
I use EM to configure/maintain SQL Server.
Thanks.
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:ux5BQDE6EHA.1596@.tk2msftngp13.phx.gbl...[vbcol=seagreen]
> I think you need to set LoginTimeout to something large - like 60.
> By default LoginTimeout is 15s.
> "Mike M." <nospam@.someplace.com> wrote in message
> news:%230FRneD6EHA.2192@.TK2MSFTNGP14.phx.gbl...
as[vbcol=seagreen]
> The
> the
snapshot[vbcol=seagreen]
new[vbcol=seagreen]
servers[vbcol=seagreen]
> system
> set
scheduled[vbcol=seagreen]
I[vbcol=seagreen]
to[vbcol=seagreen]
> restore
'xxx'.[vbcol=seagreen]
> I
> It
> during
I
>
|||Never mind. I did it using sp_configure.
Thanks.
"Mike M." <nospam@.someplace.com> wrote in message
news:OPJ9GcF6EHA.1564@.TK2MSFTNGP09.phx.gbl...[vbcol=seagreen]
> I read somewhere you can't do this from Enterprise Manager. Is that true?
> I use EM to configure/maintain SQL Server.
> Thanks.
> "Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
> news:ux5BQDE6EHA.1596@.tk2msftngp13.phx.gbl...
machines.[vbcol=seagreen]
> as
subscriber.[vbcol=seagreen]
into[vbcol=seagreen]
> snapshot
> new
> servers
originally[vbcol=seagreen]
the[vbcol=seagreen]
> scheduled
runs.[vbcol=seagreen]
> I
> to
> 'xxx'.
time.[vbcol=seagreen]
the[vbcol=seagreen]
fail.[vbcol=seagreen]
something.[vbcol=seagreen]
> I
helps.
>
|||Right click on your merge agent, select agent properties, steps, run agent,
click on the edit button, click in teh commands section, press the end key,
press the space bar, type -LoginTimeOut 120
Click Apply, ok, etc.
"Mike M." <nospam@.someplace.com> wrote in message
news:%235YnE2F6EHA.3708@.TK2MSFTNGP14.phx.gbl...[vbcol=seagreen]
> Never mind. I did it using sp_configure.
> Thanks.
> "Mike M." <nospam@.someplace.com> wrote in message
> news:OPJ9GcF6EHA.1564@.TK2MSFTNGP09.phx.gbl...
true?[vbcol=seagreen]
> machines.
P[vbcol=seagreen]
> subscriber.
> into
the[vbcol=seagreen]
> originally
> the
intermittent[vbcol=seagreen]
> runs.
need[vbcol=seagreen]
> time.
> the
> fail.
or
> something.
> helps.
>
|||Sorry about the time lapse but the holidays happened. I set the login
timeout but that did not help.
With a Merge replication, single updating pull subscription, I am wondering
what might cause the merge agent to get the following error and why does
manually running the snapshot agent allow the merge agent to succeed the
next run. Any thoughts?
The error reported by the merge agent is this:
The merge agent could retrieve article information for publication 'xxx'.
(Source:Merge Replication Provider (Agent); Error: -2147201017
SQL Server does not exist or access denied.
(Source: Primary (Data SOurce); Error Number: 17
TIA
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:en$hKHG6EHA.2568@.TK2MSFTNGP10.phx.gbl...
> Right click on your merge agent, select agent properties, steps, run
agent,
> click on the edit button, click in teh commands section, press the end
key,[vbcol=seagreen]
> press the space bar, type -LoginTimeOut 120
> Click Apply, ok, etc.
> "Mike M." <nospam@.someplace.com> wrote in message
> news:%235YnE2F6EHA.3708@.TK2MSFTNGP14.phx.gbl...
> true?
with[vbcol=seagreen]
> P
put[vbcol=seagreen]
> the
the[vbcol=seagreen]
that[vbcol=seagreen]
> intermittent
still[vbcol=seagreen]
> need
automatically[vbcol=seagreen]
or[vbcol=seagreen]
the
> or
>
|||what job owner is your merge agent running under? It should be running under
sa.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"Mike M." <nospam@.someplace.com> wrote in message
news:OCiC1pz8EHA.2600@.TK2MSFTNGP09.phx.gbl...
> Sorry about the time lapse but the holidays happened. I set the login
> timeout but that did not help.
> With a Merge replication, single updating pull subscription, I am
wondering[vbcol=seagreen]
> what might cause the merge agent to get the following error and why does
> manually running the snapshot agent allow the merge agent to succeed the
> next run. Any thoughts?
> The error reported by the merge agent is this:
> The merge agent could retrieve article information for publication 'xxx'.
> (Source:Merge Replication Provider (Agent); Error: -2147201017
> SQL Server does not exist or access denied.
> (Source: Primary (Data SOurce); Error Number: 17
> TIA
> "Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
> news:en$hKHG6EHA.2568@.TK2MSFTNGP10.phx.gbl...
> agent,
> key,
> with
> put
of[vbcol=seagreen]
login[vbcol=seagreen]
> the
> that
> still
it[vbcol=seagreen]
I[vbcol=seagreen]
> automatically
publication[vbcol=seagreen]
the[vbcol=seagreen]
> or
> the
to[vbcol=seagreen]
reboot
>
|||I have it set to run under a domain user that is in the local
administrator's group. The replication runs fine for periods of time before
the intermittent error occurs. If the owner was incorrect I would suspect
that it wouldn't run at all.
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:OS1rGN18EHA.3596@.TK2MSFTNGP12.phx.gbl...
> what job owner is your merge agent running under? It should be running
under[vbcol=seagreen]
> sa.
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> "Mike M." <nospam@.someplace.com> wrote in message
> news:OCiC1pz8EHA.2600@.TK2MSFTNGP09.phx.gbl...
> wondering
'xxx'.[vbcol=seagreen]
that[vbcol=seagreen]
60.[vbcol=seagreen]
replication[vbcol=seagreen]
successfully[vbcol=seagreen]
and[vbcol=seagreen]
parts[vbcol=seagreen]
> of
> login
to[vbcol=seagreen]
saw[vbcol=seagreen]
I[vbcol=seagreen]
> it
> I
> publication
> the
occurs[vbcol=seagreen]
in[vbcol=seagreen]
> to
> reboot
that
>
|||That is true, it should be an all or nothing.
Let me see if I am understanding this correctly. Your merge agent is working
fine. It is running continuously. Suddenly it stops with the message SQL
Server does not exist or access denied. You run a new snapshot, it then
works?
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"Mike M." <nospam@.someplace.com> wrote in message
news:uBog2228EHA.1404@.TK2MSFTNGP11.phx.gbl...
> I have it set to run under a domain user that is in the local
> administrator's group. The replication runs fine for periods of time
before[vbcol=seagreen]
> the intermittent error occurs. If the owner was incorrect I would suspect
> that it wouldn't run at all.
> "Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
> news:OS1rGN18EHA.3596@.TK2MSFTNGP12.phx.gbl...
> under
does[vbcol=seagreen]
the[vbcol=seagreen]
> 'xxx'.
end[vbcol=seagreen]
> that
> 60.
identical[vbcol=seagreen]
> replication
> successfully
> and
> parts
[vbcol=seagreen]
connect[vbcol=seagreen]
> to
> saw
this[vbcol=seagreen]
> I
I[vbcol=seagreen]
time[vbcol=seagreen]
agent.[vbcol=seagreen]
Error: -2147201017[vbcol=seagreen]
of[vbcol=seagreen]
> occurs
distributor[vbcol=seagreen]
> in
agent
> that
>
|||Exactly! I know it sounds weird but that is what happens. I see network
and domain errors in the event log. Some of them indicate trouble finding
the login and/or the domain server and I have seen at least one SQL error
saying it could not authenticate users. I think one or more of these
network issues causes the state of the replication to get munged. Here are
some things I am thinking of trying to get the replication to recover
without intervention:
1. Make the distributor the smae machine as the publisher. Currently it is
on the backup (subscriber) machine. I am thinking it may help when there
are intermittent network errors.
2. Have the snapshot agent scheduled to run every 15 minutes or so. Just to
see if it restarts things.
Unfortunately the systems are half way around the world, in a live
environment and access is spotty.
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:eAwtceC9EHA.2676@.TK2MSFTNGP12.phx.gbl...
> That is true, it should be an all or nothing.
> Let me see if I am understanding this correctly. Your merge agent is
working[vbcol=seagreen]
> fine. It is running continuously. Suddenly it stops with the message SQL
> Server does not exist or access denied. You run a new snapshot, it then
> works?
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> "Mike M." <nospam@.someplace.com> wrote in message
> news:uBog2228EHA.1404@.TK2MSFTNGP11.phx.gbl...
> before
suspect[vbcol=seagreen]
login[vbcol=seagreen]
> does
> the
run[vbcol=seagreen]
> end
Is[vbcol=seagreen]
like[vbcol=seagreen]
> identical
pull[vbcol=seagreen]
merge[vbcol=seagreen]
and[vbcol=seagreen]
> connect
had[vbcol=seagreen]
I[vbcol=seagreen]
> this
However,[vbcol=seagreen]
> I
> time
> agent.
> Error: -2147201017
most[vbcol=seagreen]
> of
> distributor
> agent
a[vbcol=seagreen]
or[vbcol=seagreen]
if
>