Opinions Please : MSSQL or Oracle?

This is a cry for some help from all you clever folks out there. I'm a long time MSSQL user, but I'm about to start work on a project that will potentially have 500,000+ concurrent user sessions running on it. Obviously we are going to need to have several front end ColdFusion Enterprise servers, and these can be added to fairly easily as required.

The bit I'm undecided about is which database engine to use (as it would be really hard to switch later!) The choice is between MSSQL Server, which I know well, or to use Oracle which seems to be much better suited to scalling/load balancing. If anyone has any thoughts I'd love to hear them!

• Posted in:
• ColdFusion

8 comments

1. I don't have Oracle experience, but I can tell you for a fact that I'm unhappy with MS SQL Server scalability. Not sure if that helps you with your decision.
   
   Cheers,
   
   David
   
   Comment by David – January 23, 2009
2. Hi David, Thanks for the comment - it does help :)
   
   Comment by John Whish – January 23, 2009
3. I've seen lots of people unhappy with MSSQL scalability (usually thinking they were unhappy with CF scalability.) I find that a well tuned MSSQL database scales very well; but finding someone who can actually tune one is another matter. A lot of MSSQL DBAs are incompetent beyond "ensuring the backups ran".
   
   MSSQL is a LOT easier to tune (in my experience) than Oracle, but finding competent professionals to do that job on Oracle is easier (tho rather expensive.)
   
   You'll pay a lot more for Oracle, but you might find it works better for you in the long run, but mainly for personnel, not technical, reasons.
   
   Comment by Daryl Banttari – January 23, 2009
4. I've had a lot of experience in both, and I'll tell you what, working with MSSQL is a thousand times easier than working with Oracle, however, Oracle can handle a lot more data and a lot more traffic. For me, though, I would still rather buy a few servers and SQL Server licenses and set up clustering as opposed to the headache that is setting up and working with Oracle. Also, I would probably save $50k in the process.
   
   Comment by Nathan Strutz – January 23, 2009
5. John - would you consider going open source? PostgreSQL can handle very large databases, has an Oracle compatibility mode, endless procedural language support and can scale horizontally in different ways depending on your needs. Competent DBAs (for any DB) don't grow on trees but I've had good luck hiring help for PG when needed. No licensing fees frees up a lot of money for other stuff - hardware or profit - and there are many big time websites running PG in the background with great success. Take a look at:
   
   highscalability.com/postgresql-high-availability-websites />aws.typepad.com/aws/2008/12/running-everything-on-aws-soocialcom.html />
   Someone had a quote in one of those articles from late 07 that PG was providing 80% of the performance of Oracle at 1/3rd the cost. The last couple of PG releases have improved performance considerably (to the point where MySQL is no longer faster in many cases). I love PG, so I'm biased, but I think it's definitely worth a look.
   
   Comment by Brian – January 23, 2009
6. @Daryl, Nathan and Brian - Thanks for your comments, they are appreciated :)
   
   @Daryl, can you do backups in MSSQL? :P How do you set up your MSSQL across multiple servers?
   
   @Nathan, do you have an active/active set up with MSSQL. active/passive seems to be how MS recommend you to configure it. Either that of set up two CF DSNs - one for read and the other for transactions.
   
   @Brian, as impressed as I am with PostgreSQL it has to be MSSQL or Oracle due to hosting company support and expertise. Of course an Access MDB is always an option...!
   
   Comment by John Whish – January 26, 2009
7. John,
   I know that I've been involved in both active/active and active/passive setups for MSSQL server, but I am a CF guy, and not a champion of database administration by any means. But go ahead, ask me a web/cf HA question and I'll have answers for you. :)
   
   I've also worked for a brief time with different DSNs, much like you noted. We didn't notice a big difference, but we left it in like that just for good practice sake.
   
   Comment by Nathan Strutz – January 26, 2009
8. Hi Nathan, Yep - I'm in the same boat, just a regular CF guy!
   
   As the project is going to use Transfer, then multiple DSNs is just not going to work.
   
   Comment by John Whish – January 28, 2009