Comparison on DB2 10.1 Vs SQL Server 2012 Vs Oracle 11g R2 latest features to suite SAP Products

[Former Member]

Hello all,

Comparison on DB2 10.1 Vs SQL Server 2012 Vs Oracle 11g R2 latest features to suite SAP Products

Summary

Comparison is aimed to highlight the latest features made available by different database vendors for SAP to suite its products on demand.

Comparison with latest features provided by Database Vendor’s

1)     Backup Compression

a)      RMAN Backup Compression for oracle

Oracle Advanced Compression includes compression technology that can dramatically reduce the storage requirements for backup data. Due to RMAN’s tight integration with Oracle Database, RMAN backup (widely used by SAP customers) data is compressed before it is written to disk or tape and doesn’t need to be uncompressed before recovery – providing an enormous reduction in storage costs.

b) SQL Backup compression.

Independent of the use of database compression, backup compression showed compression factors of 4 to 5 when backups of SQL Server databases were conducted. As with most SQL Server functionality, backup compression does not require tuning or configuration

c)      DB2 Backup compression

You can use backup compression with tables that use row compression. Keep in mind, however, that backup compression requires additional CPU resources and extra time. It may be sufficient to use table compression alone to achieve a reduction in your backup storage requirements. If you are using row compression, consider using backup compression only if storage optimization is of higher priority than the extra time it takes to perform the backup. Consider using backup compression only on table spaces that do not contain compressed data

2)     OLTP Table Compression

a)       Oracle: Oracle Database 11g introduced a new feature called OLTP Table Compression that allows data to be compressed during all types of data manipulation operations, including conventional DML such as INSERT and UPDATE. Oracle’s unique approach offers significant performance benefits by not introducing additional I/O when accessing compressed data. The benefits of OLTP Table Compression go beyond just on-disk storage savings.

**One significant advantage is Oracle’s ability to read compressed blocks directly without having to first uncompress the block. Therefore, there is no measurable performance degradation for accessing compressed data.

**The buffer cache will become more efficient by storing more data without having to add memory.

Since Oracle Database Rel. 10.2.0.2, SAP has certified the use of Index compression to save disk space for indexes and reduce total database size on disk. Customer experiences show that even after full database reorganization has taken place, an additional 20% of total disk space reduction for the whole database can be achieved using index compression.

b)      SQL: OLTP compression is supported and it doesn’t compress on every insert and it compress when reaches a certain threshold and is available with 2008 and higher release

c)       DB2: Not supported for DB2 but it has Indexes compression, including indexes on declared or created temporary tables, can be compressed in order to reduce storage costs. This is especially useful for large OLTP and data warehouse environment.

3)     Data compression:

a) Oracle:

It can compress the metadata( available from 10g R2) and table data(new in 11g) .Data Pump compression is fully inline on the import side as well, so there is no need to uncompress a dump file before importing it. The compressed dump file sets are automatically decompressed during import without any additional steps by the Database Administrator

b) SQL Server

Microsoft and SAP also worked together to introduce the highly space-efficient SQL Server Page Dictionary compression—unrestricted for data and indexes—for SAP Net Weaver applications based on the SAP ABAP stack. Many customers have reported positive experiences with Page Dictionary compression, with benefits that include:

 One-time change with no repetition: Once a table is built with Page Dictionary compression, there is no need to rebuild it in order to apply compression to new content. As data is inserted, it is compressed in the pages automatically, without help from the database administration side.

 Database volume often reduced by half with migrations from competitive platforms.

c) DB2 Adaptive compression

Your DB2 database provides comprehensive compression for almost every type of database object including table data, indexes, temporary tables, XML documents, log files and backup images. In previous versions, classic row compression helped lower storage costs and improve query performance. DB2 Version 10.1 further enhances classical table compression through a new type of compression, adaptive compression.

The adaptive compression feature improves table compression through two levels of compression dictionaries (table-level and page-level) to improve compression ratios, particularly as data changes. A page-level compression dictionary are smaller than table-level dictionaries so as data changes on a page it is easy to update automatically and quickly, and removes the need to perform table reorganizations to update page-level dictionaries.

Adaptive compression helps you:

• achieve high compression ratios without taking your data offline to perform table reorganizations
• continue to improve query performance and will reduce the size of large tables
• increase storage savings and system availability

4)     Real Application Testing (RAT)

Oracle 11g R2: Database Replay provides DBAs and system administrators with the ability to faithfully, accurately and realistically rerun actual production workloads, including online user and batch workloads, in test environments. By capturing the full database workload from production systems, including all concurrency, dependencies and timing.

The Oracle/SAP Development Team recently concluded comprehensive testing of SAP applications

using Real Application Testing to measure the effects of Advanced Compression for OLTP and Secure Files

On DML operations for SAP ERP

SQL Server 2012’s Distributed Replay: Once again this is answer to a feature that Oracle released (Real Application Testing). In Oracle it is a (very expensive) cost option to Enterprise Edition. With SQL, when you buy your licenses for Enterprise Edition, you get everything. Distributed replay allows you to capture a workload on a production server, and replay it on another machine. This way changes in underlying schemas, support packs, or hardware changes can be tested under production conditions.

DB2 version 9.7 does not have a competitive offering to Oracle’s Real Application Testing Option. Thus the task of validating changes before introducing them into production is left to the DB2 DBA. The DBA has to rely on 3rd party tools, such as Quest’s Benchmark Factory, engineer some manual custom testing procedures or blindly implement changes in production and hope for the best, which, for obvious reasons, will most likely result in system outages and severe loss of service.

5)     High Availability & Disaster Recovery

SQL Server 2012 with AlwaysOn

AlwaysOn is the new integrated, flexible, cost-efficient high availability and disaster recovery solution in SQL Server 2012 that provides redundancy within/across datacenters and enables fast application failover providing both maximum availability and data protection for mission critical applications. This feature is transparent to the SAP Net Weaver system,

IBM DB2 10.1 with Multiple HADR Standby Servers

For high availability, IBM includes HADR in the base product. Multiple HADR (High availability disaster Recovery) Standby Servers: You can now install up to three HADR standby servers with or without time delayed replay.

Oracle with RAC (from 10g onwards): Oracle requires customer to purchase additional database options called Real Application Clusters (RAC) for local high availability and Active Data Guard for remote disaster recovery. Also it has DATAGUARD and protect against industry-unique protection against lost writes.

6)     Data Encryption

a)       Oracle 11g with ASO: Advanced Security options which will encrypt all the data in a table space. ASO also includes RMAN back up encryption so that no one can read a (stolen) backup. It also supports transparent data encryption (TDE) from earlier releases

b)      Starting with SQL Server 2008, customers have the ability to encrypt an entire database using transparent data encryption (TDE). SAP supports TDE, as documented in SAP note 1380493.

c)       IBM Database Encryption Expert for encryption of data at rest

IBM Database Encryption Expert is a comprehensive software data security solution that when used in conjunction with native DB2 security provides effective protection of the data and the database application against a broad array of threats.

. The key benefits of Database Encryption Expert are:

• Proven, strong data security for the DB2 database system
• Protection of live files, configuration files, log files and back-up data
• Transparent to application, database and storage environments
• Unified policy and key management for protecting data in both on-line and off-line environments
• Meets performance requirements

  Database Encryption Expert enables you to encrypt offline database backups and to encrypt online ("live") database files. This is encryption of data on the disk, sometimes called “data at rest” as opposed to "data in flight", which is travelling over the network. 

• For database files, the operating system data files containing the data from the DB2 database are encrypted. This protects the data files from unauthorized users trying to read the “raw” database file.

7)     News On features added to Index related area

a) Oracle supports index compression from earlier releases so that we can save total 20% of the disk space for the whole data base

b) SQL Server 2012 with Column Store Index

SQL Server 2012 introduces a new optional storage format for data. In addition to the traditional row-oriented store, there is a column-oriented store as well.  SQL Server 2012 introduced an additional storage format for data. Besides the normal row-oriented store, there is a column-oriented store as well (like in SAP HANA).

SQL Server 2012 Column-store support has been integrated into SAP BW for the releases 7.00, 7.01, and 7.02, 7.11, 7.30 and newer. The general support of SQL Server 2012 for SAP products based on SAP NetWeaver (7.0x and 7.3x) is planned for Q3/2012. See SAP note 1651862 for details.

Benefits of the column-store for SAP BW

Query Performance

There was a wide spread of performance increase, dependent on the data and type of query. Some queries were up to 50 times faster, while others did hardly benefit from the column-store index. The majority of the BW queries we tested ran about 3 to 5 times faster.

Space Savings

We have seen space savings of 50% and more, even when comparing with fully PAGE compressed fact tables.

c) DB2 10.1 with Index Space Reclaim: A new function is included in DB2 10.1 that reclaims free space in index objects in an online fashion.

😎     Storage Management:

a)       Oracle ASM:

Automatic Storage Management was introduced by Oracle when Oracle Database 10g was released. Since then Oracle ASM has proven to be one of the fastest and most reliable storage platforms to run the Oracle Database.

Oracle Automatic Storage Management 11g Release 2 has two major enhancements that are important for running SAP:

- Oracle Cluster Repository (OCR) and voting files can be stored on Oracle ASM

- Oracle RDBMS Home can be stored on Oracle´s new cluster file system ACFS

This makes Oracle Automatic Storage Management 11g Release 2 the preferred storage platform for SAP running on Oracle Real Application Clusters (RAC) as well as for SAP systems running on a single instance Oracle Database.

b)      IBM 10.1 Storage Groups:

As an addition to the existing automatic storage infrastructure, storage groups can be defined and assigned to certain tablespaces. Based on these definitions data with a different "temperature" (based on how often data is accessed, e.g. hot and cold data) can be placed on different storage media.

c)       Windows Server 2012 Storage Space:

The cost to acquire and manage highly available and reliable storage can represent a significant part of information technology budgets. Windows Server 2012 addresses this issue by delivering a sophisticated virtualized storage feature called Storage Spaces as part of the Windows Server 2012 storage platform. This provides an alternative option for companies that require advanced storage capabilities at a lower price point.

Storage Spaces introduces a new class of sophisticated storage virtualization enhancements to the storage stack that incorporates two new concepts:

 Storage pools: Virtualized units of administration that are aggregates of physical disk units. Pools enable storage aggregation, elastic capacity expansion, and delegated administration.

 Storage spaces: Virtual disks with associated attributes that include a desired level of resiliency thin or fixed provisioning, automatic or controlled allocation on diverse storage media, and precise administrative control.

The Storage Spaces feature in Windows Server 2012 can leverage failover clustering for high availability and can be integrated with Cluster Shared Volumes (CSV) for scalable deployments.

9)     Online Patching:

a) In Oracle Database 11g, it is possible to install certain one-off database patches for some selected platforms, completely online, without requiring the database instance to be shut down, and without requiring RAC or Data Guard configurations. With online patching, which is integrated with OPatch, each process associated with the instance checks for patched code at a safe execution point, and then copies the code into its process space.

b)      SQL Server only support Rolling Patching (ie each node is shut down, the patch is applied, then each node is brought back up again)

c)       DB2 does not have any similar capability. DB2 at best can perform rolling application of fix packs, requiring an HADR configuration.

10)New independent functionalities for latest releases of respective database:

·       Oracle 11g R2:

a)    SecureFile Performance

SecureFiles is specifically engineered to deliver high performance for file data

comparable to that of traditional file systems, while retaining the advantages of the Oracle Database.

SecureFiles offers the best database and file system architecture attributes for storing unstructured content

SAP customers benefit from SecureFiles because of:    

• Significantly faster access times compared to LOBs in SAP environments

• Increased transaction throughput on SAP cluster tables especially with RAC

          b)   More Security – Database Vault

Oracle Database Vault is also certified for use with SAP applications. With Oracle Database Vault, protective realms around SAP application database objects can be established to prevent privileged database users from accessing sensitive data and to enforce separation of duties among privileged database users.

· S SQL Server 2012:

a)    Automatic Statistics Update Algorithm Change – Trace Flag 2371

SQL Server has long been known as an enterprise-class RDBMS that is simpler to administer than its competitors. No complex scripts are needed and you do not need to burden your system with unnecessary runs of update statistics. Usage of this feature has long been required for SAP Net Weaver systems on SQL Server. In recent years, the amount of data stored in SAP databases has significantly increased, often by a factor of three or four. Microsoft has implemented a new trace flag to alter the automatic statistics update algorithm for very large database systems. We have already seen significant benefits with this trace flag and recommend it for all SAP Net Weaver systems.

b)   Support for Windows 8

SQL Server 2012 is well-positioned to benefit from new features in Windows 8

· IIBM DB2 10.1:

a) Log Archive Compression: By setting a configuration parameter log files can now be compressed by DB2 when they are archived.

b)    Hidden Columns: Table columns can be marked as "implicitly hidden". Such columns are invisible as long as they are not explicitly references.

c)     Performance Improvements: DB2 10.1 includes many performance improvements like smart data and index prefetching, hash join improvements, zig-zag joins, index jump scans and more.

d) DB2 pureScale: Reduce the risk and cost of business growth with application cluster transparency and continuous availability. Designed for organizations that run online transaction processing (OLTP) applications on distributed systems, IBM DB2 pureScale offers clustering technology that helps deliver high availability and exceptional scalability transparent to applications. Its advantages are Extreme Capacity, Application transparency ( ie you don’t need to change your application code to run on multiple nodes), Continuous Availability.

Additional Information

1) http://scn.sap.com/community/db2-for-linux-unix-windows/blog/2012/07/24/ibm-db2-101-luw-available-fo...

2) http://scn.sap.com/docs/DOC-29222

3) White papers released by respective vendors for supporting features on SAP

a) Oracle: http://www.oracle.com/us/solutions/sap/oradb11g-article-upd-1-323074.pdf

b) Microsoft:http://ecohub.sap.com/api/resource/5117f84493aa58b97f13e93a

c) IBM: http://www05.ibm.com/bg/ibmforum/attachment/Nicola%20Polic_Why%20SAP%20customers%20are%20migrating%2...

Prepared by: Alen P S                           Cognizant Technology Solutions

arison on DB2 10.1 Vs SQL Server 2012 Vs Oracle 11g R2 latest features to suite SAP Products