EFT uses the Audit and Reporting Module to track activity in the system. This module writes to an Oracle or MSSQL database, but needs to be maintained in order to avoid performance issues impacting your EFT system.
One of the first things to do is to ensure that you have a purge script in place for your database. Scripts, including the purge scripts, are installed at the same time as EFT and can be found in %ProgramData%\Globalscape\EFT Server Enterprise\SQL Server\Helper scripts. We have previously shown how to automate this process using SQLCMD, however it is worth noting that whenever you upgrade EFT, you should check if the purge script has changed too (especially when upgrading from version 7 to version 8 of EFT). The ARM database schema uses a different numbering system to EFT (for example EFT version 8.0.6 is version 22 of ARM). You can find the list of versions (with change history) at https://kb.globalscape.com/KnowledgebaseArticle11031.aspx.
Whenever you install or upgrade EFT, you have the option to allow EFT to create/update the database schema, or else you can perform this manually ‘out of band’ instead. This involves executing the Database utility (DBUtility.exe) with one of the available options as explained below. Regardless of which option you are using, the utility relies on the scripts found in the directory above. The various modes of DBUtility are as follows:
This checks the current version of the ARM database in use. Before performing an out of band upgrade, it is worth running this to validate that you know which version of ARM you currently use.
This option performs the upgrade of the ARM database, using credentials that you provide it. UpgradeSchema first checks the database version and will not perform any action if the database is up to date.
This is similar to UpgradeSchema, except it produces a script file which you can then manually execute against your database (normally via SSMS). It allows your DBAs to take control of the database upgrade process, a requirement in some organisations.
This option uses six ‘create’ script files in order to write a SQL file that allows you to create a completely new database schema. In general, the only time that you would need to run this is if you did not create the database during the install, or else you have decided to start your database with fresh data (most commonly during an upgrade/migration activity).
This useful utility performs a check to see if the database needs to be upgraded, validates that you have the correct permissions to complete the upgrade, checks the DB size and finally looks to see how old the oldest entry is. You should run with this option if there are any performance issues on your EFT systems, regardless of whether you are upgrading or not.
The Fact tables are used to speed up reporting from aggregated tables. You should only need these if you are routinely reporting statistics – if you only occasionally check statistics, it may be worthwhile deselecting this option from the database configuration screen. You can then use this method to manually refresh the tables at a time when the system is not so busy.
When executing DBUtility.exe, you will find that all options except CreateScript and UpgradeScript require you to authenticate against the database. All options allow you to log the results of your actions.
If you would like to know more about this module or Globalscape EFT, please get in touch here.