Re: RE: Oracle vs. MS SQL

Here are excerpts from a thread posted by Jim Hawkins and Jared Still back in February.

Subj: Just Got Back from SqlServer 2000 training On Mon, 18 Feb 2002, Jim Hawkins wrote:

> During the class, I kept a list of all the "I can't believe this is
> really the case with SQL*Server..." items, and thought you might all
> like to see it. These are just notes I took on a Palm Pilot, so
> forgive me if they are a litte undetailed. I walked away from the
> class thinking, "this is just MS Access with bells and whistles."
> I'm not saying it doesn't have its place in the database market, but
> I just don't see how it competes with Oracle and DB2. If you even
> want to think about scaling, you have to implement Windows
> clustering, which is one of the hidden costs I see that Microsoft
> doesn't come right out and say.
>
> *Row size cannot span multiple 8k pages, therefore max row size = 8k
>
> *Cannot take DB out of "archivelog" mode. Can limit what is posted
> to txn log, but cannot stop it.
>
> *Txn logs not mirrored. Must rely on RAID or other mirroring
> software.
>
> *Separate permissions for RI checking. Requires two permission
> grants if foreign key exists - one for child table and one for parent
> table. Called REFERENCES permission.
>
> *Recommended that ALL production objects owned by DBO - not
> conducive to multi-schema instances.
>
> *Activities that are restricted during backups:
> 1. Creating or modifying databases.
> 2. Performing autogrow operations.
> 3. Creating indexes.
> 4. Performing nonlogged operations.
> 5. Shrinking a database.
>
> *Backups directly to tape require the tape to be attached locally to SQL Server.
>
> *When txn log fills up, have to just "truncate" the log in order for
> processing to continue. Leaves system vulnerable until you get a
> full DB backup.
>
> *If you have a 100GB DB that is full, your backup will be 100GB. No
> compression of backups!

Jared,

I was going to respond, but you did a great job for me. Your points were my points exactly. I really tried to go to the SQL*Server class with an open mind thinking "I'm adding a skill set", but I found myself constantly comparing to Oracle. I didn't mean to start the Holy War again, but thought it would make an interesting conversation.

A bit more:

Having databases in noarchivelog mode, especially during batch loads for data warehouses/datamarts is extremely important for a large database shop like ours.

In terms of RAID, I was just pointing out that while we mirror our redo logs to at least two different groups with two different members, I was shocked that the transaction log in SQL*Server was in no way mirrored by SQL*Server. It was either do it at the hardware/OS level or risk it. Not a "Mission Critical" mentality.

As for transferring 10GB over the network, this would be just backing up our archive logs, not to mention the datafiles themselves. We do it every day around the clock using our tape silo. We use RMAN with hotbackups directly to tape via Veritas NetBackup enterprise wide. 10GB is trivial in the Oracle world, however, judging by the response I got, not so trivial in the SQL*Server world.

One last thing: Having been to the Oracle education classes, I was expecting to learn in depth how SQL*Server uses memory to buffer the database, shared SQL, etc. thinking this would be a major tuning strategy for SQL*Server. Based on the nature of your system, you could gear the equivalent of an SGA accordingly. I almost spit up my two cups of coffee when the instructor showed me the GUI slide-bar that controls memory allocation to SQL*Server. "If you need more, just slide the bar to the right..." I still chuckle...

Jim Hawkins
Oracle Database Administrator

Jared.Still_at_radisys.com wrote:

>Couldn't resist responding to this.
>
>*Cannot take DB out of "archivelog" mode. Can limit what is posted to txn
>log, but cannot stop it.
>>>>>>> Why would you want to? So you have the remote possibility
>>>>>>> of ending up with a corrupt, unrecoverable database if the
>>>>>>> power supply on the system fails?
>
>JS: Taking a database out of archive mode is certainly valid for large
>load operations. Let's see, I want to load 50 gig of raw data into
>my data warehouse tonight, that will generate about 800 gig of redo.
>
>Do I really want to do generate that much redo, deal with the overhead,
>and back it up besides? Or would it be easier to put the DW back in
>archive mode and back up the new data?
>
>*Txn logs not mirrored. Must rely on RAID or other mirroring software.
>>>>>>> Hardware RAID/mirrors are much better than software, so if
>>>>>>> you are comparing Oracle software based mirrors to the
>>>>>>> hardware based ones we use then our way is much faster
>
>JS: No mention of reliability there though is there? If I don't have
>control
>over the hardware layout, I want Oracle to mirror the logs, period.
>
>
>Backups directly to tape require the tape to be attached locally to SQL
>Server.
>>>>>>> Okay, if you really want to transfer your 10+GB database over
>>>>>>> the network each night, I suppose you will need to use Oracle.
>
>JS: 10+GB over the network is trivial. If you are using anything that
>approaches enterprise level backups, you will dedicate some fast pipes
>to your network attached tape system. This means that if you're using
>for instance Tivoli with a StorageTek Tape Silo,you must copy it first
>to disk, since you're not going to have direct access. Making backups
>to disk first tends to break any Oracle specific tape cataloging system
>( RMAN for instance ) so that files must be located manually in case
>of a restore.
>
>*When txn log fills up, have to just "truncate" the log in order for
>processing to continue. Leaves system vulnerable until you get a full DB
>backup.
>>>>>>> Seems a little like disk space filling up in Oracle. How is this
>>>>>>> different?
>
>JS: This is a poor analogy. It isn't like disk space filling up in
>Oracle.
>The only disk likely to fill up is the archive log destination, and if
>you're doing your job as a DBA, that won't happen.
>
>I've been to DBA class for Sybase, which has the identical mechanism for
>transaction logging. It's crude and vulnerable.
>
>*If you have a 100GB DB that is full, your backup will be 100GB. No
>compression of backups!
>>>>>>> Valid point here. But I'd rather not trust my backup to a
>>>>>>> compression scheme anyways.
>
>Then you must not be backing up to tape, as all tape drive systems
>use built in compression. The "I don't trust compression" complaint
>is a red herring.
>
>Jared
>
>
>

>>> gsais_at_co.palm-beach.fl.us 04/05/02 07:23PM >>>
OK, timing is impeccable. My boss just got the Oracle Bill, new licensing model $40k per processor for web based apps and flipped. I have some MSCE's working here pushing him to switch to SQL*server. Does anyone know where I can find reasons to stay w/ Oracle? Some things already mentioned here, but the MSCE's would say this list is bias, go figure :)

Does SQLServer 2000 support blobs, row level locking, etc?

Thanks,
Gene
PS. Do I move on to another Oracle shop or switch to SQLserver? OMG, the thought of working only on windoze makes me puke. I know this answer!

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Jay Hostetter
  INET: jhostetter_at_decommunications.com

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).