Data access through VPN

  • We need to establish a read only access to a few tables in a huge SQL server database on a remote server, hosted by a different company.

    Right now, we can access DB through Windows PPTP VPN connection and draw data through that. This has proven flaky with anything except SSMS. We are still examining that, but assume we can establish a decent enough connection. The simplest thing then would be to open a permanent connection between our IIS server and their DB server and suck production data through that.

    Alternatively, the company that's hosting the DB can set up a WCF (or some other kind of) service, and we can then draw from there. They also talked about some kind of Azure sync server. Either way, they'd provide us with some alternative means of getting at their data. At a cost, of course.

    So what are your experiences? Can we make it work through PPTP VPN?

  • Does this have to be done on-demand/real-time or is it like a nightly batch job?

  • Ideally we would just use it as normal database, per request. A batch job clone of the needed tables is also an option, although less preferable.

  • Seems to me you've already solved the problem, you just need to figure out why it's "flakey".

    Maybe the VPN has an idle timeout, and SSMS is sending pings to prevent timeouts? I don't know.

  • I don't know either.

    Basically, an outside team is working on the backend for this, but their attitude is "Dunno, not our problem. You figure it out and just tell us were to suck the data from." Of course, I'm not getting any time tasked on this. So I'm sort of hoping someone here has experience with similar setup and can give me a clue on whether this can be made to work in general. If yes, I'll figure out specific problems later.

  • Well the answer to that specific question is kind of "duh". "Can a system that queries a database over a VPN work in general?" "Duh!" There's like a dozen people in this office who do that 8 hours a day, inbound and outbound data.

    I do also know that sometimes VPNs can be finicky beasts, especially if you don't control both end-points.

  • @cartman82 said:

    Can we make it work through PPTP VPN?

    Yes. Every cloud provider has a set of customers with part of their infrastructure hosted and part on premises. Here is Amazon's docs on how to do it. These companies run their entire business over a VPN and rarely complain of flakyness.

    If you are having problems - it's the implementation, not the idea.

  • That's the sort of thing I needed to hear.

    What worries me is that it was the guy from the DB company who hinted there "could be a problem with VPN access, in his experience". Convenient?

    So I shot them off an email, asking to give me their preferred options for data access. I'll see if the boss can give me some time to look into this as well.

  • If a VPN is giving you trouble, a mini-VPN might work: SSH port forwarding.

  • This is Windows world. None of that dirty linuxy crap here.

  • Oh sorry, the man is looking for some checkbox driven solution 😛

  • Discourse touched me in a no-no place

    @Eldelshell said:

    Oh sorry, the man is looking for some checkbox driven solution 😛

    Windows: because sometimes you've got to outsource the developer positions to India.

Log in to reply

Looks like your connection to What the Daily WTF? was lost, please wait while we try to reconnect.