SchemaSmith Documentation

Product Definition - SQL Server (Enterprise)

SQL Server-specific product configuration, AG replica targeting on product folders, and validation patterns.

Overview Product-Level Server Targeting
SchemaSmith Product.json configuration for SQL Server Enterprise database schema management.

Overview

Product.json is the entry point for a schema package on SQL Server. For the full field reference, validation script chain, template ordering rules, and version stamp behavior that apply across all platforms, see Products & Templates.

This page covers the SQL Server Enterprise-specific feature: ServerToQuench on product-level script folders for Always-On AG replica targeting.

SQL Server Example

From the SchemaSmith Enterprise Demos, a product with server-targeted script folders: 

{
  "Name": "ValidProduct",
  "ValidationScript": "SELECT CAST(CASE WHEN EXISTS(SELECT * FROM master.dbo.sysdatabases WHERE [name] = '{{MainDB}}') THEN 1 ELSE 0 END AS BIT)",
  "TemplateOrder": ["Main", "Secondary", "Bogus"],
  "ScriptFolders": [
    {
      "FolderPath": "Jobs",
      "QuenchSlot": "After",
      "ServerToQuench": "Both"
    }
  ],
  "ScriptTokens": {
    "SecondaryDB": "TestSecondary",
    "MainDB": "TestMain",
    "ReleaseVersion": "1.0.1.14",
    "TestQueryToken": "<*Query*>SELECT STRING_AGG([Name], ',') FROM master.dbo.sysdatabases WHERE [name] = '{{MainDB}}'"
  }
}

SQL Server Validation

SQL Server products validate against master.dbo.sysdatabases or master.sys.databases. The result must be a single row with a value that compares equal to 1 (BIT, INT, or any compatible type). Token references like {{MainDB}} are resolved from script tokens before execution.

Product-Level Server Targeting

Product-level ScriptFolders on SQL Server Enterprise support the ServerToQuench property, just like template-level script folders. This controls whether a product-level folder runs on primary replicas, secondary replicas, or both in an Always-On Availability Group deployment. PostgreSQL and MySQL do not have this field.

Value Behavior
Primary Run product-level folder only when connected to the primary replica.
Secondary Run product-level folder only when connected to a secondary replica.
Both Run product-level folder on both primary and secondary replicas.

Example: a Jobs folder that runs on both replicas after all templates complete: 

"ScriptFolders": [
  {
    "FolderPath": "Jobs",
    "QuenchSlot": "After",
    "ServerToQuench": "Both"
  }
]