Upgrading Engine

Engine interacts with two persistence-related components: a database, and a filesystem. Over time, as we add new features to Engine or otherwise improve the old features, it is necessary to change the underlying structure of these components while preserving the data contained therein. This only happens during a major release of a new version of Engine, and for this purpose we have created an automated upgrade tool.

This document is intended only as a reference. If you want to upgrade Engine, you will need to contact us at support@scorm.com so we can provide you with the latest version of our software, as well as arrange for a dedicated support engineer to talk through the process with you and answer any questions you may have.

Before You Upgrade

Back Up Your Database and Files

We expect that most customers are performing regular backups of both their filesystem and their database, but it is especially important to do so during the upgrade. We encourage you to back up your database and take a snapshot of your xAPI filesystem data before running the upgrade tool. Rustici Software is not responsible for data loss caused by the use of the upgrade tool.

Securing Your xAPI Filesystem Data

Prior to Engine 2015.1.x, Engine would supply a default value for the TinCanFilesPath (now xAPIFilesPath) configuration setting (which controls where large xAPI documents and xAPI attachments are stored). Unfortunately, the default value was the same as FilePathToContentRoot, the directory where package contents are stored on the filesystem). Since files under that directory are typically accessible through your web server, we now consider this a security hole. To better protect you and your data, we now require you to define an xAPIFilesPath setting value explicitly, and we also verify at runtime that this value is not the same as FilePathToContentRoot.

If you did not explicitly define a TinCanFilesPath configuration setting value, you will need to move your xAPI filesystem data to a secure location before upgrading. Moving the data is simple. First, create a directory somewhere where your xAPI data can be stored. Second, look under your FilePathToContentRoot directory for a folder named tincan. Once found, move that entire directory to the directory you designated in the first step. Third, add the TinCanFilesPath configuration setting to your engine installation's configuration file with its value set to the directory chosen in the first step. Finally, you will need to restart the Engine server for the changes to take effect.

Extra Considerations for xAPI/Tin Can "Beta" Users (2012.1.x and 2012.2.x)

