Overview

The ODS Migration Utility is a command-line tool built to upgrade the schema of an ODS instance up to the latest version, along with data migration.

It currently supports data migration from Ed-Fi Data Standard v2.0 and Ed-Fi Data Standard v2.2 to Ed-Fi Data Standard v3.2. The utility has out-of-the-box support for migrating an as-shipped ODS to the latest version. With additional customized scripting, the Migration Utility can be easily adapted and used to migrate extended ODS instances. ODS shared instances may take advantage of this utility. For year-specific instances, migration may not be a concern as a new ODS is created at the beginning of every school year.

Contents:

Prerequisites

• .Net Core version 3.1
• Add package source Ed-Fi MyGet by running the following in Powershell:
  • Register-PackageSource -Name Ed-FiMyget -Location https://www.myget.org/F/ed-fi/ -ProviderName NuGet

Usage Scenarios

The following table summarizes the supported scenarios for the migration utility:

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:

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.

Usage Walkthrough

This section explains how to upgrade an existing v2.x ODS / API to version 3.4. The ODS v3.4 database update includes the latest enhancements based on feedback from the Ed-Fi Community. 

The steps can be summarized as:

 A compatibility reference chart, a command-line parameter list, and a troubleshooting guide are included. Each upgrade step is outlined in detail below. 

Step 1. Read the Ed-Fi ODS v3.4 Upgrade Overview

Before you get started, you should review and understand the information in this section.

Target Audience

These instructions have been created for technical professionals, including software developers and database administrators. The reader should be comfortable with performing the following types of tasks:

• Creating and managing SQL Server database backups
• Performing direct SQL commands and queries 
• Execution of a command-line based tool that will perform direct database modifications
• Creating a configuration file for upgrade (.csv format)
• Writing custom database migration scripts (Extended ODS only)

General Notes

• Your v2.x of the Ed-Fi ODS / API will checked for compatibility automatically during the migration process. If changes are needed, you will be prompted at the command line by the migration utility. A summary of commonly encountered compatibility conditions has been included in this section for reference.
• The new v3.4 schema contains upgrades to the structure of primary keys on several tables. In most instances, these new uniqueness requirements will be resolved automatically for you with no action required.
• There are some areas where new identities cannot be generated automatically on your behalf during upgrade. These tables will need to be updated manually.
   

Step 2. Install Required Tools

• Ensure that you have an instance of the Ed-Fi ODS / API running locally that has been set up following the instructions in Getting Started - Installation Steps.

Step 3. Install the Ed-Fi ODS Migration Utility

•  Install the Ed-Fi ODS Migration utility from a PowerShell prompt using the following command:

c:\>mkdir {YourInstallFolder}
c:\>dotnet tool install EdFi.Ods.Utilities.Migration --tool-path {YourInstallFolder}

Step 4. Back Up and Create a Working Copy of the 2.x Target Database

• Create a full backup of the target v2.x EdFi_Ods database.

• Restore this backup to your SQL Server instance as a copy, in a new location.
• Make note of the database connection string as it will be required for a later step.

Step 5. (Multi-Year ODS Only) Create a Calendar Configuration File

In most cases, you can skip this step if the ODS contains valid calendar data for only one school year. The migration tool will usually be able to resolve this item automatically for you.

The v3.x ODS upgrade includes enhancements to the calendar, including tracking of school year information for every calendar event and session. To ensure that your new calendar is accurate, the migration tool will need to know which school year to associate with every calendar item in your ODS. 

Example calendar configuration files can be downloaded from the download panel on the right. 

The following is an example of a calendar configuration file (CSV) for Grand Bend ISD:

SchoolId,SchoolYear,StartDate,EndDate
255901001,2011,2010-08-23,2011-05-27
255901044,2011,2010-08-23,2011-05-27
255901107,2011,2010-08-23,2011-05-27

Note that each line contains a SchoolId, SchoolYear, the first calendar day and the last calendar day of the school year.

