How to approach a multi-phased multi-warehouse upgrade for RedPrairieTM Upgrade?

Introduction

RedPrairie WMS allows for having multiple warehouses in the same instance. This is a powerful feature and I always encourage the customers to consider this over the alternative where you will have a single RedPrairie instance for each warehouse. When you have a single instance the overall footprint improves significantly because for each instance you typically have:

Development, test, production, etc.

Archive environment (Do not go down the path of having a single archive instance for multiple online instances. You shall regret it)

So you can imagine how having fewer instances makes it simpler to support such an environment from IT perspective. You will also have a single code-base and a single environment to patch.

Problem

The initial go-live is done and now it is time for upgrade. At this time if you can afford to upgrade all of the warehouses at once then it is great but if you want to upgrade warehouse at a time, now we have some challenges. At a high level such a project will go as follows:

• Assume old environment is OLDE and has multiple warehouses
• Assume the warehouse to upgrade is WH1
• Install the new version. Lets call this NEWE
• Migrate the warehouse WH1 from OLDE to NEWE
• Setup a temporary environment which has same version has OLDE, lets call it TMPE
• Copy the data for WH1 from OLDE to TMPE
• Upgrade the TMPE environment to the same version as NEWE
• Copy data for WH1 from TMPE to NEWE
Refer to this document to understand that creating these new environments may not be such a big deal
• Go live with warehouse WH1 in NEWE while OLDE is still running the other warehouses
• At some later time do the same for the next warehouse

Even after following this long and arduous process, we will have some basic challenges:

• Can the host send transactions to multiple instances?
This implies that host will send some transactions to OLDE and some to NEWE. This is generally not a huge problem if you are using an ERP like SAP, Oracle, JDE, etc.
• What about the sequence numbers when the second warehouse merges with NEWE
This is the main problem. Some primary keys that are based on sequences, like wrkref, cmbcod, ctnnum, dlytrn_id, trlract_id, ship_id, ship_line_id do not have wh_id as part of the primary key. This implies that when second warehouse will merge we will have an issue with this data. Never fall for "the online data will be cleared" because that will just push the issue to the archive instance.

Some possible solutions may be:

• Do not upgrade history
This option may look good in a conference room but it is really quite ugly. This suggests that the warehouse users will go to the OLDE and Archive for OLDE to look at data before a certain date and go to NEWE for newer data. Just imagine the next upgrade now. You will always need these OLDE environments around because some industries require you to be able to access data for several years.
• Utilize the sequence prefix along with base-36 sequences
Even if you did not use base-36 sequences in old environment, set them up in NEWE. In NEWE use a different prefix from OLDE, for example A0 for wrkref, B0 for cmbcod etc. (I generally recommend not to break the sorting rules so if you are starting fresh then A and B prefixes are ok but if you already have W as the prefix then use X so that old data and new data sorts properly. Never assume that RedPrairie will always sort by date - it may [and should] be sorting by this sequence ) With base-36 you will have plenty of room to expand. Now when the next warehouse is merged the old data will simply load in and not cause any primary key issues. Also there will not be any issue with the archive environment.
Now when we do the next upgrade we can use A1 prefix and so on. You will have a lot of room for future upgrades with this approach.

Another question you may be asking is if the approach described above has to be that painful for bringing one warehouse at a time. Notice the concept of a duplicate environment, copying the data there, then removing all warehouses except yours, then upgrading it etc.
Considering the current solution-set where the "dbupgrade" process upgrades the whole database that is being referenced by the current environment that is the only option. The upgrade scripts merge the concept of schema changes and data migration.These scripts also change the whole database, i.e. do not selectively run for certain warehouses. When we are bringing in the second warehouse into NEWE ideally:

• the process could have ignored the schema changes
• Only bring data from OLDE to NEWE and at the same time change the data definition
• When bringing in the data, bring in selected rows

I have not seen the new RedPrairie solutions in this regard (like environment manager) so I cannot comment on them but if the solution works by pointing to an environment that may be a different version and load subset of data to an environment with a newer version and performs well then it would be a good solution otherwise you may have to invest in developing it yourself. Contact me if you want to consider developing such a solution.