Azure provides a number of different methods that can be used in order to implement and configure its resources. Each of the methods differs to some extent in regard to functionality and intended use cases.
The Azure portal offers friendly, intuitive interface but lacks automation capabilities. Scripting with Azure PowerShell or Azure CLI facilitates automation but does not scale well due to its limited support for parallelism. The use of REST API and Azure SDKs, while extremely powerful, requires at least some level of programming skills.
Azure Resource Manager templates, which are covered in this article, deliver superior speed, primarily due to their ability to leverage the intelligence built into the underlying cloud platform, taking advantage of concurrency and performance optimizations. In addition, their support for declarative provisioning and versioning promote compliance with principles of DevOps and Infrastructure as Code. These benefits make Azure Resource Manager templates the recommended choice for resource deployment and management, especially in environments where change control and compliance are of utmost importance.
The primary obstacle that likely has some impact on the popularity of Azure Resource Manager templates is their relative complexity. While their structure and syntactical rules are well defined and documented (for details, refer to the Understand the structure and syntax of Azure Resource Manager Templates article on Microsoft Docs), authoring more involved ones can pose significant challenges. Fortunately, Microsoft and the open source community maintain a large number of predefined, ready-to-use templates, which can also be easily modified to suit custom needs (the primary source of these templates is a GitHub-based Azure Quickstart Templates repository). Another issue specific to Azure SQL Database is that its more recent versions of underlying APIs, which are referenced by templates are in preview.
The documentation regarding Azure SQL Database specific aspects of Azure Resource Manager templates is available in the Microsoft.Sql resource types section of Microsoft Docs. This section contains the listing of all Microsoft.Sql resource provider resource types and their corresponding API versions. This includes the Microsoft.Sql/Servers resource (available, at the time of authoring this article, in versions 2015-05-01-preview and 2014-04-01) and the Microsoft.Sql/Servers/databasesresource (available, at the time of authoring this article, in versions 2017-10-01-preview, 2017-03-01-preview, and 2014-04-01).
For example, the following is the JSON-based definition of the Microsoft.Sql/Servers resource (the apiVersion element designates the version of Azure Resource Manager API that would be used to provision the server):
{
"name": "string",
"type": "Microsoft.Sql/servers",
"apiVersion": "2015-05-01-preview",
"location": "string",
"tags": {},
"identity": {
"type": "SystemAssigned"
},
"properties": {
"administratorLogin": "string",
"administratorLoginPassword": "string",
"version": "string"
}
}
As you can see, you can use this definition to specify the primary, required server parameters, such as the administrative credentials, the Azure region where the server will reside, or its name. Similarly, the following represents the JSON-based definition of the Microsoft.Sql/Servers/database resource (the apiVersion element designates the version of Azure Resource Manager API that would be used to provision the database):
{
"name": "string",
"type": "Microsoft.Sql/servers/databases",
"apiVersion": "2017-10-01-preview",
"location": "string",
"tags": {},
"sku": {
"name": "string",
"tier": "string",
"size": "string",
"family": "string",
"capacity": "integer"
},
"properties": {
"createMode": "string",
"collation": "string",
"maxSizeBytes": "integer",
"sampleName": "string",
"elasticPoolId": "string",
"sourceDatabaseId": "string",
"restorePointInTime": "string",
"sourceDatabaseDeletionDate": "string",
"recoveryServicesRecoveryPointId": "string",
"longTermRetentionBackupResourceId": "string",
"recoverableDatabaseId": "string",
"restorableDroppedDatabaseId": "string",
"catalogCollation": "string",
"zoneRedundant": boolean,
"licenseType": "string",
"readScale": "string"
}
}
As expected, this definition provides the ability to assign several database-level settings, such as the name, sku, the target Azure region (which obviously must much the value of the location parameter you specified during the server deployment), collation, maximum size, or elastic pool membership.
Now let’s leverage these two definitions in order to create a template that will allow you to provision a new logical server and an Azure SQL Database instance with a very basic configuration. To make the example more realistic, the parameters and variables sections of the template will be populated in the manner that simplifies deployment process. This will result in the following outcome:
{
"$schema": "https://schema.management.azure.com/schemas/2015-01-01/deploymentTemplate.json#",
"contentVersion": "1.0.0.0",
"parameters": {
"location": {
"type": "string"
},
"administratorLogin": {
"type": "string"
},
"administratorLoginPassword": {
"type": "securestring"
},
"databaseName": {
"type": "string"
},
"skuName": {
"type": "string"
},
"skuTier": {
"type": "string"
}
},
"variables": {
"serverName": "[concat('sql',uniqueString(resourceGroup().id))]"
},
"resources": [
{
"name": "[variables('serverName')]",
"type": "Microsoft.Sql/servers",
"apiVersion": "2015-05-01-preview",
"location": "[parameters('location')]",
"properties": {
"administratorLogin": "[parameters('administratorLogin')]",
"administratorLoginPassword": "[parameters('administratorLoginPassword')]",
"version": "12.0"
},
"resources": [
{
"name": "[concat(variables('serverName'),'/',parameters('databaseName'))]",
"type": "Microsoft.Sql/servers/databases",
"apiVersion": "2017-10-01-preview",
"location": "[parameters('location')]",
"sku": {
"name": "[parameters('skuName')]",
"tier": "[parameters('skuTier')]"
},
"dependsOn": [
"[concat('Microsoft.Sql/servers/', variables('serverName'))]"
],
"properties": {
},
"resources": [
]
}
]
}
],
"outputs": {}
}
Note that the template introduces the following changes to the original resource definitions:
- rather than hard-coding values of server and database attributes, parameters are used that allow providing these values dynamically at the template deployment time.
- the uniqueString() template function is used to generate the server name and store it in a template variable (which is referenced afterwards within the resources section). For more information regarding uniqueString(), refer to String functions for Azure Resource Manager templatesMicrosoft Docs article.
- since Azure SQL Database is a child resource of the logical server resource type, its definition is added to the resources section of the parent. To account for their relationship, the dependsOn element is also added and its name is specified in the format that consists of two segments (the name of the server and the name of the database separated by the
/character).
To provision an Azure SQL Database instance, the values of the parameters are provided at the deployment time or they can be stored in a parameter file, such as the following one:
{
"$schema": "https://schema.management.azure.com/schemas/2015-01-01/deploymentParameters.json#",
"contentVersion": "1.0.0.0",
"parameters": {
"location": {
"value": "eastus"
},
"administratorLogin": {
"value": "adminuser"
},
"administratorLoginPassword": {
"value": "53cr3tV@1u3"
},
"databaseName": {
"value": "sqldb1"
},
"skuName": {
"value": "S0"
},
"skuTier": {
"value": "Standard"
}
}
}
Frequently you might want to also automatically configure additional server and database settings, such as firewall rules, auditing, state of Transparent Data Encryption (TDE), or long-term retention. To identify the corresponding Azure Resource Manager resource types, refer to the aforementioned Microsoft.Sql resource types article on Microsoft Docs (note that you can apply these settings at the database deployment time or at any point afterwards). Examples will be provided illustrating usage of these resource types in upcoming articles published on this site.