• Create a new calendar configuration file (.CSV) by referencing the above examples. Your configuration file must match the following format (including header row):

SchoolId,SchoolYear,StartDate,EndDate
{Your_First_School_Id},20XX,{FirstDayOfSchoolYear},{LastDayOfSchoolYear}
{Your_Second_School_Id},20XX,{FirstDayOfSchoolYear},{LastDayOfSchoolYear}
...

• Store the configuration file in a location that is accessible by your SQL server

Step 6. (ODS with NO Extensions Only) Run the Migration Utility

• Open a new command prompt and navigate to the Ed-Fi ODS Migration utility installation folder:

  CD {YourInstallFolder}

• Launch the console tool. Modify the below example parameters to match your environment. Replace the example "uri://ed-fi.org" with the Namespace Prefix to insert for your new descriptors:

  .\EdFi.Ods.Utilities.Migration.exe --Database "YOUR_DATABASE_CONNECTION_STRING_HERE" --CalendarConfigPath "C:\PATH\TO\YOUR\CALENDAR_CONFIG_IF_APPLICABLE.csv" --DescriptorNamespace "uri://ed-fi.org" --CredentialNamespace "uri://ed-fi.org"

• Notes for upgrades performed on a remote database
  • The calendar configuration file path provided during upgrade must be accessible by your SQL server for importing and validation.
  • The XML files used to create v3.x descriptors are by default located in "{YourIstallFolder\.store\edfi.ods.utilities.migration\1.0.0\edfi.ods.utilities.migration\1.0.0\tools\netcoreapp2.2\any\Descriptors\Descriptors\3.1". This directory must also be accessible by your SQL server for importing. If you need to copy this directory to a new location, you may make use of the --DescriptorXMLDirectoryPath "C:\PATH\TO\YOUR\DESCRIPTOR\XML" parameter to point the migration tool to the new location of these items
• Users who are running the migration tool on an extended ODS must use the --BypassExtensionValidationCheck option during upgrade in order to allow the tool to make changes when extension dependencies are present.  
  • Details on upgrading an extended ODS are included in the steps below.

• The console tool will check your ODS for compatibility, and then proceed to perform an in-place upgrade on the specified database.
• If you encounter any compatibility messages or errors during upgrade
  • During the upgrade process, your ODS will be checked for compatibility with the latest version.  If changes are required, you may encounter a compatibility message, and the upgrade will stop.  
    Example
    
  • 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.
  • See the 75106740 below for additional guidance
• Once the process runs to completion with no errors, your upgrade is complete 
  
• For a summary of all available parameter options for the Ed-Fi ODS console-based migration tool, please see the included reference below.

Step 7. (Extended/Modified ODS Only) - Perform a Test Migration Without Extensions

• It is highly recommended that you first perform a test migration without your extension tables present in order to ensure that all core upgrade requirements have been met
  • On your ODS copy, temporarily drop all extension tables. 
  • Complete the migration process exactly as detailed in Step 6, above.
  • Once the migration has completed successfully on the core Ed-Fi data, restore your v2.0 ODS working copy from backup, including all extension tables, and proceed with the next step

Step 8. (Extended/Modified ODS Only) - Write Custom Migration Scripts for Extensions

Step 8a. Locate the Ed-Fi Migration scripts and review the current upgrade conventions

• The edfi migration scripts are located by default in {YourInstallFolder}\.store\edfi.ods.utilities.migration\1.0.0\edfi.ods.utilities.migration\1.0.0\tools\netcoreapp2.2\any\Scripts.
• All SQL scripts are run in numerical order, with version-specific upgrade further categories.
• The v2.0 to V3.1 upgrade passes though v2.5 on the way. It will execute scripts in the following version directories:
  • v24_to_v25
  • v25_to_v31
    

The directory structure appears as follows:

Version-Specific Upgrade Step Details for v2.x to v3.4

The core upgrade script directory, 02 Upgrade, contains several upgrade steps for each version which are detailed below. You can insert custom scripts at any point in the process as needed for custom Extensions.

