Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««12345»»»

Linked servers over the internet Expand / Collapse
Author
Message
Posted Sunday, August 22, 2010 4:54 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Sunday, October 19, 2014 11:12 AM
Points: 7,791, Visits: 9,545
Ninja's_RGR'us (8/22/2010)
My first instinct was to build a vpn tunnel between the servers but then I remembered that we can only have 1 vpn connection on 1 machine at any time, so how the heck can I go to 3500 (or more to the point 4-5 at the same time) and also very important, how can the connection be made in real time (1 sec delay max)?


I don't know what kind of servers you are using but I find a 1 vpn connection per machine limit astounding (and I wouldn't give such a server OS house-room!). Where I worked from 2002 to 2009 we used Windows 2000 Server at first and later Windows 2003 Server and had many simultaneous VPN connections from our main in-house server in London to customer servers all around the world and to our other offices. Not 3500 connections at one time (nowhere near, in fact) but a good deal more than 4 or 5 on Win 2000 Server and when we went to Win 2003 we grew to more connections than we had had on Win 2000.

Connection setup time depends very much on two things: firewall performance (at both ends) and network performance (both latency and bandwidth are relevant); if customers have good internet connection, decent and sensibly configured firewalls, and competent network management you can get 1 sec setup time provided the loop delay is not too great (you're obviously not going to get 1 second connection setup if the loop delay is 1.5 seconds).

Even if your connection time is good you can't guarantee that it won't suddenly go bad: our connection setup times for customers in the Middle East increased by a large factor when some sailor accidentally cut through an underwater cable there. DO you need to maintain the 1 sec connection setup time limit when something like that happens?


Tom
Post #973122
Posted Sunday, August 22, 2010 5:09 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Sunday, October 19, 2014 11:12 AM
Points: 7,791, Visits: 9,545
Ninja's_RGR'us (8/22/2010)
[quote][b]Is there a way on a firewall to accept traffic from only a single IP and redirect to another secured machine on a secured port far beyond the firewall even if it's not in DMZ? Im not network expert but that seems like a pretty safe way to go.

So that way only someone who's successfully hacked our server would be able to get in...


Depends on the firewall - it's certainly possible to allow some particular ports only to certain remote IPs and allow those remote IPs only to see those ports for certain local (ie on the inside side of the firewall) IPs on any firewall I've ever used for business (as opposed to home use). That still allows someone to get in if they can achieve the required IP spoofing, of course, which is why you should also require the thing trying to connect to know a key (and then they have to either both discover the key and spoof the IP or break into your server) - but now you've effectively got an unencrypted (except that connection set-up dialogue is encrypted) VPN; every firewall I've used for business has provide that capability.


Tom
Post #973129
Posted Sunday, August 22, 2010 5:29 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Wednesday, October 8, 2014 4:15 AM
Points: 20,581, Visits: 9,619
Tom.Thomson (8/22/2010)
Ninja's_RGR'us (8/22/2010)
My first instinct was to build a vpn tunnel between the servers but then I remembered that we can only have 1 vpn connection on 1 machine at any time, so how the heck can I go to 3500 (or more to the point 4-5 at the same time) and also very important, how can the connection be made in real time (1 sec delay max)?


I don't know what kind of servers you are using but I find a 1 vpn connection per machine limit astounding (and I wouldn't give such a server OS house-room!). Where I worked from 2002 to 2009 we used Windows 2000 Server at first and later Windows 2003 Server and had many simultaneous VPN connections from our main in-house server in London to customer servers all around the world and to our other offices. Not 3500 connections at one time (nowhere near, in fact) but a good deal more than 4 or 5 on Win 2000 Server and when we went to Win 2003 we grew to more connections than we had had on Win 2000.

