Hello, My company's web site is hosted off-site, but we want to make use of data stored in a MS-SQL database at our office location (the web site will be using PostgreSQL.) The data in the MS-SQL database is updated once per day from our VAX mainframe. (Yes, I said VAX. We are going to be transitioning away from it, but we are still early in that process.) We only need to read data from the MS-SQL database into the web site, the site will not update the MS-SQL database at this point.
My first inclination was to run a cron job on the server that would execute a script to update the PostgreSQL database with data from the MS-SQL database via an ODBC connection once per day after the VAX has updated the MS-SQL database. Then the CMS could use the data from the PostgreSQL database and not have to connect to the remote MS-SQL database for queries to customer account and product information (as I said the MS-SQL database is only updated once a day, anyway.) The MS-SQL database is behind a firewall, so we could restrict ODBC connections to those originating from the web server. The two concerns I have about this method are security and scheduling. I'm concerned that someone could get access to the MS-SQL database by spoofing their IP address and then take advantage of some security hole in MS-SQL to gain more privileges than the web server connection would even be allowed (which would be read only on a specific view.) I'm also concerned that we would run into problems if, for some reason, the job to update the MS-SQL database from the VAX were run later than the process to update the PostgreSQL database. This wouldn't cause a total system failure, but it would likely require someone to a) notice that this happened, and b) run the update job manually. I have therefore been considering the use of some third-party application which could be used to keep the necessary data updated. The one I am looking at in particular is called PeerDirect (http://www.peerdirect.com). It is compatible with both of the databases in question, and it (AFAIK) has the ability to replicate only net changes as they happen (as opposed to overwriting the entire data set each day.) The other benefit to this that I see is that--once we are not depending on the VAX anymore--the software will enable two-way updates between the web server and our internal database. It will also ensure that the two systems are more loosely coupled. If the connection between our office and the web server is down, the web server uses it's own database for reading data, and updates would be propagated once the connection came back up. Have any of you had experience with this software or other similar products? Is something like this suitable for our situation? How reliable is this (or your other recommended) product? -- Regards, John Wilger -- http://cms-list.org/ more signal, less noise.
