Upgrading Engine

This section treats the topic of major version upgrades for Engine. If you are simply deploying a new maintenance release (a "minor version"), you will be using a much simpler process than what is outlined here. Ignore this documentation and use the maintenance release documentation instead.

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

Note: This section applies only to customers who currently support xAPI.

Engine requires use of the filesystem to support some xAPI features; see this relevant section from the xAPI documentation. If your application used xAPI file storage, you will need to carefully consider it during the upgrade.

For users coming from Engine 2015.1 and later, you were required to specify an xAPIFilesPath explicitly if you were using it. Check your Engine configuration file to see if you were specifying this setting; if not, you do not need to worry about the xAPIFilesPath migrations. You can specify the SkipxAPIFileStoreMigrations setting in lieu of xAPIFilesPath. If you are using this setting, however, you will have to provide it to the upgrade tool.

For users coming from Engine 2014.1 and earlier, Engine supplied a default value for the xAPIFilesPath configuration setting. Unfortunately, the default behavior was to use the value of 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 problem. 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.

The first step will be determining whether you have any such data. Look under your FilePathToContentRoot directory for a folder named tincan. If such a folder is present, then you will need to move it. Create a directory somewhere where your xAPI data can be stored safely. Move the tincan folder there, and then specify the directory you created (not the tincan directory, but its parent) as xAPIFilesPath in your upgrade configuration file and in the configuration file of the Engine version to which you are upgrading. (This will all need to be done as part of your actual cutover process; if you do this before that your older version of Engine could end up creating more files that you would otherwise miss.)

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. That file will include the minimum list of settings you will need to support.

Running the upgrade tool involves many of the considerations necessary for running the install tool, but the tools are run slightly differently. Java customers will need to set up their JDBC connector and .NET customers running on MySQL will need to set up their MySQL connector, as described in the install tool documentation. (Java customers should also take note that their connection string format will be a JDBC URL, rather than a JNDI reference, as with the install tool.)

Copy your modified EngineInstall.xml file into the same directory as the install tool, and run the command baseCommand EngineInstall.xml (baseCommand is explained in the installer documentation). Be careful to run the upgrade tool, and not the install tool: do not provide a -install flag to the upgrade tool, and do not attempt to pass your connection string over the command line.

If using an XML file for configuration presents problems for your automated deployment tools, talk to us and we can explain a few other options that may be easier to integrate into a script.

