Migrating Database

Hello,

My company is looking to upgrade our SQL 2000 server platform over to SQL2005 or Oracle 10g. (We're still debating the long term solution)

Do you guys know of any documentation for the application configuration process for pointing JDE to a new server/db?
Another question - Should I treat the new database as a new JDE install with the setup disks? Any docs on loading objects for the db would be helpful too.

This will be staged in a production environment so I'd like to get it done over a weekend in case something goes wrong.

Thank you,

Josh

JDE - ERP8.0 SP23_U1
DB - MS SQL Server 2000
Platform - Windows2k3

I'm not sure what level your CNC understanding is, so please bear with me if I am starting at too low a level ...

1) It is possible to move your data to a new database server, but keep your your existing Enterprise Server to run your UBEs and business functions.

2) You could install your new server to be both a database and application server.

3) In either case, the new server must be 32-bit, and not 64-bit. Only 32-bit software is supported for ERP 8. If you were on a later release, you could use a 64-bit database, but you would still need a 32-bit application server.

4) Your Service Pack level (23_U1) is high enough for SQL Server 2005 support, so whatever you decide for your database is supported with your existing software.

There are 2 different approaches, depending on which database you decide on, with an additional twist depending on whether or not you keep your existing Enterprise Server as suggested above.

If you decide on SQL Server 2005, then migrating the data will be a snap, and is best done using the SQL database tools. If you go with Oracle, then you will likely have to use the R98403 UBE to copy data from the SQL 2000 databases to the new Oracle database. This could take a very long time depending on the volume of your data.

In contrast a SQL 2005 migration is almost as simple as detaching the database from SQL 2000, and reattaching it to SQL 2005. There's a bit more to it than that (recreating SQL logins, aligning the old user IDs with the new ones, updating the statistics, etc.), of course, but migrating to SQL 2005 will take a small fraction of the time it will take to migrate to Oracle.

This should not discourage you, however, as there are any number of good reasons to move to Oracle (company standard, existing expertise, etc). I just wanted you to know the comparative level of effort involved.

From the ERP 8 side, let's break it down into different cases ...

... continuing from above:

Case 1: New DB server, existing Enterprise Server, existing environments
Case 2: New DB server, existing Enterprise Server, new environment for testing
Case 3: New DB server with application server, existing environment
Case 4: New DB server with application server, new environment

Case 1 - The simplest case. When you migrate the data, make sure the user names and passwords for PRODDTA, PD7334, etc. are the same on both servers. After that, you only have to modify the existing data sources to point to the new DB server for whichever environments you moved over. For example, if you moved the PY7334 environment, including business data and the path code, then you'd modify the data sources for Business Data - CRP, Control Tables - CRP, Central Objects - PY7334, and Versions - PY7334. Don't forget to modify them in both the System and the Server Map. If you migrate to SQL 2005, you'll have to install the SQL Native Client on the Enterprise Server and your test clients, and modify the ODBC data sources accordingly. If you migrate to Oracle, you'll have to install the Oracle client connection software to the same places, but eliminate the ODBC entries since Oracle doesn't use them.

Case 2 - Migrate the data, and create new data sources for both System and Server Map. If you migrate to Oracle, you'll have to reverse that order, since you'll need the new data sources as the targets when you run R98403 to migrate the data. Other parts of Case 1, talking about installing the database client software and modifying ODBC entries still apply. There are a few white papers around, at least one each on Knowledge Garden and JDETips, on how to create a new environment.

Case 3 - This time, you will need to install the Enterprise Server CD. Install just a minimal set of files as you will be copying the most current set from your existing Enterprise Server. You'll need to create an "Add A Server" Installation Plan (directions can be found in the installation manual) to define the new Enterprise Server. I suppose you could do it manually, but I think it's easier to run a plan through Installation Workbench. You'll also need to create a new database as the Server Map for the new Enterprise Server. Since the System database is likely still on SQL 2000 initially, you'll have to install the database client connection software on the original Enterprise Server, and create ODBC entries for SQL 2005.

Case 4 - All the work of Cases 2 and 3 together.

These are merely sketches meant to give you an idea of what the process will involve, whichever scenario you choose. Some of the work (especially environment creation) can be done in advance of the data migration. While you may wish to identify the new data server to ERP 8, it is not necessary to do so if it will only be a database server.

I think Ken has documented some very good points - and has covered many of the options. Migrating the JDE database engine isn't that difficult a task and can often be done with minimal impact to the end users PROVIDING, of course, that one performs a LOT of planning and testing ahead of the actual migration !

The biggest concern isn't whether OneWorld can work with the database - or that whether the custom JDE applications work at all, it is whether there are any ancillary applications OUTSIDE of JDE that could be affected - for example, FTP data transfers into the Z tables, or perhaps 3rd party reporting tools. It is my experience that these applications cause the largest concern because they have to be tested individually.

I have migrated many customers from one database platform to another, and it is certainly recommended that you engage a consultant to help with your migration. For example, it is IMPERATIVE that you utilize the internal JDE datasources to transfer between different database types - and NOT to use any external database tools, primarily because JDE utilizes the databases differently between, say, Oracle and SQL Server - ie, nulls and blanks are often used differently between the datasource types.

Hope that helps. Good luck with your database migration.

This has been helpful information and I thank you both. I have personally not done a database migration, however our DBA's on staff have for other applications.

My one concern as far as external applications that are tied to JDE's database would be our CPM application "OutlookSoft" (aka SAP Business Planning & Consolidation". This application also uses SQL Server 2000 and I'm not sure what impact changing the target database to SQL 2005 will have.

Ken,

