Skip to end of metadata
Go to start of metadata


The migration tool is currently under active development, and a package has not yet been released. The following instructions are currently a developer preview. Code artifacts are in the /development-v3 branch.

This document will explain how to upgrade an existing 2.x ODS to version 3.0.  The version 3.0 update includes the latest enhancements based on feedback from the Ed-Fi Community. 

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)
  • Extended ODS only:  writing custom database migration scripts
  • Developer preview only:
    • Cloning source code from a specific branch on the Ed-Fi-ODS repository
    • Building the Ed-Fi-ODS Migration Tool executable from the source code above

The steps can be summarized as:

 A compatibility reference chart and troubleshooting guide are included.   Each upgrade step is outlined in detail below. 

Downloads

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

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

Developer preview note: this link currently points to a directory on the developer-v3 branch, which is under active development. The target branch will be updated at a later date

Example Upgrade - Grand Bend Sample Transportation Extension.zip: contains sample code for reference to perform an upgrade to the Grand Bend Sample Extension referenced here



Ed-Fi ODS 3.1 Upgrade Compatibility Overview


Your 2.0 Ed-Fi ODS will checked for compatibility automatically for you 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 3.0 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.  


Overview of commonly encountered compatibility conditions:

 Very common compatibility conditions (click to expand)


TableData Compatibility Requirement
[edfi].[Assessment]

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

 Expand for full list: Several tables capturing student information

