sql server replication: Push over the Internet? -- sql server replication

Re: Push over the Internet?

Kyle
7/1/2004 10:12:01 AM

Hari,
Thanks for the reply. Since writing my post I have become VERY familiar with that article. From what I understand, you can push over the Internet if the subscriber already has the schema. To get the initial schema, however, the subscriber must have a snapshot agent that is configured to pull via FTP. While this is not my ideal solution, I may be able to make it work. Some of my subscribers will be using MSDE though so I don't know how well that will work.

Thanks,
Kyle

[quoted text, click to view]

Re: Push over the Internet?

Hari
7/1/2004 12:30:32 PM

Hi,

Have a look into the below article.

http://support.microsoft.com/?id=321822

FYI, I have never tried replication over internet.

--
Thanks
Hari
MCDBA
[quoted text, click to view]

Re: Push over the Internet?

Hilary Cotter
7/2/2004 10:42:12 AM

you can't push over the internet.

--
Hilary Cotter
Looking for a book on SQL Server replication?
http://www.nwsu.com/0974973602.html

[quoted text, click to view]
with that article. From what I understand, you can push over the Internet if
the subscriber already has the schema. To get the initial schema, however,
the subscriber must have a snapshot agent that is configured to pull via
FTP. While this is not my ideal solution, I may be able to make it work.
Some of my subscribers will be using MSDE though so I don't know how well
that will work.
[quoted text, click to view]

Re: Push over the Internet?

Kyle
7/2/2004 11:58:01 AM

Hilary,
I clicked on your book link and was disappointed to see that you wrote a book on transactional and snapshot replication but not merge. I then checked out the nwsu site and was delighted to see that you are devoting an ENTIRE book on merge replication. THATS GREAT! because so far, I have not been able to find a book that deals with the subject in depth. Usually I just see a chapter on replication that just repeates the Microsoft BOL. Please let me know when it is released.

That said, I HAVE been able to push over the Internet. It's not easy, it's not obvious and it's not well documented but it IS possible. I'm still working on my application but here's what I have learned so far which has enabled me to push over the Internet with dynamic filtering:

1. The publication must be saved to an FTP site that is available to the subscribers.
2. You must make a server alias (with Client Network Utility) of the subscriber on the publisher server that MATCHES the actual NetBIOS computer name of the subscriber. It seems dumb that you have to do this but it works.
3. For a dynamic filter to work with a push subscription, you must filter on HOST_NAME() and put a -hostname [somename] parameter in the Run Agent step of the subscription.

If I've got something wrong in my head or you have any other tips, I'd love to hear about it. Also, if I can be of any help with your upcoming book, please let me know.

Thanks,

Kyle

[quoted text, click to view]

Re: Push over the Internet?

Kyle
7/2/2004 12:30:01 PM

Hilary,
I clicked on your book link and was disappointed to see that you wrote a book on transactional and snapshot replication but not merge. I then checked out the nwsu site and was delighted to see that you are currently working on a book devoted to merge replication. Selfishly, I'm hoping you don't have good surf conditions for the next few months. Please let me know when it is released.

That said, I HAVE been able to push subscriptions over the Internet. It's not obvious or easy but it IS possible. I'm still working on my application but here's what I have learned so far which has enabled me to push a merge subscription over the internet with dynamic filtering:

1. The publication must be put on an FTP site that is accessable to the subscribers.
2. The publishing server must have an alias to the subscriber that matches the actual NetBIOS computer name of the subscriber machine. This seems really dumb to me but it's the only way I could get it to work.
3. To enable dynamic filtering with a push subscription, you must filter on HOST_NAME() and you must put a -hostname [somename] parameter in the Run Agent step of the merge agent. This allows you to specify a particular subset of data to each subscriber independant of the subscriber's actual name or user (although, as I mentioned in point 2, you do have to know the subscriber's computer name).

If I've got something wrong or you have any other tips for me, please let me know. Also, if I can be of any help with your upcoming book, I'd be glad to contribute.

