Blog dedicated to Oracle Applications (E-Business Suite) Technology; covers Apps Architecture, Administration and third party bolt-ons to Apps

Monday, June 9, 2008

Oracle 11g vs. Microsoft SQL Server 2005/2008

This is a favorite topic. Oracle and Microsoft are always fighting for the top spot in database wars. A few years back it was very simple, if you had mainframes and IBM hardware DB2 was the database, if you had Unix like operating systems, Oracle was the database and if you had Windows operating system, SQL Server was the database. However, over the years, each database has grown in capabilities and the lines are blurred now.

I am going through all the material available on this subject to do a good comparison between the newest version of databases from Microsoft and Oracle. Microsoft's list for SQL Server 2008 is available here:

Feature

Microsoft

Oracle

Resource Governor

Partition-aligned indexed views

PowerShell

Policy-Based Management

Filtered indexes

Advanced sparse columns

Multithreaded partition access

Column-prefix compression

Module signing using certificates

SQL Server Data Services

Resource Governor provides consistent and predictable response times to end users. Organizations can allocate resources and define priorities for different workloads so that concurrent workloads do not interrupt consistent performance to end users. Resource Governor provides SQL Server with several important advantages over Oracle. By specifying minimum CPU and memory usage, you can prioritize workloads in order to guarantee that SLAs (service level agreements) are met for particular workloads in the database. Resource Governor also enables you to limit the amount of memory per resource pool, thereby preventing runaway queries.

Partition-aligned indexed views enable you to create and manage summary aggregates in your relational data warehouse more efficiently and use them in scenarios where you previously could not use them effectively. Partition-aligned views improve query performance. In a typical scenario, a fact table is partitioned by date. Indexed views (or summary aggregates) can be defined on the fact table to help speed up queries. When you switch in a new table partition, the matching partitions of the partition-aligned indexed views defined on the partitioned table switch, too, and do so automatically.

SQL Server PowerShell is a new provider for browsing and managing SQL Server databases, tables, and other database objects. The Windows PowerShell™ command-line interface supports more complex logic than Transact-SQL scripts to allow for more robust administration scripts. You can also use PowerShell scripts to administer other Microsoft server products so that administrators use a common scripting language across servers.

Policy-Based Management is a new system for managing one or more instances of SQL Server 2008 by using SQL Server Management Studio. Use it to create policies to manage entities such as instances of SQL Server, databases, and other SQL Server objects on the database server. It gives database administrators (DBAs) full control of their database servers from an entirely new perspective. It is an easy-to-use and powerful tool for the DBA to use to implement standard configurations in the SQL Server environment.

Filtered indexes enable indexing on a subset of rows in a table and provide numerous benefits. They provide space-saving and performance improvements when you insert or update content.

Filtered indexes can greatly improve data-warehousing performance. For example, you can index only the data for the current month rather than the data for an entire year. You can create more filtered indexes per table to speed up queries. The SQL Server Database Engine Tuning Advisor can recommend filtered indexes for database tables. Filtered indexes provide support for heterogeneous table data in applications such as content management systems (Microsoft Office SharePoint® Server, for example) that have multiple properties for each data type such as a retail product catalog that has entries for books, CDs, and clothes in the same table, but the properties of these items differ. You can create filtered indexes for data according to properties or categories.

Sparse columns efficiently manage empty data in a database because they enable NULL data to consume no physical space. SQL Server 2008 sparse columns can support wide tables that have up to 100,000 columns, whereas Oracle’s current limit is 1,000. Column sets support property-bag scenarios in content management systems such as Office SharePoint Server.

Multithreaded partition access enables SQL Server 2008 to improve query-processing performance on partitioned tables for many parallel plans. Furthermore, multithreaded partition access changes the way in which parallel and serial plans are represented, and enhances the partitioning information that is provided in both compile-time and run-time execution plans.

Column-prefix compression is part of the SQL Server 2008 advanced page compression techniques (dictionary-page compression and column-prefix compression). With column-prefix compression, SQL Server looks for a common byte pattern at the beginning of a column across all rows on the page. If it finds at least two instances of columns that have a common byte pattern, it stores that byte pattern once on the page and refers to this byte pattern from the respective columns.

Module signing using certificates gives SQL Server the ability to sign modules such as stored procedures, functions, triggers, or assemblies, within a databases. This means that you can temporarily elevate privileges without switching the user context. In addition, it is not possible to tamper with or modify the certificate (otherwise it is invalidated).

