/
How To: Upgrade an ODS Database from Version 2.x to Version 3.1

This version of the Ed-Fi ODS / API is no longer supported. See the Ed-Fi Technology Version Index for a link to the latest version.

 

How To: Upgrade an ODS Database from Version 2.x to Version 3.1

Apr 03, 2019

This document explains how to upgrade an existing v2.x ODS / API to version 3.1. The ODS v3.1 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.1 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.1 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.
     

Downloads

Ed-Fi ODS Migration Tool source code, hosted on Ed-Fi Alliance GitHub: https://github.com/Ed-Fi-Alliance/Ed-Fi-ODS/tree/v3.1.1/Utilities/Migration

Example calendar configuration files: https://github.com/Ed-Fi-Alliance/Ed-Fi-ODS/tree/v3.1.1/Utilities/Migration/Sample%20Calendar%20Config

Compatibility Conditions

This section describes compatibility conditions (i.e., requirements that may need intervention for the compatibility tool to function properly) and suggested remediation.

 

Table

Data Compatibility Requirement

Table

Data Compatibility Requirement

[edfi].[Assessment]

All assessments must have a [Namespace] set. (This data may be found in [edfi].[Assessment] or [edfi].[AssessmentFamily]).

[edfi].[StudentProgramParticipation]
[edfi].[StudentCharacteristic]
[edfi].[StudentIndicator]
[edfi].[StudentLearningStyle]
[edfi].[StudentAddress]
[edfi].[StudentIdentificationCode]
[edfi].[StudentElectronicMail]
[edfi].[StudentInternationalAddress]
[edfi].[StudentLanguage]
[edfi].[StudentRace]
[edfi].[StudentDisability]
[edfi].[StudentTelephone]
[edfi].[PostSecondaryEventPostSecondaryInstitution]

The upgrade utility must be able to locate an [EducationOrganizationId] for every student with data in the listed tables to proceed.

The easiest way to meet this requirement is to ensure that every student has a corresponding record in [edfi].[StudentSchoolAssociation] or [edfi].[StudentEducationOrganizationAssociation].

[edfi].[StaffCredential]

The column [StateOfIssueStateAbbreviationTypeId] must be non-null for all records.

This is the abbreviation for the name of the state (within the United States) or extra-state jurisdiction in which a license/credential was issued.

(Any extension table)

Additional steps are required when extensions are present. Please review the upgrade process detailed below for additional guidance.

Table

Data Compatibility Requirement

Table

Data Compatibility Requirement

[edfi].[GradingPeriod]

  • There must be no duplicate [PeriodSequence] values for the same school during the same grading period.

  • If prompted by the upgrade utility, all [PeriodSequence] values must be non-null

Technical Details:

This compatibility requirement is a result of a primary key change between 2.x and v3.1

  • Old 2.0 Primary Key: [GradingPeriodDescriptorId], [SchoolId], [BeginDate]

  • New 3.1 Primary Key: [GradingPeriodDescriptorId], [SchoolId], [PeriodSequence] (new), [SchoolYear] (new). ([BeginDate] is removed)

[edfi].[DisciplineActionDisciplineIncident]

The 3.1 schema no longer allows discipline action records with students that are not associated with the discipline incident.

Every record in [edfi].[DisciplineActionDisciplineIncident] must have a corresponding record in [edfi].[StudentDisciplineIncidentAssociation] with the same [StudentUSI], [SchoolId], and [IncidentIdentifier].

 

[edfi].[RestraintEvent]

Ensure that there are no duplicate [RestraintEventIdentifier] values for the same student at the same school

[edfi].[OpenStaffPosition]

Ensure that there are no two duplicate [RequisitionNumber] entries for the same education organization

[edfi].[AccountCode]

This table must be empty before upgrading. Due to a major schema change, data in this table cannot be preserved from 2.x to 3.1.

Other Compatibility Conditions

