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

Full settings file example

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

For environment variable mapping, see Environment variables.

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.
   • Creates a database quench task for each discovered database and runs them in parallel (up to MaxThreads concurrent operations).
   • If any database quench fails, logs the failure and exits with code 2.
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).

SchemaQuench assigns every script folder to a quench slot that determines when the folder's scripts execute and how they are handled. The slot list is the same on every platform; the default folders vary by platform.

Template quench slots

Product quench slots

Product scripts run against the administrative connection, outside the per-database template loop.

• Sequential, tracked — scripts run in alphabetical order and are recorded in CompletedMigrationScripts so they only run once (unless marked [ALWAYS]).
• Dependency retry loop — scripts are retried in rounds until all succeed or no progress is made. See Dependency retry loop.

SchemaQuench remembers what it has already run, so you never have to worry about a migration script executing twice. Migration scripts (scripts in the Before, BetweenTablesAndKeys, AfterTablesScripts, and After slots) are tracked in the SchemaSmith.CompletedMigrationScripts table:

Execution rules

• On each quench run, SchemaQuench checks which scripts in each slot have already been recorded.
• Scripts that appear in the tracking table are skipped.
• Scripts that don't appear are executed, and on success a tracking entry is inserted.

The [ALWAYS] suffix

Scripts with [ALWAYS] in the filename (before the .sql extension) run on every quench regardless of tracking:

001_SeedReferenceData [ALWAYS].sql
002_RefreshPermissions [ALWAYS].sql

[ALWAYS] scripts are never recorded in the tracking table.

Ordering

Migration scripts within each slot execute in alphabetical order by filename. Use numeric prefixes to control execution order:

001_CreateStagingTable.sql
002_MigrateData.sql
003_DropStagingTable.sql

Obsolete entry cleanup

When SchemaQuench processes a slot, it compares the tracking table entries against the scripts currently present in the package. Entries for scripts that no longer exist in the package are automatically removed.

Forcing re-execution

To force a tracked script to run again, either delete the corresponding row from SchemaSmith.CompletedMigrationScripts in the target database, or rename the script file (tracking is by path, so a renamed script is treated as new).

When DropTablesRemovedFromProduct is true (the default), ModifiedTableQuench drops tables that:

• Exist in the target database.
• Aren't defined in any table JSON file in the schema package.
• Were previously managed by this product.

This keeps the database clean as tables are removed from the schema package over time.

Environment guidance

The rollback-friendly removal pattern

1. Remove the table from your product definition.
2. Keep DropTablesRemovedFromProduct: false in the production config.
3. Write a migration script that renames or archives the table (or verifies no dependencies remain).
4. After the retention period, either enable the setting for one deployment or add an explicit DROP in a migration script.

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.

ShouldApplyExpression is a SchemaQuench feature that lives on the schema package side. Whenever SchemaQuench evaluates a table component that has a ShouldApplyExpression set, it resolves any tokens in the expression, runs the expression against the target database, and skips the component if the result is falsy. This means a single table file can declare components that only apply on certain databases, certain environments, or certain server versions — no per-environment file copies, no branching logic in your deployment pipeline. See Conditional application for the JSON shape and worked examples, and Custom Properties as drivers for how to drive ShouldApplyExpression values from team-defined metadata.

Each table that participates in data delivery carries a DataDelivery block in its JSON. SchemaQuench walks every table JSON, keeps the ones that declare delivery, orders them by foreign key dependencies, and merges each row set. Tables without a DataDelivery block are left untouched. On SQL Server, delivery is applied with the native MERGE statement.

See Data delivery for the full property reference, MergeType options, FK-aware two-pass merge, and worked examples.

DataDelivery vs hand-written Table Data scripts

You can use both. For each target database, SchemaQuench first delivers every table with a DataDelivery block in FK order, then runs any .sql files you dropped into the template's TableData-slot folders through the dependency retry loop. Use declarative DataDelivery for bulk reference data and keep the script slot for special cases — conditional seeds, one-off rebuilds, procedural loads.