Showing posts with label vpn. Show all posts
Showing posts with label vpn. Show all posts

Wednesday, March 21, 2012

Merge Replication problem over VPN

The setup:
Publisher/Distributor
Win2000
SQLServer2000
Merge replication enabled
Publication created
Subscriber
Win2000
MSDE
connect to publisher over VPN
can ping Publisher using alias
Publisher alias registered in EM
can create subscription (via wizard)
The problem:
When I creat the subscription (or Start Synchronizing), after
"connecting to distributor" (completes successfully), I get the
message:
"The remote server does not exist or has not been designated as a valid
Publisher."
I've looked all over the web and the couple of solutions seem to
suggest adding an alias in the Client Network Utility. But I've already
done that (and can ping by alias).
Configure Publishing ... | Publishers tab has the publisher listed and
checked with its distributor. But the publisher name is listed twice -
once with a distributor and checked, and once unchecked with no
distributor. I thought this was kind of strange.
So, what could it be? Please help!
- Desperate in Pittsburgh
Something is hosed here. The brainless way to fix this is to disable
replication and then to reenable it. The more masochistic but precise way to
do this is to modify the underlying system tables.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Now available on Amazon.com
http://www.amazon.com/gp/product/off...?condition=all
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
<richerwin@.ubs-europe.org> wrote in message
news:1107578304.398811.87440@.g14g2000cwa.googlegro ups.com...
> The setup:
> --
> Publisher/Distributor
> Win2000
> SQLServer2000
> Merge replication enabled
> Publication created
> Subscriber
> Win2000
> MSDE
> connect to publisher over VPN
> can ping Publisher using alias
> Publisher alias registered in EM
> can create subscription (via wizard)
> The problem:
> --
> When I creat the subscription (or Start Synchronizing), after
> "connecting to distributor" (completes successfully), I get the
> message:
> "The remote server does not exist or has not been designated as a valid
> Publisher."
> I've looked all over the web and the couple of solutions seem to
> suggest adding an alias in the Client Network Utility. But I've already
> done that (and can ping by alias).
> Configure Publishing ... | Publishers tab has the publisher listed and
> checked with its distributor. But the publisher name is listed twice -
> once with a distributor and checked, and once unchecked with no
> distributor. I thought this was kind of strange.
> So, what could it be? Please help!
> - Desperate in Pittsburgh
>

Monday, March 19, 2012

Merge Replication over VPN over the Pond

