Local Db Data To Remote Db Data Connection

Question

Ok guys I was approached by a client to create a web service that will allow users to sort through a bunch of proprietary data. This is all fine and dandy but the catch is, all of this data comes from software they have installed on local servers at their location. I'm thinking of writing a cron job to receive and parse the data at a set interval. The data can be accessed by way of an ODBC protocol but what I need to figure out is how the hell I'm going to get the data from the local server to a remote server.

My initial thinking is, is it possible to straight connect to the local server, from PHP using the odbc_connect feature but I guess some how I'd need to have an address for the local server that is public facing?

If this isn't an option would the next best thing be something that can parse the data and server it up, online, to be accessed via the remote server? I'm not much of an "I.T." guy so I'm not sure how I'd even go about making this local server accesible from the internet. Does any of this seem possible or do you guys know of any better options? Thanks!

Answer 1

I'm presuming that you've got data in a DB here rather than a single flat file:

The safest way would be to make the local data available on an external server if possible. You could use PHP/MySQL to dump, export and load the data on a timed basis using cron or similar, but this assumes you have access to the local server environment, the DB and PHP. (Hard to suggest more without understanding your local network really).

Pseudo-process:

1. Use cron to run data_dump.php (which exports required data from local DB)
2. Connect to external DB and append (or overwrite) data

Making the internal server available to the outside world is potentially dangerous - due to firewalls, configurations, internal routing and other issues. Letting people in is more risky than letting people access a remote system that can't be used as a gateway to your network.

In former projects and companies, we've always uploaded to a locked-down and secure reporting server, which contains nothing more than reporting data.

The other option I can think of is creating a master-slave DB relationship, but thats probably out of scope here and too complex for your needs at this point.

I hope this helps a little!