SQL Server Data Services is a highly scalable, cost-effective, on-demand data storage and query-processing Web service. It is built on robust SQL Server technologies and helps guarantee a business-ready service level agreement that covers high-availability, performance, and security features. SQL Server Data Services is accessible by using standards-based protocols such as SOAP and REST for quick provisioning of on-demand data-driven and mashup applications. Businesses can store and access all types of data from origination to archival by using SQL Server Data Services. Users can access information on any device, from desktop computers to mobile devices.

I found some new white papers from Oracle where they have compared specific features between 11g and SQL Server 2008:

Technical Comparison of Oracle Database 11g versus SQL Server 2008: Focus on Replication

Technical Comparison of Oracle Database 11g vs. Microsoft SQL Server 2008: Focus on High Availability

Oracle RAC 11g, Technical Comparison with Microsoft SQL Server 2008

Here's Oracle's list:

Focus on Management Automation
Oracle Enterprise Manager and SQL Server Management Studio for SQL Server
are the tools provided by Oracle and Microsoft respectively, for the management
and maintenance of the database systems. With the potential shortage of skilled
workers, it is critical that the tools used to manage database systems provide the
level of automated self-management that will enable IT to confidently manage
more systems with fewer resources.

Oracle Enterprise Manager follows the management-by-exception model, where
DBA intervention is only needed when an exception condition occurs or is about
to occur. Oracle Database 11g provides a built-in, push-based alerting mechanism
that is extremely efficient and accurate. Oracle highlights problem areas, such as
performance issues, so that potential problems can be identified and often dealt
with before the problem negatively impacts end users. Oracle Database’s servergenerated
alerts, along with Enterprise Manager’s sophisticated event notification
system, provide the foundation for a complete performance and proactive database
monitoring solution.

This differs significantly from SQL Server 2005 where DBAs themselves have to
monitor the system for potential problems. For example, if a SQL statement is
taking an inordinately long time, Oracle Database would proactively take actions to
identify, diagnose and resolve performance problems, whereas with SQL Server, a
DBA would have to first turn on monitor counters on the database, which would
negatively impact performance, then rerun the workload, if possible, and then
manually analyze the trace files to determine the offending SQL statement. Only
then can the DBA know if there are any problem SQL statements impacting
system performance. A time-consuming task for IT groups that may be managing
more databases-per-person, and larger databases, than were managed in the past.
With the potential shortage of skilled workers, it is critical that the tools used to manage database systems provide the level of automated self-management that will enable IT to confidently manage more systems with fewer resources.


As companies look to automate database management, they will require
administration tools that enable their IT personnel to be more productive while
also helping them to reduce management costs -- without compromising service
level objectives. Oracle addresses this problem with Oracle Database 11g and
Oracle Enterprise Manager, a sophisticated self-managing database that
automatically monitors, adapts and fixes itself. SQL Server’s management, on the
other hand, requires administrators to invest considerably more time for common
administrative tasks and is fundamentally reactive as opposed to proactive. For
companies seeking manageability solutions that will keep them ahead of the curve,
Oracle Database 11g with Oracle Enterprise Manager is the clear choice.

Focus on Data Security

A key component to database security is database encryption. Oracle first
introduced a built-in API for database encryption in the late 1990s with the
introduction of Oracle8i. Oracle was the first database vendor to introduce an
encryption API and Microsoft later followed with its first encryption API in SQL
Server 2005.

Oracle further addressed the business challenges around encryption in Oracle
Database 10g Release 2 with the addition of Transparent Data Encryption (TDE)
to the Oracle Advanced Security Option. Oracle Advanced Security TDE column
encryption is the industry's most advanced encryption solution that transparently
encrypts data so that applications continue working without change. Oracle
Advanced Security 11g TDE added full tablespace encryption, providing the ability
to transparently encrypt an entire application table. Oracle Advanced Security
TDE 11g column encryption can also encrypt Oracle SecureFiles for unstructured
data and Oracle Advanced Security TDE 11g integrates with external hardware
security modules (HSM) for centralized creation, storage and management of the
Oracle Advanced Security TDE master key.

SQL Server 2005 does not provide Transparent Data Encryption and instead
utilizes encrypt and decrypt Transact-SQL functions to manage the encryption and
decryption of data stored in the database. Microsoft SQL Server 2005, unlike
Oracle Database 11g, places the responsibility for implementing data encryption
not with the database engine, where it can be applied transparently to data without
requiring any application changes, but instead with individual application
developers and within individual applications. In a time of tightened budgets and
with IT groups expected to manage more database systems than ever before with
the same or fewer resources, Transparent Data Encryption not only helps to keep
sensitive data out of the hands of malicious users, but it does so without requiring
any additional IT or application development resources.

