Showing posts with label machines. Show all posts
Showing posts with label machines. Show all posts

Wednesday, March 28, 2012

Merge Replication with MSDE

I have two MSDE servers on different machines and am
trying to set up merge replication between the two. When
the merge agent runs on the publisher, it indicates that
the "process could not connect to the Subscriber" (SQL
Server does not exist or access denied).
I'm not sure if matters, but I'm running in a peer-to-
peer network. I have my SQL Agent service on the
publisher running under a local machine account, and that
account has access to the publisher, however I wasn't
sure how to grant access to that account on the
Subscriber, or if that was even necessary.
Any help would be appreciated.
- Jim T.
Jim
I am not positive, but I think that we ran into this problem as well. I
beleive that the problem is that the local machine account on the publisher
(or subscriber) can't authenticate with the other machine which means that
the SQL Server Agent can't connect to the other machine to make replication
happen. If I remember correctly, we had to have a non-local (domain)
account that each domain trusts and each machine allows access to the
database on. I'm not sure, but I think it may also be possible to have
local accounts as long as both the username and password are the same on
both machines. I'm pretty sure that there is a KB article titled somthing
like "How to run replication accross non-trusted domains" that may help. If
you need to change the SQL Agent's service acount, take a look at KB article
283811. Sorry to be so vague, but I hope it points you in the right
direction.
HTH
Ron L
"Jim Toth" <anonymous@.discussions.microsoft.com> wrote in message
news:14b301c4b3e6$5d41a650$a401280a@.phx.gbl...
>I have two MSDE servers on different machines and am
> trying to set up merge replication between the two. When
> the merge agent runs on the publisher, it indicates that
> the "process could not connect to the Subscriber" (SQL
> Server does not exist or access denied).
> I'm not sure if matters, but I'm running in a peer-to-
> peer network. I have my SQL Agent service on the
> publisher running under a local machine account, and that
> account has access to the publisher, however I wasn't
> sure how to grant access to that account on the
> Subscriber, or if that was even necessary.
> Any help would be appreciated.
> - Jim T.

Monday, March 19, 2012

Merge replication on internet