There are several other less common items not included above. The migration utility will check for these items automatically and provide guidance messages as needed. For additional technical details, please consult the Troubleshooting Guide below.
 

Step 2. Install Required Tools

Step 3. Build the Ed-Fi ODS Migration Tool

  • Open <source directory>\Ed-Fi-ODS\Utilities\Migration\Migration.sln in Visual Studio and build in Release mode.

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:

Example 1: Grand-Bend calendar-config.csv
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):

Example 2: calendar-config.csv
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 Tool 

  • Open a new command prompt and navigate to the Ed-Fi ODS Migration Console tool build location:

    CD {YourDevFolderHere}\Ed-Fi-ODS\Utilities\Migration\EdFi.Ods.Utilities.Migration.Console\bin\Release

  • 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.Console.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.1 descriptors are by default located in "\Utilities\Migration\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 Troubleshooting Guide 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

Important notes before you begin

  • When running the migration tool on an ODS with extensions or external dependencies on the EdFi schema, you must add the --BypassExtensionValidationCheck parameter at the command line. This is required in order to permit the migration tool to make changes to an extended database.

  • The Troubleshooting Guide below contains helpful advice for users upgrading an ODS with extensions.

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

  • The edfi migration scripts are located by default in <source directory>\Ed-Fi-ODS\Utilities\Migration\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:

Directory Name

What does this directory contain?

What scripts should you add here?

Directory Name

What does this directory contain?

What scripts should you add here?

00 Setup

The first group of scripts that is run before data migration begins. Setup-type scripts, such as the creation of stored procedures used during upgrade are added here.

Optional: You may place custom scripts that you would like to execute before compatibility checks or schema updates begin here. Scripts in each version directory will be executed in numerical order.

01 Compatibility Check

Ed-Fi data compatibility preconditions are checked using the scripts in this directory.

None needed.

02 Upgrade

Contains the core upgrade scripts for each version. All SQL scripts are run in numerical order by path. Details have been included below this chart.

The v2.0 to v3.1 upgrade will execute scripts in the following version directories:

  • v24_to_v25

  • v25_to_v31

Optional:

  • You may place custom scripts that you would like to execute on your v2.0 database in directory: 02 Upgrade\v24_to_v25\{step}.

  • Scripts that you want to execute on a v2.5 database may be placed in directory: 02 Upgrade\v25_to_v31\{step}.

  • Scripts in each version directory will be executed in numerical order. 

  • Details on version-specific upgrade steps are included below this table.

03 Cleanup

Removes temporary data generated during upgrade

Optional: You may place custom scripts that you would like to execute after all schema updates have been performed here.

Scripts in will be executed in numerical order order.

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

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/Directory Name

What is contained

Step/Directory Name

What is contained

1

Source Validation Check

Calculates a hash value for data key columns that are to remain unchanged throughout the migration process. The hash will be recalculated after all schema changes have been completed. Values before and after are compared. In the event of a data mismatch, the migration process will be halted automatically to protect data integrity.

2

Create Upgrade Tempdata

Version-specific temporary data is created here that will be removed at the end of the upgrade process.

3

Drop Extended Properties

Drops all extended properties on the edfi schema before updates begin.

4

Drop Views

Contains scripts that have been automatically generated to drop edfi views.

You may choose to insert scripts here to drop custom schema bound custom views before upgrade tasks begin.

5

Drop Procedures, Types

Contains scripts that have been automatically generated to drop edfi default stored procedures, triggers, and types.

6

Drop Constraints

Contains dynamic scripting that drops ALL 2.0 constraints and indexes on the edfi schema. Objects are dropped in the following order:

  1. Foreign Keys

  2. Primary Keys

  3. Unique Constraints

  4. Default Constraints

  5. Indexes

Required for most extensions: You must drop ALL foreign keys on your extension tables that have a dependency on the edfi schema. Number your scripts such that they run before the scripts are executed. This will prevent a sql exception from occurring when dependent items on the edfi schema are dropped. You may either perform this action during the setup step, or by inserting custom scripts in this directory. See the below sections for tips and details.

