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.