This is the 3rd installment in a four-part series on the concepts archiving and purging. The first article was an introduction to purging and archiving and the second article described what to purge and archive. This article will focus on best practices in archiving and purging.

Like any project, there are predictable phases that you expect. From planning through execution, a wide team may need to be included. A purging and archiving project is not solely a technical exercise and the staffing will be similar, if smaller, than an ERP project.


  • Identify the objectives for the work.
  • Define the scope of the work.
  • Recruit a project team.
  • Create a project plan.

With clear direction for the project, it will be easier to define the scope and team members for the project. Frequently, the objective is reducing the data set to improve performance or hasten a data migration during an upgrade.

The scope of the project will include the date threshold for archiving and the subject areas for the data. For example, you may choose to keep 3 years of data in production and 4 years of data in an archive. You may choose to begin with a limited scope project that provides the greatest impact. For example, tables related to sales orders and general ledger usually contain the greatest quantity of data. It is also important to determine if data resides in ancillary systems or in custom tables. When considering the scope, it is important to identify the Data Retention Policy which may dictate when data may be archived and purged. Archiving is simply moving data from production to a separate storage location—you still have the data—so it may not be covered by a Data Retention Policy.

Once you establish the objective and scope you can recruit the team. Enlist the help of the legal team to ensure the objectives meet statutory requirements. Next, you will need to gain the help of people that are familiar with the business data in the subject areas. In some organizations, there may be Business Analysts (BA’s) that perform this role, or you may need to engage with the business units directly such as Accounting. Additionally, you will need technical resources for setting up/configuring servers, databases and the ERP system.

Sample project team:

  • Project Manager
  • DBA
  • Network Engineer
  • ERP Business Analysts (number will vary by scope and skill)
  • Business Users (number will vary by scope and skill)
  • Legal liaison

Now that you have the objectives, scope and a project team you can create a project plan. Things to include:

  • Evaluate technical requirements and schedule subsequent work as necessary.
  • Documenting detailed archiving/purging requirements.
  • Configuring the necessary systems and test data.
  • Establish a base line of the data condition before the archiving project.
  • Define a testing plan.
  • Determine when the archiving may be processed in production.
  • Assign resources to tasks.

During planning, it must be determined where the archived data will be stored, assuming it is not a simple purging activity.  In the pre-planning stages, there may already be an outline for moving to new equipment or using older equipment for the data. After the assessment is completed, a separate IT project may be commissioned to get the technical environment ready.

Documenting detailed archiving requirements should identify all the tables to be included as well as the selection criteria for the data. Since the system is configured by subject areas, it is best to document the requirements by module; however, the conditions may need to evaluate data in other modules as well. For example, you may not want to archive a sales order if the related invoice remains open. The requirements should also identify how old the data must be to be considered for archiving and purging.

Once the requirements are defined you are ready to configure the systems. This will include the archiving system as well as the ERP system. The archiving system will move the data from the source database to a target (archive) database. The ERP system will need to be configured so that users may choose an environment that contains the archived data. A key point about the archive environment—you are not archiving master data at this point in a project because you must complete the transactional data first. You will need to set-up the archiving environment with read access to production for master data. This helps ensure the data is suitable for business users. Security will need to be configured for the related databases as well. It is highly recommended that you update the CRP environment with a fresh copy from production so that the testing results match what is expected in production.

To demonstrate how the objectives have been met, you need to document the current data condition. How many records are there per table included in the scope? Are there known data integrity issues? Are there known orphaned records? Using any standard system reports that evaluate data integrity are a handy tool to help simplify this process.

A testing plan should approach the data in a logical order. For example, you will want to archive sales orders prior to invoices and invoices prior to accounts receivable. Until the archiving begins it may be difficult to assess the duration of the activities. It is recommended that you create a tentative plan and then update the plan based on actual performance and system availability. Depending on the system capabilities, it may be possible to archive during business hours. If not, a series of times when the system is down must be used. Once the processes are run in test, you will want to sample the data to ensure that the results are what is expected. A recommended practice is to archive data and then look for data remaining in the source environment older than the archive date and see why they were not archived. Once a process has been completed in test, engage the business users or analysts to evaluate that data. Another thing to consider is the size of the data chunks that are being archived. For example, if you have 20 years of data, 3 years may stay in production, 4 years may be kept in archive and the remaining years may be purged. From an archiving strategy, you may want to create one or two jumbo archives of the oldest data qualified for purging to reduce the number of activities. The jumbo archives may be purged once testing is complete. For data that must be retained, create archives in one year chunks. Once the archived data qualifies for purging is may be disposed of cleanly.

Once the testing phase has been completed, evaluate the time duration of each process run and quantity of data that was included to create an execution plan for production. At this point it is important to understand the differences in capability between the test and production systems. Frequently, the production system is faster than the test system. Once you start running the processes in production you may find that performance is better than expected and you can update your plan accordingly.

Best practices, in summary:

  • Develop a plan, include a cross functional team of business users, legal and technical team members.
  • Use a retention policy for guidance to include the criteria for archiving and purging data by data type or subject area.
  • Document detailed requirements including all files and the criteria for data to be archived as well as dates for archiving and purging.
  • Establish a base line of the quantity and quality of the existing data. Compare this to the production environment once the project is complete.
  • For business user access to archived data, create a view that uses master data from production so the records are complete.
  • Refresh your test environment and run all the processes in the test environment to validate the process.
  • Document test cases that demonstrate the data was archived appropriately.
  • Have business users or analysts perform acceptance testing and verify that the archiving is working as planned and that they have access to the data as designed.
  • Create an execution plan for arching in production. Update the plan as it progresses and distribute it to the team.
  • Evaluate the system’s capabilities. Determine if you can run archiving during business hours or during down time.

Curious about what Essentio’s archiving software can do for you? Ask us for additional information.