Take the 2-minute tour ×
Programmers Stack Exchange is a question and answer site for professional programmers interested in conceptual questions about software development. It's 100% free, no registration required.

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.

share|improve this question

closed as not a real question by Robert Harvey, Walter, Glenn Nelson, gnat, ChrisF Dec 27 '12 at 12:31

It's difficult to tell what is being asked here. This question is ambiguous, vague, incomplete, overly broad, or rhetorical and cannot be reasonably answered in its current form. For help clarifying this question so that it can be reopened, visit the help center.If this question can be reworded to fit the rules in the help center, please edit the question.

add comment

2 Answers

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.

share|improve this answer
    
Ordinarily I'd agree, but in this case I think @mike is correct in saying make it obvious that there are two sources of data. Even if this is defining an abstract class/interface and working through that; there should be 2 clear objects to represent 2 data sources. However, for simplicities sake they should both have the same methods, parameters and so on. Remember the single responsibility principle; keep your classes responsibilities simple and clear. However, 1+; generally I agree - also, you highlight the often forgotten task of ensuring database privileges are nice and tight. –  Fergus Morrow Dec 26 '12 at 21:42
add comment

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.

share|improve this answer
    
I've done my best to make "Joins" between the two DBs as infrequent and simple as possible for that very reason. Both DBs are as independent as possible. Could you elaborate more on the connection pool? With the current set up, the SQL Servers are completely separate from the application so the connection is made when it is needed (which is most of the time). I don't really control the connection, just the credentials. If the client so chooses, they could turn off the server holding their data and the application would no longer function. –  MobyD Dec 26 '12 at 21:15
    
The way you worded the text, it sounded like you were arbitrarily opening a connection to SQL server when the user logged in. The same rule applies regardless of pooling. Open as late as possible; Close as soon as possible. You're probably getting pooling behind the scenes. When you Open/Close it is not literally opening a new connection but taking one from the pool. –  mike30 Dec 26 '12 at 21:32
add comment

Not the answer you're looking for? Browse other questions tagged or ask your own question.