Update to the SAP on Oracle -> Windows/SQL Server OS/DB Migration FAQ

The most time consuming part of a OS/DB migration is almost always extracting the data off the legacy UNIX system, usually because these systems are several years old and are far slower than modern Intel or AMD commodity hardware.  For example the SAPS rating on a modern HP DL 580 G7 is over 57,000 according to SAP's official benchmark site. 
One of the most effective ways of speeding up the export of data from UNIX/Oracle to Win/SQL is to run the SAP export processes (called R3LOAD) on a separate Windows Intel server.  This offers two advantages (1) the CPU load of running the R3LOAD processes is removed from the UNIX server and more CPU and memory is available for Oracle (2) R3LOAD can run on modern Intel/AMD servers much faster than older UNIX systems.
Please check section #10 of the document for more information and please feel free to post questions about OS/DB Migration to SQL Server in this blog.
Thanks
Post Script - Here are some Step by Step procedures for an export from Oracle using a Windows R3LOAD server

1.      Download the SAP System Copy Guide

2.      Clean the SAP data dictionary and make sure DB02 -> Missing Tables/Indexes is clean

3.      Clean QCM tables SE14 -> Delete temporary tables or Invalid tables

4.      Stop SAP on the source system

5.      Connect a Windows/Intel server as per my FAQ – see section 10 – you will need to copy the TNSNAMES.ORA from the Unix/Oracle server and place this on the Wintel server in the path = TNS_ADMIN 

6.      Make sure r3load –testconnect works (replace c:\export with the directory where you have about 300-400GB free)

7.      Copy Migmon.sar, Migtime.sar and strsplitter.sar off the Netweaver master Installation DVD and un-sar them in c:\export

8.      Run r3ldctl –l logfilename –p c:\export              

9.      Run this command and keep output in a Text file called tablefile.txt

set lines 100 pages 200

col Table format a40

col Owner format a10

col MB format 999,999,999

select owner "Owner", segment_name "Table", bytes/1024/1024 "MB" from dba_segments where bytes > 100*1024*1024 and segment_type like 'TAB%' order by owner asc, bytes asc;

10.   Run this command

str_splitter.bat -strDirs c:\export\abap\data -outputDir c:\export\abap\data - tableFile tablefile.txt

11.   Run this command on the top 10 or 20 tables

r3ta -f c:\export\abap\data\

.str -l whr.log -o c:\export\abap\data\ .WHR -table %

12.   Run this command

C:\export>where_splitter.bat -whereDir c:\export\abap\data\ -strDir c:\export\abap\data -outputDir c:\export\abap\data -whereLimit 1

13.   Configure the export monitor.properties file.  Here you must specify the template file, export directory and number of R3LOAD processes.  If you have any problem, email me the file

14.   System is now ready for export.  Open a command prompt on the Wintel server and type export_monitor.bat

15.   System will now export

 **************************************************************************************************

Here is the process for doing the import

1.      Check the Export Logs and ensure there are no errors - open a Command Prompt and type Findstr /C:ERROR: \*.log

2.      Configure a separate R3LOAD server.  The best R3LOAD server is a 2 processor commodity server with a high clock speed.  So far the Intel Nehalem EP 5680 has proved to be the most effective, though AMD produces good results as well.  Follow step #10 in the OS/DB Migration FAQ

3.      Install Windows 2008 R2 Enterprise Edition x64 – an evaluation copy can be downloaded from http://www.microsoft.com/windowsserver2008/en/us/trial-software.aspx

4.      Create Domain users SAPService and adm (local users can be used as well – set the password the same on the SQL DB server and the R3LOAD server, however it is recommended to user Domain users for SAP systems)

5.      Install SQL Server 2008 R2 and apply the latest CU or Service Pack – check http://blogs.msdn.com/b/sqlreleaseservices/default.aspx.  An evaluation copy of SQL 2008 R2 can be downloaded from http://www.microsoft.com/sqlserver/2008/en/us/trial-software.aspx.  It is recommended to use a separate server for SQL and a separate server for R3LOAD  

6.      Create the SAP Database on the SQL Database Server using this script. 

-- Sample script to create SAP Database

-- Most Medium Size SAP systems need 8 or 16 datafiles, 16 is generally quite common

-- Note: only 2 datafiles per LUN/disk and separate disk for Transaction Log

CREATE DATABASE [SID] ON  PRIMARY

( NAME = N'SIDDATA1', FILENAME = N'F:\SIDDATA1\SIDDATA1.mdf' , SIZE = 100GB , FILEGROWTH = 1GB ),

( NAME = N'SIDDATA2', FILENAME = N'F:\SIDDATA2\SIDDATA2.ndf' , SIZE = 100GB , FILEGROWTH = 1GB ),

( NAME = N'SIDDATA3', FILENAME = N'G:\SIDDATA3\SIDDATA3.ndf' , SIZE = 100GB , FILEGROWTH = 1GB ),

( NAME = N'SIDDATA4', FILENAME = N'G:\SIDDATA4\SIDDATA4.ndf' , SIZE = 100GB , FILEGROWTH = 1GB ),

