Benifits of using SQL server 2008 (R2) for SAP

Features added in SQL server 2008 (R2)

• Merge Command Backup Compression (optional)
• Transparent Data Encryption (optional)
• Changed Data Capture (optional)
• Star Join Optimization (BW)
• Grouping Sets (BW)
• Parallelism for partitions (BW)
• Row and Page compression
• Increased speed of partition drop: 15000 Partitions (SQL Server 2008 SP2 – feature not
  yet available in SQL Server 2008 R2).
• Unicode compression (SQL Server 2008 R2)

Merge command backup compression:

In the previous versions of SQL Server you can only make an uncompressed backup to disk. The size of this backup is almost the same as the size of the database. To decrease the size of the backup file, you can use compression software like Winzip, WinRar etc. This requires additional CPU power and disk space. As of SQL 2008 a new backup option is introduced called ‘Compression’ which directly create a compressed backup.

Advantages and Disadvantages:

• Decrease in backup size up to 65 %. This depends of course on the content of the data.
• CPU usage will increase during the backup process. The more data can be compressed the more increase in CPU usage.
• Faster backup speed (25%) because it requires less disk I/O

Transparent Data Encryption (optional)

Transparent data encryption (TDE) performs real-time I/O encryption and decryption of the data and log files. The encryption uses a database encryption key (DEK), which is stored in the database boot record for availability during recovery. The DEK is a symmetric key secured by using a certificate stored in the master database of the server or an asymmetric key protected by an EKM module. TDE protects data “at rest”, meaning the data and log files. It provides the ability to comply with many laws, regulations, and guidelines established in various industries. This enables software developers to encrypt data by using
AES and 3DES encryption algorithms without changing existing applications.

Changed Data Capture (optional)

Change Data Capture records INSERTs, UPDATEs, and DELETEs applied to SQL Server tables, and makes a record available of what changed, where, and when, in simple relational ‘change tables’ rather than in an esoteric chopped salad of XML. These change tables contain columns that reflect the column structure of the source table you have chosen to track, along with the metadata needed to understand the changes that have been made.

Star Join Optimization (BW)

SQL Server 2008 (Katmai) introduces a new automatic star join optimization feature to enhance the performance of complex BI queries. This feature needs
intra query parallelism to be activated. Intra query parallelism is disabled per default in BI systems.

SAP Note 1126568 – Enable star join optimization with SQL Server 2008

Grouping Sets (BW)

GROUPING SETS clause, which allows you to easily specify combinations of field groupings in your queries to see different levels of aggregated data. Today we’ll look at how you can use the new SQL Server 2008 GROUPING SETS clause to aggregate your data.

Parallelism for partitions (BW)

Partitions and parallel performance are most applicable to Data Warehouses, batch processing, and reporting. Not all data warehouse environments will want to enable parallelism for all queries. Specifically, parallelism is most effective if the system is running only a few queries at a time and you want as many
resources possible made available to those queries to minimize execution time. If the data warehouse is already a high concurrency environment, parallelism
will not improve throughput or response time since the collection of single-threaded queries are likely already consuming the available resources.
Likewise, for peak performance, you do not want parallelism in a high concurrency OLTP workload

Row and Page compression (Database compression)

Depending on the SAP and SQL Server release, there are different compression types which can be used to save disk space with SAP NetWeaver running on SQL Server. This note describes how to implement ROW and PAGE compression for SQL Server 2008 (and newer) for SAP products based on application Server ABAP. It does not cover older SQL Server releases or other SAP products like SAP Application Server JAVA, SAP CRM Mobile Client

or SAP Business Objects products. The attached SDN article “Using SQL Server Database Compression with SAP NetWeaver” contains a more detailed description and also covers compression types of older SQL Server releases. The SDN article is also available at http://www.sdn.sap.com/irj/sdn/sqlserver

Increased speed of partition drop: 15000 Partitions

As of SQL Server 2005 you can partition table and indexes. The maximum number of partitions used to be 1,000 for SQL Server 2005, 2008 and 2008 R2. Some customers were reaching this limit when running SAP Business Warehouse (SAP BW) on Microsoft SQL Server. Therefore it was decided to increase this limit to 15,000 for SQL Server 2008 and 2008 R2 in a Service Pack.

Benefits:

SAP supports partitioning only for specific tables in SAP BW. In BW 7.00 and newer releases, the F-fact table of an SAP BW cube is automatically partitioned by the packet dimension. Each time a new request is loaded into the cube, a new partition is created on the F-fact table. Typically customers load data once a day or less. Therefore 1,000 partitions are sufficient for almost 3 years. Furthermore, you can reduce the number of partitions by performing the SAP BW
cube compression (which you should not confuse with SQL Server data compression). However, some customers loaded data several times a day, which
resulted in hitting the 1,000 partition limit quickly.

             The 1,000 partition limit also was a pain during migrations of SAP BW systems from ORACLE to SQL Server. ORACLE supports much more than 1,000 partitions since years. Therefore we often see SAP BW systems on ORACLE, which already have more than 1,000 partitions. We had this particular scenario in mind when we decided to set the new limit to 15,000. In practice more than a few thousand partitions make no sense. Having tens of thousands partitions will not increase the overall system performance. It will very likely decrease it.

Unicode compression (SQL Server 2008 R2)

SAP encourages their customers to convert existing non-Unicode systems to Unicode. However, several SQL Server customers hesitated to perform a Unicode Conversion, because of the increased storage requirements of UCS-2. This issue has been finally solved with Unicode-compression, which is a new feature of SQL Server 2008 R2. A Unicode Conversion running on SQL Server 2008 R2 even results in a decreased database size. Therefore hardware restrictions are no longer an excuse to stay on non-Unicode.