suggestions for a database for a live site to convert to?

[Stephen Huey]

Short version: We need to switch over to a new database to back up our J2EE web app and we know very little about other databases and need suggestions for a good one for a small company like us.

Long version:
We are a small IT dept with only a couple of programmers, and the company's web app currently handles about 2000 concurrent users at peak time (though that number has doubled in the past year and will continue to do so). It's written in J2EE and all the database access consists solely of SELECT, INSERT, UPDATE and DELETE statements made over Sun's JDBC-ODBC bridge (because there's no type 4 driver available for this db). We are fed up with a fast enough and otherwise reliable multivalue db my boss has relied on for years because the ODBC driver keeps failing on us and we have to periodically restart Tomcat DURING PEAK TIME (and sometimes it takes minutes to get the ODBC driver responding correctly again by stopping and starting the service over and over again). The powers that be are breathing down our necks for a fix, and we need to choose a db fast.

My boss is used to that db system, and as for me, I'm no db admin and certainly not well versed in using a variety of databases. We've used a simple 2-table MySQL db for authentication for the site, but we hardly know more about MySQL than setting that up. I'm really not sure what's best for us since PostgreSQL and DB2 and other things like that are out there. It's been a while since I took an upper level db course in college, and I know that even if that were fresh in my mind there would be PLENTY yet to learn to take care of any kind of large database since that was really just a relatively cursory introduction to normal forms and SQL and query optimization, etc.

Someone once suggested that if we wanted to switch over to MySQL that it would probably be able to peer into our table structure and replicate that and just be pretty much ready to use. I'm not sure about that since we might have to work around some of the occasional multi-value fields.

I just saw that Oracle has something geared towards small businesses: Oracle Standard Edition One at $150 per license. I figure with connection pooling we'd need at least about a dozen licenses, and I think the powers that be would definitely be willing to pay at least that much if not double just so that we could have a stable connection to our database! However, if that started getting too expensive too soon because we needed more licenses, then I don't know where to turn to next.

I'm just wondering what would be the easiest to set up and learn to use, especially with an existing J2EE app. We don't use Websphere or Weblogic or anything like that--just Tomcat on a Win2K box. Please toss your recommendations my way!

Thanks so much...
[ July 29, 2006: Message edited by: Bear Bibeault ]

[Jeanne Boyarsky]

Stephen,
Who is going to be supporting this database? The programmers? If so, you need to think about what databases you can easily figure out how to maintain.

I've used db2 and Oracle for work. Both are stable and provide excellent failover capabilities.

[Stephen Huey]

Yeah, that's one of the most important questions--who's going to take care of this new db? I would probably be one of the people involved in that after having someone help us initially set it up...so, I see that Oracle Standard Edition One is supposedly so easy that one needs "no special technical abilities". I just wonder if that's going to be powerful enough for us for more than a few months or a year or so. It only runs on one or two-processor servers, and our db server has 4 processors (I know that doesn't necessarily mean much).

As for some of the other Oracle options, are some of them pretty intuitive to use (like with easy-to-understand GUIs, etc)? A couple of years ago I saw Microsoft SQL Server being used by an admin at a place I worked at, and that didn't seem too difficult to use if you know basic database design concepts.

[Jeanne Boyarsky]

Stephen,
Oracle has some of the best documentation of all the dbs. It also has good support once you get stuck. We have a team that maintains our Oracle for us. They say that it is easier to support than db2.

[Stefan Wagner]

I guess postgresql will handle simple insert/updated/delete/select-statements very well, and is told to perform very good - but I would go for some googling to find out about number of concurrent requests in more detail to be sure.

Postgresql is easy to install and costs no license cost.

For the oracle-server, don't you have to pay much more than $150?

Of course oracle is very solid and performant, but perhaps too big and therefore harder to manage?

[Jeremy Wilson]

I have uses Microsoft SQL Server 2000, Phoenix Firebird(Open Source Interbase), MySQL and Oracle over the past 6 years extensively. If cost is no issue then Oracle is 100% the way to go. It is alittle misleading with the $150 per license. The license agreement states the minimum named users is 5 which puts the price alittle higher. Much better than previous versions of Oracle.

If cost is an issue I would recommend Firebird due to foreign key and stored procedure support that is not part of MySQL.

If you have any questions about Oracle please let me know, I would also be available for consulting if your project leads you in that direction.

[Sergey Zolotaryov]

I agree with Jeremy on Firebird. I have used it in one successful project, and if it were not for customer's rigid commitment to MySQL, I would use it in my next projects.

[Bear Bibeault]

I support a client who uses Firebird and find it very antiquated and limiting. There are too many operations that it does not support, and it's SQL syntax for data slicing is out and out weird. It's inability to create scrollalbe result sets is severly limiting.

I much prefer PostgreSQL and MySQL clients, PostgreSQL being the one I choose for my own projects.