SchemaSmith Documentation

SchemaQuench - SQL Server (Enterprise)

Enterprise-grade state-based deployments with dependency-aware ordering.

Overview Execution Flow Quick Start
SchemaQuench automated deployment tool

Overview

SchemaQuench is a state-based, opinionated database migration tool inspired by the principles of infrastructure-as-code tools like HashiCorp's Terraform. It enables you to define the desired end state of your SQL Server databases using metadata and applies these definitions to target servers, ensuring consistency and repeatability across environments. We refer to this process as quenching.

Why Choose SchemaQuench?

Traditional migration scripts track changes over time but can become cumbersome and error-prone, especially in complex environments. SchemaQuench offers a declarative approach, focusing on the desired final state rather than the sequence of changes. This methodology:

  • Ensures Consistency: Aligns your database schema with the defined metadata, reducing discrepancies between environments.
  • Simplifies Version Control: Treats database schema as code, facilitating easier tracking, reviewing, and auditing of changes.
  • Enhances Automation: Integrates seamlessly into CI/CD pipelines, promoting automated deployments.

Where SchemaQuench Fits

  • SchemaTongs extracts your schema into metadata.
  • SchemaHammer provides powerful search and edit capabilities for your metadata.
  • SchemaQuench applies your schema and data metadata to your SQL Servers.
  • DataTongs extracts your seed data into metadata

Quick Start Guide

Follow these steps to get started with SchemaQuench:
  1. Extract Metadata with SchemaTongs: Use SchemaTongs to cast your existing database schema into metadata files.
  2. Define Your Product: Edit the product.json to include the correct server identification, version validation, and version stamping for your domain. Add any necessary Script Tokens. Verify the TemplateOrder contains all your templates in the order they need to be quenched.
  3. Define Your Templates: Edit each template.json to find the correct database(s) to apply against, along with version validation and stamping scripts.
  4. Configure Settings: Set up your appSettings.json file to specify connection details, target environment, and other configuration settings.
  5. Run SchemaQuench: Execute SchemaQuench from the command line to apply your metadata to the target server.

You can use Command Line Options to specify the log file location or an alternate config file.

See the SchemaQuench Walkthrough to help you get started with the tool.

Tip

Start by defining a small subset of your database objects. Gradually expand as you gain confidence, ensuring that each step is manageable and verifiable.

Execution Flow

SchemaQuench processes a product in two layers: product-level orchestration and per-database quenching. Understanding this sequence helps you place scripts in the correct slots and troubleshoot failures.

Product-Level Sequence

  1. Test server connections
  2. Validate server
  3. Validate baseline
  4. Resolve product query tokens
  5. Quench before-product scripts
  6. Load templates
  7. Quench templates (runs the database sequence below for each database)
  8. Quench after-product scripts
  9. Stamp product version

Database Quench Sequence (17 Steps)

For each database matched by a template, SchemaQuench runs these steps in order:

  1. Kindle the Forge — Deploy infrastructure objects (see below)
  2. Validate Baseline — Run the template's baseline validation script
  3. Object Scripts (first pass) — Execute scripts from object folders (Views, Functions, Procedures, etc.)
  4. Resolve Query Tokens — Evaluate any query-based script tokens
  5. Object Scripts (second pass) — Re-run object scripts to resolve cross-dependencies
  6. Before Migration Scripts — Run the Before migration script slot
  7. Missing Tables and Columns — Create new tables and add missing columns
  8. Modified Tables — Alter columns, rebuild dependencies, handle renames
  9. After-Tables Object Scripts — Scripts that depend on table structure being final
  10. Between Tables and Keys Scripts — Run scripts between table changes and constraint application
  11. Indexes and Constraints — Apply indexes, foreign keys, check constraints, statistics
  12. After Table Scripts — Post-constraint scripts
  13. Object Scripts (final pass) — Final object script execution
  14. Data Delivery — Run MERGE statements for seed data
  15. Table Data Scripts — Execute table data script slot
  16. After Migration Scripts — Run the After migration script slot
  17. Version Stamp — Execute the template's version stamping script

Fault Tolerance

Use Checkpointing with the --ResumeQuench flag to resume interrupted deployments from the last successful step, rather than starting over.

Infrastructure Objects

The "Kindle the Forge" step deploys helper objects into a [SchemaSmith] schema in each target database. These objects are used internally during the quench process and are safe to leave in place between runs.

Helper Functions
  • fn_StripBracketWrapping
  • fn_StripParenWrapping
  • fn_SafeBracketWrap
  • fn_FormatJson
  • PrintWithNoWait
Quench Procedures
  • MissingTableAndColumnQuench
  • ModifiedTableQuench
  • MissingIndexesAndConstraintsQuench
  • IndexOnlyQuench
  • TableQuench
  • GenerateTableJson
Tracking Table

[SchemaSmith].[CompletedMigrationScripts] records which migration scripts have already run, preventing duplicate execution. See Migration Scripts for details on managing this table.

Logging & Debug SQL

SchemaQuench writes dual-stream log files (Progress + Errors) and diagnostic SQL files when table quench steps fail. These debug files contain the exact SQL that was attempted, making failures easy to reproduce.

Logging & Exit Codes covers log file locations, backup behavior, debug SQL file names, exit codes, and platform-specific logging details including VerboseLogging for SQL Server.

SQL Server Platform Notes

Connection Behavior

ApplicationIntent is always set to ReadWrite, ensuring connections route to the writable replica in Availability Group configurations.

Temporal Table Handling

When SchemaQuench needs to alter a system-versioned (temporal) table, it automatically turns off system versioning before making structural changes and re-enables it afterward. No manual intervention is required.

Computed Column Cascading

When a physical column changes, SchemaQuench detects any computed columns that reference it and automatically rebuilds them. This ensures formula-based columns stay in sync without requiring you to track those dependencies manually.

ProductName Extended Property

SchemaQuench sets a ProductName extended property on every managed table and index. This property:

  • Records which product owns the object
  • Prevents cross-product interference when multiple products share a database
  • Flags objects for removal when they are no longer defined in the product metadata
Custom Table Drop Hooks

When SchemaQuench detects that a table should be dropped (it was once defined in the product but has been removed), it checks for a stored procedure named [SchemaSmith].[CustomTableDrop]. If found, it calls the procedure with the schema and table name as parameters and skips the actual drop.

Similarly, [SchemaSmith].[CustomTableRestore] is called near the beginning of each quench to restore previously custom-dropped tables. These hooks let you delay drops, perform custom backups, or implement whatever your process requires.

Best Practices

  • Incremental Adoption — Begin with non-critical objects to familiarize yourself with the tool's workflow.
  • Version Control Integration — Keep your metadata files under source control to track changes and collaborate effectively.
  • Environment Segregation — Use different configurations for development, staging, and production to prevent accidental deployments.
  • Regular Validation — Periodically validate that the target databases match the desired state defined in your metadata.

Forge Deeper With An Example

Say you are widening a column from VARCHAR(5) to VARCHAR(10). On the surface, that is a simple alter table script, however, what if that column participates in a check constraint, an index, a foreign key, or a full text index?

You would have to drop those constructs, make your change, and put them back on. That is a lot of bookkeeping for a simple change. SchemaQuench handles all of that for you. You widen the column in the table's JSON and SchemaQuench handles the dependencies. See Forge Deeper with SchemaQuench for details.

New Feature

We now support renaming tables and columns. See defining tables for more details.