SchemaQuench Enterprise Documentation

Apply SQL Server Metadata with Confidence

SchemaQuench turns fragile, step-by-step scripts into safe, declarative releases-so every deployment is boring, predictable, and fast.

Overview Quick Start 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.

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.

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
  • a full text index

In that case, you have to drop those constructs, make your change and then put them back on. That is a lot of bookkeeping for a simple change. Why bother? SchemaQuench can handle all of that for you. You widen the column in the table's json, SchemaQuench handles the dependencies. For more details about the quenching process see Forge Deeper with SchemaQuench.

New Feature

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

Hooks for custom table drop and restore processes

When SchemaQuench detects that a table should be dropped, ie 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

Forge Deeper with SchemaQuench. Deep dive SchemaQuench Walkthrough - A step-by-step guide for quenching the sample product. How-to Configuration Settings Reference - Explore the configuration options available in appSettings.json. Reference Product Definition Guide - Structure your metadata into products. Reference DataTongs - How to cast your seed data. Related