Migrating from MS SQL to Oracle11G

Hi Folks,

We're just beginning to explore a migration from MS SQL 2000 (yes 2000, ugh) to Oracle 11G (probably on linux) for our JDE EnterpriseOne 8.0 installation and I'm looking for someone who has experience in this exercise.

We will be upgrading to JDE E1 9.0 in the new year and we want to minimize risks and our testing efforts where possible.

Does anyone have a name and/or reference material to help us plan the database change?

Cheers
Andrew

I'll trow my name in the ting as well 100's of other on the list here.

Not a very difficult exercise. You'll likely need more help with the upgrade than the database migration.

My suggestion would be to get the database migration done before the upgrade - one less thing to worry about. You can leave all the other components where they are.

Of course this is all said with minimal knowledge about your system.

You may also want to post your location so that where possible you might get a local capable resource. However with the Internet everyone is local.

You're not going to find a lot of reference material on this subject. You're best to search for info on migratating from AS400 to SQL server. The only real difference is that the source and target are different databases - however it's the same general process.

Colin

Hi Andrew

What you're asking for isn't actually too difficult - I've migrated platforms from AS/400 to SQL to Oracle and back again for many, many customers over the past 12 years or so.

OneWorld provides excellent tools to migrate from SQL Server to Oracle - in fact, I architected the very first migration from SQL to Oracle with the Digitial Equipment migration back on B731 !

However, moving the database platform from SQL to Oracle for OneWorld isn't the issue. The big issue is everything else that touches the data OUTSIDE of OneWorld. For example, if you have any 3rd party utilities, or business intelligence, or even FTP Transfers - all of these are going to have to be looked at, rewritten/reimplemented and changed. That is where the biggest risk to your project is going to come from.

Migrating large quantities of data need to be correctly ARCHITECTED. If you look on this board, you'll see many posts about how to move from SQL to Oracle. My view is that you should use OneWorld as much as possible to eliminate any issues with "null" values by having every row pass through the data dictionary. This can be very slow, however, if you run every table sequentially. Instead, you want to run as many in parallel as possible. Secondly, it is possible taking a large table (ie, the F0911) and splitting a single table up into many, many processes - transferring the data can be then architected to run almost as fast as the disks can handle !

Secondly, you want to also consider the effect of migrating the platform to Oracle now, and then going through almost the same testing effort to get to EnterpriseOne 9.0 ? Instead, you might want to perform both the upgrade AND the data conversion at the same time - especially since going from 8.0 to 9.0.1 will incur major database changes anyway. I know others might not recommend this, but the testing effort is going to be huge for both items - and can be reduced if correctly project managed if both are done at the same time.

Feel free to call me and discuss your requirements.

Hi Andrew,

The link below is to a whitepaper I wrote for Microsoft on doing AS/400's to SQL Server. The process for going SQL 2000 to Oracle 11G is basically the same. Just swap out JDB versions in your datasources. I done several of these between just about every platform combination and it really is a very straight-forward process.

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


If you are going 11G made sure to purchase the DB compression feature from Oracle. It will drop the size of your DB by over 60% and dramatically improve performance.

Feel free to give me a call if you would like to discuss further.

Thanks for the feedback guys.

It's Jon's point of testing the database migration and the E1 upgrade that we're trying to wrestle to the ground. We want to minimize testing and the upgrade time and the risks. At this point, we see the three goals at odds with one another.

Jon, do you have experience in combining a JDE E1 upgrade with a parallel db migration?

Colin, I'm in Halifax, Nova Scotia. I know a few folks in Syntax's Toronto office. Where are you based?

Paul, you've raised another point we're trying to address. The business doesn't really care what database we use - but they do care about performance. We've seen oracle's (expected) performance improvements for the 9.0, tools 8.97 webclient but we can't find any information on performance improvements relating to the database. Where should I start looking for performance improvement statistics if we go with 11g?

I grabbed your linked document and I'll definitely give it some time.

All, Your input is appreciated.

Cheers

I would say your DBA experience with Linux/Oracle compared to MS/SQL is your biggest concern. You could add a lot of risk to the upgrade if you don't know how to manage the DB properly.

Log management can be a major issue for some. IIRC, SQL manages logs by database and Oracle manages logs by server. That can play a major roll in some installations and may force a 2 database server installation on a small JDE install just so you don't have to carry the tran log of the DV and PY databases if you choose not to.

Both systems have +s and -s. Make sure you understand those fully so you don't get caught.

Side note, if MS2000 is running your business now, MS2008x64 will have no problems and should be less expensive that an Oracle installation. Keep in mind that no database will perform if the backend disk is not designed properly.

I would have to say I agree with Sean. Jumping from sql 2000 to sql 2005 requires testing, but isn't a huge deal. There are no data conversion issues. Jumping to a new OS and a new database is a big deal, unless your company already has the skill set to manage linux and Oracle. In general, Oracle is a more expensive database platform, so look at that factor as well.

Ditto on the Disk management. make sure you have very fast drives and from a san perspective, the more spindles the better. Having ten smaller disks rather than one big one will have a big impact on performance. Down the road, keep your eye out for solid state drives. Having your database cached on a large solid state drive will dramatically speed up your performance. That's one of the areas that we are looking at for our next gen database server.

