Moving to SQL Server from Oracle - Questions

[Larry_Jones]

Given Oracle's recent licensing changes for Oracle Standard Edition (beginning with 12.xx) we've recently finished an exercise comparing Enterprise Edition Features and Costs between Oracle and SQL Server.
Net result of that is that we are going to move to SQL Server EE (2014).

We know JDE has no issue running SQL Server. But ... a lot of unknown territory making this move for us.

Some background:

• Our company is already a SQL Server shop - we have a number of applications that have run on SQL Server for many years. But we've run JDE and a few other apps on Oracle DB for 16+ years.
• JDE Production Database is about 500GB.
• Concurrent user load about 100-120.
• Currently running 9.1, Tools 9.1.5
• We have a number of interfaces between JDE (Oracle database) and other applications.
• We have an extensive reports library (Business Objects / Crystal Reports)
• All servers are virtualized except for the database server

Here's some of the questions/concerns that we have:

1. Given that our migration process is anticipated to take several (3+) months how should we architect/manage the conversion environment?

• As a separate environment on the existing Deployment and Enterprise Servers (System tables would still be in Oracle until final move)?
• Or as new environment on new server instances that are completely SQL Server based?
• Or ...

2. For those of you who have migrated from one database vendor to another how did you accomplish the move in a timely manner? Multiple instances of R98403 running?

3. Special considerations for JDE and SQL Server.

• Do we need to install new platform pack?
• Do you really have to use ODBC rather than go native?
• Deployment server considerations?
• Other?

4. Any experience or things to do or watch out for you'd care to share.


Thanks In Advance!

 

[altquark]

Moving JDE from one platform to another isn't a big issue - JDE works really well on SQL Server just as it does on Oracle or iSeries. However, your biggest concerns should be the integrations into the database outside of JDE. If you have Business Intelligence or other such integrations, you want to make sure that they are all absolutely working properly on your new Enterprise.

To absolutely minimize your risk, I would set up a separate environment in your existing enterprise with the SQL Server - ie, System Tables are the final thing to change for the move. You will need to have your existing environment "see" the SQL Server database - because ALL copying MUST be done through E1. Don't be foolish and think you can export data through some sort of third party utility. ALL your data will be copied using Table Conversions.

Personally, I create my own table conversion scripts using the old "CPYTBLE" scripting method - but R98403 is also a way to migrate if you want. I think my way is superior, and I partition large tables and run them in parallel. I never EVER end up copying sequentially - You're going to be running a bunch of table conversions in parallel. I've never had a full production database take longer than 8 hours - it just takes time and hardware to set up and run a number of iterations. 500Gb shouldn't be too difficult to get into a decent "window".

You should probably install the E1 Platform pack on the new server, as it sets up the correct user ID's and tablespace files etc.

Yes you need to use ODBC for the E1 Services and Development Client. Web will use JDBC.

The deployment server planner environment must be configured correctly by the time you complete the install. You'll want to be able to continue to install ESU's etc.

My suggestion is that you create conversion environments to "test" things out - but you migrate the original JDE environments by changing datasource definitions. IE, when you go live, you go live with PD910 using "Business Data - PROD". Don't go live on "strange" environments !

The failback will be to modify the datasource definitions back to oracle if necessary.

Hire someone whos done this before.

 

[craig_welton]

One thing to recommend for JDE on SQL Server .... turn on RCSI!

 

[Chan Rana]

I second Craig to turn on RCSI on SQL server. We had to do it in recent past. We have DB virtualized and haven't had any issues. We recently had the web servers virtualized with load balance, having some issues on the heap memory but should be fine once we figure out the best settings.
You might also have to look into the OS/DB specific scripts used in JDE for any file or table movements like FTP,COPY etc.

I would also have a separate install to evaluate system and interface impact.

Chan

 

[Soumen]

"2. For those of you who have migrated from one database vendor to another how did you accomplish the move in a timely manner? Multiple instances of R98403 running?"

While R98403 is an option but I believe it would take you a lot of time. I have heard about this product "Golden Gate" for the same purpose. But then again you might have to take the cost factor into account because Golden Gate although may be very sophisticated but could burn a hole in the budget.

Just my 2 cents!

 

[altquark]

You really don't need Goldengate for moving platform. I've heard of some JDE consulting firms using it to create a kind of "replication" for upgrades (a little like co-existence) - but believe me, I'd be VERY wary about using it (and its VERY, VERY expensive).

R98403 will take some time and you'll likely not be able to get it to correctly process in the batch window you need.

Creating your own CPYTBLE scripts and setting them up to run them in parallel to process in a batch window (say 12 hours) can be done within 2-3 weeks.

 

[jdelisths]

We successfully migrated clients from iSeries to SQL Server using SSIS (SQL Server Integration Services). It is much, much faster than R98403.

 

[Larry_Jones]

