SchemaQuench for SQL Server

SchemaQuench reads configuration from SchemaQuench.settings.json (or the file specified by --ConfigFile), environment variables with the SmithySettings_ prefix, and command-line switches. Later sources override earlier ones. For the full loading-order and precedence rules, see Configuration.

Target connection settings

Behavior settings

The two drop-control flags above are shown as representative environment-tier entries. All eight drop-control flags are settable here (and via SmithySettings_<FlagName> environment variables) and resolve through a tiered cascade — see DropTablesRemovedFromProduct and Per-type drop protection for the full set and semantics.

Full settings file example

{
  "Target": {
    "Server": "localhost",
    "Port": "",
    "User": "",
    "Password": "",
    "SecondaryServers": "",
    "ConnectionProperties": {
      "TrustServerCertificate": "True"
    },
    "Templates": [],
    "Databases": [],
    "Schemas": []
  },
  "WhatIfONLY": false,
  "SchemaPackagePath": "./MyProduct",
  "KindleTheForge": true,
  "UpdateTables": true,
  "DropTablesRemovedFromProduct": true,
  "DropColumnsRemovedFromProduct": true,
  "DeliverData": true,
  "RunScriptsTwice": false,
  "TrackRunOnceMigrations": true,
  "PruneObsoleteMigrationTracking": true,
  "CheckpointDirectory": "",
  "MaxThreads": 10,
  "VerboseLogging": false,
  "ScriptTokens": {}
}

For environment variable mapping, see Environment variables.

When SchemaQuench runs, the product quench executes these steps in order:

1. Log product info. Logs the product name, platform, template order, validation script, and any configured script tokens.
2. Test server connection. Opens a connection to the target server and runs a SQL Server liveness check. Aborts if the connection fails.
3. Validate server. If Product.ValidationScript is configured, executes it against master. Aborts if the result is falsy.
4. Validate baseline. If Product.BaselineValidationScript is configured, executes it. Aborts if the result is falsy.
5. Product Before scripts. Executes scripts from Before Product folder(s). With secondary servers, scripts run in parallel to all eligible servers.
6. Quench each template. For each template name in Product.TemplateOrder:
   • Loads Template.json and merges template-level ScriptTokens over the product token set.
   • Executes DatabaseIdentificationScript against master to discover target databases.
   • For schema templates: executes SchemaIdentificationScript against each discovered database to produce one work unit per (database, schema) pair. For regular templates: one work unit per discovered database.
   • Dispatches all work units to a pool of up to MaxThreads concurrent workers. Each worker runs the full database quench sequence for its assigned iteration.
   • If any iteration fails, logs the failure. Failure routing follows the template's ContinueOnDatabaseFailure (regular templates) or ContinueOnSchemaFailure (schema templates) settings.
7. Product After scripts. Executes scripts from After Product folder(s).
8. Stamp product version. If Product.VersionStampScript is configured, executes it.
9. Log completion. Logs "Completed quench of {ProductName}".

After the quench returns, the calling program backs up log files to a numbered directory and exits with code 0 (see Exit codes).

See exactly what SchemaQuench would do before it touches a single table. Set WhatIfONLY to true to perform a dry run. In WhatIf mode:

• Validation scripts execute normally (server validation, baseline validation).
• Table quench procedures run with @WhatIf = 1, generating the SQL that would be executed and logging it without applying changes.
• Migration scripts show detailed status for each script:
  • Would APPLY: {script} for scripts that haven't yet been tracked.
  • Would SKIP (previously quenched): {script} for scripts already recorded in CompletedMigrationScripts.
• Object scripts (Objects, AfterTablesObjects, Table Data) are logged but not executed.
• Product Before/After scripts are logged but not executed.
• Version stamp scripts aren't executed; a log message indicates the stamp would occur.

Debug SQL output

During both normal and WhatIf runs, SchemaQuench writes the SQL generated by the table quench process to files in the working directory:

• SchemaQuench - ParseJson {DatabaseName}.sql
• SchemaQuench - MissingTableAndColumnQuench {DatabaseName}.sql
• SchemaQuench - ModifiedTableQuench {DatabaseName}.sql
• SchemaQuench - MissingIndexesAndConstraintsQuench {DatabaseName}.sql
• SchemaQuench - ForeignKeyQuench {DatabaseName}.sql
• SchemaQuench - IndexedViewQuench {DatabaseName}.sql
• SchemaQuench - IndexOnlyQuench {DatabaseName}.sql (when IndexOnlyTableQuenches is enabled)

These files can be reviewed to understand exactly what structural changes were (or would be) made.

When to use WhatIf

Reach for WhatIf while you're debugging a tricky deployment or while you're still building confidence with the tooling. Inspect the generated SQL, confirm the changes match intent, then run for real. Once you trust the package and the pipeline, direct quenches are the normal mode — WhatIf isn't a required gate on every deployment.

The table quench is broken into modular stored procedures, each handling a specific aspect of the table schema. The procedures are deployed during the KindleTheForge step and called in sequence during the database quench.

The implementation lives in the deployed SQL on the target database — which means a DBA can read it on the server with sp_helptext. No black boxes.

Calling procedures directly from migration scripts

The quench procedures are deployed to the target database during the KindleTheForge step and remain there afterward. You can call them directly from Before Scripts, After Scripts, or any migration script to bootstrap specific tables or views as part of a data migration.

The typical pattern uses specific-object tokens to quench individual objects that your migration script depends on, rather than passing the entire schema. First, define a token in your Product.json or Template.json:

{
  "ScriptTokens": {
    "AuditLogTable": "<*SpecificTable*>dbo.AuditLog"
  }
}