It should also be noted that in their SQL Server Technical Article titled Protect
Sensitive Data Using Encryption in SQL Server 2005, Microsoft indicates that
encryption is a CPU-intensive operation and warns that users wouldn’t want to
implement encryption on a database of any “serious size.”

In a time of tightened budgets and with IT expected to manage more database systems than ever before with the same or fewer resources, Transparent Data Encryption not only helps to keep sensitive data out of the hands of malicious users, but it does so without requiring any
additional IT or application development resources.

Focus on High Availability
Oracle’s Real Application Clusters (RAC) option supports the transparent
deployment of a single database across a cluster of servers, providing fault
tolerance from hardware failures or planned outages. Oracle RAC provides very
high availability for applications by removing the single point of failure with a
single server. If a node in the cluster fails, the Oracle Database continues running
on the remaining nodes. No single node is responsible for any specific portion of
the data, so losing a node with Oracle RAC does not make any of the data
inaccessible. There is also no need to shutdown applications during maintenance
on nodes since individual nodes can be shutdown while application users continue
to work.

The inherent ability of an Oracle RAC database to continue running on the
remaining nodes, even if a node in the cluster fails, is also a key differentiator
between Oracle Database and SQL Server 2005. SQL Server 2005 has no
equivalent to the high-availability capabilities of Oracle RAC. Microsoft may point
towards Microsoft Cluster Server (MSCS) as a way for SQL Server 2005 to provide
a slightly enhanced level of availability. But in reality, in a MSCS configuration, the
database resides on a disk that is connected to both an active and passive node.
The active node can only perform processing and the passive node is provided as a
backup in case the active node fails. In the case of a failure, the switch to the
passive node is not transparent and modifications must be made to the software
and node before applications can re-connect. This can potentially result in
noticeable application downtime since IT intervention may be required and,
depending on when the problem occurs, such intervention may not be immediately
available.

High availability is no longer just a nice-to-have, it is clearly a business imperative
for those businesses that, along with their customers, expect that the applications
they rely upon will be available whenever there is a business need -- no matter the
day or time. Fast Application Notification (FAN) is a feature of Oracle RAC that
enables the automated recovery of applications if a cluster node fails; compare this
to the manual process, and possible delays, MSCS users face as IT races to restart
SQL Server on the surviving node should a running node fail.

Oracle RAC is an option to Oracle Database 11g Enterprise Edition and is
included with Oracle Database 11g Standard Edition at no extra charge. Oracle
RAC supports mainstream business applications of all kinds, including popular
packaged products such as SAP, PeopleSoft, Siebel, and Oracle E*Business Suite,
as well as custom applications.

The inherent ability of an Oracle RAC
database to continue running on the
remaining nodes, even if a node in the
cluster fails, is also a key differentiator
between Oracle Database and SQL Server
2005. SQL Server 2005 has no equivalent to
the high-availability capabilities of Oracle
RAC.

Staying Ahead of the Curve: Oracle Database 11g vs. Microsoft SQL Server 2005 Page 6

Focus on Risk Reduction
System changes such as software and hardware upgrades, patches and application
development changes, including query execution, are essential in order for a
business to protect its competitive edge, as well as for regulatory and security
compliance. Yet all these changes pose varying degrees of risk to production
systems and cause most companies to perform extensive testing and validation in
an effort to determine the impact these changes will have before deploying the
changes to production systems.

But often, despite their best efforts, many companies find that their various
simulation tools and test environments failed to detect one or more issues that,
once deployed into their production systems, were then experienced by end-users,
often in the form of slower performance, system instability or availability
problems. The main reason for the failure to detect potential issues before the
changes were deployed to the production systems is the inability of existing tools
to perform testing using real production workloads -- many tools simply use
“sample” data and “simulated” workloads.

Oracle Real Application Testing with Oracle Database 11g enables businesses to
quickly adopt new technologies while eliminating the risks associated with change.
Real Application Testing combines a workload capture and replay feature with an
SQL performance analyzer to help test changes against real-life workloads, then
fine-tune the changes before putting them into production.

SQL Server 2005 does not provide an equivalent to Oracle’s Real Application
Testing. Instead, it’s business as usual for companies when it comes to evaluating
the impact of software and hardware upgrades, patches and application
development changes prior to deploying the changes to production systems.
Unlike SQL Server users who remain at the mercy of their existing simulation tools
and test environments in the hopes they are able to detect issues before their endusers,
Oracle Database 11g users can evaluate changes with a higher degree of
confidence using real production workloads to fully understand the impact of the
changes -- reducing production deployment risks and lower testing costs.
With the amount of technological changes already occurring and the potential
impact these changes can have on both a company’s competitiveness and the
stability of its production information management systems, Oracle’s Real
Application Testing enables corrective action to be taken proactively and keeps the
changes from becoming the problem of the end-users.