[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.
 Less common compatibility conditions (click to expand)
TableData 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.0

  • Old 2.0 Primary Key: [GradingPeriodDescriptorId], [SchoolId], [BeginDate]
  • New 3.0 Primary Key: [GradingPeriodDescriptorId], [SchoolId], [PeriodSequence] (new), [SchoolYear] (new).  ([BeginDate] is removed)
[edfi].[DisciplineActionDisciplineIncident]

The 3.0 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.0.


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 section of this document.



Step 1. Install Required Tools

You will need:

Step 2. Download the Ed-Fi ODS Migration Tool

  • Build the Visual Studio solution file, Ed-Fi-ODS\Utilities\Migration\Migration.sln, in release mode

Step 3. Back up and create a working copy of the 2.x target database

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

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

Step 4. (Multi-Year ODS) Create a calendar configuration file

The 3.0 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. 

Tip

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

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
  • Each line contains a SchoolId, SchoolYear, the first calendar day of that school year, and the last calendar day
  • 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 5. (ODS with NO Extensions Only) Run the Migration Tool 

  • Developer preview only: 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 3.0 descriptors are by default located in "\Utilities\Migration\Descriptors\v3.0".  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
  • Note for large datasets and slow servers:  If your ODS is large, and you need to increase the default SQL command timeout, you may make use of the add the --Timeout "TimeoutInSeconds" parameter.  The default timeout is 1200 (20 minutes)
  • 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
      Example compatibility exception
    • 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.
    • Developer preview note:  Until an official version release, If pulling down new changes under active development for this version, you will need to restore your ODS from backup and start the upgrade over.   While under active development,  structural changes to scripts and journaling features are likely to occur.  Once the tool has been officially released, please be sure to run the upgrade from a clean state again even if you have previously completed an upgrade successfully.
    • See the troubleshooting section at the foot of this document 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:

Ed-Fi ODS Migration Tool:  Parameter Reference

ParameterDescriptionExampleRequired?
--Database
Database Connection String
--Database "Data Source=YOUR\SQLSERVER;Initial Catalog=Your_EdFi_Ods_Database;Integrated Security=True"
Yes
--DescriptorNamespace

Descriptor Namespace prefix to be used for new and upgraded descriptors.

Namespace must be provided in 3.x format as follows: uri://[education_organization_here]

Valid characters for an education organization name:  alphanumeric and $-_.+!*'(),

Script Usage: Provided string value will be escaped and substituted directly in applicable sql where the $DescriptorNamespace$ variable is used

--DescriptorNamespace "uri://ed-fi.org"
Yes
--CredentialNamespace

Namespace prefix to be used for all new Credential records.

Namespace must be provided in 3.x format as follows: uri://[education_organization_here]

Valid characters for an education organization name:  alphanumeric and $-_.+!*'(),

Script Usage: Provided string value will be escaped and substituted directly in applicable SQL where the $CredentialNamespace$ variable is used.

--CredentialNamespace "uri://ed-fi.org"

Yes, if table edfi.StaffCredential has data

Optional if table edfi.StaffCredential is empty

--CalendarConfigPath

Path to calendar configuration, which must be accessible from your sql server.

Script Usage: Provided string value will be substituted directly in dynamic SQL where the $CalendarConfigPath$ variable is used. The ' character is not permitted by the upgrade utility for this value.

--CalendarConfigPath "C:\PATH\TO\YOUR\CALENDAR_CONFIG.csv"

Single-Year ODS: No (unless prompted by the upgrade tool)

Multi-Year ODS: Yes

--DescriptorXMLDirectoryPath

Path to directory containing 3.0 descriptors for import, which must be accessible from your sql server


Script Usage: Provided string value will be substituted directly in dynamic SQL where the $DescriptorXMLImportDirectoryPath$ variable is used. The ' character is not permitted by the upgrade utility for this value.

--DescriptorXMLDirectoryPath "C:\PATH\TO\YOUR\DESCRIPTOR\XML"

Local Upgrade: No (applicable to most cases)

Remote Upgrade: Yes

Used if the Descriptor XML directory has been moved to a different location accessible to your sql server

--BypassExtensionValidationCheckPermits the migration tool to make changes if extensions or external schema dependencies have been found--BypassExtensionValidationCheck

Extended ODS: Yes This includes any dataset with an extension schema or foreign keys pointing to the Ed-Fi schema.

Others: No

--TimeoutSQL command query timeout, in seconds.--Timeout 1200No. (Default timeout is 1200 seconds). Increase if timeouts are encountered on a large ODS
--ScriptPathPath to the location of the SQL scripts to apply for upgrade, if they have been moved--ScriptPath "C:\PATH\TO\YOUR\MIGRATION_SCRIPTS"No. (Only needed if scripts have been moved from the default location)

Step 6. (Extended/Modified ODS Only) - Perform a test migration without your 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. (ODS with NO Extensions Only) Run the Migration Tool
    • Once the migration has completed successfully on the core Ed-Fi data, restore your 2.0 ODS working copy from backup, including all extension tables, and proceed with the next step

Step 7. (Extended/Modified ODS Only) - Write custom migration scripts for your 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

Provided Example:

An example extension upgrade is included for reference here: Example Upgrade - Grand Bend Sample Transportation Extension.zip.  The attached example upgrade performs a migration on the Grand Bend Sample Extension referenced here.  For demonstration purposes, a new script is added that adds a 3.0 session reference to the StudentTransportation extension table. 

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

  • Developer preview only:  The edfi migration scripts are located by default in \Utilities\Migration\Scripts\v2_to_v3
  • All SQL scripts are run in numerical order
  • The directory structure appears as follows

    Directory NameWhat is containedWhat scripts should you add here?
    01 Bootstrap

    The first group of scripts that is run before data migration begins.

    This is where the Ed-Fi data compatibility preconditions are checked. Stored procedures and storage of temporary reference items are handled here as well

    Optional: You may insert any setup-type scripts here. These items will be executed before any schema changes have been made.
    02 Source Validation CheckCalculates 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.

    Optional: You may choose to add a validation check for extension items here that should not change during upgrade. The specified tables/columns will be scanned before and after the update process. An error will be thrown if the data contained is not an exact match.

    The stored procedures used here are designed to be reusable for extension tables. See the included example scripts for a working example demonstrating how to make use of this feature.

    The validation check is run during step "15 Destination Validation Check"

    03 Drop Extended PropertiesDrops all extended properties on the edfi schemaOptional: If you wish, you may add scripts to drop outdated extended properties. The included example script shows an example where we make use of a stored procedure to perform this action dynamically.
    04 Drop ViewsContains scripts that have been automatically generated to drop old Ed-Fi viewsRequired if you have schema binding views, optional otherwise: You may insert all scripts here to drop views before upgrade
    05 Drop ProceduresContains scripts that have been automatically generated to drop Ed-Fi stored procedures/triggersOptional: You may insert scripts here to drop old procedures that you no longer need or that need to be upgraded for the 3.0 schema
    06 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 edfi scripts are executed. This will prevent a sql exception from occurring when dependent items on the edfi schema are dropped. See the below sections for tips and details.
    07 Import DescriptorsImports new descriptors from the included xml filesOptional: You may add scripts here that create new descriptor items
    08 Create New TablesCreates tables that are brand new in 3.0. Populates new tables with existing data where applicable.Optional: You may insert scripts here to create tables that did not previously exist.
    09 Update Existing TablesMakes schema changes to tables that previously existed.Required if your extensions depend on data that no longer exists in 3.0, such as types: If you need to make changes to the schema on your extension items, add these change scripts here. A common example would be to convert your references to all old 2.0 Type tables to descriptors.
    10 Drop Old Tables

    Drops tables no longer present in 3.0

    For non-type tables where the data has not been copied elsewhere in the schema (such as AssessmentFamily items), the data is backed to the [v2_to_v3_deprecated] schema before dropping.

    Optional: You may drop tables that are no longer needed here. If you wish, you may back up old data by copying it to the [v2_to_v3_deprecated] schema.
    11 Create ConstraintsContains scripts that are automatically generated. Creates 3.0 indexes, constraints, primary keys, and foreign keysRequired for most extensions: Add scripts here that re-create any constraints that were previously dropped. Creates all new indexes, constraints, primary keys, and foreign keys referencing the 3.0 schema here.
    12 Create User RolesContains scripts that are automatically generated. Creates default roles, if they do not existOptional: Assign users/roles as needed.
    13 Create ViewsContains scripts that are automatically generated. Creates 3.0 Ed-Fi viewsOptional: Create (or refresh) your custom views that reference the new 3.0 schema here.
    14 Create ProceduresContains scripts that are automatically generated. Creates 3.0 Ed-Fi stored procedures and triggers.Optional: If needed, you may add stored procedures or triggers that reference the new 3.0 schema here.
    15 Create Extended PropertiesContains scripts that have been automatically generated to create all 3.0 Ed-Fi extended properties.Optional: If needed, you may create/update your custom extended properties here.
    16 Destination Validation CheckRecalculates hash values for all items that were created during step 02 Source Validation Check. In the event that a data change is found, the migration process will halt and an error message will be thrown. No need to add custom scripts here: All validation tasks that were created in step "02 Source Validation Check" will be automatically handled here.
    17 CleanupRuns last after all other steps have completed. Deletes temporary data that was used for the upgrade.Optional: You may insert scripts here that delete temporary data that you have created (e.g.: stored procedures created in step "01 Bootstrap" that you no longer need)

Step 7b. 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.  
  • Insert these scripts into the directory 06 Drop Constraints
    • 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.
  • See included example scripts for reference
  • (info) 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.
Quickly show all external dependencies that will need to be dropped
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 7c. Create other migration scripts needed for your custom extensions 

  • After creating scripts that drop all dependent constraints,  you may 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.0, using the chart provided in this step for reference

Step 7d. Upgrade your extended ODS

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

 Click to expand: Objects altered or dropped during upgrade

Objects dropped during upgrade

WhatWhere 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 ObjectsChange DescriptionWhy
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

    • Beginning in v3, type tables are no longer part of the model. These objects will be replaced with several Descriptors new to v3.
    • Namespaces are now required, and must be the new v3 format exactly. The upgrade utility will do the work to convert valid v2 namespaces to the new v3 format for you.
      • Rare: The schema requires that each combination of [CodeValue] + [Namespace] be unique for each Descriptor, which could result in potential duplicates in some cases where duplicate v2 [CodeValue] entries exist.

[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]


Troubleshooting Guide

The below section provides additional guidance for many common compatibility issues that can be encountered during the upgrade process.

Error received during upgradeExplanationHow to fix it

Action Required:  Unable to proceed with migration because the BypassExtensionValidationCheck option is disabled ...

An external dependency on the edfi schema has been found. As a courtesy, the migration tool will not proceed with the upgrade process without your permission.

Common examples:

  • You have a table on the extension schema
  • An object on any schema other than edfi has a foreign key that depends directly on data on the edfi schema

Why: This notification is intended to bring extension items to your attention that will require manual handling. All primary keys and indexes on the edfi schema are dropped during upgrade. The existence of any external constraint that depends on these objects will result in an unhandled sql exception. (For details, see the troubleshooting section: "SqlException : The constraint '{CONSTRAINT_NAME_HERE}' is being referenced by table '{TABLE_NAME_HERE}', foreign key constraint '{FORIEGN_KEY_NAME_HERE}'"

After reviewing the data and dependencies on your extension tables, add the --BypassExtensionValidationCheck option at the command line. This will give the migration tool permission to proceed even if there are extension items present.

Please review (Extended/Modified ODS Only) - Write custom migration scripts for your extensions before proceeding.


Action Required: edfi.StaffCredential ...The column StateOfIssueStateAbbreviationTypeId must be non-null for all records. This value will become part of a new primary key on the 3.0 schema.

Add a [StateOfIssueStateAbbreviationTypeId] for all records in [edfi].[StaffCredential]. 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.


The table is compatible for upgrade if the below query returns 0 results.

Expand to see code
SELECT *
FROM [edfi].[StaffCredential]
WHERE [StateOfIssueStateAbbreviationTypeId] IS NULL

Action Required - An EducationOrganizationId must be resolvable for every student in the following table(s) for compatibility with the upgraded schema starting in version 3.0:

(Provided list of tables includes one or more of the following):

  • 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.

Beginning in version 3.0, the schema structure now requires that these student information items be defined separately for each associated EducationOrganization rather than simply linking them to a student.

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

The upgrade tool will use this information to handle the rest of the data conversion tasks for you.


Action Required: edfi.Assessment ...All assessments must have a [Namespace] set.  (This data may be found in [edfi].[Assessment] or [edfi].[AssessmentFamily]). Beginning in version 3.0, the schema required that this column be non-null.

Add a [Namespace] for all assessment records.

The table is compatible for upgrade if the below query returns 0 results

Expand to see code
SELECT *
FROM [edfi].[Assessment] a
LEFT JOIN [edfi].[AssessmentFamily] f
	ON a.[AssessmentFamilyTitle] = f.[AssessmentFamilyTitle]
WHERE COALESCE(a.[Namespace], f.[Namespace]) IS NULL
Action Required: edfi.OpenStaffPosition ...

There may be no two duplicate RequisitionNumber entries for the same education organization.

This is uniqueness if required for the upgraded primary key on this table.

Update the RequisitionNumber values on edfi.OpenStaffPosition. Ensure that the same value is not used twice for the the same education organization.

The table is compatible for upgrade if the below query returns 0 results.

Expand to see code
SELECT [EducationOrganizationId], [RequisitionNumber], COUNT([RequisitionNumber]) AS [NumberOfMatchingRecords]
FROM [edfi].[OpenStaffPosition]
GROUP BY [EducationOrganizationId], [RequisitionNumber]
HAVING COUNT ([RequisitionNumber]) > 1
Action Required: edfi.RestraintEvent ...

There may be no two duplicate RestraintEventIdentifier values for the same student at the same school

This is uniqueness if required for the upgraded primary key on this table.

Update the RestraintEventIdentifier values on edfi.RestraintEvent. Ensure that the same RestraintEventIdentifier is not reused for the same student at the same school.

The table is compatible for upgrade if the below query returns 0 results.

Expand to see code
SELECT [RestraintEventIdentifier], [SchoolId], [StudentUSI], COUNT([RestraintEventIdentifier]) AS [NumberOfDuplicateRecords]
FROM [edfi].[RestraintEvent]
GROUP BY [RestraintEventIdentifier], [SchoolId], [StudentUSI]
HAVING COUNT([RestraintEventIdentifier]) > 1
Action Required: edfi.GradingPeriod ...There may be no two duplicate PeriodSequence values for the same school during the same grading period.

Additionally, if prompted by the upgrade tool, all PeriodSequence values must be non-null.


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

  • Old 2.0 Primary Key: GradingPeriodDescriptorId, SchoolId, BeginDate
  • New 3.0 Primary Key: GradingPeriodDescriptorId, SchoolId, PeriodSequence (new)SchoolYear (new).  (BeginDate is removed)

Ensure that there are no two records with the same SchoolIdGradingPeriodDescriptorIdPeriodSequence, and SchoolYear.

    • Note: The edfi.GradingPeriod new SchoolYear column is derived from the old 2.0 BeginDate value. The SchoolYear from your calendar configuration will be used

    • If there are multiple records with the same SchoolIdGradingPeriodDescriptorIdPeriodSequence, and SchoolYear you must make changes to ensure that all records are unique. 
      • Tip: Depending on the needs of your organization, you may decide to remove unneeded conflicting records, update the edfi.GradingPeriod.PeriodSequence values, or assign a different value to edfi.GradingPeriod.PeriodSequence.GradingPeriodDescriptorId


Tip: You can be certain that the table is compatible for upgrade if the below query returns 0 results.


Version 1 - Simplified Calendar: Expand to see code
SELECT [SchoolId], [GradingPeriodDescriptorId], [PeriodSequence], COUNT([PeriodSequence]) AS [NumberOfDuplicateRecords]
FROM [edfi].[GradingPeriod]
GROUP BY [SchoolId], [GradingPeriodDescriptorId], [PeriodSequence]
HAVING COUNT([PeriodSequence]) > 1

(Requirements may be less strict than noted in the above query for some some multi-year Calendars. See the compatibility check script in the 01 Bootstrap directory for exact technical requirements)

Action Required: edfi.DisciplineActionDisciplineIncident ...

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

The 3.0 schema no longer allowed discipline action records with students that are not associated with the discipline incident. A foreign key will be added to the new schema enforcing this.

  • Remove any records in [edfi].[DisciplineActionDisciplineIncident] that involve a student not associated with the incident.
  • For students that are involved in the incident, add a corresponding record to [edfi].[StudentDisciplineIncidentAssociation] with the same [StudentUSI], [SchoolId], and [IncidentIdentifier].

The table is compatible for upgrade if the below query returns 0 results

Expand to see code
SELECT d.*
FROM [edfi].[DisciplineActionDisciplineIncident] d
LEFT JOIN [edfi].[StudentDisciplineIncidentAssociation] s
ON s.[StudentUSI] = d.[StudentUSI]
		AND s.[SchoolId] = d.[SchoolId]
		AND s.[IncidentIdentifier] = d.[IncidentIdentifier]
WHERE s.[StudentUSI] IS NULL
 

Calendar configuration file error - various similar messages may appear that mention a table name and a list of school ids.


Example error:
Found {#} date ranges in [edfi].[ Table name will vary: Session, CalendarDate, GradingPeriod] which did not fall within the dates specified in the calendar configuration.  The top 10 affected schools will be listed



The calendar configuration file contains the start date and end date for each school year. To support the new calendar features in 3.0, the migration tool uses this configuration file to assign a SchoolYear to all CalendarDate related entries in the database.


There are several variations of this type of error which all have a similar meaning. The migration tool found date records in the specified table that could not be assigned a school year based on the BeginDate and EndDate information provided.


Either the calendar configuration file will need to be edited, or data in the specified table will need to be modified.

  1. Make note of the list of School Ids and the Table name provided in the error message
  2. Open your calendar configuration file. Find the line corresponding to each School Id. Ensure that the BeginDate and EndDate for each SchoolYear is accurate
    • If a School Id is missing from the calendar configuration file, be sure to add it
  3. Check the date information in the specified table for accuracy.
    • Carefully check the ODS records in table specified by the error message for the specified School Id
    • Make sure there are no date entries that fall outside the school year information you have provided in the calendar configuration
    • If the table already has SchoolYear data (such as edfi.Session or edfi.GradebookEntry), make sure the SchoolYear data is accurate

Example:

Example Query: [edfi].[CalendarDate]
SELECT *
FROM [edfi].[CalendarDate] 
WHERE [SchoolId] = {School_Id_from_error_message}
AND 
(
	[Date] < {BeginDate_from_calendar_configuration_file}
	OR [Date] > {EndDate_from_calendar_configuration_file}
)




SqlException :

The constraint '{CONSTRAINT_NAME_HERE}' is being referenced by table '{TABLE_NAME_HERE}', foreign key constraint '{FORIEGN_KEY_NAME_HERE}'

or similar:

The object '{OBJECT_NAME_HERE}' is dependent on column '{COLUMN_NAME_HERE}'.

This type of unhandled SQL exception occurs when the migration process tries to alter an item that is being referenced by an external object, such as a foreign key on another table, or schema bound view

Common causes

  • The BypassExtensionValidationCheck option was enabled, and the EdFi Migration script tried to drop an index that is being referenced by a foreign key on another schema
  • There is a schema-bound view with a direct dependency on an table that is being modified

Make sure that you have dropped ALL foreign keys and views from other schemas that have a dependency on the edfi schema. This must be done by adding custom migration scripts for your ODS, or by dropping these items by hand. Any external dependencies present will result in a sql exception.

You do not need to drop any constraints on the edfi schema itself: This is handled automatically for you.

For tips on locating foreign key dependencies quickly, see: Write scripts to drop ALL foreign keys on your extension tables that depend on the edfi schema

A data validation failure was encountered on destination object {table name here} ...Data was modified in a location that was not expected to change. A validation check from script directory 02 Source Validation Check has failed

This state is triggered if certain records are modified in the middle of migration that the upgrade utility expected would remain unchanged. The upgrade will be halted for you as a precaution to prevent unintended data loss.

Common causes:

  • Updates were made (either by hand or other means) in the middle of the migration process after the data compatibility checks passed
  • Custom migration scripts performed an insert/update/delete on an object that the migration utility expects to remain unchanged by default

If you are testing potential updates to the 2.x ODS data by hand (data only / no custom scripts):

  • For testing of data updates only: Simply restore the target 2.x ODS and make the desired inserts/updates/deletes before launching the utility.
    • It is recommended to create your own custom migration scripts if testing more complex operations
  • You should ensure that all 2.x data updates have been applied before the data compatibility check passes

If you are writing your own custom scripts:

  • Check the output of the table mentioned in the error message carefully and ensure that the detected 3.0 change is as intended
  • If the error references a table/column that you know is intended to change during upgrade, you may optionally remove the data validation check.
    • These items can be found in directory 02 Source Validation Check
    • Developer Note: it is not recommended to modify the edfi schema, as this may make upgrade tasks difficult in the future. By convention, custom tables are normally found in an outside location, such as the extension schema.

If your 2.x Ed-Fi ODS schema is unmodified , and you are not making edits to the current scripting or data, this error should not occur during normal operation.

  • Try the upgrade process again, and if the same error occurs, you may have encountered a bug.