How can we achive a SQL server merge replication over the internet when both the client and server are Windows Xp machines and using SQL Server 7? Can someone please explain in a clear step by step fashion?
Is this a static IP or not.
Basically what you have to do it configure the publisher and subscriber using client network utilty, in the alias tab. Use the current TCP/IP address and a plain text server name (ie, don't use the ip address as the alias).
Then in your hosts file ( found in cd %windir%\system32\etc edit the hosts file to include both these servernames and the ip addresses. If the addresses are dynamic you will have to modify this each time you get a new ip address.
Then you have to configure an ftp server on your publisher and configure your merge publication for ftp.
Then create a pull subscription using ftp on your subscriber.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"Rammy" wrote:

> How can we achive a SQL server merge replication over the internet when both the client and server are Windows Xp machines and using SQL Server 7? Can someone please explain in a clear step by step fashion?
>
|||One more thing. When you configure your subscriber, do not use the impersonate the SQL Server Agent on the publisher, use SQL Server authentication.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"Hilary Cotter" wrote:
[vbcol=seagreen]
> Is this a static IP or not.
> Basically what you have to do it configure the publisher and subscriber using client network utilty, in the alias tab. Use the current TCP/IP address and a plain text server name (ie, don't use the ip address as the alias).
> Then in your hosts file ( found in cd %windir%\system32\etc edit the hosts file to include both these servernames and the ip addresses. If the addresses are dynamic you will have to modify this each time you get a new ip address.
> Then you have to configure an ftp server on your publisher and configure your merge publication for ftp.
> Then create a pull subscription using ftp on your subscriber.
> --
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
>
>
> "Rammy" wrote:

Merge replication odd question

Hello,
I need to configure a merge replication between 3 machines, one of these is never connected with the other two machines. I've tried to work with the off-line machine with backup and restore to the offline machine and the others in this way: I backup up a
subscripted DB and restored to the offline machine, update some data and then backup and restore again on the subscripted DB. It breaks the synchronization.
Do you think there is a way to "freeze" a subscribed DB, move it to another off-line machine (moving backups or data and log files, or something else), work on this machine and then restore the updated DB to the freezed one?
And do you think it will work also between a SQL server 2000 publisher and an MSDE subscriber?
Thank you for your help!
Michele
This certainly is an odd request.
I think it might work if you use the keep_replication switch of the restore command while you are restoring your database.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
|||But Hilary, if we RESTORE over the top of one of the two connected databases
the database that was disconnected then we will lose at the very least all
transactions that happened between the two connected databases won't we?
It is almost as though we want to RESTORE to a 3rd DB with KEEP_REPLICATION
and then fire up the Agents.
Not even sure if that will do it.
Allan
"Hilary Cotter" <hilaryk@.att.net> wrote in message
news:F1BFFB4C-C330-4BDD-9753-A7787783F03D@.microsoft.com...
> This certainly is an odd request.
> I think it might work if you use the keep_replication switch of the
restore command while you are restoring your database.
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
>
|||OK so here was my testing
Create 3 databases
M1,M2,M3
M1 = Publisher
M2,M3 = Subs
Create a table (I used a very simple table with IDENTITY())
Create an article of the table (Handle Identity ranges)
Add to a publication
Start cooking
--Now remove M3 from the topology so we can put it on the laptop for however
long we want and continue to work on it (watch identity ranges if a long
time)
--stop the merge agent and drop the database
--Restore somewhere else with KEEP_REPLICATION
Must be restored as M3 otherwise you get the following when inputting data
ERROR!!
INITCOLVS: The Parameter 'nickname' is invalid
--Input data
Backup from this box now and restore to the original (WITH KEEP_REPLICATION)
Start up the Merge agent
In it goes.
Allan
"Hilary Cotter" <hilaryk@.att.net> wrote in message
news:F1BFFB4C-C330-4BDD-9753-A7787783F03D@.microsoft.com...
> This certainly is an odd request.
> I think it might work if you use the keep_replication switch of the
restore command while you are restoring your database.
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
>
|||I'll try to repro this later.
If you have a subscriber, and you backup that database, and restore it to the 3rd machine using the keep replication switch, make your changes, backup the database and restore it to the second machine, again using keep replication, this should work.
You must have replication installed on the third machine however.
This is a variation of the way you can deploy susbcribers using a backup and restore.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
|||It works!
Thank you very much Hilary and Allan for your precious help!
Michele
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!

Monday, March 12, 2012

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
>

Saturday, February 25, 2012

Merge Replication Conflicts

Hi,
I have a strange scenario occuring and have no idea why.
I have SQL Server 2000 & Windows 2003 Server running on two machines.
Two databases are set for merge replication.
The subscriber is used purely for redundancy reasons and no client uses it unless the publisher is unavailable. While the publisher is available, I am receiving conflict messages saying that the same column in the same table has been updated on both serve
rs. I know this is not true as no client is running off the subscriber.
The publisher and subscriber have different identity seeds and the identity seed of the updated record that causes the conflict is that of the publisher (not the subscriber).
Has anyone run into this issue before? Any ideas?
Thanks,
Andrew
Having an identity value assigned by the publisher does not indicate
that it wasn't changed at the subscriber. It only indicates where the
row was originally inserted. The subscriber can still modify the rows
provided it had been replicated to the subscriber.
Having said that, the only way you should be able to have conflicts is
if there are conflicting DML statements on the same row on opposite
sides. You could try checking the MSmerge_contents table on the
subscriber database to see what rows were modified at the subscriber.
Hope this helps,
Reinout Hillmann
SQL Server Product Unit
This posting is provided "AS IS" with no warranties, and confers no rights.
Andrew wrote:
> Hi,
> I have a strange scenario occuring and have no idea why.
> I have SQL Server 2000 & Windows 2003 Server running on two machines.
> Two databases are set for merge replication.
> The subscriber is used purely for redundancy reasons and no client uses it unless the publisher is unavailable. While the publisher is available, I am receiving conflict messages saying that the same column in the same table has been updated on both ser
vers. I know this is not true as no client is running off the subscriber.
> The publisher and subscriber have different identity seeds and the identity seed of the updated record that causes the conflict is that of the publisher (not the subscriber).
> Has anyone run into this issue before? Any ideas?
> Thanks,
> Andrew
|||Hi,
I can see quite a few records in the MSmerge_contents table at the subscriber. How can I use these to track down who/what is causing this?
There is not a single client in our organisation that is using the subscriber server, so how can there possibly be modification at the subscriber?
When I run a trace on the subscriber for the database that is generating the conflict, i get nothing. (IE: no-one is using that database on that server)
Andrew
|||Not sure if this is relevant but
Have you any triggers on the tables being inserted or updated ? If so you could have changes being made on the
subscriber that could cause confilicts back on the publisher