Converting from Oracle db to SQL server

SULLY1

VIP Member
In order to streamline or ISD department we are seriously thinking about converting from an Oracle database to SQL server. Have any of you made this transition? We are on XE Update 5 SP18.1_E1. With this configuration what OS/DB release can I run? Thanks in advance for your responses.

Patty
 
I have. How big is your DB? How much downtime can you tolerate? Copying data is always slow...

Probably, around W2K/No_SP (or SP1) and SQL2K SP1 (poossibly SP2). MDAC 2.7 or 2.71
 
No big deal you can use the SQL tools to do the copy.

One thing to be careful of is that Oracle and SQL
handle capitalization differently when it comes to
duplicate records. This will cause problems when you
go to apply your indexes in SQL.

i.e. Ace Company and ACE COMPANY are ok in Oracle. In
SQL it would be a duplicate record.
 
I have done a number of these platform conversions and I would always recommend using the E1 middleware and R98403 for the job. I agree with others that SQL tools such as DTS can be used but given the chance for problems and the fact that Orace support would not sanction the copy I would never recommend using non-JDE tools for a production migration.

Here my approach which has generally been able to manage 400 GB worth of data in 18 to 24 hours:

The key weakness of R98403 is that the API it uses, JDB_CopyTable via BSFN B9800200, seems to work on a record at a time. To work around this I run many R98403 jobs in parallel each copying a table or group of tables.

I generate a record count listing by table and then use excel to order them by record count. I then take the top 100 tables or so and create R98403 versions with data selection limiting them to 1 table each. I then throw the rest of the conversions into 1 or a few other versions. I have scripted the version creation process but even if you create 100 versions manually it isn't really a big deal. Realistically you will only do this once and I think the more hands-on your approach is the less chance for error.

The R98403 needs to be run on a fat client. I run 3 or 4 copies of the R98403 per fat client/citrix session. I make sure Citrix is configured to allow disconnected sessions.

If you have an extremely large F0911, F42119, etc. You can get extra tricky by creating a database view with the same name in a DIFFERENT schema/datasource and apply a where clause. e.g.:

CREATE VIEW PRODCPY1.F0911 AS SELECT (*) FROM PRODDTA.F0911 WHERE GLDGJ <= 100000

CREATE VIEW PRODCPY2.F0911 AS SELECT (*) FROM PRODDTA.F0911 WHERE GLDGJ > 100000

Then create a datasource for PRODCPY1 and PRODCPY2. Create two versions of the R98403 which select the F0911 one which uses PRODCPY1 as a source and one that uses PRODCPY2 as a source. You can then run these two in parallel. I apply this technique so that I have the maximum number of jobs running and spread out the copy load as much as possible across all jobs. Obviously you need to tune the number of parallel copies to match your db server's capacity.

While this may seem like a lot of work you would need to put in some fair effort to make SQL DTS as parallel and load-balanced as the UBE approach. Plus with the UBE approach you are using standard tools and will be supported if you run into conversion problems.

Hope this helps.

Regards,
 
Thanks for all the responses. Keep them coming. My production database is 33GB and test is 41GB. I found the old XE cd's which are for UNIX but I see some NT pieces on the deployment server load. Will I need to re-order the XE CD to get a new Enterprise server loaded or can I use what I've got?

Patty
 
Hello,

I also of similar requiremnt, However, I also need to move my central objects. Whould you still recomend using R98403 ( I dont knwo if I can use any thing else).

Any advice will be of great help
 
YUP - you'll need CDs for WinTel to load the server side if you are also
changing from UNIX to Windows concurrent to your change from Oraple to MS
SQL.


Gregg Larkin
JDE System Administrator (CNC) / North America
Praxair, Inc.
 
Hi Sully

I'm surprised you're thinking of moving from Oracle to SQL Server in these times - however, a conversion from one database type to another is relatively simple.

First of all, ensure you use JDE to convert your data. This includes the central objects. You need to run a table copy from one datasource to another and every row will go through the data dictionary correctly - hence your data will convert cleanly.

You can use R98403 - or you can create your own Table Conversion scripts relatively simply to do this. I personally prefer to create my own scripts to copy data - it takes very little time if you use the CPYSCRIPT.DAT format.

Central objects should also be copied using OneWorld. DO NOT attempt to use some sort of external SQL copy program - it will seriously ruin your data, and you'll be looking for issues in months to come. I know, I had to rescue several customers that tried it that way !

If you need assistance - give me a shout. I'm in the midst of creating a whitepaper on converting AS/400 to SQL Server - and its an identical route for any database to any other database.

Hope that helps !
 
Jon

Thanks for the reply. The reason we are considering the move is that we were about to put out a lot of money for new UNIX servers as part of our disaster recovery stategy. OneWorld is only one of many applications that are run here. It makes sense to go with the same OS and database across the board. There are so many unanswered question when it comes to fusion and it's many years away. IT needs to be a lean, mean fighting machine and able to adapt to new technology quickly. If Larry says it must be Oracle then we will address it then, perhaps choosing another ERP vendor. SQL will be supported until at least 2013. Who knows what will happen after that. It's too far away to make plans.

Patty
 
I don't know how fast your servers/databases are, but with only 33Gb there is a good chance that they can be copied
using R98403 during the weekend.

That's even if you don't run multiple copies of R98403.

[ QUOTE ]
Thanks for all the responses. Keep them coming. My production database is 33GB and test is 41GB. Patty

[/ QUOTE ]
 
Just one comment I have about using R98403...
Make sure - especially with the size of DB you mentioned - that you monitor the transaction log filesize on the SQL Server side.

We dump transaction logs while running R98403 to prevent ours from growing - expecially when larger tables are being processed F0901/2 as examples.

Or you could make sure that you have truncate log on checkpoint turned on while doing this. Your SQL DBA should be able to provide you more info on this.

Time to run... tough guessing... but as an example: Dual 1.5G Proc with 2G Ram and a 7G database takes us just over 4 hours.

Hope this helps.
merk
 
Back
Top