SQL Versus Oracle

ROS123

Hello I am not necessarily looking for a full answer here, but if you could point me in the direction of a comprehensive, independant answer on the above. Our company is considering developing new apps and is considering SQL 2005 or Oracle 10. I know Oracle scales to much higher volumes than SQL but I am not sure we will need to go to the level where we will need Oracle. Any Pointers ?

nuada

Have you considered MySQL?

seamus

At a base level:
MSSQL Supports Multiple databases per instance.
Oracle supports one Database per instance

Ergo, if you want multiple databases and aren't overly concerned about performance, then MSSQL is better because you don't have to buy a new licence to set up extra databases.

MSSQL 2005 now supports clustering, mirroring and load balancing correctly, so performance differences are minimised quite significantly.

MySQL may not be to everyone's liking, but if you develop applications, then it may make more sense to build a database stack and support multiple DB platforms, rather than lock yourself into a single platform. If you support most of the popular platforms, then customers get to choose what databases to use, and therefore what kind of performance they want.

By going with MSSQL, for instance, you immediately lose any customers who don't run Windows Servers. By going for Oracle, you immediately lose customer with tight budgets.

On the other hand, if you're developing in-house apps (only read your post properly now ), then I personally would go for MSSQL or MySQL. Even massive companies with thousands of users use MSSQL as the basis for db-heavy applications such as SAP.

koloughlin

seamus wrote:

Ergo, if you want multiple databases and aren't overly concerned about performance, then MSSQL is better because you don't have to buy a new licence to set up extra databases.

I wouldn't agree totally with Seamus on this. He's correct that Oracle doesn't have the concept of multiple databases within an instance, but it does have the concept of multiple schemas (users). You can use different schemas in Oracle in much the same way as you would use multiple databases in SQL Server, as a means of having multiple projects sharing one instance.

Having said all of that if you don't need the scalability that Oracle offers then SQL Server is a very good option. It tends to run fairly well out of the box without needing to tweak configuration files, it provides very good GUI-based administration and querying tools and if you're a mostly a windows shop it integrates nicely with .NET and windows in general.

amen

MS SQL Server (2000/2005)will scale just as well as Oracle. In certain case Oracel is faster and in some MS SQL is. I would go for which ever you can affored and is suited to your needs especially your development environment.
If using MS tools I would prob go with MS SQL

The Corinthian

nuada wrote:

Have you considered MySQL?

Sush you. Grown ups are talking.

Seriously though, MySQL is more than adequate as long as you're not looking for features such as transactions, triggers and stored procedures - and before some MySQL fanboy points out that these are available in MySQL 5, I know, and they're ropey as hell, and certainly not yet up to a standard that I would consider for a serious production system.

As for your comprehensive, independent answer, this one's a bit dated, but a fair start.

ressem

Can you provide more info?
It depends on what you're writing and what sort of guarantees your target customer is willing to pay for.
Will you be hosting the apps, installing at customers' premises, or shrinkwrapping.

Maybe look through the feature set of the 5 Oracle database versions and say which features look appropriate (keeping in mind the old prices on Cor's link).

http://www.oracle.com/technology/products/database/oracle10g/pdf/twp_general_10gdb_product_family.pdf

In it's absence I'll waffle on, assuming a generic project aimed at the small to medium business collecting info and reporting on it, not life or death stuff...

I'd be reluctant to exclusively tie my software to either, even if your application only needs the 'express version' of the db software, without a specific reason.

I'd suggest that you look into writing an application that supports multiple databases, which sets a connection string and database driver at configuration time.

There are tools that would remove a lot of the heavy lifting for you such as.

http://msdn2.microsoft.com/en-us/library/ms971499.aspx
spring framework
http://www.springframework.org/docs/MVC-step-by-step/Spring-MVC-step-by-step-Part-4.html

and countless others. like Hibernate, Codesmith/Nettiers, what you use depends on your developers' experience.

cousin_borat

If its not going to be a huge system initially you could consider Oracle 10G XE. Easy to migrate to a full blown Oracle Database down the line. You get all the Oracle functionality, Stored Procedures, etc etc.

It supports a database up to 4gb. Main drawbacks are the amount of memory you can address (1gb) and that it supports only one (proc). For alot of small companies that don't have an OLTP environment this would be adequate.

bonkey

Strangely enough, MS also offer a free version of their system, called SQL Server Express Edition.

Coincidentally, it too is uni-processor, 1GB RAM, 4GB storage restricted, but is otherwise the full MSSQL server.

Just like XE, its a doddle to upgrade from this to the full "payware" licences.

cousin_borat

hmmm, wonder who jumped first. Never had to use MSSQL but will be useul to know for dev purposes.

The Corinthian

cousin_borat wrote:

hmmm, wonder who jumped first.

I think it was MS, as far as I remember.

seamus

bonkey wrote:

Strangely enough, MS also offer a free version of their system, called SQL Server Express Edition.

Coincidentally, it too is uni-processor, 1GB RAM, 4GB storage restricted, but is otherwise the full MSSQL server.

Just like XE, its a doddle to upgrade from this to the full "payware" licences.

Limited to two connections though isn't it? Or am I thinking of the old desktop engine?

ressem

There was a limit of 2 concurrent jobs in MSDE 1, then 5 in MSDE 2000. Not meant to be limited in this way any longer in SQL Express 2005.

Just as an OT aside, the third database player, IBM's DB2 has a free version (express-c) with limits set to unlimited database size + 4GB ram, 2 dual-core usable, and support can be bought for it.

cousin_borat

do you know whether it natively supports XML like the full blown version of DB2? Really want to try this out. Being able to mix SQL/XQuery would be very useful.

Edit: looks like you can, nice