Customers who have separate system and tenant databases should generally upgrade the system and tenant databases separately. Provide a configuration file with your system connection string, and then run the install command with the -system flag to upgrade the system database only (e.g., baseCommand EngineInstall.xml -system. To upgrade a tenant databases, provide your tenant database connection string in the settings file and then run the upgrade command with the -tenant flag (e.g., baseCommand EngineInstall.xml -tenant). You should also provide the TargetTenant setting for each tenant database. You must upgrade your system database completely before modifying any tenant databases, but once you have done that you can upgrade multiple tenant databases simultaneously to save time.

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.

Overview

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.

Configuration

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:

upgradeManager.getSettings().setSettingName(value);

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())
{
    upgradeManager.getSettings().setSourceDataPersistenceEngine("mysql");
    upgradeManager.getSettings().setSourceSystemDatabaseConnectionString("jdbc/source-system");
    upgradeManager.getSettings().setTargetSystemDatabaseConnectionString("jdbc/target-system");
    upgradeManager.getSettings().setTargetTenant("default");
    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 = @"
        SELECT
            customer_id
        FROM
            SCHEMA_PREFIX.ScormPackage
        WHERE
            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:

SELECT
    <external_package_id>,
    version_id,
    COUNT(*) as duplicate_count
FROM
    ScormPackage
GROUP BY
    <external_package_id>,
    version_id
HAVING
    duplicate_count > 1

SELECT
    <external_registration_id>,
    instance_id,
    COUNT(*) as duplicate_count
FROM
    ScormRegistration
GROUP BY
    <external_registration_id>,
    instance_id,
HAVING
    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.

Online ("Phased") Upgrade

For the vast majority of customers, the actual upgrade process itself is relatively short. Quite often the database migration can be completed in less than an hour. But for customers with larger data sets who are upgrading from a version earlier than 2015.1.x, the amount of schema changes involved make it infeasible to complete the upgrade in a reasonable downtime window.

For this reason, we offer an alternate "online" mode for the upgrade (sometimes called a "phased" upgrade), which allows the majority of the upgrade to be completed without you having to take downtime on your production system. There will still need to be a "cutover" phase that will require you to take downtime, but the downtime will be vastly reduced. This comes at a cost, however; an online upgrade is far more complicated than a traditional upgrade and will typically require close communication between us, your application developers, and your DBA.

The documentation given here is intended merely as a reference; every customer who has enough data to warrant an online upgrade is going to have unique needs. We will instruct you on configuration options to make the online upgrade faster, and will likely need to discuss many options that can improve performance by skipping steps.

Configuration

The online upgrade will more or less require copying the data in every table in Engine to a new version of that table. This means that if you use the default behavior of the tool, the upgrade tool will essentially double the size of your database.

Some customers find this hard to manage, instead finding it easier to let the upgrade tool copy the data to a different database instance or even a different server. To enable this behavior, you will need to specify two connection strings in your configuration file instead of one. The setting names for these connection strings are SourceSystemDatabaseConnectionString and TargetSystemDatabaseConnectionString, representing the original database and the new database, respectively.

Generally speaking, customers with enough data to justify an online upgrade would probably benefit from built-in performance enhancements through configuration options. The specific settings are beyond the scope of this documentation; contact us directly and we will recommend settings based on your system specifications.

Other Caveats

The online upgrade works by copying data from your current tables to updated versions of those tables. From the earliest days of Engine, every row in Engine has had an "update_dt" column that is updated with the current time (in the Engine server's current timezone) whenever that row is created or updated. The values in that column are used to discern which rows have been updated since the last time the tool was run, and thus need to be copied to the new tables.

One thing that the upgrade tool currently cannot do very well, however, is track deletes. Whether or not this is a problem depends on your particular integration. Many customers do not expose Engine functionality that can delete data to their end users. If you do, however, we would strongly recommend that you consider disabling that functionality while you are doing the migration. As always, feel free to discuss these issues with us.

Step-by-Step

The phased upgrade will require running the tool at least four times, with different arguments each time. The overall process is discussed below. For each command discussed, we will use the convention used in the installer documentation, where part of the command is represented as baseCommand. For your convenience, we will repeat the definition of baseCommand here.

• On .NET, baseCommand is simply EngineInstall.exe.
• On Java, baseCommand is java -Dlogback.configurationFile=logback.xml -cp "lib/*" RusticiSoftware.ScormContentPlayer.Logic.Upgrade.ConsoleApp

Any given step of the upgrade can be cancelled at any time; the upgrade periodically saves progress and does as much as it can not to repeat work it has already done. That said, you cannot cancel a step and go back to an earlier one. In particular, during "phase 3" (the cutover phase), you must make sure the step completes; once you have started it you cannot go back to your old version of Engine without restoring from a backup.

Now we will describe the process step-by-step. Note that there are more logical steps than phases, so the "phase" won't always match the number of the step.

1. A gentle reminder: Back up your database before running any of the steps. You will not need to back up again, however, until the cutover step.
2. [Phase One] The first step is to add indexes to your current production schema that will be used to support later steps. The tool can do this automatically, but you may require some downtime to execute this step, depending on your DBMS. The step can always be completed online if you are running SqlServer. If you are running MySQL, you must explicitly specify OnlineIndexingSupported in your configuration file with a value of "true" to enable online indexing, but this will only work if your database server has always been running MySQL 5.6 or later. (If you are running the correct version of MySQL but the database was originally MySQL 5.5 or earlier, you will need to talk to us first.) If you are running Oracle, you will need to consult your specific version's documentation and your license to see whether online indexing is supported; online indexing is only enabled if your license permits it. If your server does support it, you will also need to add OnlineIndexingSupported to your configuration file with a value of "true". If you are running PostgreSQL, you will have to discuss your possibilities with us.
   When you are ready to run the indexing phase, first bring your engine server down if your DBMS requires you to take downtime based on the above considerations. Then, run the following command:
   baseCommand YourConfigFile.xml -p1index
3. [Phase Two] The second phase can always be done online, and will comprise the bulk of the migration. This step will copy rows from your source tables to the "target" tables (which may or may not be in the same database; see the configuration section, above. The command to run this is:
   baseCommand YourConfigFile.xml -p2onlinerowcopy
4. Once phase two completes for the first time, you are ready to schedule the cutover phase. In the meantime, we recommend running phase two once daily to bring in any new rows that have come in since the last time you ran it. On the day of the cutover phase, we recommend running phase two every couple of hours to minimize the number of rows the cutover phase will have to process.
5. When the scheduled cutover time arrives, bring down your current Engine server. Nothing can use Engine during the cutover.
6. Back up your current production database right after you bring the Engine server down.
7. [Phase Three] You are now ready to run phase three. Phase three will run phase two to bring in any rows that have come in since the last time phase two was run; there is no need to run it yourself. Phase three will also add foreign key relationships to the target schema, clean up orphan rows, and do a number of audits to make sure the upgrade completed successfully. The command to run phase three is:
   baseCommand YourConfigFile.xml -p3offlineschema.
8. Once phase three completes, you are ready to deploy the current version of Engine. If you did a two-database upgrade, make sure your new version of Engine is pointing at the new database. Bring the engine server up internally so that you can smoke test the new deployment before bringing up the server to the outside world.
9. [Phase Four] The last upgrade phase performs a number of migrations that could not be performed until after the schema was in a known state. Phase four can be completed online, but many customers go ahead and run this during their cutover phase. The important consideration here is that some xAPI data may not be available until phase four completes. Regardless of whether you are running phase four while Engine is online or not, the command to run it is:
   baseCommand YourConfigFile.xml -p4onlinedeferred.

Migrating Away From Custom Integration Layer

Starting with Engine 2015.1, new customers are able to integrate Engine into their applications exclusively through Engine's API. This allowed them to loosely couple Engine with their application and forgo the need for tightly-coupled custom integration layers that older customers had to write. We now offer the ability to configure the upgrade tool to allow customers that are currently using these custom integration layers to migrate to an integration that exclusively uses the REST API. This process involves changing the database's schema by removing the columns that are defined in the implementations of ExternalPackageId and ExternalRegistrationId and adding the columns and tables that are required for Engine's API integration.

Please reach out to our support team before attempting to upgrade in this way. There are a number of mitigating factors that can potentially prohibit your upgrade from using this process, so let us know that you're interested in this process before starting. We can look at your integration layer and let you know if this path seems like an appropriate option for your situation.

To enable this migration in your upgrade, you have to provide values for the settings below. Please note that our SQL parameters and column names use underscore_case, while the properties in your ExternalId classes follow PascalCase.

• IntegrationToApiRegistrationIdQuery
  The value of this setting should be a SQL query that returns a unique identifier that will be used when communicating with the API about a given registration. The columns of ScormRegistration, including the column(s) that are defined based on the implementation of ExternalRegistrationId, will be passed into the query as parameters and should be used by the query to return an identifier for that specific registration.

For example, if an integration layer's implementation of ExternalRegistrationId has two properties called int CourseId and string UserName, then the SQL query that customer provides might be SELECT CONCAT(@course_id, '-', @user_name) or SELECT u.ApiRegistrationId FROM ApplicationSchema.Users u WHERE u.CourseId = @course_id AND u.UserName = @user_name

• IntegrationToApiCourseIdQuery
  This is similar to the above setting, but it is used to uniquely identify courses instead of registrations. Like above, the columns of ScormPackage are passed into this query as parameters, and this query should return an identifier that corresponds to the package identified by those columns. For integrations that only provide a single property on ExternalPackageId, this query could be something as simple as SELECT @course_id.
• RegistrationToApiLearnerQuery
  Given a row from ScormRegistration, including the columns defined by the implementation of ExternalRegistrationId, this query should return a learner_id (which must be a unique varchar), learner_first_name, and learner_last_name for the learner associated with that registration. If an ExternalRegistrationId that includes a property string UserName to identify individual learners, this query could look like:
  

  SELECT u.Id as learner_id,
      ui.FirstName AS learner_first_name,
      ui.LastName AS learner_last_name
  FROM ApplicationSchema.UserInfo ui
  JOIN ApplicationSchema.Users u ON ui.UserId = u.Id
  WHERE u.UserName = @user_name;
  

• IncludeApiIntegration
  This setting should be provided with a value of true. This will tell the upgrade tool that is needs to include the columns needed for an API integration and to not copy the integration layer columns to the target database.

Once the upgrade tool has been run this way, you should remove the settings LogicIntegrationAssemblyName and LogicIntegrationClassName from your Engine config file, as you will not be able to use your integration layer with your upgraded database. You will also need to replace any logic in your integration layer with calls to Engine's API. You can replace overrides of RollupRegistration and RollupRegistrationOnExit by setting up an endpoint for Engine's postback system to post rollups to. You do not need to replace the logic provided by GetLearnerInformation; the upgrade tool will use the query provided in RegistrationToApiLearnerQuery to populate the learner information of previously created registrations. Refer to the API documentation for a full reference of the endpoints available for your application to make use of.

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:

SELECT
    *
FROM
    ScormRegistration
WHERE
    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:

SELECT
    reg.*,
    act.*,
FROM
    ScormRegistration reg
INNER JOIN
    ScormActivity act ON
        act.engine_tenant_id = reg.engine_tenant_id AND
        act.scorm_registration_id = reg.scorm_registration_id
WHERE
    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.

BatchSizeOverrides (upgrade)

A mapping of phase names to batch sizes which will override the BatchSize setting, for situations where different phases call for different batch sizes.

MaxProcessingQueueSize (upgrade, default: 3)

The maximum number of batches that can be queued up at once.

ThreadCountOverrides (upgrade)

A mapping of worker names to thread counts to allocate a given number of threads for a specific worker.

ThreadCountMultiplier (upgrade, default: 1)

A multiplier that will be applied to the configured thread counts. This allows customers to increase the number of active threads easily while maintaining the default proportions.

DatabaseReadThreads (upgrade, default: 2)

The number of threads available for workers that read from the database. This does not affect the number of threads devoted to getting the rows into the batch pipeline in the first place, which is always a single-threaded operation.

ProcessingThreads (upgrade, default: 1)

The number of threads available for workers that process data in memory. Since I/O is not involved in these threads, the benefit from multi-threading is presumably small.

DatabaseWriteThreads (upgrade, default: 4)

The number of threads available for workers that write to the database.

FilesystemThreads (upgrade, default: 2)

The number of threads available for workers that access the filesystem.

OnlineIndexingSupported (upgrade)

Stipulates whether the CREATE INDEX statement can be used without locking the table. This setting is checked only for Oracle and MySQL; lock-free online indexing is always available on SqlServer, but for technical reasons we can't do lock-free indexing on PostgreSQL. This setting should only be marked true if you are running Oracle Enterprise Edition or MySQL 5.6 or later.

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 explicitly specify the definitions of external package key columns on ScormPackage. If used, the columns must match with the properties of the integration layer's implementation of ExternalPackageId.

ExternalRegistrationKeyColumns (upgrade)

Allows a customer to explicitly specify the definitions of external registration key columns on ScormRegistration. If used, the columns must match with the properties of the integration layer's implementation of ExternalRegistrationId.

ExtraPackageColumns (upgrade)

A comma-delimited list of column specifications for columns the customer added to ScormPackage, but are NOT part of the external key.

ExtraRegistrationColumns (upgrade)

A comma-delimited list of column specifications for columns the customer added to ScormRegistration, but are NOT part of the external key.

IncludeApiIntegration (upgrade)

This setting is often set automatically, but can sometimes be set manually. If given, tells the upgrade tool that it should include the Engine API-related columns.

IntegrationToApiRegistrationIdQuery (upgrade)

This setting must be provided for customers that are migrating from an integration layer to our API integration. This query returns a string that will be used as the registrations' IDs when communicating with the API. The customer's current integration columns will be passed in as parameters.

IntegrationToApiCourseIdQuery (upgrade)

This setting must be provided for customers that are migrating from an integration layer to our API integration. This query returns a string that will be used as this packages' IDs when communicating with the API. The customer's current integration columns will be passed in as parameters.

RegistrationToApiLearnerQuery (upgrade)

This setting must be provided for customers that are migrating from an integration layer to our API integration. Given a row from ScormRegistration (which will contain any integration layer-defined columns), the query must return a learner_id (which must be a unique varchar), learner_first_name, and learner_last_name for the learner associated with that registration.

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.

SkippedPhases (upgrade)

A comma-delimited list of phases that the upgrade should skip entirely. Ask Rustici Support for the name of particular steps you want to skip.

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: 5)

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.

ScormPackageDeduplicationIndex (upgrade)

The index to use for deduplication, if different from the index we are upgrading to. Required when external key columns are removed or renamed.

ScormRegistrationDeduplicationIndex (upgrade)

The index to use for deduplication, if different from the index we are upgrading to. Required when external key columns are removed or renamed.

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.

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.

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.

CtasTables (upgrade)

A list of tables to transform using 'CREATE TABLE AS SELECT' statements instead of ALTER TABLE statements. Set to '*' for all tables.

LwsEnabled (upgrade, default: true)

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