Templates for SQL Server - Enterprise

Overview

Each Template.json file targets one logical group of databases on SQL Server. For the full field reference, quench slot pipeline, and template ordering rules that apply across all platforms, see Products & Templates.

This page covers SQL Server Enterprise-specific features: Always-On AG replica targeting via ServerToQuench, index-only deployments for secondary replicas, and the SQL Server identification script pattern.

SQL Server Example

From the SchemaSmith Enterprise Demos, a template targeting a single database with custom script folders:

{
  "Name": "Main",
  "DatabaseIdentificationScript": "SELECT [name] FROM master.dbo.sysdatabases WHERE [name] = '{{MainDB}}'",
  "VersionStampScript": "PRINT '{{ReleaseVersion}}'",
  "ScriptTokens": {
    "TestTableData": "<*File*>Tables/dbo.TestTable.data",
    "TemplateQueryToken": "<*Query*>SELECT 'true'"
  },
  "ScriptFolders": [
    { "FolderPath": "MigrationScripts/Before", "QuenchSlot": "Before" },
    { "FolderPath": "Schemas",                 "QuenchSlot": "Objects" },
    { "FolderPath": "DataTypes",               "QuenchSlot": "Objects" },
    { "FolderPath": "FullTextCatalogs",        "QuenchSlot": "Objects" },
    { "FolderPath": "FullTextStopLists",       "QuenchSlot": "Objects" },
    { "FolderPath": "Functions",               "QuenchSlot": "Objects" },
    { "FolderPath": "Views",                   "QuenchSlot": "Objects" },
    { "FolderPath": "Procedures",              "QuenchSlot": "Objects" },
    { "FolderPath": "Triggers",                "QuenchSlot": "AfterTablesObjects" },
    { "FolderPath": "MigrationScripts/After",  "QuenchSlot": "After" }
  ]
}

SQL Server Identification

SQL Server templates use DatabaseIdentificationScript to query master.dbo.sysdatabases or master.sys.databases. The {{MainDB}} token is resolved from script tokens at runtime.

13 Default Folders

SQL Server Enterprise templates default to 13 script folders including SQL Server-specific types: FullTextCatalogs, FullTextStopLists, XMLSchemaCollections, and DDLTriggers. See Configurable Script Folders.

Server Targeting

The ServerToQuench property is exclusive to SQL Server Enterprise. It controls which replica types in an Always-On Availability Group receive a template's scripts and DDL. PostgreSQL and MySQL do not have this field.

Value	Behavior
`Primary`	Apply only when connected to the primary replica. Use for most templates.
`Secondary`	Apply only when connected to a secondary replica. Typically paired with `IndexOnlyTableQuenches: true`.
`Both`	Apply on both primary and secondary replicas.

SchemaQuench does not auto-detect whether the connected server is a primary or secondary. Your deployment pipeline must connect to the correct server(s) and indicate the role via appsettings.json configuration.

Index Only Table Quenches

When IndexOnlyTableQuenches is true, SchemaQuench skips all structural table changes (column additions, modifications, and removals) and only processes index definitions. Table definitions in the Tables/ folder are still loaded and parsed, but only index creation and modification DDL is generated.

Use Cases

AG Secondary Replicas

In Always-On Availability Group deployments, table schema on readable secondary replicas is synchronized automatically by the AG. Index changes that are not replicated (such as non-replicated filtered indexes or fill factor adjustments) can still be applied without attempting structural DDL.

Third-Party Databases

When maintaining indexes on a third-party database where you do not control the table structure, use index-only mode to manage custom indexes without risking modifications to vendor-owned columns or constraints.

Configuration

A common pattern pairs IndexOnlyTableQuenches with ServerToQuench: "Secondary" to create a dedicated template for secondary replica index management:

{
  "Name": "SecondaryIndexes",
  "DatabaseIdentificationScript": "SELECT [name] FROM master.sys.databases WHERE [name] = '{{AppDb}}'",
  "IndexOnlyTableQuenches": true,
  "ServerToQuench": "Secondary"
}

Table Definition

Tables in an index-only template need only the Schema, Name, and Indexes properties:

{
  "Schema": "[dbo]",
  "Name": "[MyTable]",
  "Indexes": [
    {
      "Name": "[IX_MyTable_Status]",
      "Clustered": false,
      "IndexColumns": "[Status], [CreatedDate]"
    }
  ]
}

When this table is quenched, only the indexes are maintained. The table structure itself is managed through AG replication, the vendor's process, or some other means.

Update Fill Factor

The UpdateFillFactor template setting (default: true) controls whether SchemaQuench updates index fill factor values to match the definitions in your table JSON files. When enabled, any fill factor difference between the definition and the live index triggers a rebuild.

`true` (Default)

Fill factor values from your table definitions are enforced. Indexes with a different fill factor on the server are rebuilt to match.

`false`

Fill factor differences are ignored. Use this when DBAs tune fill factor per-server based on workload characteristics and you do not want schema deployments to override those adjustments.

Templates - SQL Server (Enterprise)