As you likely know, anyone in your organization working with Prophet 21® should handle its underlying SQL database with extreme care. One of the best ways to protect it is to create more than one P21™ database for different purposes. In this post, I’ll look at how multiple, separate P21 databases work in practice with four common scenarios.
Handle with Care
Wholesale distributors’ P21 systems contain some of the most vital information in their businesses. Should anything go wrong with your P21, you likely couldn’t operate very long. Many distributors would bleed money every minute they’re down.
By creating separate databases, you cordon off activities that could impact your central P21 database — the one that moment-to-moment tracks orders and inventory, as well as any associated information. This is your organization’s core repository of truth. Mess with it and you can no longer trust your own data, which is deeply troubling.
Working with multiple P21 databases in segregated environments ensures that no matter what happens you always have the raw foundational data to go back to start again.
Here are four common scenarios that demand creating copies of your P21.
1. Testing and Play
Your P21 system most likely came with a database specifically intended for testing. It’s ideal for confirming changes or that new scripts are 100% safe before pushing them into Production. However, some P21 administrators aggressively lock down this testing database and restrict which users can access it.
This approach has important shortcomings to consider. Given how difficult it can be to revert changes on the Production database, it’s essential to thoroughly assess what a change does or does not do. But even if a change won’t negatively impact the database, it still might not successfully achieve the purpose of the change. Allowing other users to test scripts, or at least preview their effects, can ensure that the changes that are made are worthwhile and effective.
Moreover, if you’re too restrictive with a database where mistakes are supposed to occur, you might be missing a chance to discover better ways to use P21 data. There is value in playing around with test data, to see what different scripts can and cannot do. Epicor, the developers behind P21, make sure there is a test database in every P21 system for good reason — don’t forget to use it.
Anyone developing in P21 typically insists upon using a separate dev environment, usually a smaller subset of the database that still accurately reflects the database. Usually a developer works with it on their machine, where they have all the tools they need. Essentially, it’s a safe space for developers to code and write new scripts that until perfected could otherwise wreak havoc on your primary P21 system.
Although the two preceding scenarios are standard IT best practice, many organizations using P21 may want to generate snapshots of their databases at specific points in time, like month’s end or year’s end. Some may see this as an antiquated approach, but these snapshots help some organizations analyze information, like inventory at year-end, or for month-end accounting, with data that remains accurate and unchanging. Third-party tools also sometimes pull a separate copy of the database at certain intervals you determine and re-index it so they can generate reports faster and in different ways. Running a script across these databases or cleaning up the data can occur without affecting the rest of the operation.
4. Backup and Disaster Recovery
The above scenarios all demonstrate why your should use separate copies in order to protect the live Production database. But there is another worst-case scenario you need to consider: should anything damage or prevent access to the primary P21 system — fire, flood, natural disaster, negligent or malicious employee, data corruption, ransomware, etc. — you need a current backup safely stored separately. We live by the 3-2-1 Rule of Backup: Have at least three copies of your data; Store the copies on two different media; Keep one backup copy offsite. (Read about The Single Most Common Mistake in Data Backup Processes.)
Practically speaking, that requires you to make at minimum two additional copies of your P21 Production environment: one updated very regularly (every 15 minutes is common) stored locally on a different server, and a second copy updated daily and kept offsite, likely in a secure cloud service.
Talk to an Expert
P21 is a powerful ERP, but as with any complex machinery, tinkering can cause breakdowns very quickly. Before you start making any changes to improve how your P21 works, you need to know what you’re doing. Even the most seasoned experts can have an off day or overlook some crucial detail and make a mistake — which is why it’s never a bad decision to work with a backup.