If you are on Engine version 2012.1 or 2012.2 and have xAPI (Tin Can) data, you will need to run a separate upgrade tool to convert your xAPI tables to their 2013.1.x equivalents. (At this time, we suspect that most of these customers have already upgraded, and that this will apply to very few customers, if any. After all, in 2012 xAPI was still in its infancy and hadn't even reached version 1.0 yet.) Instructions for that tool are outside the scope of this document; you must discuss this with your support engineer.

Update Your Integration Layer (If You Have One)

This applies primarily to customers who began using Engine before the release of Engine 2015.1.x.

At the start of your upgrade progress, our support engineers will ask you for a copy of your integration layer and external key classes. We will then provide specific advice on how to update the integration layer to match the modern version of Engine.

At a minimum, you should attempt to recompile your integration layer against the latest version of Engine, as signatures may have changed.

Running the upgrade tool

Your upgrade deliverable will come with a bundled configuration template file. The file is called EngineInstall.xml.template. Fill out the fields in this file, and save the result without the .template extension. For information on formatting connection strings (Java customers should especially take note), see the installer documentation.

Running the upgrade tool is essentially the same as running the install tool. The tool must be run from the same directory, and any necessary caveats about copying database connectors still apply. Copy your EngineInstall.xml file into that directory. The command will be: baseCommand EngineInstall.xml, where baseCommand is described in the installer documentation.

Multi-database customers should run the upgrade once per database. The same SystemDatabaseConnectionString should be used for each database you upgrade; the TenantDatabaseConnectionString will be the only connection string that differs. You must also select a different default TargetTenant for each database. Multi-database customers will likely need to change any code that interacts with Engine, particularly anything related to ExternalConfiguration.

Customers who wish to update to our new tenancy model (which allows multiple tenants in each database) are highly encouraged to talk to us before attempting an upgrade, but in brief, the upgrade will require the customer to provide a SQL query that will return the name of the tenant associated with a package, given that package's external keys.

Integrating the Upgrade/Install Tool

Most customers will run the upgrade tool as a command-line tool, but for many others, this solution is impractical. For example, some Engine customers integrate Engine into an "off-the-shelf" LMS that is sold to other customers and may even have its own upgrade tool. For that reason, we have made it possible to integrate the upgrade tool into another .NET or Java application.

Design Considerations

It is absolutely imperative, even when integrating the upgrade tool into your application, that you back up your production data before using the upgrade tool with it. The upgrade tool makes changes to the schema, and despite our best efforts there are always going to be certain failure modes (e.g., power failures at certain moments) from which we cannot recover. If your application's upgrade tool does not already include a backup step, we strongly recommend adding one.

We also recommend against doing "silent upgrades"—that is, having the upgrade tool automatically run in a context where the upgrading customer is not aware that it would be running (e.g., at application startup). If this is part of your workflow, make sure that your users understand the risks and are taking frequent backups.


Initial Set-up

Upgrades require modifying the global environment for Engine; most notably, changing the way configuration settings and the integration layer work. To do this, all upgrade code must be enclosed in a block like the following on .NET:

using (EngineUpgradeManager upgradeManager = new EngineUpgradeManager())
    // All configuration of the upgrade goes here
    // As does running the actual upgrade

And on Java (note that the EngineUpgrademanager constructor itself can throw an exception, so you will either have to surround this method with a further try-catch block, or declare its containing method as throws Exception):

try (EngineUpgradeManager upgradeManager = new EngineUpgradeManager())
    // All configuration of the upgrade goes here
    // As does running the actual upgrade

EngineUpgradeManager is contained in the main Engine library (RusticiSoftware.ScormEngine.dll/.jar), and located under the package/namespace RusticiSoftware.ScormContentPlayer.Logic.Upgrade.


Within the upgrade management block, you must now configure the upgrade. In general, to set a given configuration setting for the upgrade, you code will take the form, where "SettingName" is the name of the setting you wish to set:

upgradeManager.Settings.SettingName = value;

Or on Java:


The value will be the type associated with the setting, so numeric settings will require numbers and text settings will require strings.

A complete reference of settings will follow, but at a bare minimum you will need to specify at least two settings for establishing database connectivity:

  • SourceDataPersistenceEngine: A string (sqlserver, mysql, postgresql, or oracle) corresponding to the DBMS flavor in use for the source database.
  • SourceSystemDatabaseConnectionString: The connection string to use when connecting to the system schema on the source database. (Java customers take note: if you are running the context of an application container and can use JNDI resources, you can use the JNDI name of your connection information here. Pooling information will be inherited therefrom.)

The following are also used to supply connection strings in case you need more than one:

  • SourceTenantDatabaseConnectionString: If your source database has separate source and tenant connection strings, then an alternate connection string should be supplied here.
  • TargetDataPersistenceEngine: If using separate source and target databases, this represents the DBMS flavor for your target database.
  • TargetSystemDatabaseConnectionString: If using separate source and target databases, this is the connection string to your target system database.
  • TargetTenantDatabaseConnectionString: If using separate source and target databases and separate system and tenant databases, this is the connection string to your target tenant database.

Finally, you will need to specify some options related to tenancy if you are using the upgrade tool; these settings have the same name as they do regular upgrade configuration file, described above. If you have xAPI data, you will also need to specify xAPIFilesPath, as described in that section as well.

Running the Upgrade

Lastly, you will need to run the upgrade. Generally speaking, you will need to run one of two methods: upgradeManager.Install(); to run the install tool, or upgradeManager.FullUpgrade(); to run the full upgrade. The code is exactly the same in both .NET and Java.

Both methods will return an UpgradeStatus object with Warnings and Errors properties (getWarnings() and getErrors() on Java) that can be used to report on the status of the upgrade. Upgrades with even a single error are considered to have failed.

Example Upgrades

.NET, Install, Different System and Tenant Databases

using (EngineUpgradeManager upgradeManager = new EngineUpgradeManager())
    upgradeManager.Settings.SourceDataPersistenceEngine = "mysql";
    upgradeManager.Settings.SourceSystemDatabaseConnectionString = "server=myserver.net;Uid=scormengine;pwd=secret;Database=rusticisystem;";
    upgradeManager.Settings.SourceTenantDatabaseConnectionString = "server=myserver.net;Uid=scormengine;pwd=secret;Database=rusticitenant;";
    UpgradeStatus status = upgradeManager.Install();
    if (status.HasErrors) {
        // report on the failure

Java, Single-Tenant, Copy Upgrade

try (EngineUpgradeManager upgradeManager = new EngineUpgradeManager())
    UpgradeStatus status = upgradeManager.Install();
    if (status.getHasErrors()) {
        // report on the failure

.NET, Multi-Tenant, In-place Upgrade

using (EngineUpgradeManager upgradeManager = new EngineUpgradeManager())
    upgradeManager.Settings.SourceDataPersistenceEngine = "sqlserver";
    upgradeManager.Settings.SourceSystemDatabaseConnectionString = "server=localhost\SQLEXPRESS;uid=sa;pwd=secret;database=RusticiEngine";
    upgradeManager.Settings.XapiFilesPath = @"C:\\server\store\xapi\";
    upgradeManager.Settings.PackageToTenantQuery = @"
            scorm_package_id = @scorm_package_id
    UpgradeStatus status = upgradeManager.FullUpgrade();
    if (status.HasErrors) {
        // report on the failure

External Key Deduplication

Packages and registrations are identified by a set of "external keys" that you can use to reference the package and registration later on. For customers coming from 2014.1 or earlier, these keys were user-defined by special columns on the ScormPackage and ScormRegistration tables. The external key consisted of those columns plus a column added by Engine (version_id for packages; instance_id for registrations).

In order for the external key to work, at the database level there must be an index over the external key columns, as well as a unique constraint. In some rare cases, the unique constraint is missing, and in even rarer cases the index is missing as well. If either of these are the case, the upgrade tool will remedy the problem.

Unfortunately, if there was no unique constraint, it is entirely possible that some external keys were duplicated, meaing the upgrade cannot add a unique constraint without remedying the duplicate rows.

The default behavior of the upgrade tool upon encountering duplicate data is to display an error message and stop. The upgrade tool is capable of automatically addressing the problem, but we intentionally do not enable that behavior by default because we want our customers to understand the risks before proceeding. What follows is an explanation of how Engine chooses which row to deduplicate and of what options you have for handling the duplicated rows (and the associated risks).

Given a number of duplicate rows, the upgrade tool will designate one of those rows as the "survivor" and the rest as "casualties". For registrations, we will always choose the row with the most recent update time as the survivor. For packages, we will choose whichever row has the most child registrations as the survivor.

Because we cannot assume that, when a database looks up a duplicate key, it consistently chooses the same row, these methods are not foolproof. Practically speaking they have been "good enough" on every data set we've seen, but there is always a risk that the upgrade tool chooses the wrong survivor, or even worse that there really is no one "survivor". More bluntly: automatic deduplication runs the risk of deleting (or moving) real data, including whole registrations or stored learner progress for a registration!

After a survivor has been chosen, the upgrade has several methods for dealing with casualty rows, which can be chosen by the configuration setting DeduplicateExternalKeysMode. The three modes are summarized below:

  • REASSIGN: The upgrade tool will reassign the version_id or instance_id field of the external key to a unique negative number. This method will only work if there is currently no data with negative values for those fields; you may encounter undefined behavior if there are. The advantage of using this method is that it does not delete any data; the data will still exist in the database somewhere and could be recovered manually on a case-by-case basis if needed, although it will be, practically speaking, inaccessible until then.
  • DELETE: The upgrade tool will delete all casualty rows. This is cleaner and requires making fewer assumptions, but carries more risk as the data is actually deleted.
  • SQL: The user will provide custom SQL for ScormPackageDeduplicationQuery and ScormRegistrationDeduplicationQuery that will delete casualty rows or modify their keys in such a way that the rows that they will no longer duplicate anything.

When assessing the risk of deduplication, one important thing to consider is your reporting workflow. We have always very strongly recommended that customers do not report directly off of our tables, and instead use the RollupRegistration event hook to send data to reporting tables in their application. If that's the case, deleting data within Engine poses far less risk, because the relevant data has already been imported into your system. If you are reporting directly off of Engine's tables, however, deleting data may pose more risk.

The other important factor to consider is the scope of the problem. To figure out how many duplicates you have, run the following queries, substituting <external_package_id> and <external_registration_id> with your external columns:

    COUNT(*) as duplicate_count
    duplicate_count > 1

    COUNT(*) as duplicate_count
    duplicate_count > 1

Most likely, only a few rows on each table will be duplicated, but if you have a large number, you may want to speak with us before proceeding.

In summary, for most customers, the preferred solution to duplicate rows will be for you to add the DeduplicateExternalKeysMode setting with a value of REASSIGN to your upgrade configuration file, so long as you are comfortable with the risks involved.

If you have any questions about how deduplication works, please discuss it with our support team, who can offer clarification or guidance.

Updating Queries

For several years now, it has been our recommendation that you should not interact with our tables directly, certainly not to write data, but not even to read data, either. Instead, we recommend that you use the REST API to get the data that you need for your reporting, or for some legacy customers, methods in the ScormEngineManager class. The reason is that we actually do guarantee that the behavior of those methods will remain backwards compatible in maintenance releases and generally avoid changing them in major releases as well. On the contrary, we can change the schema in any given major release, which can lead to surprises.

This is especially the case for any customers who are upgrading from 2014.1 or earlier to a modern version of Engine. Every single table in Engine's database now has an additional column in every primary key and every index. This means that queries that you relied on before are no longer using indexes correctly, which will lead to crippling performance problems.

Our preferred resolutions, of course, involve using officially sanctioned methods to get the data you need access to, either by using the REST API or by collecting more registration data in your RollupRegistration method. That said, we also recognize that doing this can require significant reimagining of your application's architecture, which you may not have time to do (or may be unwilling to risk). If so, what follows is a brief summary of how you change queries to make them compatible with Engine 2015.1 and later.

First, to fetch data from a table, make sure you are including engine_tenant_id in any WHERE clauses. Generally, you will use 0 as the value for engine_tenant_id, unless you have a multi-tenant setup. For example:

    my_external_key = ? AND
    engine_tenant_id = 0

You will also need to include engine_tenant_id in any JOIN clauses as well. We recommend an approach like the following:

    ScormRegistration reg
    ScormActivity act ON
        act.engine_tenant_id = reg.engine_tenant_id AND
        act.scorm_registration_id = reg.scorm_registration_id
    reg.my_external_key = ? AND
    reg.engine_tenant_id = 0

Upgrade Configuration Settings Reference

The following is a reference of all the additional configuration settings that can be used in the context of an upgrade.

The format here follows that in the main configuration setting reference.

UpgradeConnectionInformation Settings

SourceDataPersistenceEngine (upgrade)

A string (`sqlserver`, `mysql`, `postgresql`, or `oracle`) corresponding to the DBMS flavor in use for the source database.

SourceSystemDatabaseConnectionString (upgrade)

The connection string to use when connecting to the system schema on the source database.

SourceTenantDatabaseConnectionString (upgrade)

The connection string to use when connecting to the tenant schema on the source database.

SourceDatabaseSchema (upgrade)

The DBMS-level schema to use when connecting to the source database. At this moment, it is not possible to specify different tenant and system DBMS schemas.

SourceDataHelperAssemblyName (upgrade)

The assembly containing the custom data helper class for the Engine instance. Used only in .NET.

SourceDataHelperClassName (upgrade)

The fully-qualified name of the class to be used as the Data Helper for the source database.

TargetDataPersistenceEngine (upgrade)

A string (`sqlserver`, `mysql`, `postgresql`, or `oracle`) corresponding to the DBMS flavor in use for the target database.

TargetSystemDatabaseConnectionString (upgrade)

The connection string to use when connecting to the system schema on the target database.

TargetTenantDatabaseConnectionString (upgrade)

The connection string to use when connecting to the tenant schema on the target database.

TargetDatabaseSchema (upgrade)

The DBMS-level schema to use when connecting to the target database. At this moment, it is not possible to specify different tenant and system DBMS schemas.

TargetDataHelperAssemblyName (upgrade)

The assembly containing the custom data helper class for the Engine instance. Used only in .NET.

TargetDataHelperClassName (upgrade)

The fully-qualified name of the class to be used as the Data Helper for the target database.

UpgradeTenancy Settings

TargetTenant (upgrade)

If given, use this tenant's ID anytime we need to determine the value of an engine_tenant_id column. For single-tenant single-database customers (most customers), the recommended value of this setting is 'default'. For multi-tenant multi-database customers, this should be the name of the tenant associated with the database you are currently upgrading. For multi-tenant single-database customers, this setting should be omitted or left blank.

PackageToTenantQuery (upgrade, default: SELECT app_id FROM SCHEMA_PREFIX.ScormPackage WHERE scorm_package_id = @scorm_package_id)

For multi-tenant upgrades, this setting allows the user to specify a query that takes a scorm_package_id as input and returns the name of the associated tenant as output. This setting is required for multi-tenant upgrades.

TenantIdCacheSize (upgrade, default: 1000000)

For multi-tenant upgrades, this setting affects the size of the cache used to map various kinds of internal Engine database IDs to tenants. Keep in mind that there are four such caches that will use this setting!

ZeroTenantOverride (upgrade, default: default)

This setting allows the user to specify what name the 'zero' tenant has. This will go on to determine what the default value of their external configuration's tenant name parameter will be post-upgrade.

UpgradeSystemTables (upgrade, default: true)

This setting can be disabled in order to skip upgrading the system tables. A system connection string will still need to be provided when upgrading tenant tables.

UpgradeTenantTables (upgrade, default: true)

This setting can be disabled in order to skip upgrading the tenant tables. A system connection string will still need to be provided when upgrading tenant tables.

UpgradePerformance Settings

BatchSize (upgrade, default: 500)

The number of rows to process in a single batch.

MaxProcessingQueueSize (upgrade, default: 3)

The number of rows to process in a single batch.

WriteThreads (upgrade, default: 2)

The number of threads available to write processed batches to the database.

ConverterThreads (upgrade, default: 1)

The number of threads available to pre-process batches before writing. This number should probably be only a fraction of the number of write threads.

OnlineIndexingSupported (upgrade, default: true)

Stipulates whether the CREATE INDEX statements used during phase 1 of an online upgrade can be used without locking the table. This setting is not checked for PostgreSQL. For technical reasons we can't do lock-free indexing on PostgreSQL. This setting should only be marked false if you are running a non-Enterprise Edition of Oracle/SQL Server.

RowCopyStartAfter (upgrade)

If given, only copy rows after this date.

UpgradeCloud Settings

AwsSecretKey (upgrade)

The secret key used for AWS API access to S3.

AwsSecretId (upgrade)

The secret id used for AWS API access to S3.

S3BucketName (upgrade)

The name of the S3 bucket used for object storage.

S3RegionName (upgrade)

The name of the AWS region the S3 bucket is located in.

FailOnMissingXapiStatements (upgrade, default: true)

If enabled (default), the upgrade will raise an error and fail in the event that the upgrade requests certain statements from the object store and doesn't find them. If disabled, there will simply be a warning instead.

CleanUpFileStore (upgrade, default: true)

If enabled (default), the upgrade will move objects in the document store. If disabled, the upgrade will copy those objects instead.

SkipXapiSandboxes (upgrade, default: true)

If enabled (default), the upgrade will ignore any rows associated with an xAPI sandbox. If disabled, the upgrade will copy sandboxed data.

DropObsoleteTables (upgrade, default: true)

If enabled (default), 'obsolete' tables (tables that the upgrade tool recognizes as having belonged in a prior version of Enging but are no longer included in the current schema) will be dropped. If disabled, these tables will be left alone.

Schema Settings

DropExternalForeignKeys (upgrade)

If false (default), the upgrade will fail upon encountering foreign keys that involve an Engine table and a customer's table. If true, the upgrade will instead issue a warning and then drop those foreign keys. It will not add the foreign key back at the end of the upgrade.

ExternalPackageKeyColumns (upgrade)

Allows a customer to specify the names of external package key columns explicitly.

ExternalRegistrationKeyColumns (upgrade)

Allows a customer to specify the names of external registration key columns explicitly.

ExternalKeyColumnsToRemove (upgrade)

A list of external key columns that should be removed. This is recommended for customers who did a multi-tenant upgrade based on one of their external ID fields.

Migration Settings

DropXapiTables (upgrade)

If enabled, drops and recreates all xAPI-related tables instead of upgrading them.

DropLaunchHistory (upgrade)

If enabled, drops the ScormLaunchHistory table and recreates it instead of upgrading it. This table generally has a lot of data, and therefore this can result in significant time savings for the upgrade.

SkipxAPIFileStoreMigrations (upgrade)

If enabled, skips any migration or phase related to xAPI File Store.

Deduplication Settings

DeduplicateExternalKeysMode (upgrade, default: ERROR)

The external key deduplication mode to use. Using this setting may result in lost data, so be warned! Recommended values are DELETE or REASSIGN.

MaxCasualtiesPerDuplicate (upgrade, default: 1)

When deduplicating external keys, imposes an upper limit on the number of duplicates that can be reassigned. If the limit is exceeded, the upgrade stops in error. If you are having problems, the limit can be raised, but be sure to clear this with the support team first.

ScormPackageDeduplicationQuery (upgrade)

If DeduplicateExternalKeys is enabled, this query will be run for each duplicated package row. Contact the Engine support team if you wish to use this setting.

ScormRegistrationDeduplicationQuery (upgrade)

If DeduplicateExternalKeys is enabled, this query will be run for each duplicated registration row. Contact the Engine support team if you wish to use this setting.

Debug Settings

AsynchronousEnabled (upgrade, default: true)

If disabled, runs the upgrade in synchronous mode. Occasionally useful for debugging at the cost of performance.

SkipRowCountAudit (upgrade)

If set, the row count audit will be skipped altogether. This is used when the row count audit would take too much time for the designated cutover window in phase 3. Use with caution, and preferably only after running a test cutover with the row count audit enabled first. Overrides IgnoreRowCountAudit.

IgnoreRowCountAudit (upgrade)

If set, row count audit errors becaome warnings and the upgrade will proceed past a failed row count audit. This is intended for when the row count audit is expected to fail, but the information may still be valuable

OrphanRowThreshold (upgrade, default: 0.0078125)

The upgrade checks for foreign key violations. If it finds a number of orphaned rows on a table that expressed proportionally to the number of rows in the table is larger than this threshold, the upgrade will stop and raise an error. The default is 1 row out of every 128.

UpgradeDefinitionFile (upgrade)

Allows a user to override the default migration definitions file. One should use this setting only if instructed to do so by a Rustici Software support engineer.

OnlineMode (upgrade)

This setting set automatically during phased upgrades. This setting can be set to true during an all-in-one upgrade to make it behave like a phased upgrade (in that it will aggressively choose row-copying over alter table), which can be useful for testing.

LimitRowsByStartTime (upgrade)

This setting is set automatically for phased upgrades (though not necessarily always to true). This setting can be set to true during an all-in-one upgrade to enable this feature in that upgrade type as well, which is useful for testing.

DeltaMode (upgrade)

This should never be set via configuration. This really shouldn't even be a setting, but it's left here so as not to break Cloud's upgrade tool.

AllowUnrecognizedSettings (upgrade)

If false (default), raises an error and stops the upgrade if there is something in the configuration setting store that we don't recognize. If true, issue a warning instead.

SaveProgressIntervalSeconds (upgrade, default: 120)

Progress will be saved during row copy at an periodic interval defined by this setting, as well as whenever the upgrade switches to a different table.

SkipPreTransformationRowCount (upgrade)

If enabled, do not count the number of rows before performing ALTER TABLE operations.

SkipOrphanRowCleanup (upgrade)

If enabled, do not look for orphan rows before trying to add foreign keys.

UpsertUsingExternalKeys (upgrade)

If enabled, when row-copying data to ScormPackage and ScormRegistration, use the external key columns as the keys when matching upserts instead of the primary key. This is meant to cover the rare situation where packages and registrations are deleted and recreated with the same external key while the row copy upgrade is in progress.

ProgressTag (upgrade)

When given, this tag is appended to all progress tracking IDs, so multi-DB upgrades won't share state when run in parallel.

LwsEnabled (upgrade, default: true)

If enabled and LWS tables are detected, enables LWS-specific upgrade migrations.

results matching ""

    No results matching ""