Overview

Multi-instance management of Ed-Fi ODS/API instances has been a requested feature within administrative applications by field implementations.  This need continues to be expressed and active for ODS/API instances in collaborative and state scenarios.  Between implementations, there are various ways to deploy ODS/API databases and instances as per requirements for each project.  This design targets scenarios where the same database server hosts multiple ODS/API instances, as well as the ODS/API Admin/Security databases and Admin API database.  This hosting methodology is supported in both SQL Server and Postgres scenarios, and both on-premise and hosted/cloud environments as well.  Given a number of Ed-Fi deployments are deployed using a single SQL server model, it is assumed this design will accommodate them once implemented and released. 

Use Cases

Below is the use case for instance management with administrative tools in the Ed-Fi domain:

• An Ed-Fi administrator would like to manage (create, view and destroy) ODS/API instances by database
• The Ed-Fi administrator should have an API and user interface to be able to do instance management tasks (create, view and destroy)

Requirements into the use case as described above:

• ODS/API instance databases are housed on one database server
  • On-premise or hosted:  Postgres 11 through 13 or SQL Server 2019-2022
  • Cloud databases: AWS RDS database (Postgres or SQL Server), Azure SQL Database (Postgres or SQL Server) or Google Cloud database (Postgres or SQL Server).
• ODS/API instances databases follow an active Ed-Fi mode such as Year, District or InstanceYear-specific modes.
• Ways to manage instances
  1. Via PSQL and/or T-SQL/DBCC
     • The Ed-Fi administrator should provide a role and database login that has access to the following commands:
       • SQL Server - DBCC CLONEDATABASE and DROP DATABASE
       • Postgres - CREATE DATABASE WITH TEMPLATE and DROP DATABASE
     • Solution Design - Option 1: Direct Command Execution
  2. Via PowerShell
     • PowerShell is used to call commands (such as psql.exe, sqlcmd.exe, az.exe and so on)
     • Ed-Fi Alliance publishes a set of known PowerShell scripts
       • Ed-Fi Tech Docs: Database-Lifecycle
       • Ed-Fi GitHub Repo
     • Docker instances
       • Do we need to consider PowerShell Core for Docker instances?  Or already supported today?
     • Solution Design - Option 2: Decoupled Command Execution
  3. Other ways in use?
     1. Terraform?
     2. Cloud platform commands (Google Cloud CLI, AWS CLI,?)
     3. Does the PowerShell case usable to cover other ways in use today?

API Endpoints

Below are recommended API endpoints to allow for instance management within the Admin API.  Please note, these are highly privileged and potentially destructive operations, so this service should be highly protected and managed likewise.

{
  instanceName:  "Grand Bend SY 2022-2023",
  databaseName:  "edfi_100100_2223" 
}

{
  instanceName:  "Grand Bend SY 2022-2023 A",
  databaseName:  "edfi_10010010_202223" 
}

Design

Multi-Instance Management Solution Design

Tickets

• AA-1284 - Getting issue details... STATUS
• AA-1080 - Getting issue details... STATUS
• AA-1438 - Getting issue details... STATUS
• AA-1311 - Getting issue details... STATUS
• AA-1402 - Getting issue details... STATUS
• AA-1445 - Getting issue details... STATUS