Focus on Scalability and Performance
Oracle RAC running on clusters provides the highest level of availability and
scalability, enabling low-cost computing for any size company. Oracle Real
Application Clusters provides flexibility for scaling applications up by allowing the
addition of hardware processors and memory to increase overall system
Oracle Real Application Testing
with Oracle Database 11g enables
businesses to quickly adopt new
technologies while eliminating the risks
associated with change.
performance and scalability, and for scaling out by distributing the computing and
data workload among multiple servers using load balancing.
To keep costs low, clusters can be built from standardized, commodity-priced
processing, storage, and network components. When users need more processing
power, they simply add another server without taking applications offline to gain
horizontal scalability. This allows service levels to be easily and efficiently
maintained while lowering Total Cost of Ownership (TCO). Oracle RAC provides
dynamic distribution of workload, transparent protection against system failures
and supports mainstream business applications of all kinds across server platforms
including Unix, Windows and Linux.
SQL Server 2005 is only available on Windows and has no equivalent to the scaleout
capabilities provided by Oracle RAC. Microsoft provides two somewhat
limited options for scalability: companies can exploit a single SMP server, or they
can create a Federated Database. However, SQL Server cannot scale beyond a
single SMP server; if an application reaches the processing limits of the single SMP
server, then SQL Server has scaled as far as it can go. Once this limitation has been
reached, users have no recourse, as they cannot simply add more nodes as is
possible with Oracle RAC. Instead, the only option available to increase scalability
is to buy a bigger SMP server and migrate data and applications to the new server.
The other limited scalability option is a Federated Database. Federated Databases
are composed of independent databases and an application’s data is spread across
multiple servers. If set up and managed properly, a Federated Database
configuration can provide some scale-up benefits for SQL Server users, but
performance and scalability problems are also a distinct possibility. This is because
within a Federated Database configuration, SQL Server only allows the node that
owns the data to read the data. This places a burden on the DBA to be very careful
on how they partition the data to avoid a “hot” node. A hot node can lead to
performance and scalability issues if the node becomes a bottleneck and restricts
processing throughput. Even if the DBA manages to partition the data perfectly
and is able to spread the workload out across the nodes, over time, as data is added
and changed, what started out as a perfectly distributed system could end up as an
unbalanced system with hot nodes.

With the need for additional scalability being unpredictable by nature due to
seasonal processing demands, and with the explosive growth of data and
applications due to the dynamic nature of the global economy and other business
drivers such as mergers and acquisitions, it is critical that companies be flexible
enough to accommodate increased processing loads but also be able to reallocate
excess resources that are needed elsewhere – while still keeping an eye on their IT
budget. Forklift hardware upgrades of maxed-out SMP servers, and the added cost
of re-partitioning application data to achieve limited scalability gains, don’t provide
the flexibility companies need to be able to respond quickly and cost-effectively to
constantly changing processing demands.
Oracle RAC provides dynamic distribution
of workload, transparent protection against
system failures and supports mainstream
business applications of all kinds across
server platforms including Unix, Windows
and Linux.

Staying Ahead of the Curve with Oracle and .NET
The flexibility and ability to adapt to change that is found in Oracle Database 11g
can also benefit developers working with .NET. Oracle's .NET features provide
access to unique Oracle Database functionality that is not found in SQL Server,
making .NET a more powerful application platform. Unique features include
access to Oracle performance and tuning capabilities including: client result cache,
configurable result set data retrieval, array parameters, REF Cursors, configurable
LOB and SecureFiles retrieval, and more robust database change notification
(DCN).

SQL Server provides notifications at the row-level only for DCN. Oracle, on the
other hand, can support object level notifications in addition to row-level. In
addition, Oracle DCN has the ability to support any type of join and any type of
view, except for materialized views and fixed views. SQL Server doesn't support
views, outer joins or sub-joins. Oracle DCN registrations can persist in the
database server after the first change, while SQL Server will force you to re-register
the registration in the database after each change.
In terms of high availably functionality, Oracle provides Oracle RAC and Oracle
Data Guard aware .NET connection pools – which enable automatic clean up of
"dead" connections and automatic connection load balancing across nodes. For
security and auditing, Oracle enables end-to-end tracing capabilities from .NET
applications, enabling easier use of security features such as Virtual Private
Database (VPD) and Label Security.

