Running Migrations — SQL Server Plugin

Automatic Migrations (Default)

With RunMigrationsOnStartup: true (the default), the plugin calls MigrateAsync() during OnStartAsync. This is safe because:

EF Core applies only pending migrations — already-applied migrations are skipped
Migrations run inside a transaction — a failed migration rolls back automatically
The application does not accept requests until migrations complete
In multi-instance deployments, EF Core uses a distributed lock to prevent concurrent migration runs

Suitable for most deployments. Automatic migrations on startup are safe for all Octopus migrations because the Octopus team designs migrations to be zero-downtime and additive-only (new tables, new columns with defaults). Breaking changes are never added to Octopus migrations.

Manual Migration Commands

# Navigate to the project that contains OctopusDbContext
cd C:\YourSolution\BizFirst.Octopus.SqlServerPlugin

# Create a new migration (development only — do not create migrations manually in production)
dotnet ef migrations add AddMyCustomTable --context OctopusDbContext

# List applied and pending migrations
dotnet ef migrations list --context OctopusDbContext

# Apply all pending migrations to the target database
dotnet ef database update --context OctopusDbContext \
    --connection "Server=sql01;Database=OctopusAI;..."

# Roll back to a specific migration
dotnet ef database update 20240101_InitialOctopusSchema --context OctopusDbContext

# Generate a SQL script (for DBA review before applying to production)
dotnet ef migrations script --idempotent --context OctopusDbContext \
    --output octopus-migration.sql

Generating an Idempotent SQL Script

The --idempotent flag generates a script that checks which migrations have been applied and only runs the pending ones. This is the recommended approach for production deployments where DBAs must review changes before they run:

-- Generated idempotent migration script excerpt
IF NOT EXISTS (
    SELECT 1 FROM [__EFMigrationsHistory]
    WHERE [MigrationId] = N'20240615000000_AddAreaTables'
)
BEGIN
    CREATE TABLE [Octopus_Areas] (
        [Id]          UNIQUEIDENTIFIER NOT NULL DEFAULT NEWSEQUENTIALID(),
        [TenantId]    NVARCHAR(100)    NOT NULL,
        [Name]        NVARCHAR(200)    NOT NULL,
        -- ...
        CONSTRAINT [PK_Octopus_Areas] PRIMARY KEY ([Id])
    );

    INSERT INTO [__EFMigrationsHistory] ([MigrationId], [ProductVersion])
    VALUES (N'20240615000000_AddAreaTables', N'8.0.0');
END;
GO

Safe Migration Practices

Practice	Reason
Always take a database backup before applying migrations	Provides a rollback point if migration has unexpected side effects
Use `--idempotent` scripts for production	DBA can review SQL before execution
Test migrations on a copy of production data first	Catches performance issues from large table modifications
Never modify migration files that have been applied	EF Core uses the migration ID hash — modifying applied migrations corrupts the history
Never run `EnsureCreated()` alongside migrations	`EnsureCreated()` bypasses migration history and causes conflicts

Disabling Automatic Migrations

For deployments where a DBA applies migrations separately from the application deployment, disable automatic migrations and apply them via the generated SQL script:

// appsettings.Production.json
{
  "SqlServerPlugin": {
    "RunMigrationsOnStartup": false
  }
}

The application will still fail to start if the database schema is behind — the startup health check verifies that no pending migrations exist:

[SqlServerPlugin] RunMigrationsOnStartup is disabled
[SqlServerPlugin] Checking for pending migrations...
[SqlServerPlugin] ERROR: 2 pending migration(s) found.
    Run 'dotnet ef database update' or apply the idempotent script before starting.

← SQL Schema Next: Multi-Tenant Configuration →