database migration | requesting feedback and advice

We are looking to migrate from AS/400 DB/200 database to Microsoft SQL server

I have some hardware / architecture questions, hopefully the experts here can provide feedback.

1) Our current production data is 1.2 Terybyte on the AS/400 - roughly what would be the size when moved to MS SQL platform ?

2) When moving production data, what would be the best methodology to follow. Running R98403 where source is AS/400 and destination would be MS SQL - that process would be very long. Are there any 3rd party tools or something within MSSQL such as bulk copy or Data Transfer service that I can leverage to expedite this process.

3) We are concerned with having enough arms for the database. Currently, we have 42 - 15K drives on the AS/400. From performance perspectives, what would be a recommendation when looking into a storage system that would at least match the I/O of the current system, hopefully, far exceeding the current performance.

Lastly, what are your thoughts on using SSD drives, how does E1 behave with those ? For some reason our hardware vendor, in general, is steering us away from those, not becuase of E1, just in general.

Thank you in advance for your valuable feedback.

Hi Cncjay,

The link below is to a whitepaper I wrote for Microsoft a few years back on how to optimize AS/400 to SQL conversions for SQL.

http://download.microsoft.com/download/8/6/7/867A7D31-5AAA-4C09-9DD7-E802459A0696/migrating_from_iseries_to_sql_server.pdf

Also if you shoot me an email I've got a spreetsheet that will help you with estimating the size of the converted data.

-Paul

As for the architecture I'd recommend doing a shared-nothing database mirroring cluster and using SSD cards from FusionIO. We are using four of the 640GB Duo cards for our DB mirror and performance totally rocks. After converting to them from EMC DMX4 SAN our average UBE runtime dropped by over 60%. Also plan on using SQL Server page compression. It will drop the size of your JDE_PRODUCTION by about 80% and speeds up performance quite a bit.

Paul,

Thank you So much for your feedback. I just sent you an email.

Regards,

It's awesome threads like these that remind me how much I love this forum.

You guys rock.

Max

Just a quick follow up question to the original post - when migrating data from AS/400 to SQL and if R98403 is used, does it bring indexes across also along with the tables ?

thanks,

The R98403 will create the indexes for you but you should plan on either doing a index defrag or rebuild once the import is done. Also note with compression you will need to manually script out the indexes to compress them.

Paul - Thanks for that additional feedback. We are planning on doing a test run of CRPCTL from iSeries to SQL to get a rough idea of what the end result database looks like. I wasn't sure if indexes would come across - which you clarified. While it may not make a big difference for control tables, for business data, the indexes add significant overhead.

regards,

Make sure to have your SQL db in Simply Recovery mode while doing the import lest your t-log fill up.

[ QUOTE ]
Just a quick follow up question to the original post - when migrating data from AS/400 to SQL and if R98403 is used, does it bring indexes across also along with the tables ?

thanks,

[/ QUOTE ]

I'd suggest R98403B instead of R98403:

E1: ENV: What R98403A, R98403B, R98403E and R98403G UBES Do? [ID 963382.1]
https://support.oracle.com/CSP/main/article?cmd=show&type=NOT&id=963382.1

I have a whitepaper too.

It shows how to create a Table-Conversion in about 5 minutes that ensures ALL tables are copied from AS/400 to SQL Server using the JDE logic - including custom tables. It also demonstrates how to partition large tables (like the F0911 and F42199) with multiple boxes to ensure VERY fast and reliable conversion of data.

Just look on my website - or google "using CPYTBLE".

Much more reliable than using R98403. Much, MUCH faster.

Yeah, Jeff's right about the R98403B.