Thanks,

Kyle
[quoted text, click to view]

Re: Push over the Internet?

Hilary Cotter
7/2/2004 11:37:26 PM

But if FTP is in the mix you are using a pull.

You don't have to register you Netbios name of the Subscriber on the
publisher. It can be any name as long as that name matches the ip address of
your subscriber and this name is entered in your hosts file.

--
Hilary Cotter
Looking for a book on SQL Server replication?
http://www.nwsu.com/0974973602.html

[quoted text, click to view]
book on transactional and snapshot replication but not merge. I then checked
out the nwsu site and was delighted to see that you are devoting an ENTIRE
book on merge replication. THATS GREAT! because so far, I have not been able
to find a book that deals with the subject in depth. Usually I just see a
chapter on replication that just repeates the Microsoft BOL. Please let me
know when it is released.
[quoted text, click to view]
not obvious and it's not well documented but it IS possible. I'm still
working on my application but here's what I have learned so far which has
enabled me to push over the Internet with dynamic filtering:
[quoted text, click to view]
subscriber on the publisher server that MATCHES the actual NetBIOS computer
name of the subscriber. It seems dumb that you have to do this but it works.
[quoted text, click to view]
on HOST_NAME() and put a -hostname [somename] parameter in the Run Agent
step of the subscription.
[quoted text, click to view]
love to hear about it. Also, if I can be of any help with your upcoming
book, please let me know.
[quoted text, click to view]

Re: Push over the Internet?

Kyle
7/3/2004 1:56:02 AM

I see your point about the FTP but I don't think I'm creating a pull subscription. For the initial data... on the publisher I must tell the subscribers how to get to the FTP site (IP, login, password, folder). This is done via the Snapshot Location tab on the publisher properties form, but this information does not show up anywhere (that I can find) on the subscriber. The subscriber shows that it has one subscription and that it is a push type. From the subscriber, I cannot modify or delete the subscription - meaning that the publisher has complete control (as long as the connection remains in tact). Maybe internally there is an invisible pull subscription but from both sides all I see is push and the publisher maintains complete control (which is what I want). Also, on the Subscription Options tab of the publication properties, I have un-checked the Allow pull subscriptions checkbox further convincing me that there are no pull subscriptions (even while moving the initial snapshot).

I tried using the lmhosts file instead of registering an alias with the Client Network Utility to map the subscriber IP to a name other than it's actual NetBIOS name. That allowed me to connect and it allowed me to push the initial snapshot. The merge agent failed, however, giving me the following error:

The subscription to publication 'TestPush1' is invalid.
(Source: Merge Replication Provider (Agent); Error number: -2147201019)
--------------------------------------------------------------------------------------
The remote server is not defined as a subscription server.
(Source: SACRAMENTO (Data source); Error number: 14010)
--------------------------------------------------------------------------------------

SACRAMENTO is the name in the lmhost file that is mapped to the subscriber's IP address. It IS defined as a subscription server. The same publication 'TestPush1' works fine with the other subscription (where the alias = the actual NetBIOS name).

This is all pretty much consistent with the KB article 321822 except that the article implies that you can't move the initial schema and data without a pull subscription. It does say, however, that you need to use the actual NetBIOS name although I would sure like to find a way around this requirement.

Kyle

[quoted text, click to view]

Re: Push over the Internet?

Hilary Cotter
7/3/2004 2:07:33 PM

Can you script out your publication so we can see exactly what you have
done. It is possible you did a no sync push which can be done over the
internet.

FTP information is stored on the publisher not the subscriber in SQL 2000,
on SQL 7 IIRC it was also stored on the subcriber. The subscriber connects
to the publisher (using pull) to get ftp information.

Don't modify the lmhosts file, but the hosts file. LMhosts is primarily used
during netbios name resolution and if you are using TCPIP in the client
network utiltity it will use cache-hosts-DNS resolution to get the IP
address. It may then do a broadcast and consult the LMHosts files (IIRC).