7

Import Descriptors

Imports new Ed-Fi descriptors from the included xml files. For V3.1, this operation is performed via a basic bulk load.

8

Create New Tables

Creates tables that are brand new in 3.1. Populates new tables with existing data that has moved in the latest version where applicable.

9

Update Existing Tables

Makes schema changes to tables that exist in both the old and the new versions.

10

Drop Old Tables

Drops tables no longer used in v3.1.

11

Create Constraints

Contains scripts that are automatically generated. Creates v3.1 indexes, constraints, primary keys, and foreign keys.

12

Create User Roles

Contains script from the v3.1 ODS build to insert default users.

13

Create Views

Contains scripts from the v3.1 ODS build to create v3.1 views.

14

Create Procedures

Contains scripts from the v3.1 ODS build to create v3.1 stored procedures.

15

Create Extended Properties

Contains scripts that are automatically generated. Adds extended properties to all edfi objects

16

Destination Validation Check

Recalculates hash values for all items that were created during step: Source Validation Check. In the event that a data change is found, the migration process will halt and an error message will be thrown.

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/step: 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.1, using the chart provided above for reference.

Step 8d. 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

Objects dropped during upgrade

What

Where to find it after upgrade

What

Where to find it after upgrade

Dropped tables no longer part of the v3 model:

  • Tables capturing Assessment Family data

  • Some PostSecondaryInstitution related references no longer used

  • V2 Ed-Fi Descriptors no longer part of the v3 model

Tables will be copied to schema [v2_to_v3_deprecated] if they are not empty at upgrade time

References to Ed-Fi *Type tables that were contained in v2x descriptors. Types are no longer a part of the v3 model.

The table [v2_to_v3_deprecated].[TypeToDescriptorAssociationBackup] will contain a list of deprecated types ([CodeValue] only) that were associated with Descriptors at the time of upgrade.

Characteristic data that was stored directly on [edfi].[Student] incompatible with the new model because an [StudentEducationOrganizationAssociation] reference is now required beginning in v3 . This includes economic disadvantaged status, school food services eligibility, and displacement status.

For all students that contain non-null data in the related columns on [edfi].[Student] , an entry will be generated in [v2_to_v3_deprecated].[Student].

Notes on key important items altered during upgrade

Affected Objects

Change Description

Why

Affected Objects

Change Description

Why

Types, Descriptors

  • Namespace changed from v2 format (http://{edorg}/Descriptor/{DescriptorName}.xml) to v3 format (uri://{edorg}/{DescriptorName})

    • Rare: In the event that duplicate descriptors associated with the same organization are encountered, an integer will be added to the [CodeValue] for uniqueness

  • New v3 descriptors inserted during upgrade with namespace specified at upgrade time

  • In the event that a Type table was modified from the default, these changed types will be converted into new descriptors as well

Note: The state of the descriptor table with old namespaces is copied to [v2_to_v3_deprecated].[Descriptor] at upgrade time

Significant type/descriptor changes have occurred between v2 and v3

[edfi].[Section]:
[UniqueSectionCode] (v2x)

  • The v2 [UniqueSectionCode] is used to create the new v3 [SectionIdentifier]

  • For sections that are considered duplicates under the new v3 model, the new [SectionIdentifier] will contain the old [UniqueSectionCode] plus an integer that is automatically appended during upgrade.

In the older v2 model, the primary key on [edfi].[Section] permits a [UniqueSectionCode] can be associated with the same School/Session/Course multiple times.

As of v3, the key has been simplified. A [SectionIdentifier] can only be associated with the same School/Session/Course once.

[edfi].[Session]:
[SessionName]

  • In the event that there are two sessions with the same [SessionName] are associated with the same school and year, the new [SessionName] after upgrading will include the name of the associated term.

In the older v2 model, the Session table used [TermDescriptorId] in the key. The v3 key replaces [TermDescriptorId] with [SessionName]