Using proxy server for replication over Internet

Will replication work if database on the publisher is replaced everyday?
Yes, but you may need to rebuild the merge publication as a result (everyday) to syncronise with the timeframe of the snapshot that you generated to ensure consistancy between the sites depending on how you handle your error conditions.

If yes how to configure Main local sql server (which has the database) to use proxy server (Not MS Proxy Server) to connect to Internet for replication.
--> The proxy server would normally pick up traffic to a specific destination and forward this information a like a firewall allowing/disallowing access to the world from source ip addresses.  This would be a proxy server configuration issue.

I have read the MS Articles but they use MS Proxy Server.
-->You don't have to use MS Proxy Server, any Proxy server system should do.  Bit like any VPN system will do it does not have to be Microsofts RRAS System, Cisco hardware VPNs also work well.


You can forward a publication from a site to another, then to the web-server.  IT is possible for a publication to be used at a second site, and also published to the third site.  OR have the second server working as the distribution server from the database on the first server to the web-server.

What is the question here ?

Main problem is that Main server which has the database should not be connected to internet.

What I wanted to know the steps which are required to configure sql server to use proxy server.
Which configuration is needed to be changed to make it use proxy server. Like server network utility.

I have added the address of proxy server under  "Enable WinSock proxy" using Server Network Utility but Sql server is unable to find the server on ther other side of the proxy server which is internet. This utility may be used for some other purpose. I don't know.

Secondly if I don't use proxy server and want to directly connect to sql server on the internet.
What steps are required.
Is only adding an alias required?

Its not the case of connecting to the destination proxy or source proxy server, but the enabled external ip address of the sql-server on the internet that has been enabled by the proxy service.

Secondly if I don't use proxy server and want to directly connect to sql server on the internet.
--> A router generally is required to forward internal ip addreses (and multiple ports) to an external ip address.

Image a setup like this (my ACME network):

DB-Server                                                  Proxy Server in internal setup                                   Proxy Server on Internet                Internet DB
IP: 192.168.0.1 ------------------------ 192.168.0.2 : 65.62.10.2 ------------------------------------------ 65.62.10.1 : 192.168.0.3 ----------192.168.0.5
(Internal IP Address)                      (Internal IP Address)  (External Address)                           (External Address) (Internal Address)  (Internal IP)

The proxy server requires rules to forward all traffic on the network from ip address 192.168.0.1 going to 192.168.0.5 via 65.62.10.2 -> 62.62.10.1.  This is normally handled by a proxy server config setup.


Secondly if I don't use proxy server and want to directly connect to sql server on the internet.
What steps are required.
Is only adding an alias required?
(No you need to open ports between the two connections, allowing each machine to listen on the external ip addresses -- this is usually performed via a router to route an external subnet to an internal address range for communication to the internal server... such as:

DB-Server 1                           Router1                                     Router2                                             DB-Server 2
192.168.0.1 ------------ 192.168.0.1|62.54.10.2  ------------------- 62.54.10.3 | 192.168.0.2 ---------------- 192.168.0.2
(Int IP Addr)               (Ext Assigned IP Address)                      (Ext IP)           (Int IP)

In your setup, you should just need to define you want to talk to the external ip address (in the server config tools of the external router ip destination address as on Router 2 in this example).  This means DB-Server1 talks to 62.54.10.3 in this example.  

Ok. I'll check and reply.

OK, I'll keep watching ;-)

"The proxy server requires rules to forward all traffic on the network from ip address 192.168.0.1 going to 192.168.0.5 via 65.62.10.2 -> 62.62.10.1.  This is normally handled by a proxy server config setup."

But how will sql server find the proxy server? I think I have to somehow tell sql server to use proxy server (Spoon).

I don't have any router here. So I cannot control anything. There are just two compuers in their network. One is the database server and other is the proxy server using which I want to enable replication.

This thing is getting very network related. I don't have any expert network administrator here. So I have to manage everything myself.

Decrypting the proceduers, extracting data from local database and then replicate it through proxy server to web server on Internet and then develop the website.
My other problems were solved with your help and only thing remaining to be configured is replication.

I am using spoon proxy server.

Is there any simple way to make it work.

Simple + Computing: If Only...

Try setting a static route on your server (SQL ) PC, with the following information:
route add <network_destination> netmask gateway

Have you checked the proxy servers log files for any traffic that has been transmitted between the two sites ?

How does the sql-server find the proxy server ?
You will need someform of route from the sql-server -> proxy server (normally the router is setup as a default gateway or modem/proxy server).
You can check your proxy server is listed when you run:
Route Print

on your sql-server (especially if no traffic is making it to the proxy server).

I have been able to successfully connect to server on internet directly using a modem and dialup connection and replication has been done. I just had to add an alias.

Proxy server ip is not displayed when I run route print..

Active Routes:
Network Destination        Netmask          Gateway       Interface  Metric
        127.0.0.0        255.0.0.0        127.0.0.1       127.0.0.1       1
      192.168.0.0    255.255.255.0      192.168.0.4     192.168.0.4       1
      192.168.0.4  255.255.255.255        127.0.0.1       127.0.0.1       1
    192.168.0.255  255.255.255.255      192.168.0.4     192.168.0.4       1
        224.0.0.0        224.0.0.0      192.168.0.4     192.168.0.4       1
  255.255.255.255  255.255.255.255      192.168.0.4     192.168.0.4       1
===========================================================================
Persistent Routes:
  None

My system's ip is 192.168.0.4 and proxy server ip is 192.168.0.5. Port of proxy is 8080.
Proxy server ip is not displayed in the list above.

I have added route to proxy server on Win2k Server where database is residing.

route add 207.106.22.8 MASK 255.255.255.255 192.168.0.5

Still Internet Server is  not found by enterprise manager.

This is only for testing...(When you used the modem / dialup connection: you had all ports open between your source -> internet connected machine).

Do you have all ports open for this source -> destination address ?
(SQL Server uses port 1433 as default to establish communication, then may use any port to communicate on after establishing a connection..)

I think you are looking for more answers in a question than solutions: http://www.experts-exchange.com/Databases/Q_20689996.html.
We try to assist/help with a exact problem to provide an exact solution where possible (not design entire databases in a single question...)

From: I just had to add an alias.
--> Have you added the alias for the server on the internet (via the proxy server destination address: 207.106.22.8 ?) in your client network config ?

You may also want to check out only enabling a specific IP address inbound traffic to your sql-server that on the internet (its not perfect security -- but its better than nothing).  

>>(When you used the modem / dialup connection: you had all ports open between your source -> internet connected machine).

I did not open any port. On my soruce server only single instance of sql server is running and uses default port. 1433

And I think  destination server also uses the default port that is why I was able to connect without specifying any port


>>Do you have all ports open for this source -> destination address ?

How many ports are there. I think only one. 1433. Which ports are needed to be open?

Question remains how sql server uses proxy to reach destination

All ports would have been open as default when you initiate a modem / dial-up connection, thats why Win XP now has a built-in firewall to block ports on an incoming/outgoing basis to help prevent service port hacking.

Which ports are needed to be open?
All ports for sql-server: refering to:
http://support.microsoft.com/default.aspx?scid=kb;en-us;287932&Product=sql2k

I presume you have also gone through this setup for the proxy server/sql-config:
http://support.microsoft.com/default.aspx?scid=kb;en-us;216415&Product=sql2k

"I think you are looking for more answers in a question than solutions: http://www.experts-exchange.com/Databases/Q_20689996.html.
We try to assist/help with a exact problem to provide an exact solution where possible (not design entire databases in a single question...)"

I have only when question "Using proxy server for replication over Internet".

Question is about  replication using proxy  and not about designing database. Main thing is USING PROXY.

--> Have you added the alias for the server on the internet (via the proxy server destination address: 207.106.22.8 ?) in your client network config ?

When specifying alias only one IP address can be added. And I have added the adresss of the Internet Server there.
Where can I add the proxy address?

--> Yes this question is about using a proxy, I'm just indicating why the other question is still on hold / unanswered.  Its an old question, if you want it deleted post a note to community support and they will remove it.

Where can I add the proxy address?
In the SQL Server Network Utility

http://support.microsoft.com/default.aspx?scid=kb;en-us;216415&Product=sql2k

Does your error log show:
2002-02-12 17:11:32.28 server SQL server listening on Winsock Proxy, Shared Memory, Named Pipes.
2002-02-12 17:11:32.28 server SQL Server is ready for client connections
on startup ?

Sorry for delay in replying but nothing has worked.

I had already tried that using Server Network Utility and local sql server log shows this:

2004-04-08 13:28:54.15 server    SQL server listening on Winsock Proxy, TCP, Shared Memory, Named Pipes.
2004-04-08 13:28:54.15 server    SQL server listening on 192.168.0.4:1433, 127.0.0.1:1433.
2004-04-08 13:28:54.15 server    SQL Server is ready for client connections

I think this server is ready to receive client connections from clients on the Internet as it is listening on proxy server address and not vice versa.

I cannot control remote sql server. I can only configure proxy server and local sql server (which is behind a proxy) to use proxy for replication.

Ms Article uses 2 nic cards.

Should I use external IP address of the proxy server in the Server Network Utility under WinSock Proxy?
Will it work?

You have tried hard to help me so I am accepting your answer first and then try to implement what you have suggested.

I've have noticed that there is some free support at :http://groups.yahoo.com/group/pisoft/messages
 for spoon web-site:  http://www.pi-soft.com/spoonproxy/ (for anybody else, who wishs to chip in on this / new trhread)

For SQL-Server client mappings from Pi-Soft have a look at:
http://www.pi-soft.com/spoonproxy/sql_server.html

The two NIC card issue : Any PC with a network card and a modem or similar device is considered to have two NIC cards and is multi-homed for the purposes of the MS Article.  It does not mean that you have to have two network cards in the machine.

All a proxy server is is a representation of server representing a source.

Ever seen an auction or voted by proxy ? (where somebody else casts your vote for you -- the ip address of the proxy server is aliased or NAT in this case).