Upgrading a RedPrairie(tm) Environment - The right way

How should we approach a RedPrairie^tm WMS (DCS) upgrade - from a technical perspective?  Should I put the CD in and let the installer's upgrade do the magic?  In this post I will explain my perspective on this.  My approach is as follows:

1. Never use the RedPrairie^tm installer's upgrade option
2. Always do a new install into an empty database
3. Then use the RedPrairie provided upgrade script.

Why not use the installer's upgrade option?

This is a general suggestion for all products that when we are approaching an upgrade, at that time we must keep in mind the possibility that the upgrade may fail.  If and when it fails, we want to be able to quickly fall back to the previous version.  Upgrades also take some time and during that time we do not want the environment to be unavailable.  When troubleshooting an upgrade, it is important to understand if the issue is with the upgrade or with the install itself.

How are RedPrairie^tm upgrade scripts organized?

Every sub-product in RedPrairie^tm; for example MOCA, MCS, SAL, DCS, etc. has a "db" sub-folder and within that we have following sub-folders:

Folder	Description
ddl	DDL scripts for creating tables, indexes, sequences
data	mload and slexp files
Upgrade	This folder has multiple sub-folders based on versions that contain scripts to upgrade the database

When we are doing a new install the scripts from "ddl" folders are used to create the objects.  When we are doing an upgrade the scripts from the "upgrade" folder are used.  So we definitely have two separate code paths.

If you look at this folder, you will also notice that by definition you should be able to upgrade from any version to the latest version.  So if someone is trying to go from 2005 to 2012.1.4 - that should work.  You should not have to go through intermediate versions.  Unfortunately sometimes RedPrairie^tm does not keep them straight so we run into issues.  For example what could happen is that say 2008 to 2009.1.4 upgrade scripts had an issue - but understand that RedPrairie^tm product group is working on 2010 version at that time as well.  So what should have happened is that the fix to the upgrade scripts should have been merged to with the active development branch - but it is possible that it was not done.  In such cases you may have to go through intermediate versions - but from an architecture standpoint that was not needed.

What is the "RedPrairie^tm provided upgrade script"?

If you go to the RedPrairie environment folder (parent directory of %MOCADIR%), it has a folder called "install" and then a subfolder "database".  In version prior to 2010, there was a "dupgrade.pl".  Now we have a "dbupgrade.jar".  This script is responsible for going through all the sub-products and running all the upgrade scripts.  Those upgrade scripts will create new tables, modify existing tables, and change data as needed.  The installer also runs this same script to do the upgrade.

How does RedPrairie^tm determine the current version?

Every sub-product has a special table called [product]_dbversion; for example moca_dversion, dcs_dbversion, etc.  This table has a single column and single row that has the exact version that this database was create with or upgraded to.  The upgrade scripts look at this version and compare to the current version (as reported by, for example, "list library versions") and figures out which upgrade directories to process.  When it is done with the upgrade process it updates this table.

Why install to an empty database first?

As we can see that upgrade is a different code path, installation will most likely have a better chance of success than an upgrade.  Then we will have a clean database to compare an upgraded environment to.  For example in one case when I upgraded from 2008 to 2012.1.4 there were over 1000 differences between that database and a clean database.  If I did not have a clean database to compare to, I would be constantly fighting battles to figure out what is going on.  Before any testing starts we should compare the upgraded schema to this clean schema and make sure it is ok.

I recommend that this pristine environment is kept permanently.  This provides a place where we can check if the issue is due to a customization or is in standard code.  If we are able to reproduce an issue in the standard code it will be much easier to obtain support from RedPrairie^tm.

So the exact steps are:

1. Create an empty database.  I always call them with embedded version#, e.g. PRSWM201214.  "PRS" stands for pristine.
2. Run the installer and choose "new" install.  Name it differently from the other environment on this server.
3. Install this into the newly created database.
4. Load the base data as well
5. Check to make sure the instance looks good
6. Now change the "registry" and point the "database" settings to the database that you want to upgrade.
7. Run the dbuprade from the command line
1. If version is less than 2010, run "perl -S dbupgrade.pl"
2. If version is after 2010, run "java -jar dbupgrade.jar"
8. Change the port in the registry if you want the existing clients to not change their connection.

You can choose some other variants to this approach as well, e.g.

• In step #6, do not point to exactly the same database but a copy of it.  This way you could keep both the old environment and the new environment running in parallel.  In this case in step #8 if port in the  registry for the new environment is changed, also change it in the old environment.
• You could install it twice one to keep the pristine environment running and one to upgrade.  This may not be necessary because it is very easy to create new environment from the same install - I will explain that in a separate blog post.

Conclusion:

RedPrairie^tm solutions are very intuitive once we understand the concepts and details behind them.  By taking this simple approach the task of upgrading will become simpler and more importantly if when the first attempts to upgrade fail we would not have created a mess for existing environments.