Advanced Upgrade Maneuvers

Online 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 phase, we include an in-depth description of its purpose and include the command to execute it. 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 undo 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 number 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, when you run the upgrade tool, include the argument EngineInstall.exe EngineInstall.xml -p1indexjava -Dlogback.configurationFile=logback.xml -cp "lib/*" RusticiSoftware.ScormContentPlayer.Logic.Upgrade.ConsoleApp EngineInstall.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 argument to run this phase is EngineInstall.exe EngineInstall.xml -p2onlinerowcopyjava -Dlogback.configurationFile=logback.xml -cp "lib/*" RusticiSoftware.ScormContentPlayer.Logic.Upgrade.ConsoleApp EngineInstall.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 argument to run phase three is EngineInstall.exe EngineInstall.xml -p3offlineschemajava -Dlogback.configurationFile=logback.xml -cp "lib/*" RusticiSoftware.ScormContentPlayer.Logic.Upgrade.ConsoleApp EngineInstall.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 argument needed to run it is EngineInstall.exe EngineInstall.xml -p4onlinedeferredjava -Dlogback.configurationFile=logback.xml -cp "lib/*" RusticiSoftware.ScormContentPlayer.Logic.Upgrade.ConsoleApp EngineInstall.xml -p4onlinedeferred.

Integrated Upgrade

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

The upgrade tool requires the use of global environment; 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:

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

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.Engine.dllscplogic.jar), and located under the package/namespace RusticiSoftware.ScormContentPlayer.Logic.Upgrade.

Configuration

Within the upgrade management block, you must now configure the upgrade. Setting a value for a given configuration setting looks something like upgradeManager.Settings.SettingName = value;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

Different System and Tenant Databases

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

try (EngineUpgradeManager upgradeManager = new EngineUpgradeManager()) { upgradeManager.getSettings().setSourceDataPersistenceEngine("mysql"); upgradeManager.getSettings().setSourceSystemDatabaseConnectionString("jdbc/source-system"); upgradeManager.getSettings().setSourceTenantDatabaseConnectionString("jdbc/source-tenant"); UpgradeStatus status = upgradeManager.Install(); if (status.getHasErrors()) { // report on the failure } }

Single-Tenant, Copy Upgrade

using (EngineUpgradeManager upgradeManager = new EngineUpgradeManager())
{
    upgradeManager.Settings.SourceDataPersistenceEngine = "mysql";
    upgradeManager.Settings.SourceSystemDatabaseConnectionString = "server=myserver.net;Uid=engine;pwd=secret;Database=source-system;";
    upgradeManager.Settings.TargetSystemDatabaseConnectionString = "server=myserver.net;Uid=engine;pwd=secret;Database=target-system;";
    upgradeManager.Settings.TargetTenant = "default";
    UpgradeStatus status = upgradeManager.Install();
    if (status.HasErrors) {
        // report on the failure
    }
}

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
    }
}

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
    }
}

try (EngineUpgradeManager upgradeManager = new EngineUpgradeManager())
{
    upgradeManager.getSettings().setSourceDataPersistenceEngine("mysql");
    upgradeManager.getSettings().setSourceSystemDatabaseConnectionString("jdbc/source-system");
    upgradeManager.getSettings().setXapiFilesPath("/path/to/xapi/files");
    upgradeManager.getSettings().setPackageToTenantQuery(
        "SELECT" + 
            "customer_id" +
        "FROM" +
            "SCHEMA_PREFIX.ScormPackage" +
        "WHERE" +
            "scorm_package_id = @scorm_package_id;");
    UpgradeStatus status = upgradeManager.FullUpgrade();
    if (status.getHasErrors()) {
        // report on the failure
    }
}

Migrating To API Integration

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.

results matching ""

    No results matching ""