This is part of the post-blog response started by this overview of Migrating from Oracle to SQL Server, and deals with experiences with Kronos migrations, again by our SQL Server guru friend Ken Lassesen.
----------------------------------------------
Javed Iqbal, a Kronos Migration specialist has just finished doing the first migration of data from Oracle to SQL Server on a client’s test system. He was not ecstatic about the performance and asked me to assemble recommended practices to improve performance. I contacted the SSMA team and Welly Lee kindly provided me with some reference material and information about changes coming this summer with the release of version 5.0 of SQL Server Migration Assistant for Oracle.
Field Experience
Javed had done some of the items in the recommendations below and he was running on a production-grade test system that had more cores than I have fingers. The data migration took 12 hours for 120 GB of data in Oracle (excluding indexes) for around 10GB/hour. This is better than the typical 4GB/hour reported from developer-grade systems (illustrating the value of having 15,000 RPM SCSI drives in RAID arrays).
This is respectable performance but when you are dealing with terabyte databases, waiting becomes old.
I checked with Welly if SSMA 4.2 was doing the migration in a single-threaded style and the answer was yes L. He also informed me that version 5.0 will exploit multiple cores :-) with initial results suggesting that improvement may be proportional to the number of cores.
I should add a caveat that your performance with 5.0 may be considerably less depending on hardware configuration. Issues of insufficient memory, hard drive spindle contention and network congestion tend to bottleneck performance.
Recommendations to improve SSMA Performance
SSMA uses bulk import operations which help us understand how it should be configured. I will skip over the mechanics and provide the key points:
Run Business Performance Analyzer on the destination SQL Server instance and check for any issues that could impact performance, for example incorrect disk partitioning.
Setting the Batch Size in SSMA4O to a larger value
Tables should not be replicated
If you need replication, do it after the migration
Predefined TempDB and Database size to estimated size
Disable all indexes
Disable all triggers
Disable all constraints
Set data recovery to simple
Set Table Lock to true
For more information on bulk import see: Prerequisites for Minimal Logging in Bulk Import. For some numbers on performance see: Making data imports into SQL Server as fast as possible.
The Coding Solution
Some of the items above can result in carpal tunnel syndrome trying to do it through SQL Server Management Studio. If you are interested in the code I'm speaking of you can see it here.
