Re: HELP: Oracle 8i vs 9i vs MS SQL 2000

Hi JDA,

backdoor schrieb:
> Could please outline some advantages or disadvantages of "upgrading"
> an existing Oracle 8i RDBMS to the newer 9i or even MS SQL 2000? I am
> employed as a Systems Analyst for a company that has a WAN with around
> 5000 employees, though the number of database users is only around 12
> people in total.
>
> At present, we are using Oracle 8i Client / Server with an HR-Payroll
> Application (NOW! Solutions), and I am wondering if there are any
> compelling reasons to switch from 8i to 9i or MS SQL. Please note that
> I am interested in saving my organization some $$$, but I am also
> concerned about fault tolerance, scalability and vendor support. Any
> suggestions would be greatly appreciated.

If i were you, i would not think of switching to another database system escepially not from Oracle to Microsoft. The underlying concepts of Oracle RDBMS and MS SQL-Server are completely different. IMO, switching from Oracle to SQL-Server is only possible with a complete redesign of the application.

E.g. (for MS SQL-Server 7, i do not know SQL-Server 2000): 1. SQL-Server 7 has only one trigger type (after trigger on table level) instead of four trigger types in Oracle. 2. The locking behaviour is completely different. Simultanous access to rows from different transactions works _MUCH_ better in Oracle. 3. In SQL-Server 7, there are no delete cascades. As there are also no before triggers and no deferred constraints, you only can realize a foreign key constraint with delete cascade with a set of triggers (one for each event type), the declarative way cannot be used. This causes much more work.
4. There are no Sequences in SQL-Server. They use a special option on column level to create a surrogate key. To access the last generated key, you can read out the value from a system variable. In SQL-Server 7 this variable is global at least for the transaction. So if you have a trigger, which fires on insert and which generates a new row in another table using this AUTOINC option, you will get the key generated by the trigger and not the new key in your table. MS claimed to change it in a later version, therefore i don't know, if it's the same in SQL-Server 2000. 5. In our testing, SQL-Server 7 was much slower than every other investigated database system. Compared with Oracle it reached between 30% and 50% of the throughput.

Due to these restrictions i came to the conclusion, that for our project needs SQL-Server 7 was not suitable. In addition, i estimated about 3 - 5 times the code volume compared with Oracle. I assume, that developping and testing an application with SQL-Server 7 would become much more expensive as with Oracle.

-- 
All emails sent to this address are never read and never will be
answered. Sorry, but until someone cleans up the spam mess, that's the
way it has to be.

E-Mails, die direkt an diese Adresse geschickt werden, lese und
beantworte ich nicht. Ich bedauere diesen Umstand sehr, kenne derzeit
aber keine bessere Möglichkeit, um die Spam-Flut abzustellen.

Mit freundlichen Grüßen / Best Regards
Michael Gast
SEPP MED GmbH