Category: Automation

Posted on by — 4 Comments

Pass parameters from Power automate to Azure DevOps pipeline using rest api

Recently I had to implement the scenario that is depicted below.

In more detail I had to implement a way to get user input (usernames) in order to pass this information on an Azure DevOps pipeline and through this pipeline make some actions on Azure through az cli.

For the described solution I used the below services:

  • Azure Devops
  • Power Automate
  • Azure DevOps rest API
  • Azure

The first thing that I created was the form. In this form the user has to provide the input of the usernames in a requested format in order to pass this information on the later components.

Then I created a new power automate flow that would handle the input of this form and make a POST request on Azure DevOps api in order to trigger a build pipeline with the parameters of the form as input.

The flow and the task that have been used are depicted below.

Select response ID on the form.

On the POST request you should enter your details regarding the pipeline ID, organization and project. The body of the request should be as shown in order to get the parameters parsed correctly.

The azure devops pipeline will have as an input parameter and empty object. 

trigger: none
pr: none 
pool:
  vmImage: windows-latest

parameters:
  - name: users
    type: object
    default: []

jobs:
  - job: vdi
    displayName: rest api pipeline
    steps:
      
    - ${{ each user in parameters.users }}:
      - task: PowerShell@2
        inputs:
          targetType: 'inline'
          script: |        
            Write-Host "${{user}}"

When user submits the form

then the power app will run

and as a result the azure devops pipeline will be triggered through the rest api.

Finally the pipeline will parse the parameters provided by the form.

Posted on by — Leave a comment

Test your backup mechanism – Automated restore for MS SQL using Azure DevOps

Most organizations rely on their backup solutions for application faults or data corruptions. However the backup is not frequently tested in order to verify that restore would be successful. In this post I implement a backup testing mechanism.

Lets examine a scenario for an MS SQL database server. The server will output a backup file (.bak) on a storage account based on a retention policy. This backup will be automatically restored on a SQL server through a pipeline and a result will be written as an output. The result can be then reported on the monitoring solution.

The flow is depicted below. An azure devops agent should be installed on the server on which the database will be restored. The pipeline will fetch the backup file from the storage account and store it on a data disk (in my case R:\files). Then sqlcmd command will be used to restore the .bak file and record the result. The backup file is provided by a parameter on the pipeline. Also a service connection should be created with your subscription on which the storage account is located.

Pipeline code:

trigger: none
pr: none
pool:
vmImage: windows-latest
parameters:
– name: backupfile
type: string
jobs:
– job: download
displayName: Download DB backup file
steps:
– task: AzureCLI@2
displayName: az cli download backup file from storage account
inputs:
azureSubscription: 'Azure-Service-Connection'
scriptType: 'ps'
scriptLocation: 'inlineScript'
inlineScript: |
$container_name_input = "container_name"
$saccount_name = "storage_account_name"
#$json = az storage blob list –container-name $container_name_input –account-name $saccount_name
az storage blob download –file "R:\files\${{parameters.backupfile}}" –name "${{parameters.backupfile}}" –container-name $container_name_input –account-name $saccount_name –auth-mode login
– job: restore
displayName: Restore SQL backup
dependsOn: download
steps:
– task: PowerShell@2
displayName: sqlcmd restore backup
inputs:
targetType: 'inline'
script: |
sqlcmd -q "RESTORE DATABASE [Database_Name] FROM DISK=N'R:\files\${{parameters.backupfile}}' WITH REPLACE,RECOVERY" -o R:\files\result.txt;
[string]$result = Get-Content R:\files\result.txt
if ($result.contains('successfully')) {
Write-Host "Restore was succesfull…"
}
elseif ($result.contains('terminating')) {
Write-Host "Terminating…"
}
view raw azure-devops-sql-restore hosted with ❤ by GitHub

Executing pipeline:

Result:

Important:

Azure DevOps agent service is configured to run with a specific account (in my case NT/ Local System). This account should have the appropriate permissions on the SQL server for the restore procedure. The easier way would be to make this account a database sysadmin.

Adding the NT Authority\System on SQL server sysadmins

Posted on by — Leave a comment

Pause automatically Dedicated SQL pool (formerly SQL DW) – REST api

I was struggling to make a dedicated sql pool pause automatically through a pipeline on non working hours. Although there are some options that you can use with az cli, it seemed that they could not work with an error that indicated resource could not be found.

az synapse sql pool | Microsoft Docs

Searching further I found that this action can be performed through the azure management API an example of which is shown below.

https://management.azure.com/subscriptions/a23eef11-f200-4722-866c-248ca45142f6/resourceGroups/sql-pool/providers/Microsoft.Sql/servers/geralexgr-sql/databases/geralexgr-sql/pause?api-version=2020-11-01-preview

In order to stop a dedicated sql pool (formerly SQL DW) you can follow the below guide that uses az cli to get an access token.

az account get-access-token

Add this token on Authorization tab with Bearer type and make your request.

By checking the result you can see that pause is completed sucesfully.

You can also automate the procedure using an Azure DevOps pipeline.

– task: AzureCLI@2
displayName: az cli stop command
inputs:
azureSubscription: 'YOURSUB'
scriptType: 'pscore'
scriptLocation: 'inlineScript'
inlineScript: |
$token = az account get-access-token | ConvertFrom-Json
$mytoken = $token.accesstoken
$headers = @{
Authorization = "Bearer $mytoken"
}
Invoke-RestMethod -Method Post -Uri "https://management.azure.com/subscriptions/xxxxx-xxxxx-xxxxx/resourceGroups/resource-group/providers/Microsoft.Sql/servers/sql-server/databases/database/pause?api-version=2020-11-01-preview" -Headers $headers -UseBasicParsing -ContentType 'application/json'
view raw dedicated-sql-pool-pause-powershell-azure-devops hosted with ❤ by GitHub

https://docs.microsoft.com/en-us/rest/api/sql/2021-02-01-preview/servers

Posted on by — Leave a comment

Dynamically checkout github repository based on parameter

In some cases, the DevOps team may need to checkout multiple github repositories in the current working directory of the pipeline. By default the pipeline will download the code of the repository where it belongs.

The below pipeline will download a github repository based on a parameter that user provides. In order to accomplish that you will need a service connection with your github account/organization.

Then using the below pipeline you can download a repository with a specific branch by providing the repository name.

trigger:
- main

pool:
  vmImage: ubuntu-latest

parameters:
- name: repoparam
  type: string
  default: reponame
- name: branchparam
  type: string
  default: main

variables:
  - name: repositoryvar
    value: ${{parameters.repoparam}}
  - name: branchvar
    value: ${{parameters.branchparam}}


resources:
  repositories:
    - repository: gitrepo
      type: github
      name: geralexgr/$(repositoryvar)
      ref: $(branchvar)
      endpoint: geralexgr

steps:
- checkout: gitrepo
  displayName: download repository

For example given that I have created a repository geralexgr/terraform-az-lin-win I will provide only the name of it terraform-az-lin-win and the main branch.

When running the pipeline the selected repository will be downloaded into C:agent/work/buildID/s or /home/vsts/work/buildID/s