Oracle imposes no limits in accessing the latest Microsoft .NET features. Oracle
supports the latest version of Microsoft's .NET development environment, Visual
Studio 2008, and .NET runtime, .NET Framework 3.5. The tools are natively
integrated with Visual Studio Server Explorer via Oracle Developer Tools for
Visual Studio. Oracle Data Provider for .NET (ODP.NET) provides data access
to the Oracle Database. The Oracle Providers for ASP.NET allow application
state to be stored/modified/retrieved transparently inside the Oracle Database
from any .NET web application using standard ASP.NET controls and services.
Lastly, Oracle provides the ability to deploy and run .NET stored procedures
within the Oracle Database.

ORACLE'S CONCLUSION
Automated management, security, high availability, risk reduction and scalability
are just a few of the areas where SQL Server has been slow to adapt to real world
data management needs. Yet the demand for a flexible database management
solution will continue to grow as IT departments struggle to cope with managing a
growing information infrastructure with fewer resources.
Simply providing a database management solution that is “good enough” won’t be
acceptable in a growing, dynamic environment where administrators who
previously managed a handful of databases are now responsible for hundreds,

Oracle's .NET features provide access to
unique Oracle Database functionality that is
not found in SQL Server, making .NET a
more powerful application platform.
Staying Ahead of the Curve: Oracle Database 11g vs. Microsoft SQL Server 2005 Page 9
possibly thousands of databases. IT personnel don’t have the time to manually
monitor performance and perform analysis of each and every performance issue,
they don’t want high-availability to be dependent on a number of manual
operations and they don’t want the cost and resource burden of having to change
applications any time a new node is added to the database configuration for
scalability reasons. And as hardware and software changes are rolled out across
production information management systems throughout their global enterprise,
they want to be assured that their end-users won’t be the first ones to find an issue.
Oracle has lead the way with numerous innovations that are intended to keep
Oracle customers ahead of the curve and to enable them to respond to whatever
changes may occur.
• Oracle Database’s focus on innovation has helped ensure that data encryption
is performed transparently by the database server without requiring application
changes and without placing any burden on developers.
• Oracle innovation also ensures that the database server helps identify the rootcause,
and resolve problems automatically, instead of forcing DBAs to spend
valuable time manually diagnosing problems.
• Oracle Database 11g leads the way in terms of taking the uncertainty out of
hardware and software changes, and helping to ensure that end-users don’t
become unexpected system testers as changes are rolled out across the
enterprise.
• And finally, Oracle innovation guarantees that scalability doesn’t come by the
way of a forklift and weekends aren’t spent migrating data and applications to
larger SMP servers. Instead, Oracle RAC ensures that scaling out is as simple
as adding more low-cost commodity servers, without any application changes.
Oracle understands that simply being a “good enough” database management
solution doesn’t help you in the long run and doesn’t help solve serious business
problems. If you don't want to be left behind, you need to standardize on a proven
platform that enables you to both anticipate change and stay ahead of the curve.

Here's some stuff from Gartner about 11g:

Oracle Database 11g builds on the features in Oracle Database 10g, adding a long list of capabilities to enhance manageability and performance. Some of the highlights include:

  • Greater functionality and manageability in Automatic Storage Management (ASM), especially when used in conjunction with Real Application Clusters (RAC)
  • New partitioning techniques specifically for enhanced data warehouse performance, including Partition Advisor to suggest which type of partitioning to use
  • Cost-effective information life cycle management and storage management with new data partitioning and compression (Oracle Advanced Compression)
  • The ability to perform online upgrades and patches
  • The ability to run queries on data from selected tables "as of" a point in the past (Oracle Total Recall) without the hassles of data archiving
  • Enhancements to Oracle Data Guard, including real-time query capability on the standby server to improve the performance of production systems and provide more cost-effective disaster recovery solutions
  • Oracle Real Application Testing, which combines Database Replay (the workload capture and replay feature) with SQL Performance Analyzer to allow the testing of application changes against production workloads and the tuning of SQL before moving the application into a production environment

This new release will increase performance across all types of applications:

  • Data warehouses will benefit from new types of partitioning and Partition Advisor, OLAP enhancements, continuous query notification and advanced SQL tuning capabilities.
  • RAC customers will benefit from Automatic Database Diagnostics Monitor (ADDM), the automated tuning function that now runs on and tunes RAC clusters.
  • All customers will benefit from the enhanced manageability features, especially the testing of applications using Database Replay and online patching and upgrading.
  • Database administrators (DBAs) will benefit from many of these functions, which will eliminate the need to find and analyze necessary tuning information; now DBAs need only decide whether to accept the recommendations from the DBMS.

