Archive Data to Improve Performance

Data archiving is an optional feature for on-prem customers that improves performance and preserves access to your old documents. For each document type (Point of Sale, Purchase Order, Inventory, Accounts Receivable, Accounts Payable, General Ledger, and Manifests), you can specify the number of archive months and specify the number of months/years for purging these records and data tables. This is set up in the Parameters > Setup > Database tab (shown below). When these settings are complete, the archive process moves the documents that are older than these entries to a new database. After the documents are archived, the old data remains available, but only when specifically requested and from limited areas.

This feature requires database setup and a new nightly process configuration before you can begin the archive process. Please contact your Aftermarket Sales Representative to get started. This work is performed by the ECI Implementation and Support teams.

Parameter Settings > Setup > Database

Why Should I Archive Data?

The more data your SQL database maintains, the longer it can take to retrieve information in Point of Sale, Inventory and all other areas of the software. The performance impact, due to the database size, varies based upon a number of factors, including server speed and the size of your company (and therefore, the amount of data being maintained). A smaller business is going to have fewer data-performance concerns than a larger company with many locations.

A secondary reason for archiving is to clear out old documents and data that may be appearing in inquiries, selection listings, searches, etc. Moving the data to the archive database removes it from the "live" database but keeps it available if and when you need it.

Setting the Archive Options

After the data archive feature setup is complete, you are ready to choose the archive options. It may make sense to discuss these settings with the ECI Associate who is setting this up for you.

To choose the data archive settings:

1. From the Main Menu, choose Maintenance > Database > Parameters to display the Parameter Settings form. Then from the  Setup tab, choose the Database subtab.

2. In the Document column, review the document types and consider how long you want to keep them in your current database and when you want to archive and then purge them.
See How Should I Choose When to Archive or Purge Data for more ideas.

3. Then in the Archive and Purge columns, enter the time in months that you want each of these document types to be archived and purged. The number of months in the Purge column must exceed the number of months in the Archive column.

4. In the Purge Data Tables after field, enter the number of years after which the data tables should be purged.

5. In the Backup Start Time field, enter the time of day you want to begin the backup process.

6. In the Backup End Time field, enter the time of day you want to end the backup process.

7. In the SQL Timeout for Report Queries field, enter 180.

8. Select the Enable SQL Batch Updates check box unless the Implementation or Support team member advises you not to.

9. When you have completed these settings, choose Process (F12) to save them.

How Do I Choose When to Archive or Purge Data?

First, it's important to decide if archiving is even necessary. Archival is not going to be helpful if your company is a relatively new to the application or you have a smaller company and aren't experiencing performance issues related to the SQL database size. Larger companies or businesses that have been using the software for longer should consider archiving if you experience slowness or other issues related to the quantity of data the application is maintaining. In these cases and for each document type (inventory receipts, orders, etc.), think about how often old information is really accessed. For example, how often does a customer ask about a statement from three years ago or how often do you need to view an inventory receipt that's older than a year? The answers to these types of questions will vary by user. This feature is not needed for Cloud-based customers as ECI manages the database environment for you and expands it when you need it without your intervention.

Archiving data doesn't remove documents from the system, and you can still access the data if needed; however, archived data is not as readily available to you as the live data, so there is a downside. Choosing an appropriate number of archive months for documents can help minimize any negative impact.

In the Data Retention table (Parameters form), use the Purge column to choose a time to permanently remove (delete) data from the archive database. Purge months must be a higher figure than the number of months for archival.

How Do I Access Archived Data?

The documents form has a "Search Archive" check box for use when an archival database is enabled. The same selection/search options are available with Archived Documents as are with live documents. It's important to understand that archived data is not going to be available from the majority of folders, inquires, and other areas of the software. Remember, the point of enabling the Archive feature is to increase performance. Accessing the archived data everywhere that the live data is also accessed would defeat the whole purpose of doing archive.

In Addition to Documents, What Data is Archived?

The Spruce system is a document-based application, so most of the things you do in the software affects documents in some way. All documents maintain a small amount of common data which is kept in a "Documents" table; however, the details behind each type of document varies considerably. These "details" are maintained in many different tables in the SQL database. The details are what become archived, not the document information.

When archive procedure runs, it uses the document information to determine whether a document meets the criteria for archival or not. This criteria includes the date of the document, the type of document, and the status of other documents linked with the document being considered for archival. If the document meets the conditions, the procedure copies the data relevant to the document to the archive database and updates the document in the "live" database to have a status of "archived." After data has been successfully copied to the archive, the data is then removed from the "live" database.

Even if a document meets the condition of the archive date based upon the Archive months parameter, the document may not be ready for archival if any other linked documents are found which are not also ready for archival. The types of document linkages which would cause any particular document to be considered "not ready" vary by type. For example, a Cost Correction document wouldn't be archived if it were linked to an Inventory Receipt document that was not ready to be archived at that time.

Can I turn Data Archiving Off and Restore the Documents to the Active Database?

No. After you turn on the archive process, there is no way to turn it off. This process runs automatically between business days on a daily basis after it has been configured.

You can disable the archive process for a specific document type by entering 0 in the Archive column of that document type on the Setup > Database table in the System Parameter Settings form. However, this will not restore any previously archived data back to the "live" database. If you change the archive months from a lower number to a higher number of months, this change does not restore the archived documents either. There is no undo or restore option after you enable the Archive option.

Archival is an optional feature and requires set-up prior to use. Installing an upgrade does not enable archival even if the parameter's for archive months are completed. Archival is only configured upon request.