Step 8b. Write scripts to drop ALL foreign keys on your extension tables that depend on the edfi schema

• All edfi primary keys and indexes are dropped automatically during upgrade. The existence of any external constraint that depends on edfi data will result in an error state.
• You may insert these scripts in the following directory
  • Option 1: Insert with similar Ed-Fi scripts in directory: Scripts/02 Upgrade/{version}/## Drop Constraints
  • Option 2:  (To run before all upgrade tasks begin):  Scripts/00 Setup/{version}
    
• Scripts will be executed in numerical order. Number your scripts such that they run before the edfi scripts are executed. This will prevent a sql exception from occurring when dependent items on the edfi schema are dropped.
•  Tip: The following query can quickly identify all constraints that need to be dropped for migration to proceed. Use this as a guide when writing your custom migration scripts.

SELECT DISTINCT
		parentSchema.name AS 'External Schema Name',
		parentObject.name AS 'Table Name',
		constraintObject.name AS 'Constraint Name',
		CONCAT('References ', referencedSchema.name, '.', referencedObject.name) AS [Conflict Reason],
		CONCAT('ALTER TABLE ', parentSchema.name, '.', parentObject.name, ' DROP CONSTRAINT ',  constraintObject.name) AS [Example Code For Reference]
	FROM sys.foreign_key_columns fk
	INNER JOIN sys.objects parentObject
	ON fk.parent_object_id = parentObject.object_id
	INNER JOIN sys.objects referencedObject
	ON fk.referenced_object_id = referencedObject.object_id
	INNER JOIN sys.schemas parentSchema
	ON parentObject.schema_id = parentSchema.schema_id
	INNER JOIN sys.schemas referencedSchema
	ON referencedObject.schema_id = referencedSchema.schema_id
	INNER JOIN sys.objects constraintObject
		ON constraintObject.object_id = fk.constraint_object_id
	WHERE referencedSchema.name = 'edfi'
		AND parentSchema.name NOT IN ('edfi', 'migration_tempdata')
		AND parentObject.type = 'U'
		AND referencedObject.type = 'U'

Step 8c. Create Other Migration Scripts as Needed for Custom Extensions 

• After creating scripts that drop all dependent constraints, you can re-test your data migration to ensure that it will complete without error. Be sure to add the --BypassExtensionValidationCheck option at the command line.
• Create the remaining scripts needed to upgrade your extension tables for v3.x, using the chart provided above for reference.

Step 8d. Create Changed Record Queries Migration Scripts for Custom Extensions (Required only if the feature was enabled)

• Changed Record Queries is an optional feature that was introduced in ODS 3.1. Changed Record Query artifacts for the core entities will be upgraded automatically by the migration utility if the feature was enabled in the source database. However if you are using Changed Record Query feature in the source ODS and you have custom extensions, then the change scripts to update Changed Record Query artifacts for the extension tables will need to be added into the scripts folder located in Scripts/02 Upgrade/{Version}_Changes.

Step 8e. Upgrade Your Extended ODS

• With all custom scripting in place, you may now proceed with the upgrade as detailed in Step 6. (ODS with NO Extensions Only) Run the Migration Tool, above.

Step 9. Post-Upgrade: Review Data Changes 

1. Review warnings/action items generated during upgrade
   
   • By default, these will be stored in "C:\ProgramData\Ed-Fi-ODS-Migration\Migration.log"

   • This message list will contain action items that my require your attention
2. Review your upgraded ODS.

   • A schema named [v2_to_v3_deprecated] will be created to store a copy of major objects that were dropped or altered significantly during upgrade, including v2 descriptor/type references. If desired, this entire schema is safe to delete once you are positive that the data contained will no longer be needed.

Some data elements that were part of the v2 model are either no longer a part of the v3 model, or may be altered to meet the upgraded schema requirements. The following chart summarizes a list of major items that will be altered or dropped during upgrade