You have to enable Sacremento as an enabled subscriber which won't work over
the internet. So you should be using anonymous.

Please contact me offline so we can resolve this quickly and I can get a
handle on what you are doing.

--
Hilary Cotter
Looking for a book on SQL Server replication?
http://www.nwsu.com/0974973602.html

[quoted text, click to view]
subscription. For the initial data... on the publisher I must tell the
subscribers how to get to the FTP site (IP, login, password, folder). This
is done via the Snapshot Location tab on the publisher properties form, but
this information does not show up anywhere (that I can find) on the
subscriber. The subscriber shows that it has one subscription and that it is
a push type. From the subscriber, I cannot modify or delete the
subscription - meaning that the publisher has complete control (as long as
the connection remains in tact). Maybe internally there is an invisible pull
subscription but from both sides all I see is push and the publisher
maintains complete control (which is what I want). Also, on the Subscription
Options tab of the publication properties, I have un-checked the Allow pull
subscriptions checkbox further convincing me that there are no pull
subscriptions (even while moving the initial snapshot).
[quoted text, click to view]
Client Network Utility to map the subscriber IP to a name other than it's
actual NetBIOS name. That allowed me to connect and it allowed me to push
the initial snapshot. The merge agent failed, however, giving me the
following error:
[quoted text, click to view]
subscriber's IP address. It IS defined as a subscription server. The same
publication 'TestPush1' works fine with the other subscription (where the
alias = the actual NetBIOS name).
[quoted text, click to view]
the article implies that you can't move the initial schema and data without
a pull subscription. It does say, however, that you need to use the actual
NetBIOS name although I would sure like to find a way around this
requirement.
[quoted text, click to view]

Re: Push over the Internet?

Hilary Cotter
7/4/2004 7:55:45 AM

Kyle, thanks for sending me your scripts off line.

Yes! You are successfully doing a push over the internet, and yes, it is
possible to do a push over the internet.

The reason it is working for you is that you are connecting over DSL (I did
a tracert to the IP address you provided me with and found out you are using
pacbell DSL). The reason it is working is that the you have the netbios
ports open. On most corporate internets this is locked down as it is a
security risk.

I assumed you were on a corporate network, or behind a firewall, and that
these ports will be blocked.

So my advise to you is to use a personal firewall, and some form of internet
security software, and migrate to a pull subscription.

--
Hilary Cotter
Looking for a book on SQL Server replication?
http://www.nwsu.com/0974973602.html

[quoted text, click to view]

Re: Push over the Internet?

Hilary Cotter
7/5/2004 10:56:33 PM

To follow up on this - Kyle is using a firewall and blocks everything but
1433.

It is possible to to a push over the internet. The reason for this is that
the authentication is done by the distribution agent which runs on the
Publisher, or the Distributor.

If you use NT or SQL authentication, the account that connects to the
Snapshot share is the SQL Server account running on the Publisher or
Distributor. The authentication is done by the distrib component.

When you are using a push subscription both the publisher and the subscriber
are behing the firewall.

When you are using a pull, the SQL Server agent on the Subscriber is what
connects to the Publisher/Distributor. Authentication is done on the
Subscriber, and the distrib.exe handles the authentication - but its still
the SQL Server agent on the Subscriber that connects with the Publisher's
snapshot share. The netbios ports which are used to map a drive are
typically - but not always - not open to internet traffic. This is why you
can only do a pull subscription using FTP (or by copying the files locally
by FTP, courier, etc) over the internet.

BOL, recommends you use a VPN or FTP across the internet. See Implementing
Replication Over the Internet in BOL for more information on this.

--
Hilary Cotter
Looking for a book on SQL Server replication?
http://www.nwsu.com/0974973602.html

[quoted text, click to view]

Got something to say? Click here to post a reply to this thread.

Don't see what you're looking for? Try a search.

View other messages in the sql server replication group.