Oracle will likely see increased revenue from sales of optional features and additional licenses as the size of the databases grows. In addition to binary XML storage, Oracle FastFiles allows customers to store all types of large object data, including multimedia in the database, managed by the DBMS. Because of the new functionality and features, we expect between 15% and 20% of the Oracle customer base to begin using Oracle Database 11g during the initial 12 months. Due to this higher-than-normal early adoption rate, we expect many software vendors to certify Oracle Database 11g early, accelerating in 2008.


Recommendations

Oracle customers:

  • If you are in need of the new features and willing to accept some degree of risk, begin to evaluate Oracle Database 11g when it becomes generally available. The risk vs. reward may be worthwhile for many of these features.
  • The average Oracle customer should wait about 12 months before evaluation and testing, allowing the early adopters to "work out the kinks" associated with new releases.

Prospective customers:
  • Through mid-2008, consider Oracle Database 10g Release 2 for your initial implementation.

25 comments:

RichPhan said...

I find it interesting that Oracle compared 11g with SQL Server 2005 and not with SQL Server 2008.

Anonymous said...

Wonder when the Oracle comparison is published. Yet, most certainly a number of the features mentioned in the Oracle comparison are now available in SQL Server 2008, and further added with more options.

With the pace SQL Server is going, nothing stop SQL Server having all features available in Oracle, and beyond, staying ahead of technology.

The interesting findings is that Oracle have been ahead of SQL Server specifically in the area of database management. However, the TCA and TCO have always been higher than SQL. Interesting?

Anonymous said...

I would just say this -- I don't know if I like Oracle or SQL Server better than the other, but I don't like spotty blogging and reporting. I look at Micrsoft
s paper and it's got some misleading marketing.

At the same time, this Oracle response here has lots of issues. For example, telling readers that RAC let's you keep on running is just non-factual; you have to remaster the blocks and peform the undo and that's a TOTAL database outage; perhaps short (depending on the cluster). Sure RAC is free with Standard Edition (who just got about a 20% price increase last week like most Oracle productS), but it's limited to 4 sockets for THE ENTIRE CLUSTER -- so that is paying for a 4-way licensed product and you can only run it on a 2-way for a balanced failover scenario (or 4 1-ways I guess).

RAC is a decent HA solution, but in no way a scalability solultion. Ask Oracle why are they benchmarking on SMP SuperDomes ALL the time except in rare cicurmstance. RAC will scale with application change -- anything else is usually almost always cooked. Outgrowing on SMP? for OLTP, it just isn't going to happen to be honest and Don Burelson (the world's most popular ORACLE expert tends to support that notion except for the most extreme applications). For BI I agree -- you want scale-out -- but Oracle MUST use an additionally charged partitioning feature to get parallelism out of RAC for the most part (performance I should say). Then ask yourself, why is Oracle benchmarking on SMP for high end BI like 10 TB and 30 TB. Why when Oracle ran a RAC 10 TB result it went slower and cost more as you scaled it.

Finaly thing, 1/2 of the things mentioned in this article are extra cost options (ya, the sames ones that just had a price increase). So you want to tune your SQL, you want to monitor that stuff proactively (for the most part) and run diagnostics on it, you want to do database replay -- that's a lot of dollars.

I'm not trying to beat people up - but one would hope as we follow the lives of smart people (which the write of this blog is) we would ensure we don't get corrupted by those marketing people that obsfucate the world.

Rick said...

Nothing like an evaluation done by someone who obviously doesn't know Oracle's capabilities.

Anonymous said...

Mr. Vikram,

Your comparison says something else then your title. Its like comparing apple with orange. Thats not fair.

Aasim

Vikram Das said...

Hi Anonymous,

Both Oracle 11g and MS SQL Server 2005 and 2008 are Relational Database Management Systems. So I don't understand how you can call this a comparison between apples and oranges. It is true that I have not created a pretty table with 3 columns of Feature, Oracle 11g, SQL Server. Then check what is present in Oracle and SQL Server. That kind of thing is a lot of work. I leave that to Microsoft and Oracle. My intention of writing this post was to bring in one place whatever material I could find about comparison of these two RDBMS.

- Vikram

JAW said...

In Oracle the Resource Governer (SQL Server and DB2 terminology)is called the Resource Manager and Oracle also has an older technology in PROFILES that can also perform some governing.


http://multi-platformdba.blogspot.com

Anonymous said...

either change the title of this comparison to SQL Server 2005, or update it so it actually compares to SQL Server 2008, because right now the title is misleading with the content that's provided

Anonymous said...

This comparision is all scrap and meaningless!!

Vikram Das said...

Hi Anonymous,

This information is obtained from Microsoft and Oracle sources. If you don't like it, don't read it.

