Fog Creek Software
Discussion Board




MS SQL Server vs. Oracle missconception?

In a recent thread I saw the following statement:

>The general reasoning is SQL Server is easy for
>developers to manage. Oracle isn't. Hire a DBA for Oracle.

Unless you are doing trivial administration on MS SQL Server, you would need to know the same things as when doing non-trivial administration on Oracle would you not?

Or would this simply lead to the MS SQL Servers out there is being more missconfigured than your average Oracle database?

Im not really all that familiar with how you can administer the storage parameters in MS SQL Server or what you can tweak when optimizing indexes and such these days,
(I used MSSQL 6.5 back in the day) and I have used Oracle since.

In my experience, you need a deep knowledge and deep understanding of the application data model to be a good DBA.  You as a developer knows which tables will grow this and that much over time. This is important when setting the storage parameters obviously.

Your average DBAs, be it Oracle ones or MS SQL ones simply does not care about application knowledge.

Feel free to disagree; I maintain the standpoint that the developers of an application knows it best and therefore can do the DBA.

Patrik
Monday, September 22, 2003

I'll tell you what kills us with SQL Server Administration is the ongoing maintenance of the DB.  All the scripts to move data from this installation to that installation, the scripts to update the schema, the scripts to configure security, rebuilding the indexes, truncating the logs, etc.  We don't have a DBA to do this stuff correctly and it's really aggravating. 

To be honest, though, I can't see how a DBA has an easier time of it other than just being experienced with this kind of thing.  The tools seem to offer only the barest minimum of support for a lot of common tasks.  Even figuring out what order a set of Views has to follow in order to install correctly is an exercise in frustration.

non-DBA
Monday, September 22, 2003

I've been a developer/DBA for SQL Server and Oracle production systems. I have five years of Oracle DBA experience, and just three years as a SQL Server DBA.

The executive summary: SQL Server is far, far easier to administer while providing a solid application response time.

My current baby is one that I installed and have managed for over a year - it's been in production since February and the main table just hit 100,000 records (subordinate tables are over 500k records). I have a 300-line Stored Procedure that executes against several tables joined and unioned (you can see an excerpt of it in a thread last week). That SP executes in under 80msec in production.

The same database server serves an active website that sees 10k hits/day.

Admittedly it's a "medium to low" size database, but managing it is something I spend less than an hour a week on. In my experience with Oracle, this has about a 50/50 chance of being one of those "when we needed help tuning our database, we got two dozen Oracle consultants the next day and they took the dev team to the Four Seasons" stories. In other words, I think the reason there are so many "Oracle helps us so much" stories is that Oracle needs so much help in areas where SQL Server in managed by a single dev/DBA.

SQL's GUI management tools make it excessively easy to manage, and a LOT of it is auto-tuned. I believe you need a dedicated DBA at a level when it "seems" appropriate, as opposed to Oracle, where you very quickly need a dedicated DBA just to babysit it.

