Difference between Oracle and MS Sql Server

[Neol]

Hi all,

I am new to this world of forums and would like to get assistance in understanding the differences (on dB working aspect) between the 2 RDBMS giants Oracle and MS Sql Server.

I am a Software Testing Engineer and am assigned to a migratory Digital Asset Management Systems project which uses Oracle as it core database server but is coming up with a new version supporting MS Sql Server2000 too.

I would greatly appreciate if someone can help me understand what are the areas which will get affected and how the 2 RDBMS are different in terms of key functionalities.

Anxiously awaiting responses.

Thansk in advance,
Neol

 

[Turkbear]

Well, you have opened a thread that may become immense...
For many folks, including some users of this forum, you question will evoke much debate and claims and counter-claims...
To start the fuss consider the following
(It is long, but perhaps the most complete comparison I've seen )

-----------------------------------------------------
Oracle versus Microsoft Sqlserver 2000
 SQL SERVER TECHNICAL LIMITATIONS
----------------------------------------------------------
    By Faulkner, Kent - USA
    Updated by Havewala, Porus - Australia

 1. Single platform dependancy.

 SQL Server is only operable on the Windows platform, and this is a major
 limitation for it to be an enterprise solution. Oracle is available on
 multiple platforms such as Windows, all flavours of Unix from vendors
 such as Ibm, Sun, Digital, HP, Sequent, etc. and VAX-VMS as well as MVS.
 The multi-platform nature of Oracle makes it a true enterprise solution.

 2. Locking / concurrency

 SQL Server has no multi-version consistency model which means that "writers
 block readers and readers block writers" to ensure data integrity. In
 contrast, with Oracle the rule is "readers dont block writers and writers
 dont block readers". This is possible without compromising data
 integrity because Oracle will dynamically re-create a read-consistent
 image for a reader of any requested data that has been changed but not
 yet committed. In other words, the reader will see the data as it was
before
 the writer began changing it (until the writer commits). SQL Server's
 locking scheme is much simpler (less mature) and will result in a lot
 of delays/waits in a heavy OLTP environment.

 Also, SQL Server will escalate row locks to page level locks when too many
 rows on a page are locked. This locks rows which are uninvolved in any
 updates for no good reason.

 3. Potential of long uncommited transactions HALTING database activity

 In sql server 2K, a long uncommited transaction can stop other
 transactions which queue behind it in the single transaction log, and
 this can stop all activity on the database,

 Whereas in Oracle, if there is a long uncommited transaction, only
 the transaction itself will stop when it runs out of rollback
 space, because of the use of different rollback segments for
 transactions.

 Oracle allocates transactions randomly to any of its multiple
 rollback segments and areas inside that rollback segment.
 When the transaction is committed, that space is released
 for other transactions, however Sql server allocates transactions
 sequentially to its single transaction log, the space
 occupied by commited transactions is not released to
 new transactions until the recycling of the transaction
 log is complete (in a circular round-robbin manner).
 This means if there is an uncommited transaction in the
 middle, and the transaction log cannot grow by increasing
 the file size, no new transactions will be allowed. This
 is the potential of a single uncommited transaction to
 halt database activity.


 4. PERFORMANCE and TUNING

 a. No control of sorting (memory allocation) in Sql Server.
    Oracle can fully control the sort area size and allows it
    to be set by the Dba.

 b. No control over SQL Caching (memory allocation) in Sql Serv.
    This is controllable in Oracle.

 c. No control over storage/space management to prevent fragmentation in
    Sql Serv. All pages (blocks) are always 8k and all extents are always
    8 pages (64k). This means you have no way to specify larger extents
    to ensure contiguous space for large objects. In Oracle, this is
    fully configurable.

 d. No range partioning of large tables and indexes in Sql Server,
    whereas in Oracle a large table (eg. 100 GB or more) can be seamlessly
    partitioned at the database level into range partitions, for eg. an
    invoice table can be partitioned into monthly partitions.

    Such partitioned tables and partitioned indexes give performance
    and maintenance benefits and are transparent to the application.

 e. No Log miner facility in Sql Server. Oracle 8i and 9i supply a
    Log Miner which enables inspection of archived redo logs. This comes
    free with the database. But in the case of Sql Server, external products
    from other companies have to be purchased to do this important Dba task.

 f. A Sql-Server dba claimed that fully qualifying the name of an object
    in Sql Server code would lead to performance gains of 7% to 10%.
    There are no dictionary performance problems like that in Oracle.
    Oracle would have some gains if it fully qualified all names - say
    0.01 percent. This actally shows the difference in the internal
    database technology between Oracle and MS and implies that the
    technology of resolving object names via the dictionary is
    more advanced in the case of Oracle, ie. Oracle seems to better
    access its internal dictionary and resolve names, unlike Sql server.

 5. MISSING OBJECT TYPES IN SQL SERVER
 a. No public or private synonyms
 b. no independent sequences
 c. no packages ie. collection of procedures and functions.

 6. PROGRAMMING

 a. Significant extensions to the ANSI SQL-92 standard in Sql Server,
    which means converting applications to a different database later
    will be a challenge (code re-write).

 b. Sql Server has no inbuilt JAVA database engine as in Oracle.
    In Oracle, Java classes can be loaded and executed in the database
    itself, thus adding the database's security and scalability to
    Java applications.

 c. In Sql Server, stored Procedures are not compiled until
    executed (overhead). In Oracle, packages and procs/functions
    are compiled before execution.

    In Oracle 9i it is also possible to translate Pl/Sql into C code
    and then compile/link the code, which gives very good performance
    gains for numeric itensive operations. SqlServer has no such
    ability.

 d. In Sql server, there is no ability to read/write from external
    files from a stored procedure. Oracle has this ability.

 e. Sql Server uses cryptic commands for database adminstration like:

    exec sp_addrolemember N'db_datareader', N'davidb'
    GO

    This is to add the user davidb to the role db_datareader.

    On the other hand, Oracle uses standard English-like Sql
    commands to do the same:

    grant db_datareader to davidb;

    This one statement does all, in simple English, what the
    cryptic Sql server command does.

 f. Oracle Sql and Pl/Sql are more powerful and can do things more
intuitively
    than Microsoft Transact-Sql. Try to sum up a column by each month, and
show
    the totals for the month, in Sql Server you do it in T-Sql by grouping
    on strings, in Oracle it is possible to do this grouping by the
    trunc(<datecolumn>,'month') function. This method in Oracle is more
    intuitive, it understands the dates, the method in Sql Server does not.

 g. In Sql Server, you cannot issue a &quot;create or replace&quot; for either
    procedures or views, in Oracle you can. This one facility simplifies
    code writing, since in Sql Server the procedure or view must be
    dropped first if present and then recreated ie. 2 commands, in
    Oracle there is no need - a single command &quot;create or replace&quot; is
    enough.

 h. In Oracle, a procedure/function/package/view is marked as invalid
    if a dependant object changes. In Ms Sql there is no concept of an
    invalid procedure, it will run but give unexpected results.
    The former is more suitable for change control and preventing
    unexpected errors.

 i. A recompile reuses the code that is in the Oracle database,
    the actual command is &quot;alter procedure <procedure name> compile&quot;.
    This is applicable to procedures/functions/packages/views.
    This concept of recompiling is not there in MS Sql server
    where you have to resubmit the whole code if you want to
    recompile a procdure.

 j. Triggers in Oracle do not allow transactional control ie.
    commit/rollback/savepoint statements. Whereas, triggers
    in Sql Server allow commits/rollbacks, which is potentially
    dangerous and can cause problems with transactions which
    fire the trigger. Triggers in Sql Server also can start
    transactions of their own which is not very good and
    shows lack of maturity of the language.

 7. STANDBY DIFFERENCES
    Sql Server and Oracle have differences regarding standby databases.
    A standby is a database set up on a second server and to which
    logs are applied ie. all database changes, so that the standby
    can be activated and used in the case of a failover.

  a) In the case of Sql server, when there is a failover, the
    &quot;master&quot; and &quot;msdb&quot; databases have to be restored from backup
    or copied over from the primary to the standby and then the
    standby is activated after all logs are applied. In Oracle,
    there is no need for a restore to be done, the standby can
    be activated at any time after all logs are applied. This
    difference exists because of the fact that in Sql server,
    new users/roles added to the primary are not carried over
    to the standby (these users/roles go in the master/msdb)
    and backups have to be done continuously of the master
    and msdb, these backups are then restored when the
    time comes for a failover. In the case of Oracle,
    users/roles when created in the primary are automatically
    carried over to the standby. So when the failover time
    arrives, all that is needed is to activate the standby.

  b) In the case of Sql Server, if the standby is opened
    as read only, to switch it back to standby again, a
    restore from backup has to be done. In the case of Oracle,
    from 8i onwards, if a standby database is opened as
    read only, it can be reopened as a standby without
    restoring from backup.

  c) The time delay to apply logs between the primary and
    the standby can be varied, but it can never be 0 minutes
    in the case of Sql server. In the case of Oracle,
    in 9i it is possible to have logs applied simultaneously
    to the primary as well as standby, using Sql-Net.
    This means Zero data loss in the case of a failover
    whereas Sql Server's log shipping cannot avoid data loss
    during the time gap.

  d) Sql Server's log shipping mechanism also happens
    at the OS level, whereas Oracle's mechanism can take
    place directly at the Sql-Net level where logs are
    automatically applied to standbys without any
    scripts or OS batch files, this mechanism in
    Oracle is called managed standby.

  e) One deficiency of Oracle in the standby was that
    datafiles, if created on the primary, had to be manually
    created on the standby whereas Sql Server does this
    automatically. However, in 9i, this deficiency is
    fixed and data files are created automatically
    at the standby.

  f) Another deficiency of Oracle in the standby is that
    direct loads, if using the unrecoverable facility to
    bypass redo logging, require the data files of the
    primary database to be manually copied across to the
    standby. This is not fixed in 9i. Sql Server's version
    of log shipping and direct loads do not require this
    copying across.


 8. CLUSTER TECHNOLOGY
    In clustering technology, in the case of Sql Server,
    2 nodes cannot work on the same database, they &quot;share
    nothing&quot;. At the best, to utilize the power of both nodes,
    the application must be manually spit up and redistributed
    between the hosts, working on different sets of data, and
    it is not possible to seamlessly scale upwards by adding
    another node to the cluster in the case of Sql Server.
    Most cluster configurations in Sql Server use the power
    of only 1 node, leaving the other node to take over only
    if there is a problem with the first node.

    In the case of Oracle Parallel server, it is possible to have
    2 or more instances of the database on different nodes acting
    on the SAME data in active-active configurations. Lock management
    is handled by the Oracle Parallel server. With the new version of
    Parallel Server in Oracle 9i, renamed as the Oracle real application
    cluster (9i RAC), there is diskless contention handling of
    read-read, read-write, write-read, and write-write
    contention between the instances. This diskless contention
    handling is called Cache Fusion and it means for the first
    time, any application can be placed in a cluster without
    any changes, and it scales upwards by just adding another
    machine to the cluster.

    Microsoft has nothing like this clustering technology
    of Oracle, which can best be described as &quot;light years ahead&quot;.


 9. REPLICATION DIFFERENCES
    In Microsoft Sql Server's version of simple replication
    ie, publisher-subscriber using transactional replication,
    even if only one table is being replicated, the entire
    transaction log is checked by the log reader agent
    and transactional changes applied to the subscribers.
    In Oracle's simple replication, changes to a single
    table are stored in a snapshot log and copied across,
    there is no need to check all the archive logs.

 10. As of 2002, Oracle has 14 independant security evaluations,
    Microsoft Sql Server has one.

 11. Sql server magazines and internet articles of the magazine
    are only available with paid subscription. Whereas, Oracle
    has given its magazine free for many years, all articles are
    free on the internet, and the Oracle Technical network (OTN)
    is also free on the internet.

 12.Some people say Microsoft Sql Server tools, like Enterprise
    manager, are easy to use. Oracle Enterprise Manager is a huge
    tool and seems daunting to unexperienced people. This is
    true to an extent, however ease of use cannot be compared
    with the many features in Oracle, and its industrial-level
    strength, and its many technical advantages.

 13.TCP Benchmarks:
    March 2002 Benchmarks from Tpc.org show that Oracle 9i
    is seen in the majority of top benchmarks in &quot;non-clustered&quot;
    tpc-c for performance (oltp), whereas Sql Server is seen
    in the majority of entries for &quot;clustered&quot; tpc-c for
    performance (oltp).

    This gives the strange impression that Sql server is
    faster in a cluster than in a non-cluster, which is
    misleading. The fact is that this result is due to the
    use of &quot;federated databases&quot; in clusters by Microsoft
    in which pieces of the application are broken up and
    placed on separate active-active servers, each working
    on separate pieces of the application.

    While excellent for theoretical benchmarks, this is not a
    practical approach in the real life IT world because it
    requires massive changes to any application, and also
    ongoing changes to the application when new servers are
    added to the cluster (each server has a view that sees
    the data in the other servers, adding a new server would
    mean rewriting the views for all tables on all servers)
    and would be rejected by any practical headed manager.

    Using this impractical approach of federated databases
    in clusters, the impression is that Sql-server leads
    in clustered performance, but the practical reality is
    otherwise. This is seen in the way Sql-server is not
    to be seen in the non-clustered benchmarks.

    Also, Oracle leads the way for Tpc benchmarks for
    Decision Support systems with 1000GB and 3000GB sizes
    (Tpc-H by performance per scale), whereas Sql server
    is only seen to a small extent in the 300GB range.

 14. XML Support:

    To provide a more native support for XML, the first release
    of Oracle9i Database introduced the XMLType datatype and
    associated XML specific behavior. In addition, built in
    XML generation and aggregation operators greatly
    increase the throughput of XML processing.

    With the second release of Oracle9i, Oracle significantly
    adds to XML support in the database server. This fully
    absorbs the W3C XML data model into the Oracle
    database, and provides new standard access methods
    for navigating and querying XML - creating a native
    integrated XML database within the Oracle RDBMS.

    The key XDB technologies can be grouped into two major classes - XMLType
    that provides a native XML storage and retrieval capability strongly
    integrated with SQL, and an XML Repository that provides
    foldering, access control, versioning etc. for XML resources.
    The integration of a native XML capability within the database
    brings a number of benefits.

    In summation, Oracle9i Release 2's XDB functionality
    is a high-performance XML storage and retrieval technology
    available with the Oracle9i Release 2 database. It fully
    absorbs the W3C XML data model into the Oracle Database,
    and provides new standard access methods for navigating
    and querying XML. With XDB, you get all the advantages
    of relational database technology and XML technology
    at the same time.

    In contrast to this, Microsoft Sql Server 2000 only has
    limited ways to read and write xml from its tables.



 SUMMARY.
 SQL Server is clearly positioned between MS-ACCESS and ORACLE in terms of
 functionality, performance, and scalability. It makes a work group level
 solution (small number of users with small amount of data), perhaps at
 the departmental level.

 Oracle is much more advanced and has more to offer for larger applications
 with both OLTP and Data Warehouse applications. Its new clustering features
 are ideal for Application service providers (ASPs) on the internet
 who can now start with a cluster of 2 small servers and grow by just
 adding a server when they need to. Besides, Oracle's multi-platform
 capability makes it the most convincing argument for an enterprise.

