Upgrading the EFT Database

This introduction describes in general how an EFT database upgrade works.

A Database Utility (DBUtility.exe) is used to upgrade the database, when applicable. You have the option of either upgrading the database during the upgrade process within the EFT Installer or choose to upgrade the database "out of band" later using the Database Utility. Because the EFT installer uses the same Database Utility internally to perform the upgrade, the methods are synonymous.

As part of this new approach to upgrading the ARM database, the database schema maintains an independent version number. This version is used to determine if the database schema and data require an upgrade across various releases of the EFT application. This version number is maintained in a new table called "TBL_SCHEMA_VERSION." This new table is created as part of the initial ARM upgrade process when upgrading EFT.

During the upgrade, you will be prompted to provide the user credentials that should be used when connecting to the database. You must provide the credentials for the EFT database user account, as opposed to the super-user accounts such as "sa" or "sys", because the upgrade process assumes it is operating as the database account that owns the schema to be upgraded. (Refer to Database User Account Privileges > Upgrade for information on the required database user account privileges necessary to successfully upgrade.)

When upgrading from within the EFT installation process, the installer will analyze the database prior to performing the actual upgrade. The results of this analysis are displayed in an upgrade preview page of the installer. Administrators are urged to read the results carefully prior to continuing with the upgrade. (EFT's upgrader does not check the database for fragmentation.)

The analysis step will determine whether the database requires an upgrade by examining the version number in the new version table. Across many builds and releases of the EFT application, the ARM database may not require any changes. As such, the version number for the database may not change as often as the EFT version. If the database does not require an upgrade, then the installer will state this and essentially skip the ARM upgrade process.

As with the database upgrade, the database analysis process used in the EFT installer is actually performed by the Database Utility and is equivalent to running the utility with the "-action=PreviewUpgrade" command line option. In addition to checking the database version number, the installer/utility will also check for various prerequisites needed to perform the upgrade. Prerequisites that have been met will be displayed with a "PASS" status. Any prerequisites that have not been met will be displayed with a "FAIL" or "WARN" status. These issues should be researched and rectified prior to proceeding with the upgrade.

Recommendations are provided along with any failed prerequisites suggesting how to resolve the issue. After remedying any errors, you can run the analysis again by clicking Reanalyze.

The analysis will also display information about the database such as the approximate size of the user data as well as the age of the user data within the database. Additionally, the SQL script that will subsequently be used to perform the actual upgrade will be displayed.

You may decide to upgrade the database later. If so, you can retain the upgrade script by clicking View to open the database analysis results in a text editor and then save to a file of your choosing.

Alternatively, you can run the EFT installer in maintenance mode or run the Database Utility using the "-action=UpgradeSchema" option to upgrade the database another time.

User Account Permission/Privilege Requirements

When upgrading the ARM Database, either through the EFT Installer or the Database Utility, you should use the EFT Database user account to connect to the database to perform the upgrade. This is as opposed to using one of the more privileged system accounts such as the "sa" account on SQL Server or the "sys" or "system" accounts on Oracle.

For additional information related to database user account privileges refer to Database User Account Privileges.

SQL Server

The user account used to upgrade the database should have the "db_owner" privilege. This is the default for the user account created for, and used by, the EFT. As such, no action is required on your part prior to upgrading.

Oracle

The ARM module makes use of database views. In previous releases, the database user account created for use by the EFT was not granted the ability to create views. As such, prior to upgrading an Oracle ARM database, you must grant this privilege to the EFT database user account manually. This is done by granting the "CREATE VIEW" privilege to the account using a more privileged account such as the "sys" or "system" account.

One method of granting the privilege is to connect to the database using the Oracle command line "SQL Plus" utility. On the computer where Oracle is installed, launch the SQL Plus utility:

Click the SQL Plus Start menu shortcut (for example, Start Menu > All Programs > Oracle - OraDb11g_home1 > Application Development > SQL Plus)
If the utility is available on the system path, then open a Windows command prompt (for example, Start > Run > cmd.exe), type sqlplus at the command prompt, and then press ENTER.

Once SQL Plus has started, you will be prompted for login credentials. Connect using a privileged account such as "sys" or "system". Be aware that when connecting as the "sys" account you must provide the "as sysdba" option; for example:

sys as sysdba

Complete the login process by providing the password.

Use the following command to grant the "CREATE VIEW" privilege to the EFT database user account:

GRANT CREATE VIEW TO <User>;

Where <User> is the name of the EFT database user account. For example:

GRANT CREATE VIEW TO EFTDBUSER;

Exit the SQL Plus tool by typing Exit and pressing ENTER.

Time Requirements

The time it takes to perform the upgrade depends both on the size of the database and the performance characteristics of the computer on which the database resides.

Our internal testing has shown that the database upgrade can take as little as 15 minutes for a moderately sized database of 5GB, up to 3 hours or longer for larger databases of 30GB or more. Because the time it takes to perform the upgrade is greatly dependent on CPU and Disk I/O speed, it is difficult to provide an exact time for any given situation.

For additional information related to upgrading large databases please refer to Upgrading Large Databases.

Refer to Upgrade Paths below for a discussion of the available upgrade paths that may be used to minimize down time.

Disk Space Requirements

The size of the database will grow as part of the upgrade because of changes in the data types used for storing character-based data.

Our internal testing indicates that, on average, the size of user data in the database will increase by approximately 31% as part of the upgrade process. For example, if your database consumes 10GB before upgrading, then it will use approximately 13GB after upgrading.

If disk space is limited, you should consider purging older data from the database prior to upgrading. For information on purging data from the ARM database, refer to Purging Data from the Database.

For additional information related to upgrading large databases, refer to Upgrading Large Databases.

SQL Server Considerations

If you have limited disk space and are using SQL Server, it may be possible to reduce the size of the database prior to proceeding with the upgrade. This may be accomplished by "shrinking" the database, which will reclaim unused space.

For information and considerations on shrinking a SQL Server database, please refer to the Shrink a Database topic on the Microsoft Developer Network site.

During the upgrade process, the increase in size of the user data will be reflected by an increase in the size of the database's MDF file by approximately 31%.

Additionally, the database transaction log file, the LDF file, may temporarily grow in size. In testing, the LDF file typically increased to between 1% and 3% of the starting size of the corresponding MDF file. For example, if your MDF file is 10GB in size, then the LDF file could be expected to grow temporarily to approximately 300 MB in size.

Oracle Considerations

When upgrading Oracle databases you must ensure that not only is the appropriate amount of disk space available, but that the USERS tablespace is capable of growing to accommodate the additional storage requirements. You may consider allowing the USERS tablespace data files to auto extend during the upgrade process. Additionally it may be necessary or desirable to create additional data files for use with the USERS tablespace.

Upgrade Paths

Depending on the size of the ARM database and the time constraints on performing the upgrade of EFT, you may choose to consider alternate methods of upgrading the ARM database. Described below are pros and cons of two alternate methods of upgrading the database, when each method is appropriate, and how to perform the upgrade using each method.

Method 1: During the EFT upgrade

This is the typical method of upgrading the ARM database. When running the EFT installer, you can upgrade the ARM database as part of the full upgrade process.

Pros

Simplest method, requiring minimal manual steps

Cons

The EFT will be unavailable for the time it takes to perform the entire upgrade

Appropriate When

The ARM database is relatively small or the computer running the database is sufficiently powerful
The ARM database is large, but a few hours of downtime is acceptable

How to Perform

When upgrading using the EFT installer, on the EFT Auditing and Reporting database configuration page of the wizard, click Configure Auditing and Reporting and proceed accordingly.

Method 2: Out of band

With this method, EFT may be upgraded independent of the ARM Database. Specifically, you would upgrade EFT using the EFT installer application, but choose to skip upgrading the ARM database at that time. Once the EFT application has been upgraded, it may be restarted and will thus be available to service end users. During the time that the ARM Database has not yet been upgraded, the EFT application can temporarily store audit information to disk.

You can then upgrade the ARM database using the Database Utility. Once the upgrade has completed, the EFT will then be able to reconnect to the database as normal.