This Blog will provide information about running SAP applications on SQL Server and Windows. The Blog is written by folks of Microsoft who are working with SAP and SQL Server for more than a decade.
SAP supports SQL Server Data Compression (row- and page-compression) for all applications based on ABAP application server as of NetWeaver (NW) Basis 7.00 and SQL Server 2008. Per default, SAP used to create tables with compression type ROW and indexes with compression type NONE. This decision was made in the past for the following reasons:
Due to the great experience SAP made with productive page-compressed SQL Server databases, it was decided to change the default for new SAP NW ABAP installations to page-compression (for data and indexes).
In order to change the default behavior of an SAP installation, it was decided, not to change SAPINST. Instead, the default behavior of R3LOAD and the SAP Data Dictionary (DDIC) was changed.
The template file DDLMSS.TPL defines the compression type for data and indexes. This file is created during data export by SAP (to create the installation export DVDs) or by a customer (for a system copy or a DB/OS migration). DDLMSS.TPL is used by R3LOAD during SAP system installation or system copy. The newest version of R3LDCTL creates a template file for page-compression. The newest version of R3LOAD is able to use the template file DDLMSS.TPL for page-compression. SAP note 1505884 (R3load: compression for table / index) tells the necessary versions of R3LOAD and R3LDCTL. It also describes, how to change the template file DDLMSS.TPL manually.
One year ago, the Data Compression support for SAP DDIC was introduced by SAP note 1459005 (Enabling index compression for SQL Server). By setting the profile parameter dbs/mss/compression you can change the compression type, which is used by the DDIC when creating new tables. The default used to be row-compression. After applying the correction instruction of SAP note 1581700 (PAGE compression support for DDIC & Hom./Het. System Copy), the default is changed to page-compression. This correction will be included in Basis Support Packages of all SAP releases as of NW 7.00. This may result in an unexpected behavior, if you have not set the profile parameter dbs/mss/compression. By applying the according Basis Support Package, the default compression type (for newly created tables) will change from row-compression to page-compression. As a result, you may have tables with different compression type within the same database.
However, SAP works fine with different compression types. A few tables are neither created by R3LOAD nor by the DDIC, for example all tables created by report MSSPROCS. You can manually change the compression type for tables and indexes using the report MSSCOMPRESS as described in SAP note 1488135 (Database compression for SQL Server).
All future SAP products and Service Releases based on NW ABAP will create the database tables and all secondary indexes with page-compression out of the box. There is nothing you need to configure in SAPINST or in the SAP profiles.
The official and by SAP supported way to install a page-compressed, older SAP release is to
However, you may speed-up this process as follows. Once SAPINST has copied the executables to the local disk, replace R3LOAD with the newest version. Modify the file DDLMSS.TPL as described in SAP note 1505884 to use page-compression. Then start the data load. Once the installation has finished, immediately apply the corrections of SAP note 1581700. Use report MSSCOMPRESS to detect non page-compressed tables and compress them. Since this procedure is not officially supported by SAP, it should only be used by an experienced SAP consultant.
If you follow all the instructions of the system copy note 888210 (NW 7.**: System copy supplementary note), then the target database will automatically be created page-compressed. The key points are
However, this only works for a system copy using R3LOAD. Using simply detach/attach or backup/restore of the database will not change the data compression type of any table.