( NAME = N'SIDDATA5', FILENAME = N'H:\SIDDATA5\SIDDATA5.ndf' , SIZE = 100GB , FILEGROWTH = 1GB ),

( NAME = N'SIDDATA6', FILENAME = N'H:\SIDDATA6\SIDDATA6.ndf' , SIZE = 100GB , FILEGROWTH = 1GB ),

( NAME = N'SIDDATA7', FILENAME = N'I:\SIDDATA7\SIDDATA7.ndf' , SIZE = 100GB , FILEGROWTH = 1GB ),

( NAME = N'SIDDATA8', FILENAME = N'I:\SIDDATA8\SIDDATA8.ndf' , SIZE = 100GB , FILEGROWTH = 1GB )

LOG ON

( NAME = N'SIDLOG1', FILENAME = N'L:\SIDLOG1\SIDLOG1.ldf' , SIZE = 80GB , FILEGROWTH = 5GB)

ALTER DATABASE [SID] SET AUTO_CREATE_STATISTICS ON

GO

ALTER DATABASE [SID] SET AUTO_SHRINK OFF

GO

ALTER DATABASE [SID] SET AUTO_UPDATE_STATISTICS ON

GO

ALTER DATABASE [SID] SET AUTO_UPDATE_STATISTICS_ASYNC ON

GO

ALTER DATABASE [SID] SET RECOVERY SIMPLE

GO

ALTER DATABASE [SID] SET PAGE_VERIFY CHECKSUM 

GO

7.      Download the zip file attached to OSS Note 551915 – this contains a script that creates the require database users and mapping between Windows user account and database logins

8.      Copy the latest MIGMON.SAR, R3LOAD.EXE and DBSL onto the R3LOAD server.  MIGMON.SAR can be downloaded or copied of the Netweaver Installation Master DVD

9.      Run R3LOAD –testconnect and ensure connection is successful

10.   If required create a file called importorderby.txt.  this file can be used to prioritize certain tables/packages so they start first otherwise Migmon will prioritize by size

11.   Run sapcar –xvf migmon.sar

12.   Edit the file specified in ddlFile=c:\export\ABAP\DB\DDLMSS.TPL  and set system to import PAGE compressed as per section #19 in the OS/DB Migration FAQ     

13.   Configure the import_monitor_cmd.properties file (change “c:\export” as needed)

# Import Monitor options

#

# List of import directories, separator on Windows ; on UNIX :

importDirs= c:\export\abap\data\

# Installation directory

installDir=c:\import

# Package order: name | size | file with package names

orderBy=c:\import\importorderby.txt

# DDL control file, default is DDL.TPL

ddlFile=c:\export\ABAP\DB\DDLMSS.TPL

# Optional path of R3load executable

r3loadExe=c:\import\R3load.exe

# Generation of task files: yes | no

tskFiles=yes

# DB code page for the target database

dbCodepage=4103

# Migration key

migrationKey=

# Additional R3load arguments for LOAD phase

loadArgs=-stop_on_error -merge_bck -loadprocedure fast

# Number of parallel import jobs

jobNum=160

# Trace level

trace=all

14.   Set SQL Server Trace Flags 1117, 610 and 3917.  Ensure SQL Server Logging mode is set to SIMPLE

15.   If required limit SQL Server Index build memory as per section 22. m. in the OS/DB Migration FAQ

16.   Set environment variable BCP_LOB=1 and BCP_BATCH_SIZE=50000

17.   Run Import_monitor.bat

18.   Migmon will import the system

19.   Check logs – open a Command Prompt and type Findstr /C:ERROR: \*.log

20.   Run Migration Time Analyzer as per section #6 of  OS/DB Migration FAQ (adjust importorderby.txt if needed)

21.   Run SAPInst -> Additional Lifecycle Tasks -> System Copy -> Target System.  At the appropriate menu screen select “Homogenous System Copy Backup/Restore or Attach”.  The reason for not selecting “R3Load based system copy” is that we have manually performed these steps already. 

22.   Remove SQL Server Trace Flags 610 and 3917

23.   Remove environment variable BCP_LOB=1 and BCP_BATCH_SIZE=50000

24.   Set SQL Recovery mode to FULL

25.   Run Full Backup with Backup compression

26.   Follow post processing steps as per SAP system copy guide

27.   Compare database size and backup sizes with Oracle.  The SQL database should be ~25% of the original size on Oracle and much faster and easier to backup and administer

28.   Compare performance on SQL 2008 R2 on powerful new low cost Intel/AMD commodity servers.  2 processor Intel servers with 96-128GB RAM should cost absolutely no more than $12,000 to $15,000 USD (including HBA, RAM, CPU & 3 years support) and can easily outperform UNIX/Oracle servers that cost hundreds of thousands of dollars, if not millions.

 Please feel free to post questions in this blog post and if you wish to share the compression ratios and performance results other customers may find this useful.

Note: It is possible to do the entire process via the SAPInst GUI, however this has some restrictions and also forces customers to run steps such as R3 Size Check, something that takes a long time and is not required for SQL Server. 

Good luck!