Oracle database 11g and Microsoft SQL Server 2008

Introduction

In this article, I want to cover the new features in Oracle 11g and SQL Server 2008 database systems which are two important players always fighting for the top spot in this important market segment of the IT industry. By analyzing these two giants, we can see the trends in the database systems. It's remarkable to say that I will analyze these database systems from the developer perspective.

Getting started with the analysis

As everyone knows, Microsoft SQL Server has been winning a good market share on the database management systems segment in the recent years.

According to the TPC (http://www.tpc.org) which is a non-profit corporation founded to define transaction processing and database benchmarks and to disseminate objective, verifiable TPC performance data to the industry; SQL Server 2008 has the most accepted benchmark test on performance and price/performance in the industry (the TPC-E y TPC-H tests). Important clients such as Unilever, Citi, Barclays Capital y Mediterranean Shipping are using SQL Server 2008 for their critical mission applications. In the other side, Oracle 11g has a better position in the TPC-C tests.

In the scalability area, SQL Server 2008 provides several improvements to support 64-bit systems with up to 8 Terabytes of memory.

SQL Server 2008 and Oracle 11g both enable the installation of several passive instances in inexpensive servers to provide high availability. Resource Governor, a new admin console in SQL Server 2008, allows the database administrator to set limits (specify a minimum CPU, RAM memory) and assign priorities on the workload, for example, when different instances coincide in a concurrent way, then they don't need to interrupt each other. DBA can now manage unpredictable workloads in a very easy way. This feature enables consistent response times to end users.

It's very important to say that Oracle 11g database offers similar admin consoles since quite some time. And in a recent comparative management cost study, Edison Group analysts found that Oracle Database 11g holds a big advantage over Microsoft SQL Server 2008 when it comes to features that help database administrators perform everyday administrative tasks. You can read more about this report at http://www.oracle.com/goto/edison_db_report/index.html?evite=NAMK03070008MPP016 .

SQL Server 2008 includes the Performance Studio which is an integrated environment to gather, store and analyze diagnostic data to facilitate the problem solving process on unexpected incidences.

The Analysis Services engine in SQL Server 2008 now includes improvements such as block computation and MOLAP partition's writeback.

In the security area, it's remarkable to say that SQL Server 2008 is the most robust platform in the field of database management systems. SQL Server 2008 provides the built-in TDE (Transparent Data Encryption) service to cipher and de-cipher data without coding anything in the application. This feature is also included in Oracle although with an additional cost of $10,000.00 per processor. Additionally, SQL Server 2008 supports EKM (Extensible Key Management) and HSM (Hardware Security Modules) which enables third parties to register their own modules on SQL Server in order to provide key management independently of the database system engine.

Policy-based management in SQL Server 2008 is a new system for managing one or more instances by using SQL Server Management Studio.

Now from the developer perspective, we have a lot of good features in SQL Server 2008 such as data compression, partitioned table parallelism, partition-aligned indexed views, PowerShell, filtered indexes, advanced sparse columns, multithread partition access, column-prefix compression, module signing using certificates and SQL Server data services.

Data compression reduces the amount of storage space needed to store the tables and indexes. With this technique, SQL Server looks for a common byte pattern at the beginning of the column across all the rows on the page. If it finds at least two columns with a common byte pattern, then it stores that byte pattern once on the page and refers to this byte pattern from the respective columns.

Partitioned table parallelism enables to process in parallel the queries that reference to partitioned tables. This feature will improve the query processing performance on partitioned tables for many parallel plans.

Partition-aligned indexed view enables to create and manage summary aggregates in your data warehouse more efficiently, so this feature improves the query performance. For example, a fact table is commonly partitioned by dates, and we may have several indexed views with summary aggregates which can be defined on the fact table to help speed up the queries. When you switch in a new table partition, then the matching partitions on the partition-aligned indexed views defined on the partitioned table switch automatically. You can find this feature on Oracle 11g and SQL Server 2008. To read more about this feature and the implementation on every database management system, you can see at http://download.oracle.com/docs/cd/B10501_01/server.920/a96521/partiti.htm and http://technet.microsoft.com/en-us/library/bb964715.aspx.

PowerShell in SQL Server 2008 is a new provider for browsing and managing databases, tables and other database objects. This command line supports more complex logic than traditional T/SQL scripts for more robust administration scripts.

Filtered indexes enable indexing on subset of rows in a table, think about this as having a WHERE clause, so the B-Tree data structure will only contains search information of data rows that match the filter criteria. This may provide space-saving and performance improvement when you insert or update (reducing maintenance cost) the data and it's useful for queries that return a small percentage of rows from a large data set. One common scenario is to index only data to a particular date (that is, current month). This is the recommended method by the SQL Server Database Engine Tuning Advisor.

Sparse columns efficiently manage empty data in a database because they enable NULL data to consume no physical space. This feature is also enabled in Oracle, although in SQL Server 2008 sparse columns can support wide tables with up to 100,000 columns, unlike the counterpart Oracle implementation which has a limit to 1,000 columns.

Module signing using certificates is a way to sign database objects such as stored procedures, functions, triggers and assemblies. This means that you can temporarily elevate privileges without switching the user context.

SQL Data Services is a highly scable, cost effective, on demand data storage and query processing 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 mash up 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.

In the Business Intelligence field, SQL Server 2008 has industry leading capabilities and offers tremendous advances to the already extensive BI capabilities of SQL Server. Gartner regards Microsoft as the highest rated company for its success in making its BI visions a market reality. The product includes support for enterprise level data warehousing, online analytical processing (OLAP), reporting, scorecards, data mining, ETL, key performance indicators (KPI), and the integration with Microsoft Office.

Now let's talk about the new features of Oracle 11g database.

Starting with 11G, more table partitioning options have been provided and these should reduce the burden of the DBA to a great extent. The new extensions are interval partitioning, REF partitioning, virtual column-based partitioning and the introduction of Partition Advisor.

Regarding workload balancing, Oracle JDBC and ODP.NET provide connection pool workload balancing facilities through the integration with the new workload balancing advisor tool. This replaces the listener-based workload balancing techniques. Automatic Storage Management (SAM) now enables a single storage pool to be shared by multiple databases for optimal workload balancing. It's also possible to enable the workload balance between standby databases using the Data Guard Load Balancing feature.

Regarding data encryption and compression, now it's improved the table and index compression, so that the compression works for all DML, so any database object can be compressed and used transparently by the applications. 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.

Regarding the Oracle's Real Application Clusters (RAC) option, it 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. 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.

Regarding to server-side connection pooling, we have that server-side connection pooling is an additional layer to the shared server to enable faster session creation. This enables that several clients can share a server-side pool of sessions by matching the USERIDs. So, clients can connect and disconnect several times without the cost of the creation of a new session.

Regarding to the Query results cache, the /*+result_cache*/ SQL hint for query results cache is one of the most important new feature on Oracle 11g because it can enable boosting the application performance in a way ever seen before. This feature caches the result of a SQL query as opposed to the data blocks cached by the buffer cache to be accessed to obtain the query results, and it works on both client and server side. So this is another level of buffer cache in the database system. You can cache both SQL and PL/SQL results for a very fast subsequent data retrieval.

Virtual columns are columns that are actually functions. In this same way, we can create index on virtual columns.

New table Data Type "simple_integer" - A new 11g datatype dubbed simple_integer is introduced. The simple_integer data type is always NOT NULL, wraps instead of overflows and is faster than PLS_INTEGER.

You can store data using XML format as well as to process this XML information using Xquery, SQL XML and other emerging standards in Oracle 11g. You can also embed XML directives in your PL/SQL code and embed PL/SQL code inside XML documents.

Now let's talk about PL/SQL new features.

The continue keyword enables a C-like continue in a loop in order to bypass any else boolean keyword. So, it's not necessary to use the goto keyword to exit a loop.

Disable state for PL/SQL is a feature to disable state as opposed to enable and invalid in dba_objects.

Now the stored procedure compilation is improved because it's not required a C compiler to compile the PL/SQL and the code goes directly to a shared library. The next step is the creation of native PL/SQL. This provides a great performance boost for your code.

Conclusion

In this article, I've discussed the new features in Oracle 11g and SQL Server 2008 database systems. Now you can choose the database system that best satisfies your requirements.