- Vikram

Anonymous said...

You need to remove "SQl Server 2008" from your heading. This is a comparison with the newest oracle released to an older SQL server released.

Vikram Das said...

Hi Anonymous,

If you go through the post, you'll notice that the Microsoft table is for SQL Server 2008. The features presented are from SQL Server 2008. Its true that Oracle's report is a comparison between SQL Server 2005 and 11g, but I have started the article with the description of SQL Server 2008 features. I have changed the text to make that obvious.

- Vikram

CC said...

I only have a known question... the oracle developers always say "oracle is better, oracle can not compare to SQL Server" but.. why?, I don't know any issue that you can do with oracle that you can't with SQL Server, in a big scope, for example, I don't care if the SQL Server pages are 8k size or if I can customize it, in fact I don't need to to this... the point is I can do ANYTHING I need in a DBMS with SQL Server, it can handle terabytes of data, it has a good and robust security management... so, what's the real difference??... It's like talking about .NET vs java, I don't know the real differences either!!

Vikram Das said...

Hi CC,

While it is true that SQL Server is good for medium sized applications, it has major challenges when your application scales up. When the number of users increase, SQL Server can not cope up because of its simplistic locking architecture. Readers block writers in SQL Server. Readers never block writers in Oracle. This is my own experience with SQL Server.

Oracle Database’s implementation of multi-version read consistency always provides consistent and accurate results. When an update occurs in a transaction, the original data values are recorded in the database's undo records. Oracle uses the current information in the undo records to construct a read-consistent view of a table's data, and to ensure that a version of the information, consistent at the beginning of the uncommitted transaction, can always be returned to any user.

The default isolation model for SQL Server 2005 uses shared read locks for read operations (READ COMMITTED with locking). Shared locks prevent data that is read from being changed by concurrent transactions.
This implementation restricts the ability of the system to properly service concurrent requests in environments involving a mix of reads and writes. Moreover, it increases the number of locks held by applications and thus, increases the likelihood for the system to perform lock escalation and to further reduce concurrency, with more potential deadlock situations2. This is why SQL Server 2005 introduces two new isolation levels:


1.read committed with snapshots (statement-level read consistency)
2. snapshot isolation (transaction-level read consistency)
These isolation levels correspond to Oracle’s READ COMMITTED and SERIALIZABLE isolation levels, respectively.
In these isolation levels, readers do not block other readers or writers accessing the same data. Similarly, the writers do not block readers.
Snapshot isolation level is based on row versioning, which supports read consistency by following a linked chain of versions containing committed rows of data. This linked chain is placed in a separate version store located in tempdb4.
SQL Server’s snapshot isolation does not represent any innovation: Oracle has supported multi-version read consistency for several releases, where it is the default behavior. Moreover, with SQL Server:
1. Administrators have to explicitly establish snapshot isolation at the database level to enable read-committed with snapshot or snapshot isolation.
2. Existing SQL Server applications have to be modified to take advantage of this mode, just as SQL server applications have to be modified to run on Oracle because there are fundamental differences in the way locking mechanisms are implemented. An application designed to run using read locks has to be somewhat re-designed and re-written to run under multi-version read consistency.

- Vikram

Anonymous said...

I don't see how you call a DBMS "enterprise" when it only runs on Windows.

CC said...

Well, the bottom line is... Oracle born robust, powerful and other good things... but SQL Sever is taking the right way to reach ambitious goals, the market points to oracle as the winner of all times, but soon (and with sql 2008 I can be sure) Microsoft will be a very competitive opponent, I'm glad of we can discuss these issues about 2 products that sometime they were incomparable, we have more choices besides their creators, the DBMS market is growing and offering better solutions, I think that's the key point.

Anonymous said...

Oracle's implementation of isolation levels is proprietary and non-ANSI standard. It allows non-repeatable reads (reads are not repeatable). Popular with applications looking to build on Oracle, like pThe mechanism used to employ this is the redo log or rollback segment - which has its own problems (good ole "ORA-1555" error). It's an extra burdon to overcome this isolation level violation , and can produce inconsistent results - the very thing a database isn't supposed to do (the "C" in "ACID") when priocessing transactions.

RAC is better at scaling and SQL clustering/MSCS is better at HA. For all practicle purposes, most capacity planning efforts are successful in predicting resource needs, so scalability is generally less of an issue in prod; what you're after is HA, so that leans twd SS.

Oracle is still using "shared-disk" clustering, while SS can employ "shared-nothing".

Filtered indexes are a great feature in SS (no available in Oracle).