Hi,
We have two servers, one in North America, one in Europe, that are connected
by a firewall VPN. The firewalls have built-in encryption processors, so the
VPN encryption doesn't cause a speed issue. We are running SQL 2000 SP3 on
Windows 2003 Server.
We are currently hosting about 25 databases on the European server and using
Merge Replication to replicate between the servers. The Merge Agents are set
up to start every hour, and they are also running continuously (I manually
added the -continuous flag to the merge agent run step).
Right now we get various error messages during peak usage times. I have
already gone into the agent profiles and tweaked them based on
recommendations from this site among others, but we still get these errors...
I have read somewhere (including in Hilary's book) that there is an
-FTPAddress flag that you can put in the merge agent. Does this mean that if
you put that flag into the agent details, it will use FTP instead of TCP/IP
to transmit the merge data?
We get significantly faster download speeds when using FTP, so I would like
to set up as much FTP usage as possible, not just FTP'ing of the snapshots
(only once/week), but FTP of all merging information.
Right now our publisher with live data is in Europe, and our distributor is
in North America on the same server as the "backup" subscription databases.
Any insight would be greatly appreciated.
FTP uses tcp/ip protocol under the covers, so it probably won't help you.I
suspect you see performance improvements using FTP in applying your
snapshots as they are likely compressed. Otherwise I don't think you will
see performance improvements over UNC with FTP.
Exactly what are the error messages you are getting with merge replication.
Many of these errors are transitory and clear the next time the merge agent
runs.
Merge is a difficult beast to tune - you might want to review -
http://msdn.microsoft.com/library/de...artitioned.asp
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
"Hugh" <Hugh@.discussions.microsoft.com> wrote in message
news:E34D79B5-A506-4129-8F87-B94D90558F4D@.microsoft.com...
> Hi,
> We have two servers, one in North America, one in Europe, that are
> connected
> by a firewall VPN. The firewalls have built-in encryption processors, so
> the
> VPN encryption doesn't cause a speed issue. We are running SQL 2000 SP3
> on
> Windows 2003 Server.
> We are currently hosting about 25 databases on the European server and
> using
> Merge Replication to replicate between the servers. The Merge Agents are
> set
> up to start every hour, and they are also running continuously (I manually
> added the -continuous flag to the merge agent run step).
> Right now we get various error messages during peak usage times. I have
> already gone into the agent profiles and tweaked them based on
> recommendations from this site among others, but we still get these
> errors...
> I have read somewhere (including in Hilary's book) that there is an
> -FTPAddress flag that you can put in the merge agent. Does this mean that
> if
> you put that flag into the agent details, it will use FTP instead of
> TCP/IP
> to transmit the merge data?
> We get significantly faster download speeds when using FTP, so I would
> like
> to set up as much FTP usage as possible, not just FTP'ing of the snapshots
> (only once/week), but FTP of all merging information.
> Right now our publisher with live data is in Europe, and our distributor
> is
> in North America on the same server as the "backup" subscription
> databases.
> Any insight would be greatly appreciated.
|||Hi Hilary,
Thanks for the response. Here are some of the errors we get:
The subscription to publication "xxxxx" is invalid. General Network Error.
or...
The agent is suspect. No response within the last 10 minutes.
or...
The process could not enumerate deletions at the Publisher. General Network
Error.
You are right, these errors go away when the merge agent restarts itself,
but I would like to get rid of them entirely, of course.
I have read the article you suggested, but we aren't doing any partitioning,
as the entire database is being mirrored. But from what I read there, the
replication will work better if the database is tuned, so looking into index
tuning is probably a good next step?
Oh, and when is the Merge book coming out? ;)
Thanks for the help.
|||Hi Hugh,
Just curious, what Firewall VPN product do you use? I'm intrigued by the
built-in encryption processor feature...
Thanks,
Brad
"Hugh" <Hugh@.discussions.microsoft.com> wrote in message
news:E34D79B5-A506-4129-8F87-B94D90558F4D@.microsoft.com...
> Hi,
> We have two servers, one in North America, one in Europe, that are
> connected
> by a firewall VPN. The firewalls have built-in encryption processors, so
> the
> VPN encryption doesn't cause a speed issue. We are running SQL 2000 SP3
> on
> Windows 2003 Server.
> We are currently hosting about 25 databases on the European server and
> using
> Merge Replication to replicate between the servers. The Merge Agents are
> set
> up to start every hour, and they are also running continuously (I manually
> added the -continuous flag to the merge agent run step).
> Right now we get various error messages during peak usage times. I have
> already gone into the agent profiles and tweaked them based on
> recommendations from this site among others, but we still get these
> errors...
> I have read somewhere (including in Hilary's book) that there is an
> -FTPAddress flag that you can put in the merge agent. Does this mean that
> if
> you put that flag into the agent details, it will use FTP instead of
> TCP/IP
> to transmit the merge data?
> We get significantly faster download speeds when using FTP, so I would
> like
> to set up as much FTP usage as possible, not just FTP'ing of the snapshots
> (only once/week), but FTP of all merging information.
> Right now our publisher with live data is in Europe, and our distributor
> is
> in North America on the same server as the "backup" subscription
> databases.
> Any insight would be greatly appreciated.
|||Hi Brad,
Sonicwalls have a built in HMAC MD5 processor for VPN, so if you leave the
vpn tunnels with thier default encryption settings, everything runs through
the encryption processor.
Cheers,
Hugh.

Merge replication over VPN

I currently have a client who needs to replicate a database between a server
in the office and a computer at home using a VPN connection.
Here is the currnet setup:
-Merge Replication
-Both the server and the client have SQLSERVERAgent logged in with the same
username and password for pass-through authentication
-Subscriber impersonates SqlServerAgent
-Anonymous subscription
The snapshot has been created successfully on the server and I am able to
register the client on the server's Enterprise Manager (so they can see each
other). The replication starts and runs for quite a while (most of the work
is done). Eventually, it fails with the message
//<servername>/<sharename>/<filname>.dri could not be propagated to the
subscriber. With no further details. I don't think it is security related,
because most of the work is already done.
Any ideas what I'm missing?
You might try, from the client, to see if you can browse this share. When I
have set it up, it required an entry in the HOSTS file to map the IP address
to the NETBIOS name.
HTH,
Paul Ibison
|||The share is visible. I fixed that with an entry in the HOSTS file in the
beginning.
"Paul Ibison" wrote:

> You might try, from the client, to see if you can browse this share. When I
> have set it up, it required an entry in the HOSTS file to map the IP address
> to the NETBIOS name.
> HTH,
> Paul Ibison
>
|||OK, if it is running, transferring the snapshot and fails after a while, you
might try increating the -QueryTimeout parameter of the merge agent's
profile.
HTH,
Paul Ibison
|||I did that already as well after having a timeout problem. The current
problem just says that the file could not be propagated to the subscriber.
"Paul Ibison" wrote:

> OK, if it is running, transferring the snapshot and fails after a while, you
> might try increating the -QueryTimeout parameter of the merge agent's
> profile.
> HTH,
> Paul Ibison
>
>
|||Problem Solved:
It turns out that there is no guarantee that the order that records are
inserted during replication will be the same as when they were inserted
originally. Therefore, it is possible that a chile record can be inserted
before its parent, causing a conflict with the foreign key constraint.
After setting all foreign key constraints to "not for replication", the
client took the snapshot without a problem.
"b1@.ckh013" wrote:

> I currently have a client who needs to replicate a database between a server
> in the office and a computer at home using a VPN connection.
> Here is the currnet setup:
> -Merge Replication
> -Both the server and the client have SQLSERVERAgent logged in with the same
> username and password for pass-through authentication
> -Subscriber impersonates SqlServerAgent
> -Anonymous subscription
> The snapshot has been created successfully on the server and I am able to
> register the client on the server's Enterprise Manager (so they can see each
> other). The replication starts and runs for quite a while (most of the work
> is done). Eventually, it fails with the message
> //<servername>/<sharename>/<filname>.dri could not be propagated to the
> subscriber. With no further details. I don't think it is security related,
> because most of the work is already done.
> Any ideas what I'm missing?