Connection setup time depends very much on two things: firewall performance (at both ends) and network performance (both latency and bandwidth are relevant); if customers have good internet connection, decent and sensibly configured firewalls, and competent network management you can get 1 sec setup time provided the loop delay is not too great (you're obviously not going to get 1 second connection setup if the loop delay is 1.5 seconds).

Even if your connection time is good you can't guarantee that it won't suddenly go bad: our connection setup times for customers in the Middle East increased by a large factor when some sailor accidentally cut through an underwater cable there. DO you need to maintain the 1 sec connection setup time limit when something like that happens?



Thanks for the correction on VPN, my only experience was from desktop to server with Cisqo where everthing gets cutoff besides that connection.

The site I'm working on basically takes in a few parameters, wades through 20 000 items and suggests all available matches (like all possible cpu cores to fit the sockets of mother board X) and shows about 25 columns of relevant data to make a decision.

The site is already slow as hell (to my taste) and adding another 2-5 seconds to get that single extra column of data is just out of the question in my mind. So I want to have instant access to the data like if it were in the sams db in the same server. Now I know I have no control over the internet and the clients' network but I'll do everything I can to limit the steps and required time it takes for the connection.

We have no hard data yet as this is the first go-live for this project, but on a similar seach form we do get over 5000 searches per hour in peak rush. Certainly no google volumes, but nope a mom and pop shop either. And since this is a new flagship system, it is being used to bring in larger whale customers so I imagine those number will greatly increase in the near futur.

Now I can't imagine myself being happy having to wait 5-8 seconds to load a page everytime I do a search in rush hours (where you have 5 other customers waiting in line to be served).

Hence my requriement for as 0 ms connection time as possible.
Post #973135
Posted Sunday, August 22, 2010 5:32 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Wednesday, October 8, 2014 4:15 AM
Points: 20,581, Visits: 9,619
Tom.Thomson (8/22/2010)
Ninja's_RGR'us (8/22/2010)
[quote][b]Is there a way on a firewall to accept traffic from only a single IP and redirect to another secured machine on a secured port far beyond the firewall even if it's not in DMZ? Im not network expert but that seems like a pretty safe way to go.

So that way only someone who's successfully hacked our server would be able to get in...


Depends on the firewall - it's certainly possible to allow some particular ports only to certain remote IPs and allow those remote IPs only to see those ports for certain local (ie on the inside side of the firewall) IPs on any firewall I've ever used for business (as opposed to home use). That still allows someone to get in if they can achieve the required IP spoofing, of course, which is why you should also require the thing trying to connect to know a key (and then they have to either both discover the key and spoof the IP or break into your server) - but now you've effectively got an unencrypted (except that connection set-up dialogue is encrypted) VPN; every firewall I've used for business has provide that capability.


Thanks again Tom.

Since you seem like the local expert on the subject I'll ask you this :

If a nut job like me were to send you a request to expose your precious sql server... and Main ERP DB to the internet, what would be your preffered setup to ensure maximum security and super fast connection (assuming you can't kill me and make this go away) ?
Post #973136
Posted Sunday, August 22, 2010 6:25 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Sunday, October 19, 2014 11:12 AM
Points: 7,791, Visits: 9,545
Ninja's_RGR'us (8/22/2010)
[quote][b]If a nut job like me were to send you a request to expose your precious sql server... and Main ERP DB to the internet, what would be your preffered setup to ensure maximum security and super fast connection (assuming you can't kill me and make this go away) ?

Security in a number of ways: first I'd insist that you use a particular username with capabilities that I ca see are the minimum for what you are trying to do (I think that's read only access to a view which contains only the columns you need). Second I would put a firewall between me and the internet that allowed your machine (identified by a set of IP addresses) to connect only to a specific IP address behind the firewall, and only on the appropriate port (whatever port my DB provides for remote access), using a key-protected VPN. Third I would insist that you undertake not to disclose the username, the password, or the VPN key (all specified by me) to any third party (like another one of your customers).
For performance, I'd want plenty of bandwidth between firewalls and servers and plenty of bandwith from firewall to internet, and good high performance firewalls.
The trouble is that I'm not sure how scalable this is. I've done it with a few dozen remote machines, not more. You talk about 3500 customers who should (if they care about security) be people like me, so you would end up not only configuring 3500 sets of access data in the firewall at your end (for a PIX, if it could handle that much, that would be something around 21000 or 28000 - can't remember the number needed - lines of config data) but also 3500 remote security mappings in your server each mapping your local user to a different username and password and I'm not sure what the performance of SQLServer would be in handling all that security (trusted connection) data (or how you would do it all in about 4 weeks, if you don't change the deadline); I don't know what sort of firewall can cope with that many VPNs in its configuration, but of course the load can be shared between multiple firewalls by doing some subnetting of the external addresses, so in principle there's no problem there, but (since all the VPN target addresses and keys are different) there may be a problem entering all that data in your timescale. One nasty little issue that may or may not crop up is address clashes - will there be address space clashes between your network and some of the customer networks (this is quite probable if all are using local address space behind firewalls and the machines involved don't have global addresses); if so you need to give your server one or more extra IP addresses and use the subnetting (maybe with multiple firewalls) at your end to eliminate the address clashes.
And of course, sitting where you are, I'd make sure that the central firewall configuration didn't allow inward connections from the remote sites (unless you need that too for something else).


Tom
Post #973140
Posted Sunday, August 22, 2010 6:50 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Wednesday, October 8, 2014 4:15 AM
Points: 20,581, Visits: 9,619
Exactly what I tought... completly out of my league network wise .

Seriously, that's what I call expert advice.

We do have 3500 clients and we had 100% response rate for people wanting this feature. Now I highly doubt that they'll all signup for this once they know what they have to do to get in with the program (there's a lot a work to map all the sku # from our erp to theirs, among other things, and of course all the setup for secure data access, which may include extra hardware and certainly a good amount of people ware). Also I've begged for a smaller trial with our best clients for the first "release".

The main thing for me is that I don't have to coordinate all that data entry and network setup, I just have to show them what to do and they take care of it from there on out. It's also their call to add as many clients as they want... or can handle to.

Last question. We currently only have T1 access for our entire building and extranet (which is not enough at the moment). We've ordered a T2 but now I'm wondering if it can handle all that traffic?!?! Correct me if I'm wrong but using encrypted / vpn connections must certainly increase the bandwith usage for that data transfer?!

My understanding is that if you encrypt at 128 bit it'll take 128 times more bandwith to communicate your message back and forth. So assumming 2 searches per second avg and 1K of real data exchange, we're talking about 256 KB/S of bandwith.... almost 20% of the total new bandwith. Are those calculations valid?

Thanks again.

Post #973141
Posted Monday, August 23, 2010 2:11 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Yesterday @ 3:22 AM
Points: 1,602, Visits: 5,654
No, that's definitely not right. Encryption will make the data larger, but that's only because encryption algorithms usually have a fixed block size to deal with. For 128-bit encryption that block will be 16 bytes, so whatever you send will be scaled up to the nearest multiple of 16 bytes--e.g. to get a 128x inflation of size you'd have to be sending a single bit of data, which doesn't seem very likely! I wouldn't expect an overhead of more than a couple of percent in most cases, and I don't think anyone would ever use a VPN if that wasn't the case.
Post #973256
Posted Monday, August 23, 2010 4:23 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: 2 days ago @ 7:28 AM
Points: 5,584, Visits: 6,380
It bothers me that data latency is an issue in this situation. The main problem I see is network hubs going down or your clients having modem lines instead of fiber. You're going to have data latency issues, whether you build them into your system or not. It takes time for data to move from client site to your site and back to client site. There is no 1 second magic bullet, no matter how hard you look for it. There's gonna be lag.

You can't promise instantaneous responses because you don't control all the environments and the data is spread out so far between clients. And if you try to promise it, and don't deliver, that's going to make your company take a serious reputation hit.

Talk to your bosses. Make it clear that no matter what solution is implemented, that data latency of some sort will be a real, and uncontrollable, issue.


Brandie Tarvin, MCITP Database Administrator

Webpage: http://www.BrandieTarvin.net
LiveJournal Blog: http://brandietarvin.livejournal.com/
On LinkedIn!, Google+, and Twitter.

Freelance Writer: Shadowrun
Latchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.
Post #973318
Posted Monday, August 23, 2010 4:32 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Wednesday, October 8, 2014 4:15 AM
Points: 20,581, Visits: 9,619
paul.knibbs (8/23/2010)
No, that's definitely not right. Encryption will make the data larger, but that's only because encryption algorithms usually have a fixed block size to deal with. For 128-bit encryption that block will be 16 bytes, so whatever you send will be scaled up to the nearest multiple of 16 bytes--e.g. to get a 128x inflation of size you'd have to be sending a single bit of data, which doesn't seem very likely! I wouldn't expect an overhead of more than a couple of percent in most cases, and I don't think anyone would ever use a VPN if that wasn't the case.


Thanks.
Post #973324
Posted Monday, August 23, 2010 4:40 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Wednesday, October 8, 2014 4:15 AM
Points: 20,581, Visits: 9,619
Brandie Tarvin (8/23/2010)
It bothers me that data latency is an issue in this situation. The main problem I see is network hubs going down or your clients having modem lines instead of fiber. You're going to have data latency issues, whether you build them into your system or not. It takes time for data to move from client site to your site and back to client site. There is no 1 second magic bullet, no matter how hard you look for it. There's gonna be lag.

You can't promise instantaneous responses because you don't control all the environments and the data is spread out so far between clients. And if you try to promise it, and don't deliver, that's going to make your company take a serious reputation hit.

Talk to your bosses. Make it clear that no matter what solution is implemented, that data latency of some sort will be a real, and uncontrollable, issue.


Ok, awesome warning. I was aware of the fact that I can't garantee anything and my bosses are aware of this, but I do need to maximize the chances of a fast connection.

Also our situation might be a little unique. I'd say that 99% of our clients are located within 400 miles of the main office. And 100% are in Canada (Québec, Ontario).

So did you experience that latency problem when doing something similar worldwide, or even locally (assuming each client has T1 access in his building or fast cable connection)?

Also keep in mind that for each of the separate clients, we might exchange 5-20 MB of data (after encrytion) over an 8 hours day. With each transfer being at most 20 KB total (extreme case). So even on a slow connection, 20K shouldn't be all that big of an issue (I'd think)!

I'm ok with 1 sec to connect even if I'd prefer 0, but I don't want it to to sporadically jump to 5-10 secs while the clerck waits on his data.
Post #973327
« Prev Topic | Next Topic »

Add to briefcase ««12345»»»

Permissions Expand / Collapse