As far as it not being an "enterprise" RDBMS because it only runs on Windows, that's an indication of it's hig volume, scalability and transaction processing workload; being able to run on multiple platforms is discussing its portability. This is a conscious decision by MS; the market just isn't there for Linux/Apple, and soon, UNIX will also go the way of the Dodo. Simple marketing/survival and focus.

Anonymous said...

Oracle doesn't support COMIT/ROLLBACK within triggers. I can't say I disagree with this, as any updating DML that triggered the SP to begin with - along with any updating DML batch exec'd in a trigger - should all be considered part of the same batch (atomicity). Although a conceptual no-no and ACID-counteracting practice, triggering and triggered updating DML could differ semantically though, just were strung together because of bad pgming practices; even so, Oracle shouldn't assume this rule for the user and force this limitation.

The Oracle work-around is to use "autonomous transactions", to comit/roll back DML separetly from an outer transactions (essentially nesting trxs). This is more non-standard behavior. The problem with these is that if the outer transaction decides to roll back after any inner/autonomous ones comit, the entire batch orchestrated by the governing call/outer transaction is no longer atomic/has lost control over what was happening nested w/in it (outer call no longer ACID as some stuff in and within it behaved differently). The database is now (at least business-wise) in an inconsistent state.

SQL Server is smart enough to recognize when a transaction block is atomic or nested. Nested/inner rollbacks are ignored if within an outer transaction. This way, all SPs containing explicit transactions behave as expected whether they stand alone (in which case their own indiv COMITs/ROLLBACKs are upheld), or whether they're part of a larger orchestration (where their own COMITs/ROLLBACKs are ignored, allowing the outer/calling SP/transaction to maintain total atomicity/control over what's being commited/rolled back in their midst).

Engr. Clement said...

What I see here on this issue of comparing is that every DBA will always want to defend its vendor. Sql Server and Oracle are good enterprise database that can handle interested users at interest and knoledge level. Also, the person above that said he doesn't see any thing specific about sql server as enterprise when it it limited to os is to me a small experienced mind.
I also want to use this meduim to appreciate the interest and knowledge of the site developers. U are trying and keep encourage. Engr. Clement. Nigeria

Anonymous said...

You can compare both only if you have years of experience with both and have done real world projects with both too and realise that the big difference is on the capacity to deliver whatever the case and load.
On that side, Oracle DB is an enabler, you don't have to fight with it, your issues are mostly on the design side and dev side not on the technology side if you see what I mean.
PL/SQL has been one of the great pillar of Oracle DB at all levels and its robustness and consistency since +20 years has guaranteed protection of your DB development.
Never been able to experience that with SQL Server and won't bet on it today to protect any dev investment for the next 10 years. Sorry MS DBAs.

Kensai Yanesha said...

Well, even after almost 2 years after you published this article, I still found it useful in a way of taking look at basic concepts differences between these 2 similar RDBMS technologies (even though if it is totally different inside from the architecture point of view). Thanks.

Anonymous said...

Yes MSSQL is a good SGBD.
But it only runs on Windows O/S.

And Windows O/S in a Production-Environment is not "industry-able" as linux or unix servers.

Anonymous said...

Gotta love misinformed bloggers. Anyone can take vendor marketing content and paste it in a table. Until you've used a platform end to end, you shouldn't be writing a comparison post side by side because your ignorance shows up quickly to anyone who knows the topic you're trying to write about. You've discredited the other topics you may actually know and have lost a reader. Nice work copy/paster.

Anonymous said...

Hi,

You missed most of the Oracle good stuff. What about Oracle RAC? Is there any equivalent in SQL Server side.

jeffery said...

I use windows and linux servers at work. I use fog on linux and tangle firewall on linux at work. Oracle tends to be more suited for linux but when tiered as a database server microsoft sql server works better especially 2010 version. I do not think this article applys to sql server 2010. I could do crazy shit on windows server that could make oracle guys wish they were never born. Iam very good at bending software to my will on windows.

Real points to consider:
1. Most people are dumb and would rather use windows because they are used to it (most people here are IT who probably do not work in the field with real people, but in a cubicle, I DO).
2. Linux's fog program I consider to be top notch open source if i ever saw it. I like it because its easier to setup then SQL imaging solutions and supports microsoft imaging solutions. They have an internal mysql database.
3. I hear more use of mysql or SQLLite by peoople wanting power.
4. I have taken Java already so I can argue so points with oracle DBAs. In addition, Iam writing a research paper on dialect differences right now between oracle, SQL server, MySQL, and sybase. So i can post inimate command differences and feature differences when I get done.
5. Linux has flaws too: since its open source the kernel code is known and open to exploitation (security risk if you have experts hacking into your system).