TableQuench — ensures a specific table exists with the right structure before your migration script runs:

-- Bootstrap the AuditLog table so we can insert into it during this migration
EXEC SchemaSmith.TableQuench
    @ProductName = '{{ProductName}}',
    @TableDefinitions = '[{{AuditLogTable}}]',
    @WhatIf = 0,
    @DropUnknownIndexes = 0,
    @DropTablesRemovedFromProduct = 0,
    @UpdateFillFactor = 1;

The same pattern works for views. Define the token, then pass it to the procedure:

{
  "ScriptTokens": {
    "OrderSummaryView": "<*SpecificIndexedView*>dbo.vw_OrderSummary"
  }
}

IndexedViewQuench:

EXEC SchemaSmith.IndexedViewQuench
    @ProductName = '{{ProductName}}',
    @IndexedViewSchema = '[{{OrderSummaryView}}]',
    @WhatIf = 0,
    @UpdateFillFactor = 0;

You can also pass the full schema tokens ({{TableSchema}}, {{IndexedViewSchema}}) to quench all objects of that type, but the specific-object pattern is more common in migration scripts where you need one table or view to exist before proceeding.

Parameter reference

When to use direct calls: When a migration script needs a table or view to exist before it can run — for example, bootstrapping an audit table in a Before Script before inserting migration tracking data, or ensuring an indexed view is deployed before populating dependent tables.

You shouldn't have to name your files in dependency order just so they deploy correctly. Scripts in the Objects, AfterTablesObjects, and TableData slots execute using a dependency retry loop rather than simple sequential execution:

1. Execute all pending (not yet quenched) scripts in the slot.
2. For each script, attempt to execute all its batches. If any batch fails, record the error and move on.
3. If at least one script succeeded in this iteration, loop back to step 1 with only the remaining failed scripts.
4. If zero scripts succeeded in an iteration, the loop terminates.

On the final attempt (the last pass when errors are reported), failures are logged as errors and the quench fails.

This mechanism allows scripts with interdependencies to coexist in the same folder without requiring a specific naming order. For example, if View B references View A and is alphabetically first, it will fail on the first pass but succeed on the retry after View A has been created.

The Objects slot gets four opportunities to resolve: (1) before the table quench, (2) after missing tables are created, (3) after table modifications are complete, and (4) during the AfterTablesObjects pass alongside triggers. This handles cases where a view or function references a table column that doesn't yet exist on the first pass.

When TrackRunOnceMigrations is false, SchemaQuench treats all migration scripts as if they had the [ALWAYS] suffix — no script is recorded in CompletedMigrationScripts, no script is skipped based on prior runs. Every migration script in every slot runs on every deployment.

When tracking is off, PruneObsoleteMigrationTracking is forced off regardless of its configured value.

For how this flag fits partial-package deployments, see Data fixes — the datafix profile.

Long deployments fail. Network blips, transient lock timeouts, a migration script that tripped on bad data at step 14 of 20. Without checkpointing, a failure in the final stretch means the next run starts from zero — re-running every step you've already successfully applied.

SchemaQuench writes checkpoints as it goes. Every completed quench step and every completed migration script is recorded to disk. On the next run, already-completed work is skipped and execution resumes at the first incomplete step.

Enabling resume

SchemaQuench --ResumeQuench

With --ResumeQuench, SchemaQuench reads the existing checkpoint files (if any) and skips anything already recorded as complete. Without the switch, the resume logic is off — every step executes regardless of prior state.

Where checkpoints live

SchemaQuench --CheckpointDirectory:C:\schemasmith\checkpoints

By default, checkpoints live in %TEMP%/schemaquench-checkpoints. Override with --CheckpointDirectory:<path> when you need them on a specific volume — for a CI runner with ephemeral temp storage, a shared build server, or a mounted volume that outlives the container. The directory is created if it doesn't exist.

The same value can be set in SchemaQuench.settings.json via the CheckpointDirectory key. The CLI switch wins if both are present.

Checkpoint scopes

SchemaQuench tracks two kinds of progress:

Product-scoped — Cross-database work shared by all templates:

• Before and After product-level scripts (per server, for Availability Group deployments).
• Completed templates (a template with every database finished is itself recorded as complete).

Database-scoped — One checkpoint file per {product, template, server, database} combination:

In addition, each template slot (Before, Objects, BetweenTablesAndKeys, AfterTablesScripts, AfterTablesObjects, TableData, After) records the exact scripts that ran, so resumed runs skip each individual script that already succeeded.

Automatic cleanup after success

Checkpoints exist to protect against failures. When the quench completes without error, SchemaQuench deletes every checkpoint file associated with the product. A clean run leaves no residue to mislead the next deployment. A failed run leaves the checkpoint files in place, ready for the next --ResumeQuench invocation.

Practical resume workflow

A 90-minute deployment to a large production database fails at minute 75 because a migration script hit a transient deadlock. You fix the data, re-run the deployment:

SchemaQuench --ResumeQuench

SchemaQuench reads the checkpoints, sees that KindleTheForge, ValidateBaseline, missing tables, modifications, indexes, constraints, and every Objects-slot script already succeeded, logs what it's skipping, and picks up at the first incomplete step. Minutes of work instead of starting from the top.

When to leave resume off

• Normal, clean deployments — The resume flag is opt-in. Without it, every step executes, and at the end the checkpoint files get cleaned up regardless. There's no cost to leaving it off for fast, green runs.
• CI pipelines that rebuild databases from scratch — Each run is a fresh slate, so resume has nothing to do.

Use --ResumeQuench when you specifically expect that a prior run may have left partial state — typically when re-running after a real failure in a non-trivial deployment.