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
11. Further information
1. Unsorted Exports
As of SQL Server 2008 R2 the import of unsorted exports to SQL Server is released without restriction.
2. 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.
3. 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
4. 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
5. 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"
6. 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.
7. 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
8. 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
9. 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.
10. 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
11. Further information
Review the OS/DB Migration FAQ at this Microsoft Blogsite http://blogs.msdn.com/b/saponsqlserver/
