PI Nexus+ / Reference
Schema Migrations
This reference describes how PI Nexus+ keeps its database schema in step with the installed version: the migration files, the ledger, and the time limits that apply.
Overview
This reference describes how PI Nexus+ keeps its database schema in step with the installed version: the migration files, the ledger, and the time limits that apply.
Who applies migrations
| Applied by | When | Rights |
|---|---|---|
| Database Setup | At installation, from Admin\Migrations | The account running Database Setup |
| The web application | At every start, before it answers requests, when bootstrap.json exists | Service account, db_owner |
| Admin > SQL Server > Apply | On request | App pool identity, db_owner |
The scanner service never applies migrations. At startup it waits, and retries, until the schema is current; its log then says why it is waiting.
Migration files
| Migration | Version | Content |
|---|---|---|
0001–0008 | 1.0 to 1.5.1 | Earlier releases |
0009_release_1_6_0 | 1.6.0, unchanged in 1.6.1 and 1.6.2 | The 1.6.x schema |
0010_release_1_7_0 | 1.7.0 | PI Adapter monitoring, point sources, sites, daily health history, host monitoring and the other 1.7.0 tables. Rebuilds one index on the AF attribute table |
A new installation applies all files; an upgrade applies only the missing ones. Released files never change.
The ledger
Each migration is recorded in dbo.PINexusSchemaMigrations, shown under Admin > SQL Server:
| Column | Meaning |
|---|---|
MigrationId | Number, for example 0010 |
Name | Name from the file |
AppVersion | Version that applied it, or DatabaseSetup |
StartedAt, CompletedAt, DurationMs | Timing |
Succeeded | Whether it finished |
Checksum | SHA-256 of the file. A mismatch with an applied migration stops the update with Applied migration ... has checksum ..., but this build expects ... |
ErrorMessage | Error of a failed run |
Never edit the ledger unless support asks you to.
Behaviour and limits
| Item | Value |
|---|---|
| Transactions | One transaction per migration. A failed or stopped migration is rolled back; earlier ones stay applied. The next start continues with it |
| Concurrency | One migration run at a time per database (SQL application lock PINexus.SchemaMigration, 60-second wait) |
| Statement timeout in the web application | 3,600 seconds |
| Statement timeout in Database Setup | 300 seconds |
| Start window of the web application | 120 minutes; under IIS at most 55 minutes. Set with startupMigrationTimeoutMinutes in bootstrap.json |
| IIS start limit | 60 minutes (startupTimeLimit="3600" in web.config) |
| When the start window ends | The running migration is rolled back, the web application starts without it and logs which migration ran out of time. Scans stay blocked. Finish with Admin > SQL Server > Apply |
| Interrupted migration | Admin > SQL Server and the scanner log report that it "was started ... but never completed". It runs again from the start; no repair is needed |
Status on Admin > SQL Server
| Card | Value | Meaning |
|---|---|---|
| Database | Reachable | The connection works |
| Offline | The connection failed; the card shows the error | |
| Schema | Compatible | All migrations of this version are applied; the card names the schema version |
| Not ready | Migrations are pending | |
| Unknown | Cannot be checked while the database is offline | |
| Migration | Current | Nothing pending |
| N pending | Choose Apply after confirming a backup exists | |
| Running | The web application is applying a migration. Large databases can take up to an hour; Apply waits for it | |
| Failed | The last migration failed; the card shows the error | |
| Backup | A backup is recommended before the next schema upgrade | |
| No history | No ledger yet: run Database Setup or Apply |
