Understand automated deployment

Automated deployment is a crucial aspect of managing and administering Microsoft Azure SQL solutions. It allows you to streamline and simplify the process of deploying and managing your SQL databases, while reducing the scope for human error and minimizing downtime. In this article, we will explore the key concepts and tools associated with automated deployment in Azure SQL solutions.

Azure Resource Manager (ARM) Templates

One of the primary tools for automated deployment in Azure SQL is Azure Resource Manager (ARM) templates. These templates are JSON files that define the infrastructure and configuration of your Azure resources. By using ARM templates, you can deploy and manage your SQL databases consistently across different environments, such as development, testing, and production.

Let’s take a closer look at how ARM templates work. Below is an example of an ARM template that deploys an Azure SQL database:

{
"$schema": "https://schema.management.azure.com/schemas/2019-04-01/deploymentTemplate.json#",
"contentVersion": "1.0.0.0",
"parameters": {
"databaseName": {
"type": "string",
"metadata": {
"description": "The name of the SQL database."
}
},
"edition": {
"type": "string",
"metadata": {
"description": "The edition of the SQL database."
},
"defaultValue": "Standard"
},
"collation": {
"type": "string",
"metadata": {
"description": "The collation of the SQL database."
},
"defaultValue": "SQL_Latin1_General_CP1_CI_AS"
}
},
"resources": [
{
"type": "Microsoft.Sql/servers/databases",
"apiVersion": "2020-11-01-preview",
"name": "[parameters('databaseName')]",
"location": "[resourceGroup().location]",
"dependsOn": [
"[resourceId('Microsoft.Sql/servers', 'mySql')]"
],
"properties": {
"collation": "[parameters('collation')]",
"edition": "[parameters('edition')]",
"requestedServiceObjectiveName": "S0"
}
}
]
}

In the above ARM template, we define parameters such as the database name, edition, and collation. These parameters can be customized during deployment to meet specific requirements. The “resources” section specifies the Azure SQL database resource to be deployed, including its properties like collation, edition, and service level objective.

To deploy an ARM template, you can use Azure PowerShell or Azure CLI. Here’s an example of deploying the ARM template using Azure PowerShell:

New-AzResourceGroupDeployment -ResourceGroupName "myResourceGroup" -TemplateFile "path/to/template.json" -TemplateParameterFile "path/to/parameters.json"

In this command, “myResourceGroup” is the name of the resource group where the deployment will be created. The -TemplateFile parameter specifies the path to the ARM template file, and the -TemplateParameterFile parameter specifies the path to the parameter file, which contains the values for the parameters defined in the ARM template.

Azure DevOps Pipelines

Another tool that can be used for automated deployment is Azure DevOps. Azure DevOps provides a complete set of DevOps services to enable end-to-end automation of application delivery. By leveraging Azure DevOps pipelines, you can define CI/CD (Continuous Integration/Continuous Deployment) processes for your Azure SQL solutions.

With Azure DevOps pipelines, you can automate the building, testing, and deployment of your SQL databases. You can create a pipeline that triggers whenever changes are committed to your source control repository, such as Azure Repos or GitHub. The pipeline can then build your database project, run automated tests, generate deployment artifacts, and deploy the changes to the target Azure SQL database.

Here’s an example of an Azure DevOps pipeline YAML file that deploys a database project to an Azure SQL database:

trigger:
- main

- task: AzureSqlDatabaseDeployment@1
displayName: 'Deploy to Azure SQL Database'
inputs:
azureSubscription: 'myAzureSubscription'
serverName: 'mySqlServer'
databaseName: 'mySqlDatabase'
deployType: 'DacpacFile'
deploymentAction: 'Publish'
dacpacFile: '$(Build.ArtifactStagingDirectory)/db_publish/*.dacpac'

In the above YAML file, the pipeline is triggered whenever changes are committed to the main branch. The pipeline runs on an Ubuntu agent (ubuntu-latest) and consists of several steps. It builds the database project using .NET Core CLI, deploys the compiled database project to a folder, and then deploys the changes to the target Azure SQL database using the AzureSqlDatabaseDeployment task.

By leveraging ARM templates and Azure DevOps pipelines, you can achieve automated deployment of your Azure SQL solutions, enabling faster and more efficient delivery of your applications. Whether you are deploying a single database or managing a complex database estate, these tools provide the flexibility and scalability required for successful automated deployment in Azure SQL.