Case 1 will most likely be the route I take for this project. I intend on keeping the app version 8.0 on the current/separate enterprise server. The new db server will have SQL2005 loaded. I plan on migrating the DV/PY/PD databases/environments to this server in advance for testing. I can then change all the data sources for DV/PY prior to changing the database sources for PD. Does this sound like a good plan?

Are there any other application changes I should consider in order for JDE to pickup on the new server/db?

Thank you again for taking the time to write such an informative post. It's appreciated.

This is what I've done for other clients. Always try it out with DV/PY before you affect the Production environment. You should be able to tell right away whether or not it's going to work, but I'd recommend at least a week or 2 of testing.

As for the connections from other applications, the key will be how they connect. Do they use ODBC, or their own internal method to get to the database? You'll at least have to point them to the new server.

One other consideration that I forgot about with Xe, are you using the HTML client at all? You'll need the new JDBC driver for that and a modification to JDBJ.INI if you do.

Last note, since you seem to be headed towards SQL 2005, request the latest cumulative update from Microsoft. This goes on top of SQL 2005 SP2, and the current one is #9. You have to at least get cumulative udpate 6. It may not be easy to find, so here is the link to request it.

Ken,

Thanks again for your assistance with this. I just had a few questions.

[ QUOTE ]
"If you decide on SQL Server 2005, then migrating the data will be a snap, and is best done using the SQL database tools."

[/ QUOTE ]

You mentioned the SQL db tools are the way to go when migrating the data. Can you be more specific as far as the tool name and what the process looks like? Does Microsoft have a white paper on this some where I can read? The DBA will handle this but for my knowledge I'd like to be able to do this myself if the need arises.

[ QUOTE ]
If you migrate to SQL 2005, you'll have to install the SQL Native Client on the Enterprise Server and your test clients, and modify the ODBC data sources accordingly

[/ QUOTE ]

Are you saying I need to install the SQL 2005 native client alongside the SQL 2000 on the Enterprise server? Can these two run together on the same box?

[ QUOTE ]
One other consideration that I forgot about with Xe, are you using the HTML client at all? You'll need the new JDBC driver for that and a modification to JDBJ.INI if you do.

[/ QUOTE ]

We're not using HTML at the moment but I am considering this for the near future. Can you expand on the driver and .ini change necessary?

Thank you,

Josh

[ QUOTE ]
[ QUOTE ]
"If you decide on SQL Server 2005, then migrating the data will be a snap, and is best done using the SQL database tools."

[/ QUOTE ]

You mentioned the SQL db tools are the way to go when migrating the data. Can you be more specific as far as the tool name and what the process looks like? Does Microsoft have a white paper on this some where I can read? The DBA will handle this but for my knowledge I'd like to be able to do this myself if the need arises.

[/ QUOTE ]

If you can somehow get the SQL Server 2005 Books On Line installed (part of the SQL 2005 installation), you can read Microsoft's recommended approaches to migrating a SQL 2000 database to SQL 2005.

The 2 most common approaches are the "Copy Database" wizard, or simply detaching the database from SQL 2000 and attaching it under SQL 2005. If you do the latter, Books On Line will tell you to Update Statistics for the entire database. The statistics update will actually be the longest part of the whole operation.

In either method, you will have to create the JDE SQL user IDs and matching passwords (PRODDTA, JDE, SYS7334, etc., plus any others you may have created custom). Because you are creating new user IDs, the internal ID number used by SQL needs to match the ID numbers in the database coming over. This is fixed using the procedure sp_change_users_login with the Auto_Fix parameter. You might want to create a script for this since you'll have to run the procedure for each user on each database.

At some point, after you are sure things are working, you should change the compatibility level of the database from 80 (SQL 2000) to 90 (SQL 2005). And keep a copy of the original handy if you need to reattach to SQL 2000.

Again, using these built-in SQL Server tools will take a fraction of the time it would take to use R98403 to copy a database over table by table, record by record.

[ QUOTE ]
[ QUOTE ]
If you migrate to SQL 2005, you'll have to install the SQL Native Client on the Enterprise Server and your test clients, and modify the ODBC data sources accordingly

[/ QUOTE ]

Are you saying I need to install the SQL 2005 native client alongside the SQL 2000 on the Enterprise server? Can these two run together on the same box?

[/ QUOTE ]

Yes. The only noticeable thing you will see after installing the SQL Native Client is that there is another entry in the ODBC drivers list, right above "SQL Server," which you will use when creating ODBC data sources to connect to SQL 2005.

[ QUOTE ]
[ QUOTE ]
One other consideration that I forgot about with Xe, are you using the HTML client at all? You'll need the new JDBC driver for that and a modification to JDBJ.INI if you do.

[/ QUOTE ]

We're not using HTML at the moment but I am considering this for the near future. Can you expand on the driver and .ini change necessary?

[/ QUOTE ]

When you install your JAS server, you will see that the instructions tell you to download JDBC drivers for your database and configure your JAS server to use them. For SQL 2000, the JDBC driver consists of 3 files. For SQL 2005, it's only 1 file.

In both cases, configuring the JAS server for the right JDBC driver is done in the JDBJ.INI file. Either in the current instructions, or in related white papers, you will see that one line in the INI file tells the JAS server which SQL Server driver to use.

For SQL 2000, this line reads:
SQLSERVER=com.microsoft.jdbc.sqlserver.SQLServerDriver

For SQL 2005, this line reads:
SQLSERVER=com.microsoft.sqlserver.jdbc.SQLServerDriver

You see that the difference is only reversing two parts of the line, but makes all the difference in which JDBC driver that the JAS server will look for.

Josh...DB conversions/upgrades are processes that don't need to be performed often. My take on your questions is that you are unfamiliar with the process and perhaps SQL 2005. I'd suggest getting help with the migration, as that's a one time thing, spend money on the admin staff to learn 2005, and then just deal with the DB after the migration.