Bottom line: Oracle is a Forumla I racer, and needs a support team; while SQL Server is a Honda that you just plain drive. (Only problem is that the brand new Formula I is only 5% faster than a 2000 Honda, but 33% more expensive, and Honda's got a new model coming out next year...)

Philo

Philo
Monday, September 22, 2003

Philo,

I disagree you have to babysit Oracle 24/7 and fiddle here and tweak there from day one. The Oracle DBAs would tell you this obviously, its in their interest.

I did an Oracle Application back in 1999 that have been seeing alot of usage. Its a extranet web application, and if you schedule statistics collection jobs and things like that Oracle will perform very decently without constant fiddling.

The formula 1 racer would just perform a bit slower lap times, it wouldnt crash.  This is also for a "low to medium" application. If you have millions of records going into the database daily that is another story.

What I have found though is that the "you need DBA attention daily" is not true. All Oracle instances does not need constant DBA babysitting.

Patrik
Monday, September 22, 2003

Patrik - thanks for that. I'll grant the "babysitting" was a bit of hostile hyperbole. ;) But I have still found that running Oracle requires more attention than SQL Server.

I won't even get into the arcane rituals required to *set up* Oracle.

Philo

Philo
Monday, September 22, 2003

I remember a specific banking application that ran on Oracle 7 on a NT4SP1 (or maybe SP2) Unisys server running a custom data acquisition application (related to the DP500 system). You could expect it to be left alone without much administration and acceptable performances with a few tens of thousands of transactions a day (60-120k transactions or so.) HW wise, it was a dual pentium pro 200 with probably 512 megs of RAM. The system is still in production and running strong after 5-6 years with no plans for "upgrades." In fact, the only major issue is the high rate of keyboard replacements from the users...

Morale of the story: It doesn't really matter what DB system you use, if you did your homework right and you've got sensible hardware sizing, you shouldn't have to "babysit" a DB. Just run regular back-ups and a few script to make sure it remains within acceptable bounds. The fact that you can do this is the difference between "production" and "developement" systems.

Saruman
Monday, September 22, 2003

Patrick,

I'm the one that made the original comment, so I'll clarify.

I've been using SQL Server since 6.5. Over the years, I've picked up a lot of knowledge about how to tweak and administer it. I've read Delaney's tomes on SQL Server so I feel pretty comfortable doing just about anything with it. In short, my experience with it plus lots of reading makes me feel comfy with my SQL skills. Bottom line for me: Anyone can learn DB fundamentals and then easily apply them to SQL Server. (MS SQL Server for the anal retentive out there.)

For over two years now, I've been using Oracle at my client. I'm da bomb with PL SQL (Thank God for TOAD!), but when it comes to managing Oracle it is still largely a mystery to me. I've learned a lot, but Oracle is just much more complex and detailed when it comes to configuration. (And I'll second Philo's sentiments about installing it.) I've read plenty about it, but I still feel as if I haven't scratched the surface yet.

This isn't to say that Oracle is needlessly complex or that SQL Server is a toy. It's just SQL Server is pretty easy for a developer to get up and running and Oracle...well, it really isn't. On the other hand, Oracle has done well marketing itself as the superior database system.

Mark Hoffman
Monday, September 22, 2003

Mark,

Thanks for the clarification. I can understand where you are coming from. I have used Oracle heavily and MSSQL
to a small extent. I think every database has its own somewhat bizarre ways of doing things. Both systems have their own quirks and when you get used to them they are OK.

IMHO, Oracles zillion of parameters, (both storage parameters for tables and global DB settings) need not really be tampered with for normal application developement and when running in production.  They are mostly "set-and-forget" parameters. And unless you are doing something extreme, the suggested settings are fine.

They are however useful when you find out that your square-peg needs to fit in that round hole.

Patrik
Monday, September 22, 2003

In my experience properly set up Oracle database requires wery little babysitting.  Of course - proper implies knowlede - and as performance consultant I have seen many Oracle databases set up poorly and these do perform also poorly.

On the other side one can squeeze lot [performance] out of Oracle. And Oracle has superior stability and recovery options - it is very hard to lose data w/ Oracle (excluding user errors).

Actually Oracle goes beyond being database. It is actually rdbms + application server inside database. Developer finds many API-s inside database and once knowing PLSQL you can go far without need for other programming languages.

msc
Tuesday, September 23, 2003

Oracle setup goes more/less like this:
1. Put CD in server
2. Run setup
3. Start Admin utility and create database (answer where do you want it). You can have database created during setup procedure.
4. Create tablespaces for data (that is create few big files on disk for data storage )
5. If you do not want to use Windows security then create users and access rights.
6. Create database structure (tables, indices, ...)
7. Put it into production and forget about it

How's that different from MSSQL?
About tuning: there are just two questions: how much memory on your server would you like Oracle to use and how much concurrent sessions will be allowed.

If you want parallel query execution on MP server, super-duper clustering, of course someone need to read the fine manual to find what is going on and what can you get. Call him DBA or whatever.

drazen
Wednesday, September 24, 2003

*  Recent Topics

*  Fog Creek Home