THE INFORMATION IN THIS ARTICLE APPLIES TO:
QUESTION
How do I archive historical data from ARM in a manner that allows access to reports and visibility of the archived data?
ANSWER
A database utility (DButility.exe) is included as part of the EFT installation. The "PURGE" option can be used to trim data with a historical database to include data for a specific period.
The instructions below explain how to:
- Ensure all transaction data is retained
- Provide reporting capabilities on archived data
- Reduce the size of the production ARM database
- Create yearly archived ARM databases
Assumptions
- EFT and ARM are being used to capture transactional data
- SQL Server is hosting the ARM data
- Business has sufficient storage for the ARM archives
- Production ARM data spans multiple years
Create ARCHIVED database copies
- Create a copy of the production ARM database and name it to indicate ARCHIVED data.
- Using DBUtility.exe on the production database, purge all data EXCEPT for the current year.
- Create a copy of the ARCHIVED database for each year of data included, and name each database to reflect the ARCHIVE-YEAR.
- Use the DBUtility.exe to purge or trim each ARCHIVE-YEAR database to contain only data for that specific year.
Copy and Edit Report Connection Strings
Now that you have individual databases containing historic ARM data by year, you can create reports to match. From within the VSReport Designer, copy and edit the connection string for each report needed to query archived data.
- Create copies of Globalscape reports needed for each archived database.
- Edit report connection strings for each archived database.
- By default, the ConnectionString information included is for the production database, click the database icon to edit and define the connection string. The Data Link Properties dialog box appears.
- Edit the string to match the archived database name. For example:
provider=SQLNCLI10;server=mysqlserver;database=ARM-ArchiveDB2012;UID=armadmin;Pwd=xxxxx;
- Save the reports with the new connection strings.
- Distribute reports to users as needed.
- Repeat the process to regularly to maintain a consistent archiving strategy.
This process can be adapted to cover shorter or longer time spans. Archives can be created monthly, quarterly, and yearly, as needed.
Example: Strategy by Data Age
Suppose your business must retain 1 year of transactional EFT data within the production ARM and must retain all transactional data in archive for an additional 4 years. After the data ages to 5 years, the database can be purged or deleted. This can be achieved by creating an ARM database for each year.
With ARM data segregated by year in unique databases you have the following storage options:
- 1 year in production
- 1 year on local network storage – reportable, but not active
- 3 years of offline – offsite inactive historical
Example: Strategy by Database Size
Determine an optimal SQL database size based on the volume of your transactional data and SQL Server resources. For this example, suppose the business has determined that 100 GB is the optimal size. Create two copies of the current production database.
- Copy and purge the production database
- Label your copies in a meaningful manner
- Purge data from each copy in chronological order
- ARM – production 20 GB
- ARM 1 – oldest archive 100 GB
- ARM 2 - archive 100 GB
- ARM 3 - newest archive 100 GB
- ARM 4 – next archive (when prod exceeds 100 GB)
Follow standard SQL Server tuning guidelines provided in the database vendor documentation to maintain a healthy database.
MORE INFORMATION
For more information, refer to the following knowledgebase articles and online help for your version of EFT: