Template - Schema-Smith/SchemaSmithEnterpriseDemos GitHub Wiki
A template is a metadata representation of a database. Conceptually, in object oriented terms, a template is a class and a database is an instance. A template defines what a database should look like.
The Test Product has multiple templates defined:
{
"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" }
]
}{
"Name": "Secondary",
"DatabaseIdentificationScript": "SELECT [name] FROM master.dbo.sysdatabases WHERE [name] = '{{SecondaryDB}}'",
"VersionStampScript": "PRINT '{{ReleaseVersion}}'",
"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" }
]
}| Property | Description |
|---|---|
| Name | The name of the template is referenced by the TemplateOrder in the [[product.json |
| DatabaseIdentificationScript | A result set containing the names of every database that is of this type. |
| VersionStampScript | This script is run last when updating a database of this type. Whatever is in this script block will be executed as a non query (ie, like an insert/update script). |
| UpdateFillFactor | Whether index fill factors should be updated within this template. Default true. |
| IndexOnlyTableQuenches | Support for index only table quenches |
| ScriptFolders | The configurable folder list for the template level script folders. Configurable Product and Template script folders |
| ScriptTokens | You can define as many script tokens as you require. In the example above, you can see the TestTableData script token is being used to capture the content of a json file which can then be part of a script to populate or update seed data. |
| ServerToQuench |
Primary, Secondary or Both just like the product folder definition |
Important
If there is a high amount of drift within your databases with index fill factors, you may elect to fix this over time instead of paying for all the index recreations at one time. An alternative is the judicial use of incremental releases targeting a few objects per release cycle.
With the proper definition of templates, you can enable some powerful patterns like the Multi-Tenant Database Registry.
This option is for use cases such as:
- Supporting products that use transactional replication and need to have different indexes in the replica than in the publisher.
- Maintaining indexes on a self hosted 3rd party database.
For these use cases, you can define your tables with just the schema, name and indexes properties.
This option can be defined in the template.json like:
{
"IndexOnlyTableQuenches": true
}The default is false.
A table using this would be defined like:
{
"Schema": "[dbo]",
"Name": "[MyTable]",
"Indexes": [
{
"Name": "[MyClusteredIndex]",
"Clustered": true,
"IndexColumns": "[ID]"
}
]
}When this table is quenched, only the indexes will be maintained and the table structure is being maintained through the article setup or some other means.