Designing a Web Application with Two Different Backend Databases [closed]

Question

I'm working on a pretty standard LAMP based web application. The server itself has a standard MySQL database and the application is written in PHP. The kicker is, we also use SQL Server databases with this application.

The current data setup is this:

• MySQL stores the user account information (Username, password, privileges, etc.) and a "connection identifier".
• The connection identifier links to another table that stores connection credentials for a SQL server database.
• When a user logs in, a connection is made to a remote SQL Server database (these are hosted independently of the web application and not in my control).
• Using that connection, the web application processes and outputs the data (only reads, no writes).

The application has evolved greatly and, while the organization and practices have improved, the design is still pretty messy and confusing. I struggle to implement any kind of framework or ORM because most are designed with one database in mind.

I have stitched together a framework that works very well for reports (where only the SQL Server connection is needed) but it doesn't go well with things like account interaction (where SQL server is used for validation of data that goes into MySQL).

The setup doesn't look like it will change so I was wondering if there was any advice on designing and maintaining a web application with two different database backends at the same time.

On a side note, storing the credentials for remote SQL Server databases on our MySQL database makes me nervous. I can only encrypt the passwords as I need them for connections and, as it currently stands, all the accounts used are admin accounts with full privileges. Not something you want getting out especially when these databases are opened up for internet connections. So any advice for that would be nice as well.

Answer 1

When a user logs in, a connection is made to a remote SQL Server database (these are hosted independently of the web application and not in my control).

I hope the connection to SQL server is not made and held when the user logs in arbitrarily. The connection should be taken from the pool only when needed, as late as possible, then returned to the pool as soon as possible.

Using that connection, the web application processes and outputs the data (only reads, no writes).

If the data is read only, then do not use admin accounts with full privileges. Only use what you need.

Advice:

The only issues I see are operations that span both databases, or keeping duplicate data in sync.

If data must be duplicated, try to restrict it to surrogate keys that NEVER change. Avoid duplicating data for performance reasons. This will make your life much easier and ironically could improve performance if there are massive updates.

Don't abstract the fact that there are 2 DB's in the data layer. Make that fact as loud and explicit as possible.

Do not make the closure of a connection dependent on the completion of an operation on the 2cd database. (except in the case of atomic transactions)

Joining sets between MySQL and SQLServer will be an issue. Retrieve the data sets from both DBs (filtered as finely as possible) then perform the "join" in your code. Functional style code would be very useful here, but imperative will get the job done.

Atomic transactions that span both DBs is an issue. (although not in your scenario) You must commit to 1 db before the other so you need to do a manual rollback if the commit fails on the 2cd DB. I don't see an easy way out there.

Answer 2

Separate the data base code from the rest of the application so that the rest of the application gets it's data from one source, your separated (abstracted) data base code.

As far as your concern about the SQL Server credentials, I hope that the connection between your web software and SQL Sever is secure. As far as storing the credentials, encrypt as you say, and name the table with the credentials something innocuous (like customer hobbies). Lock down the privileges on the credentials table to read only by your application.