Merge replication on MSDE using VPN

I suspect your issue is that you don't have an alias, but
have a look at this article which should help:
http://support.microsoft.com/?id=321822
Rgds,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
Hi Paul,
Thanks for the reponse. I have created an alias for the msde at the server
and registered it in EM. I was still getting the same error so I enabled
output logging for the merge agent. Apon inspection of the log it indicated a
logintimeout error had occurred and suggested increase the timeout value. I
created a new profile for the merge agent extending both the logintimeout and
the querytimeout parameters. I stopped and started the merge agent and now
the log contains the following message ("Cannot generate SSPI context"). Just
to recap, I am trying to replicate a database using SQL 2k sp3 enterprise
(windows 2003 server) and msde 2k sp3 (xp professional) between two
non-trusted domains using a vpn.
Thanks Jeff
"Paul Ibison" wrote:

> I suspect your issue is that you don't have an alias, but
> have a look at this article which should help:
> http://support.microsoft.com/?id=321822
> Rgds,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
|||Can you change to sql serurity if you don't have it
already. Also, change the jobs to be owned by sa. If the
VPN is set up correctly, I believe you should be able to
assign rights to the replication share for the sql server
agent of the pull job. In my case I used FTP but this
shouldn't be necessary. This error message has loads of
potential causes mostly connected with authentication
that you can find in Google. For my part, I have seen it
when the system clocks weren't synchronized at all
accurately.
HTH,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)

>--Original Message--
>Hi Paul,
> Thanks for the reponse. I have created an alias for the
msde at the server
>and registered it in EM. I was still getting the same
error so I enabled
>output logging for the merge agent. Apon inspection of
the log it indicated a
>logintimeout error had occurred and suggested increase
the timeout value. I
>created a new profile for the merge agent extending both
the logintimeout and
>the querytimeout parameters. I stopped and started the
merge agent and now
>the log contains the following message ("Cannot generate
SSPI context"). Just
>to recap, I am trying to replicate a database using SQL
2k sp3 enterprise
>(windows 2003 server) and msde 2k sp3 (xp professional)
between two[vbcol=seagreen]
>non-trusted domains using a vpn.
>Thanks Jeff
>"Paul Ibison" wrote:
but[vbcol=seagreen]
www.replicationanswers.com
>.
>
|||Hi Paul,
I set the SQL Server Agent service on both machines to run under a local
windows account with the same name and password and everything is working
fine now. Is there a preferred configuration for security purposes. Our SQL
Server Services have always run under a network account. I have read several
articles on how to make this work but, I would like to make sure I am paying
significant attention to security. If you know of a good source for
replication security let me know.
Thanks for all you help! Jeff
"Paul Ibison" wrote:

> Can you change to sql serurity if you don't have it
> already. Also, change the jobs to be owned by sa. If the
> VPN is set up correctly, I believe you should be able to
> assign rights to the replication share for the sql server
> agent of the pull job. In my case I used FTP but this
> shouldn't be necessary. This error message has loads of
> potential causes mostly connected with authentication
> that you can find in Google. For my part, I have seen it
> when the system clocks weren't synchronized at all
> accurately.
> HTH,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
>
>
> msde at the server
> error so I enabled
> the log it indicated a
> the timeout value. I
> the logintimeout and
> merge agent and now
> SSPI context"). Just
> 2k sp3 enterprise
> between two
> but
> www.replicationanswers.com
>
|||What you have set up is pass-through security. As you
say, normally between computers Active Directory users
are used, or Workgroup users.
Rgds,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||Jeff,
I don't know what your deployment timeframe is but SQL 2005 has the ability
to perform synchronization over HTTPS. Is this something you would be
interested in trying?
Philip Vaughn
Program Manager
SQL Server Replication
This message is provided "AS IS" with no warranties, and confers no rights.
"Jeff Rowley" <JeffRowley@.discussions.microsoft.com> wrote in message
news:4C8310C5-AC8D-4FD9-8152-1BFCADE59A29@.microsoft.com...[vbcol=seagreen]
> Hi Paul,
> I set the SQL Server Agent service on both machines to run under a local
> windows account with the same name and password and everything is working
> fine now. Is there a preferred configuration for security purposes. Our
> SQL
> Server Services have always run under a network account. I have read
> several
> articles on how to make this work but, I would like to make sure I am
> paying
> significant attention to security. If you know of a good source for
> replication security let me know.
> Thanks for all you help! Jeff
> "Paul Ibison" wrote: