Solved

Using proxy server for replication over Internet

Posted on 2004-04-06
21
621 Views
Last Modified: 2008-03-10
I want to enable one - way incremental replication between two SQL servers but there is a third server between the two. I want to use push replication as two computers won't be always connected.

There are two local 2000 servers and third 7.0 is on the web.

Client wants that main local server where main database is located and changes etc. are made should not be directly connected to server on the Web.

He says that he will take the backup of database from main server everyday and copy it to a second local server which will work as a publisher.

Will replication work if database on the publisher is replaced everyday? Or replication will have to be configured everyday and all the contents will again be replicated to server on the web.

Is there any other way to make this setup work without any complications.

The solution I think is to also enable replication between the two local servers but then again the main database server will be connected to the Internet indirectly.

To avoid that can I use the second local server just as a proxy server. 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. I have read the MS Articles but they use MS Proxy Server.
0
Comment
Question by:Sukhwinder Singh
  • 13
  • 8
21 Comments
 
LVL 13

Expert Comment

by:danblake
ID: 10764022
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 ?
0
 

Author Comment

by:Sukhwinder Singh
ID: 10764880
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?
0
 
LVL 13

Expert Comment

by:danblake
ID: 10764970
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.  
0
 

Author Comment

by:Sukhwinder Singh
ID: 10765499
Ok. I'll check and reply.
0
 
LVL 13

Expert Comment

by:danblake
ID: 10766085
OK, I'll keep watching ;-)
0
 

Author Comment

by:Sukhwinder Singh
ID: 10771969
"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.
0
 
LVL 13

Expert Comment

by:danblake
ID: 10772521
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).
0
 

Author Comment

by:Sukhwinder Singh
ID: 10773884
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.
0
 
LVL 13

Expert Comment

by:danblake
ID: 10773914
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..)


0
 
LVL 13

Expert Comment

by:danblake
ID: 10773987
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 ?
0
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 
LVL 13

Expert Comment

by:danblake
ID: 10773999
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).  
0
 

Author Comment

by:Sukhwinder Singh
ID: 10774143
>>(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
0
 
LVL 13

Expert Comment

by:danblake
ID: 10774215
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
0
 

Author Comment

by:Sukhwinder Singh
ID: 10774450
"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?
0
 
LVL 13

Expert Comment

by:danblake
ID: 10774508
--> 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 ?
0
 

Author Comment

by:Sukhwinder Singh
ID: 10796352
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?
0
 
LVL 13

Accepted Solution

by:
danblake earned 250 total points
ID: 10796534
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.
--> ...
2004-04-08 13:28:54.15 server    SQL server listening on 192.168.0.4:1433, 127.0.0.1:1433.
The internal sql-server system seems fine...
(192.168.0.4 -- is in the internal reserved address range, 127.0.0.1 is the localhost connection).


Ms Article uses 2 nic cards.
--> Only on the Proxy Server !, We do not need to worry about this (really we do not)

Should I use external IP address of the proxy server in the Server Network Utility under WinSock Proxy?
No, as detailed in the article you use an internal address of the server and setup the proxy server to transfer from the external address -> internal address/routing.  

--> And you will need to add an alias for the external server, as previously detailed

You will probably need to cover how ms-sql server connectivity troubleshooting:
http://support.microsoft.com/default.aspx?scid=%2Fservicedesks%2Fwebcasts%2Fen%2Fwc020603%2Fwct020603.asp

Question is about  replication using proxy  and not about designing database. Main thing is USING PROXY.
-- I believe this to be answered, I believe you have a different problem, it looks like it is not a proxy issue.

Still Internet Server is  not found by enterprise manager. Did you add in your client network config ?
This is mentioned in the article.. .using the client network config (on your client machine..) the external address presented on the internet by your second proxy server (or the external address of the machine)?

...End of responses to this Q.
0
 

Author Comment

by:Sukhwinder Singh
ID: 10797999
You have tried hard to help me so I am accepting your answer first and then try to implement what you have suggested.
0
 
LVL 13

Expert Comment

by:danblake
ID: 10800272
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

0
 
LVL 13

Expert Comment

by:danblake
ID: 10811252
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.
0
 
LVL 13

Expert Comment

by:danblake
ID: 10862769
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).
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.

705 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now