_Migration Utility
The migration utility is a command-line tool built to upgrade the schema of an ODS instance to the latest version. It also supports the execution of custom scripting for the upgrade of extensions.
This documentation covers the general principles and concepts required to develop for the Migration Utility. When you understand this material, or if you just want to get a sense of how to use the utility, you can put it into action using the version-specific, step-by-step guidance in:
Developer Quick Start
The basic steps are simple:
Restore a backup copy of the target ODS to your local SQL Server instance. We recommend:
Start with the basic Version 2.x: Sample ODS Download: EdFi.Samples.Ods/2.0.0.21. This is a small sample ODS, ideal for initial development and testing.
Move on to the larger Version 2.x: Glendale 2.0 ODS Backup (created 2018-06-13). This is a much larger ODS containing sample data, useful for validation and QA.
Choose one of the two options below to launch the migration utility:
Development Overview: The Basics
The table below describes files and folders used by the Migration Utility along with a description and purpose for each resource.
Overview Item | Needed by Whom? | Brief Description & Purpose |
|---|---|---|
Script Directory: |
|
|
Console Utility: |
|
|
Directory: |
|
|
Library: |
|
|
Test Project: |
|
|
Troubleshooting
This section outlines general troubleshooting procedures.
Compatibility Errors
Before the schema is updated, the ODS data is checked for compatibility. If changes are required, the upgrade will stop and exception will be thrown with instructions on how to proceed.
An example error message follows:
After making the required changes (or writing custom scripts), simply launch the upgrade utility again. The upgrade will proceed where it left off and retry with the last script that failed.
Other Exceptions During Development
Similar to compatibility error events, the upgrade will halt and an exception message will be generated during development if a problem is encountered.
After making updates to the script that failed, simply re-launch the update tool. The upgrade will proceed where it left off starting with the last script that failed.
If you are testing a version that is not yet released, or if you need to re-execute scripts that were renamed or modified during active development: restore your database from backup and start over.
Similar to other database migration tools, a log of scripts successfully executed will be stored in the default journal table. DbUp's default location is
[dbo].[SchemaVersions].A log file containing errors/warnings from the most recent run may be found by default in
C:\ProgramData\Ed-Fi-ODS-Migration\Migration.log.
Additional Troubleshooting
See the version-specific migration guides such as How To: Upgrade an ODS Database from Version 2.x to Version 3.2.
Design/Convention Overview
The table below outlines some important conventions in the as-shipped Migration Utility code.
What | Why | Optional Notes |
|---|---|---|
In-place upgrade Database upgrades are performed in place rather than creating a new database copy | Extensions
As a secondary concern, this upgrade method was chosen to ease the upgrade process for a Cloud-based ODS (e.g., on Azure). |
|
Sequence of events that occur during upgrade Specifics differ for each version, but in general the upgrade sequence executes as follows
| Minimize the number of scripts with complex dependencies on other scripts in the same directory/upgrade step.
| The
|
One script per table in each directory, where possible Scripts are named in the format:
This convention does not apply to operations that are performed dynamically | Troubleshooting, Timeout prevention Custom, unknown extensions on the ODS are common. As part of the process of upgrading a highly-customized ODS, an installer is likely to run into a sql exception somewhere in the middle of upgrade (usually caused by a foreign key dependency, schema bound view, etc). In general, we do not want to attempt to modify an unknown/custom extension on an installer's behalf to try and prevent this from happening. It is important that a installer be aware of each and every change applied to their custom tables. Migration of custom extensions will be handled by the installer. Considering the above, in the event an exception does occur during upgrade, we want to make the troubleshooting process as easy as possible. If an exception is thrown, an installer should immediately be able to tell:
Many issues may be fixable from the above information alone. If more detail is needed, the installer can view the code in the referenced script file. By separating script changes by table, we make an effort to ensure that there are only a few lines to look though (rather than hundreds) In addition, each script will be executed in a separate transaction. Operations such as index creation can take a long time on some tables with a large ODS. Splitting the code into separate transactions helps prevent unexpected timeout events | The major downside of this approach is the large number of files it can produce. For example, the With updates becoming more frequent in the future, future versions should not be impacted as heavily. |
Most change logic is held in sql scripts (as of V3) As of | As of Given this advantage, effort was made to ensure that each part of the migration tool (console utility, library, integration tests) could be replaced individually as needed The current upgrade utility contains a library making use of DbUp to drive the upgrade process. In the future, if/when this tool no longer suits our needs, we should be able to take existing scripting and port it over to an alternative upgrade tool (such as RoundhousE), or even a custom built tool if the need ever arises. This convention could (and should) change in the future if upgrade requirements become too complex to execute from SQL scripting alone | |
Two types of data validation/testing options
| Prevent data loss The first type of validation, (dynamic, sql based) is executed on on data that we know should not ever change during the upgrade.
The second type of data validation, integration test based, is used to test the logic and transformations where we know the data should change
Together, the two validation types (validation of data that changes, and validation of data that does not change) can be used to create test coverage wherever it is needed for a given ODS upgrade | The the dynamic validation is performed via reusable stored procedures that are already created and available during upgrade See scripts in the "* |
Upgrade Issue Resolution Approach
It is common to encounter scenarios where data cannot be upgraded directly from one major version to another due to schema changes. A common example is that a primary key change causes records allowable by the previous version schema to be considered duplicates on the upgrade version schema and therefore not allowed.
The general approaches included here are a result of collaboration with the community on how to resolve these common situations, and are documented here as a reference for utility developers to apply in their own work.
Approach 1: Make non-breaking, safe changes to data on the user's behalf. This is the preferred option when practical and safe to do so.
Example: Duplicate session names in the v2.x schema that will break the new v3.x primary key will get a term name appended during upgrade to meet schema requirements.
Consider logging a warning for the user to review to inform them that a minor change has taken place, and mention the log file in documentation.
Approach 2: Throw a compatibility exception asking the user to intervene with updates. Used when we are unable to safely make assumptions on the user's behalf to perform the update for them
See the troubleshooting section of this document for an example of this
The returned user-facing message should explain what updates are required, and very briefly include the reason why if it is not immediately obvious.
Compatibility exceptions should be thrown before proceeding with core upgrade tasks.
We want to make sure that we are not asking someone to make updates to a database in an intermediate upgrade state.
Each message class has a corresponding error code, mirrored in code by an enumeration
This is done so that specific compatibility scenarios can be covered by integration tests where needed
Approach 3: Back up deprecated objects that cannot be upgraded into another schema before dropping them. Last resort option that should be mainly restricted to deprecated items that are no longer have a place in the new model
As a general approach, it is preferred to avoid dropping data items without a way to recover them in case of disaster. The user may choose to delete the backup if they desire.
Avoid this option for tables that exist in both models but simply cannot be upgraded. Should this (hopefully rare) situation occur, consider the option throwing a compatibility exception instead and ask the user back up/empty the table before proceeding.
In general, the option requiring the least amount of user effort while safely preserving all data has been chosen in order to reduce user burden as much as we are able.