Footnote:
Oracle is the first commercial Sql database and is 25 years old in 2002,
ie. it has been around since 1977. Larry Ellision the founder of Oracle
has been championing the Sql language before there was any company around
like Microsoft.

------------------------------------------------------------
[b][i]Some views from Industry people:[/b][/i]

The only thing wrong with MS SQL Server is the &quot;MS&quot; in the name. That
denotes
&quot;proprietary&quot;. As a system administrator and DBA with 25 years in IT it's
the industries opinion that &quot;Open Systems Standards&quot; should be the guiding
light when implementing &quot;enterprise&quot; solutions. When &quot;MS&quot; ports &quot;SQL Server&quot;
to UNIX I'll consider it for &quot;mission critical&quot; operations. Until then let
the local &quot;mom and pop&quot; operations have fun with their &quot;toy&quot;. If you're
interested in web enabled databases I suggest you look at IBM's UDB
or Sybase 12.0, or even Oracle. No site getting millions of hits
(et al Yahoo) would consider an NT Server running &quot;MS&quot; SQL Server..


stuart
Consultant, Financial
16-Aug-2000 06:17:18 am



A non-techie perspective
I am a Project Manager who has worked on both SQLserver and Oracle projects
in internet and standard LAN environments. My opinion is based on user
perceptions
of the databases and may have no justification technically, but they are
what
people are saying. Most users don't care what the database is. Often they
will
be a MS or Oracle shop and that's that, you wont change them. So usually
there
is no choice or even comparison of technologies, it's just &quot;we have an
Oracle
licence, use it&quot; and that's it. There is more to speed than the database.
The raw grunt of the database is rarely a problem. You are far more likely
to find speed issues that come from poor database design, crappy data
models,
slow networks, poor application architecture or just plain bad software
design than from the actual speed of the database. When you get to the
really high end, sure it matters, but the time and effort spent changing
from RDBMS to the other to get extra speed (if there is any, and
marginal at best) could be better spent on a gruntier box, you'll
need one next year anyway. Once an MS shop, always an MS shop.
MS has the &quot;integration&quot; of its products down pat. If you implement
an MS solution from end-to-end, likely you will never get out. Their
products are so dependent upon each other that to replace one
component is just too hard. You can build similar implementations
in Oracle by embedding logic in the database, but you don't have to.
Oracle is far more OPEN to different clients and platforms. If you
want an SQLserver system to run at optimal performance, it's
MS end-to-end.