Thanks everyone for the response to date. A couple of questions on these:

Jon:
If I understand you correctly you advocate setting up new JDE servers with the standard environments and on the old (Oracle DB based) systems create a new environment (Data source, etc) that points to the new SQL Server Database(s) for purposes of data migration. You do NOT advocate attempting to use the same Enterprise server to house old and new, correct?
What is the CPYTBLE method you refer to? Is that a AS/400 command?

Craig / Chan:
Thanks for the heads up on RCSI. Will definitely apply and configure Tempdb correspondingly.

All: Agreed that R98403 would not be optimal. Still looking for more suggestions on mass conversion of data that requires minimal amount of work (lazy me).

Thanks All and keep the suggestions coming!

 

[altquark]

If I understand you correctly you advocate setting up new JDE servers with the standard environments and on the old (Oracle DB based) systems create a new environment (Data source, etc) that points to the new SQL Server Database(s) for purposes of data migration. You do NOT advocate attempting to use the same Enterprise server to house old and new, correct?

correct. You shouldn't have SQL and Oracle running on the same piece of hardware (even if it IS virtualized !)

What is the CPYTBLE method you refer to? Is that a AS/400 command?

Its a method to script copying of tables into the Table Conversion Aid. Its been around for donkeys years (and unfortunately, so have I) - but theres no documentation anymore. JDE thought that it was too powerful for the "average" customer (!). You might want to do a quick search on jdelist to see how many times I've mentioned "CPYTBLE" in the past... (!)

I'll admit, I've often used the lack of documented knowledge to get myself onto projects to ensure guaranteed platform migrations such as the one you've mentioned many times. Its how I earn a living. As I stated, it takes approximately 2-3 weeks of billable time to create a full migration of an entire E1 architecture from one platform to another (provided the standard environments are used. Additional "custom" environments and/or pathcodes cost a little more time for each)

Alternatively you could run R98403 yourself....

 

[ice_cube210]

Hi Larry,

To add to all the other valuable points.

- Yes install the platform pack on the new SQL Server. There are bunch of JDE supplied stored procedures etc ( I haven't spent time figuring out what exactly they do), but apart from that the PP will also create all the required (and some not required) schemas , logins etc.

- Setup a new environment , that will use the SQL datasources , install the SQL Native Client and configure ODBCs (32 bit) on your Enterprise Servers.

- R98403 is obviously the easiest route , but not necessarily the fastest route . Creating custom TCs like Jon suggested give you the ability to split up big tables into multiple runs. You only need to do this for your biggest 4-5 tables , the rest can be done using the standard R98403 type of UBEs

You can also look at R98403B , it has fewer options in the PO and simpler in my opinion (Its true benefit is the bulk copy and that it creates only the PK and unique keys first , does the data copy and then creates the remaining indices , this would result in faster copies for bigger tables, but I am not sure if it does this when going across different datasource types) .

R98403A can also be used if you want to generate an empty table first in the target datasource and then copy in append mode (useful if you want to change default properties of the table like move it to a different data group that might be on a different drive etc).

The DISK IO is probably going to be the bottle neck as am sure your new servers a nice and beefy when it comes to CPU and Memory, so what ever optimization you can do there will result in an overall faster conversion process.

You can run R98403B etc on your Windows Enterprise Server that can access both Oracle and SQL Server , and you can run multiple instances and stack them up and throttle them using the job queues etc.

Some general SQL Server tips you might already be familiar with since you have other SQL Server DBs

- Put data and log files on separate drives
- put tempdb on a separate drive and set tempdb to use multiple files (N-1 at least , where N is number of CPUs available to SQL Server). Put tempdb on a SSD drive if possible
- Set SQL Server MAX memory parameter (The default setting is 2TB , and this makes SQL Server consume all available memory and release back to OS when needed.
- Keep the DB in simple recovery mode during the conversion process., to keep those transaction logs in check and also speed up your process. Change to Full Recovery mode once your conversion is done.

 

[shearerj]

Isn't there also something about case sensitivity on this specific move? Oracle is case sensitive, SQL and iSeries are not. The practical impact when doing a conversion is that valid unique constraints from an Oracle perspective are violated when you move to SQL/iSeries. This would require cleanup on the Oracle side before the move.

 

[altquark]

Or you could just turn on case sensitivity on the new PRODCTL/PRODDTA SQL Server datasources.

ALTER DATABASE JDE_PRODUCTION COLLATE SQL_Latin1_General_CP1_CS_AS

To turn it back :

alter database JDE_PRODUCTION collate Latin1_General_CI_AI

 

[shearerj]

Good to know. Thanks Jon.

 

[Larry_Jones]

Thanks Ice Cube - good stuff

 

[Larry_Jones]

Once of the nice side benefits for us will be getting rid of case sensitivity ... therefore I think we'll deal with any constraint issue as they occur.