Gregg Larkin, MBA
North American JDE Systems Engineer
Praxair Incorporated

The performance of the database is directly related to the infrastructure it's running on so trying to benchmark this is almost futile.

The truth is all the databases work well when they are tuned for the platform.

If you're moving from SQL Server 2000 to Oracle 11g then you're looking at 7 years of innovation so the comparison is easy - 11g will be faster. To do a proper comparison you would need to compare SQL Server 2008 and 11g.

How much data are you looking at converting? This does depend on how you plan the project.

Yes it's fairly simple to combine a platform migration with a upgrade. Somewhat depends on if you're farming this out or doing it yourself. Doing it yourself typically requires picking the low hanging fruit and going the easy (but longer) route.

Colin

P.S. I reside at the centre of the known universe (Toronto) and will work for Lobster and Scampi

Yeah, the performance benchmarking is very difficult to quantify and consequently it's hard to justify spending time and money on something where we can't demonstrate/estimate the value for the business. Yep, performance will be better but deciding between option x vs option y needs some cost/benefit evaluation.

We typically like to be self sufficient by the end of a project which means we typically do the work ourselves with consultants joining the team at key points to ensure we don't make too many blunders. Some blunders are good - they keep us on our toes.

Lobster season started just this month so there's lots to go around at a good price.

Off to do more research....(on databases, not lobsters)

Hi Andrew,

we migrated beginning this year our SQL2000 to Oracle 10g RAC but using the same OS for enterprise-, HTML- and deployment servers (Windows 2003). We are running Oracle RAC on HP Itanium. It took some time to have the POC ready and determine the right migration plan. We used Oracle SQL Developer for the migration from SQL to Oracle. The test effort was big as we had to test all JDE processes as well WM interfaces etc.
Let me know if you need more information.

cheers,

Adrian

Andrew,

a couple of late points to add.

1. For performance improvements, there's nothing you can do that will have more of an impact than a well architected I-O subsystem (disk). I've attached a sample report from the statistics we keep on some of our jobs. This custom job went from around 2 hours to 1/2 hour when we implemented an EMC SAN - RAID 1+0 configuration. We previously were using a Dell internal raid 10 setup. We also upgraded from Oracle 8.1 to 11g, and placed the Database on its own server (Win 2003 64bit). The two things that made the most difference were the I-O system and the extra memory that Win64 allowed us to give Oracle.

2. Regarding Database Compression option. Both Oracle and SQL Server have this as a Add-On option to their more expensive configurations. In Oracle's case its an add-on only to the Oracle Enterprise version - so its quite pricy if all you needed was Standard Edition. On the other hand, the following benchmarks have been documented with JDE.
- ~60% average space savings on disk AND in memory (data remains compressed in buffers until needed). Higher savings if using UniCode.
- ~1% performance penalty on inserts.
- ~2% performance penalty on updates/deletes.
- ~270% performance improvement (2.7 times faster!) on reads
SQL Server compression option reportedly shows similar numbers.

Hi Andrew

my apologies, I just got back from 2 week vacation - and I was banned from JDEList over Christmas and the New Year by my girlfriend !

Yes, I have plenty of experience with customers doing both the platform migration and the upgrade at the same time. In my opinion, although you're changing two significant pieces of your enterprise, they're both significant enough that they somewhat add the same risk together as if done separately. I think that if you attempted an upgrade and THEN did a platform migration, you'd have double the downtime to the users, twice the length of project and absolutely twice the testing.

The fact that you'll have to test EVERYTHING no matter which of the tasks you do first makes me feel that you might as well do both changes and test thoroughly. Maybe add a couple of iterations of test scripts for good measure.

ok - when it comes to performance of, say, Oracle over SQL Server, you're into some tricky ground. I've worked with a customer who hired the BEST Oracle DBA's in the country and we proved that just switching out the database doesn't always give you any immediate "boost" in speed. Certain applications run faster, others run slower. I'd say the fact you're implementing new hardware is going to provide more of a performance improvement. I'd say migrating to Oracle just is a more STABLE platform - and moving from one release of a database platform from a few years ago to something that is newer - then that would also give a better performance too.

Its hard to justify the performance - especially when you're dramatically going to change the platform. You're going to certainly see productivity tank initially as the users get used to the new web front end, for example, but eventually the users will get used to the new web client and you'll end up running a lot faster than before. Plus, all those E1 improvements will help.

E1 is traditionally the big bottleneck in the equation. Because its "all things to all men" (platform independent) - it means that its code is "chatty" and can be tricky to tune. But the code is the denominator. Adding hardware and improving database speed helps, but improving the code is certainly the biggest gain. And, over the past 10 years, JDE, Peoplesoft and Oracle have been doing just that.

Let me know if I can help with any more info.

Hi Folks,

That's all great stuff! Thanks for all your time and input.

As a part of our due diligence, we've contacted MS to give us some help on the database in a JDE E1 installation and they've been helpful.

From a technical perspective, there are pros/cons with either Oracle or MS so the evaluation will be in other factors: ie skills, training, costs etc.

We haven't made our final decision yet but we're now leaning toward staying with SQL but upgrading to 2005/2008 with 64 bit on new hardware.

Cheers
Andrew