Robert Green, 
Software Developer, Consulting/Systems Integrator



SQL Server is a joke
With all the hype, MSFT still can't keep locks from escalating
and DB reads clean. Until MSFT, SYBS, and IBM do so they will
not be taken seriously in the high OLTP world demanded by the
web. Check the stats, 10 of the top 10 web sites in the world
use Oracle. As far as the TPC benchmarks are concerned, IBM
and MSFT are using smoke and mirrors. It took TWELVE separate
databases to be only twice as fast as ONE Oracle database.
Oracle has been able to use federations of databases (used
by MSFT and IBM) for benchmarks for five years. Oracle could
beat IBM numbers 10 times over using a federation, but won't
because any real DBA would never consider using this useless
configuration in a real situation. Shared disk is the cluster
configuration of every major system on the web, MSFT, IBM and
everybody else can't yet do it so they use Shared nothing,
which is good for just that, NOTHING. If you buy into the hype,
be sure to keep your receipt!

Joe DBA,Software Engineer, Government - Federal

------------------------------------------------------------

Let the flames begin...

 

[Neol]

Thanks a ton Joe! the information you have provided really is of a big help. Sorry could not reply earlier (was on sick leave).

 

[Turkbear]

Hi,
actually only the last paragraph of the 'user comments' was from Joe DBA .. The main article was by
Faulkner, Kent - USA
Updated by Havewala, Porus - Australia

and was submitted to the comp.oracle.databases.server newsgroup..
I got it from there and wanted to share it with this forum..

 

[JavaBill]

That article was well worth the reading. Thanks!

I was also wondering about reports I've heard of MS-SQL Server (and/or MS-Windows) instablility, and that the system had to be re-booted every so often to keep it from crashing unexpectedly. Operationally, this is scary. You would think that with an unstable platform, they would put more effort into fail-over technology. Our HPUX/Oracle servers just run.

 

[Turkbear]

IIRC,the instability was mainly reported for NT versions of Windows in that they tended to 'hang up' due to shared memory usage and caching, so re-booting once a week was sometimes recommended to clear stuff out..I think the 2K and XP Pro versions may have corrected that...We use W2K for many of our Oracle instances without many problems related to the OS, mainly due to the skill sets of our DBAs and Administrators, but we are gradually evolving to a UNIX environment for the servers as soon as the training we need is done...

cheers,