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 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!
0 nhận xét:
Đăng nhận xét