Basis and others: Note 1054852 - Recommendations for migrations to MS SQL Server

Summary

Symptom
You want to perform a platform migration to or a Unicode conversion on Microsoft SQL Server using the R3load-based tools.
Other terms
Heterogeneous migration, R3load, R3loadmss, sorting, logging, clustered, nonclustered, Unicode, Unicode migration, Unicode conversion, performance, unsorted
Reason and Prerequisites
We strongly recommend that you only make the changes to the standard migration process that are mentioned in this note in collaboration with experienced certified OS/DB migration consultants.
Experience shows that incorrect or incomplete changes may have serious consequences (for example, missing primary keys usually lead to data inconsistencies).
Solution
The topics that are explained are as follows:

1. Import of Unsorted exports

2. Primary key handling in relation to clustering and time of creation

3. Improving secondary index creation performance

4. Deactivation of logging on SQL Server

5. Information regarding sorting and Unicode conversion

6. Information regarding BW migration and sorting

7. Information regarding BW migration and partitioning

8. Information about SQL Server compression

9. Information about splitting when exporting or importing

10. General Guidance

Unsorted Exports

As of SQL Server 2008 R2 the import of unsorted exports to SQL Server is released without restriction.

Clustering and time of primary key creation

The standard OS/DB Migration tools create a clustered index on the primary key prior to loading data into the table. This ensures that data is stored in a "sorted" format in SQL Server tables. All tables in SAP systems such as ECC 6.0 are created this way. Tables in BW systems may not have a clustered index on the primary key.

Modern powerful hardware with highly performing disk systems will generally not greatly benefit from deferring the creation of the clustered index until after the data has been loaded. While it is technically possible to do this it is suggested to engage Microsoft support resources or contact the hardware vendor to ensure that there is not an infrastructure bottleneck.

Improving secondary index creation performance

The standard SAP OS/DB Migration tools create all secondary indexes within one R3Load Package. This is true of both split (DTP package will build secondary indexes) and non-split tables. Therefore each index is created sequentially: secondary index 1 is built, then secondary index 2 etc. If a large table contains too many indexes, the import time will be much shorter than the creation of all the indexes. In such cases the creation of some or all secondary indexes can be done manually via T-SQL commands. Multiple secondary indexes on the same table can be built simultaneously. SQL Server can create indexes fully online meaning index creation can continue in the background during uptime phase of the migration.
It may be worthwhile to globally increase the MAXDOP value (the default value is 1). Use the following TSQL commands to change the MAXDOP to 4 for the entire server:

sp_configure 'max degree of parallelism', 4;
reconfigure with override

Deactivation of logging on SQL Server

It is not possible to completely deactivate all transaction logging on SQL Server however it is possible to minimize logging during migrations as described in Note 1241751 - SQL Server minimal logging extensions

Information regarding sorting and Unicode conversion

The Unicode conversion process requires certain database tables to be exported sorted. Tables such as RFBLG and other cluster tables are examples of such tables. R3Load will automatically export tables that require sorted export in the correct mode even if the default export setting has be set to "unsorted"

Information regarding BW migration and sorting

BW migrations should always be exported unsorted. The reason for this is that the large fact tables either do not have any primary key at all (for example, the F Fact tables on MS SQL Server or the E Fact tables on ORACLE) or their primary key is not clustered (for example, the E Fact tables on MS SQL Server). Due to a missing primary key, you must subsequently sort the database manually using many resources (CPU, I/O, tempdb). If the index is not clustered according to the primary key (which is always the case for ORACLE), the entire table must be read along the index. This involves an enormously high I/O effort in comparison with an unsorted export.

Information regarding BW migration and partitioning

Table partitioning information for BW systems is created by ABAP report SMIGR_CREATE_DDL. It is recommended to import BW systems into SQL 2008 R2 (using the latest Service Pack level) or SQL 2012. Review Note 1593998 - SMIGR_CREATE_DDL for MSSQL and Note 1494789 - Enabling 1000+ partitions support on SQL Server.

Maximum Partitions per Table
SQL 2005, 2008, 2008 R2 = 1,000
SQL 2012, 2008 SP2+, 2008 R2 SP1+ = 15,000

Information about SQL Server compression

SAP have defaulted PAGE compression for all SAP ABAP systems (ECC, BW, CRM etc) for 100% of all tables & indexes without exception. SAP OS/DB migration tools have been defaulted to PAGE compress all tables and indexes. All customers should use PAGE compression if the database release is SQL 2008 or higher. Ensure that Note 1581700 is applied with SNOTE or included in support packs

Information about splitting when exporting or importing

              When exporting from SQL Server with split tables use the R3Load and R3ta versions listed in: Note 1650246 - R3ta: new split method for MSSQL

It is not possible to give generalized guidance about how many table splits to create on source systems (Oracle, DB2, MaxDB etc).  Export and import performance will vary dramatically based on the hardware resources, SAN storage IO capabilities and factors related to the table (data distribution, data types, size etc).

Customers have successfully exported systems with table splits ranging from 2 to over 80.
              Parallel import of split tables is fully supported on SQL Server 2008 R2 and higher.  Older SAP documentation might mention limitations or restrictions, but these  restrictions are now obsolete.
If deadlocks are observed during import to SQL 2005 or 2008 (non-R2) then reduce the BCP_BATCH_SIZE.

General Guidance

It is highly recommended to:

a) Use the latest R3Load.exe, dbmsslib.dll and r3ldctl.exe

b) Use the latest SAP Software Provisioning Manager (SWPM)

c) Use the latest patched Kernel DVD

d) Use the latest SQL Server release available

e) Use the latest SQL Server Service Pack

f) Apply any OSS Notes required for SQL Server in the source system (even if it is running UNIX/Oracle or DB2). This ensures there are no problems on the target system. SQL Server specific code in OSS Notes will not be executed in the source system

g) Review Note 888210 or 1738258 and carefully check SAP systems that have very old support packs as notes required to support compression or other SQL Server features may need to be applied with SNOTE

Further information

Review the OS/DB Migration FAQ at this Microsoft Blogsite http://blogs.msdn.com/b/saponsqlserver/

Header Data


Release Status:	Released for Customer
Released on:	15.08.2013 14:12:56
Master Language:	English
Priority:	Recommendations/additional info
Category:	Performance
Primary Component:	BC-DB-MSS Microsoft SQL Server
Secondary Components:	BW-SYS-DB-MSS BW Microsoft SQL Server

Affected Releases

Release-Independent

Related Notes

Note 1054852 - Recommendations for migrations to MS SQL Server

No comments: