SchemaSmith Enterprise Documentation

Forge Deeper with SchemaQuench

Gain a deeper understanding of SchemaQuench.

Overview Understanding QuenchSlots The Quench Process Resources
SchemaQuench

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.

Understanding the QuenchSlots

Quenching a product happens as a series of ordered events. Each slot must complete successfully before we move to the next except as noted below.

  • Validate the server when a Product validation script is provided.
  • Validate the server baseline version when a Product level script is provided.
  • Resolve any product level Query Tokens.
  • Run any Before product scripts. See Configurable Script Folders
  • Quench each template based on the TemplateOrder defined in the Product.
    • Validate the baseline version if a script is provided in the template definition.
    • Apply any Object scripts without query tokens. Object scripts located in folders configured at the Template level with the Object quench slot. Object scripts will loop to resolve dependencies each time they are applied.
    • Quench missing tables and columns using SchemaSmith.MissingTableAndColumnQuench.
    • Apply any remaining Object scripts without query tokens.
    • Resolve template query tokens.
    • Apply any Migration scripts from folders defined with the Before quench slot.
    • Quench modified tables using SchemaSmith.ModifiedTableQuench.
    • Apply any remaining Object scripts after resolving query tokens.
    • Apply any Migration scripts from folders defined with the BetweenTablesAndKeys quench slot.
    • Quench missing and modified indexes and constraints using SchemaSmith.MissingIndexesAndConstraintsQuench.
    • Apply any Migration scripts from folders defined with the AfterTablesScripts quench slot.
    • Apply any remaining Object scripts plus any scripts in folders defined with the AfterTablesObjects quench slot. This is the final chance to resolve script dependencies, and any remaining scripts that fail to apply in this pass will be reported as errors.
    • Perform Data Delivery for any tables with those properties defined.
    • Apply any table data scripts from folders defined with the TableData quench slot.
    • Apply any Migration scripts from folders defined with the After quench slot.
    • Stamp the template version if a script is provided in the template.
  • Run any After product scripts.
  • Stamp the product version if a script is provided in the product.

Tip

Object scripts are always applied and Quench will loop through them to resolve dependencies if possible. We lean heavily on deferred name resolution and only fail if we cannot resolve a dependency multiple times after tables have been updated.

The Table Quench Process

The process of quenching tables is broken into three separate slots in the Enterprise SchemaQuench to allow more robust and granular control over the entire process.

Quench missing tables and columns

  • Parse the JSON definitions into temp tables and keep them for use in the later quench steps below
  • Handle Table Renames
  • Handle Column Renames
  • Attempt custom table restore for tables being added in case they were custom dropped previously if SchemaSmith.CustomTableRestore exists in the database
  • Add New Tables
  • Add New Physical Columns

Quench modified tables

  • Turn off Temporal Tracking for tables no longer defined temporal
  • Collect table level extended properties related to the quench process. Each table is "owned" by a product which is tracked via the extended property ProductName
  • Validate Table Ownership. We will error if this product definition conflicts with a table already "owned" by another product.
  • Identify tables removed from the product
  • Drop tables removed from the product using SchemaSmith.CustomTableDrop when it exists, otherwise DROP TABLE
  • Collect index level extended properties. Indexes also have a ProductName property so that we can drop the indexes that have been removed from the product even if the product is configured with DropUnknownIndexes set to false.
  • Identify indexes removed from the product
  • Detect Column Changes
  • Detect Computed Columns Impacted by Other Column Changes
  • Detect Column Drops
  • Collect Foreign Keys To Drop
  • Drop Foreign Keys No Longer Defined In The Product
  • Identify Fulltext Indexes To Drop Based On Column Changes
  • Drop FullText Indexes Referencing Modified Columns
  • Collect Existing FullText Indexes
  • Identify Indexes To Drop Based On Column Changes
  • Fixup Table Compression
  • Fixup Index Compression
  • Collect Existing Index Definitions
  • Detect Index Changes
  • Detect Index Renames
  • Handle Renamed Indexes And Unique Constraints
  • Collect Existing XML Index Definitions
  • Detect Xml Index Changes
  • Detect Xml Index Renames
  • Handle Renamed Xml Indexes
  • Identify unknown and modified indexes to drop. Unknown indexes are only dropped when DropUnknownIndexes is set to true at the product level OR when the unknown index is impacted by a column change
  • Drop Referencing Foreign Keys When Dropping Unique Indexes
  • Drop FullText Indexes Referencing Unique Indexes That Will Be Dropped
  • Drop Unknown and Modified Indexes
  • Fixup Modified FillFactors. This behavior is based on setting UpdateFillFactor to true at the template, table, or index level. The highest level wins when the values are mixed. This is intended to allow rolling out fill factor changes in a controlled manner for large tables to manage the update time, especially when resolving drift in the initial rollout of these tools.
  • Identify Statistics To Drop Based On Column Changes
  • Drop Statistics Referencing Modified Columns
  • Identify Foreign Keys To Drop Based On Column Changes
  • Drop Foreign Keys Referencing Modified Columns
  • Identify Defaults To Drop Based On Column Changes
  • Drop Defaults Referencing Modified Columns
  • Identify Check Constraints To Drop Based On Column Changes
  • Drop Check Constraints Referencing Modified Columns
  • Drop Modified Computed Columns
  • Drop Columns No Longer Part of The Product Definition
  • Detect Default Changes
  • Add missing ProductName extended property to tables
  • Add Missing Physical Columns (for the edge case of replacing a computed column with a physical column)
  • Drop Modified Defaults
  • Collect Existing Foreign Keys
  • Detect Foreign Key Changes
  • Drop Modified Foreign Keys
  • Collect Existing Statistics Definitions
  • Detect Statistics Changes
  • Drop Modified Statistics
  • Collect Existing Check Constraints
  • Detect Column Level Check Constraint Changes
  • Detect Table Level Check Constraint Changes
  • Drop Modified Check Constraints
  • Alter Modified Columns
  • Identify Existing Clustered Index Conflicts
  • Drop Conflicting Clustered Index
  • Drop Modified or Removed FullText Indexes

Quench missing and modified indexes and constraints

  • Collect index level extended properties
  • Add New Computed Columns. This is deferred here to allow one last chance to create objects that they might depend on.
  • Add Missing Indexes
  • Add Missing XML Indexes
  • Turn on Temporal Tracking for tables defined as temporal
  • Add missing ProductName extended property to indexes
  • Add missing ProductName extended property to XML indexes
  • Add Missing Statistics
  • Add Missing Defaults
  • Add Missing Check Constraints
  • Add Missing Foreign Keys
  • Add Missing FullText Indexes

Hooks for custom table drop and restore processes

When SchemaQuench detects that a table should be dropped, i.e., it was once defined in the product but has been removed, it looks to see if there is a stored procedure named SchemaSmith.CustomTableDrop. If there is such a procedure, it calls it passing the schema and name of the table as the first and second parameters respectively and skips actually dropping the table.

Similarly, near the beginning of the quench process, an attempt is made to restore tables that had been custom dropped previously. This hook expects a stored procedure named SchemaSmith.CustomTableRestore passing the same schema and name parameters.

These hooks allow you to time delay dropping tables or to perform some custom backup procedure or whatever else your process may require.

Additional Resources

SchemaQuench Walkthrough - A step-by-step guide for quenching the sample product. How-to Configurable Script Folders - Make the project folder structure fit your team. Reference Migration Scripts - How to handle edge cases. Reference Data Delivery - Making data deployment easier. Reference Declarative vs Imperative, Which Migration Strategy Is The Best? Video