A template is a metadata representation of a database.
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 two templates defined for two different database definitions
{
"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" }
]
}
With the proper use of templates, you can enable some powerful patterns like
Multi-Tenant Database Registry.
| Setting | Value |
|---|---|
| Name | The name of the template is referenced by the TemplateOrder in the Product definition. |
| 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. Defaults to true. |
| IndexOnlyTableQuenches | Support for index only table quenches. |
| BaselineValidationScript | Optional script to ensure that the current state of the database being updated is the expected version. It can be any sql needed to validate the version as long as the result is a scalar true or false result (BIT). This can help prevent accidentally applying an old version and creating an outage. |
| ScriptFolders | The configurable folder list for the template level 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. |
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.
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 updates at one time. An alternative is the judicial use of incremental releases targeting a few objects per release cycle.