Phases of a KTA Database Upgrade

Database Scripts

Database scripts are found in the install source at \TotalAgility\TotalAgilityInstall\DatabaseScripts\SQL Server\Upgrade Database Scripts\, under which there is a folders of scripts to convert between each version with a database change.  These are run sequentially to get from the starting version to the upgrade target version.

If an error occurs at this stage, details should be written to CEBPMDatabaseErrorLog.txt file (on the desktop of the user running the database upgrade), however the installer does not stop.  It continues on and tries to do the next phases, even though any problems at this stage mean the upgrade needs to be treated as failed, and the database should be restored from backup.

Capture Data Migration

Starting with KTA 7.8, the structure of the TotalAgility_Documents database was significantly redesigned.  The Capture Data Migration phase migrates data from the old structure used in KTA 7.7 and earlier to the new structure used in KTA 7.8 and higher.  Thus this phase is only applicable to upgrades from KTA 7.7 and earlier to KTA 7.8 and higher.

Database Artifacts Upgrade

Finally, data in the database needs to be upgraded and these data changes in this phase can involve more elaborate changes better suited to code instead of SQL scripts.  The installer updates Agility.Installation.Server.Upgrade.exe.config with necessary values such as connection strings, and the UpgradeFromVersion setting that determines the correct logic to be applied.  And then Agility.Installation.Server.Upgrade.exe is run to upgrade the data.    

If error occurs at this point that refers to an invalid table or column, that generally means that the database scripts failed first.  This results in an unexpected schema that causes errors for the database artifacts upgrade.