SchemaSmith 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 template happens as a series of ordered events. Each slot must complete successfully before we move to the next except as noted below.

  • Validate the baseline version if a script is provided in the template definition.
  • Apply any Before Migration scripts which are located in MigrationScripts\Before under each template.
  • Apply any Object scripts which are located in the following folders under each template.
    • Schemas
    • DataTypes
    • FullTextCatalogs
    • FullTextStopLists
    • XMLSchemaCollections
    • Functions
    • Views
    • Procedures
  • Validate the existing table structures against the table JSON files and apply needed updates.
  • Apply any remaining Object scripts that failed to apply before the table changes plus any AfterTablesObject scripts. We loop again to resolve dependencies and finally report errors if any scripts failed to apply.
    • Triggers
    • DDLTriggers
  • Apply any table data scripts that are located in the TableData Folder under each template.
  • Apply any After Migration scripts which are located in MigrationScripts\After under each template.
  • Stamp the template version if a script is provided in the template.

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

Quenching tables is achieved via a stored procedure SchemaSmith.TableQuench that we add to each database during the quenching process. This procedure is provided with the JSON definition for all tables in the product as a template and performs a step-by-step automated process to reshape them if needed. We cannot automate 100% of every possible change, but we do the bulk of the heavy lifting here and provide the migration scripts concept for edge cases and things you need to perform beyond this automation.

The following are the steps for quenching tables:

  • Parse the JSON definitions into temp tables
  • Handle Table Renames
  • Handle Column Renames
  • 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
  • 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 based on no longer being defined in the product
  • 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 is only done when UpdateFillFactor is set to true in the template definition.
  • 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
  • Add New Tables
  • Add missing ProductName extended property to tables
  • Add New Physical Columns
  • Detect Default Changes
  • 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
  • Add New Computed Columns
  • Identify Existing Clustered Index Conflicts
  • Drop Conflicting Clustered Index
  • 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
  • Drop Modified or Removed FullText Indexes
  • Add Missing FullText Indexes

Additional Resources

SchemaQuench Walkthrough - A step-by-step guide for quenching the sample product. How-to Migration Scripts - How to handle edge cases. Reference SchemaQuench - Learn how to quench your databases. Related Declarative vs Imperative, Which Migration Strategy Is The Best? Video