Looping a task in Azure Devops

I came across with a requirement where I had to create around 100 SQL databases through the DevOps pipeline in one release. In this kind of solution, the native support of the Powershell in Azure DevOps always helped! And I have a good faith on Powershell as it helped me to implement any complex logic :) However, I wanted to check how this problem can be solved in Azure DevOps natively? How can I loop a task in Azure Devops? AI found a solution going through the Azure Devops documentation, and I am going to post my learning here.

P.S I found some problem related to the variable as it only accepts string, however little tweak in implementation logic, can help us to loop through a task.

Problem Statement :

We already have SQL Server Elastic pool, and a SQL Server created using the release pipeline . We should now have a step which can create multiple databases in different firms , and can attach to the same SQL server elastic pool.

Assumption :

1. We already have a SQL Server and Elastic pool present inside a Resource group. you can use Azure CLI to create this.

2. Azure Devops service connection has the contributor access to the Resource group.

Implementation :

I have used the Azure yaml pipeline. More about the Azure yaml pipeline can be found here.

In order to do this, we need two yml files. One yml file will do the actual operation of creating the sql database, its kind of a template (createSQLDB-template.yml). All the parameter of this file will be supplied from a release pipeline (callerPipeline.yml) , and template file will loop through the given list. Lets look at the file once..

# File Name : createSQLDB-template.yml


parameters:
  sqlservername: ''
  sqlelasticpoolname: ''
  firms : [] #defining array here to expect multiple values. 
  resourcegroupname: ''


steps:
- ${{ each firm in parameters.firms }}:
  - task: AzureCLI@2
    displayName: 'Azure CLI to create the SQL Server database inside an elastic pool'
    inputs:
      azureSubscription: 'azure-service-connection'
      scriptType: bash
      scriptLocation: inlineScript
      inlineScript: |
        dbprefix="db-"
        firmname=${{ firm }} 
        sqldbName=$dbprefix$firmname
        echo $sqldbName
        az sql db create --resource-group ${{ parameters.resourcegroupname }} --server ${{ parameters.sqlservername }} --name $sqldbName --elastic-pool ${{ parameters.sqlelasticpoolname }}

The above script (createSQLDB-template.yml) creates the sql server database. It expects some parameters from the caller function, and creates the sql database in a specific resource group attaching to a SQL Server Elastic pool.

The below script is being used to setup the Azure Devops pipeline. This script calls the template file passing few parameters, which then creates separate databases.

# The use of this pipeline to create the sql databases for the multiple firms in an existing SQL Server  and SQL Server Elastic pool. 


name: $(Date:yyyyMMdd)$(Rev:.r)pool:
  vmImage: 'ubuntu-latest'


variables:


- name: sqlservername # name of the parameter; required
  value: damosqlserver # provide the name of the sqlserver


- name: sqlelasticpoolname # name of the parameter; required
  value: demosqlserverepool # provide the name of the sql server database pool


- name: resourcegroupname # name of the parameter; required
  value: "RGSam"


steps:
- template : createSQLDB-template.yml
  parameters:
    sqlservername: ${{ variables.sqlservername }}
    sqlelasticpoolname: ${{ variables.sqlelasticpoolname }}
    firms: ["firm200","firm201","firm202","firm203","firm204"]
    resourcegroupname: ${{ variables.resourcegroupname }}

Lets now look at the DevOps piepeline which is created using the above script.

If we now trigger the pipeline, we can see that template yml pipeline is now being called 5 times as we have provided list of 5 values in the parameter section ( firms: ["firm200","firm201","firm202","firm203","firm204"] ).

If we look at the Azure portal, we can see the databases are created..

We can use the template as a task group ( combination of multiple tasks) and perform a loop combining multiple tasks.

Hope this helps!