Thursday, May 21, 2020

Part II - Integrating Automated Reverse Azure Database Migration PowerShell Script into Azure DevOps

In my last post, we wrote a handy PowerShell script that takes the latest Master and Web SQL Databases from a Production-level Azure Resource Group and imports them into a Staging/UAT/Dev Azure Resource Group for a seamless reverse database promotion process.  

The original script, however, relies on a developer to run the script manually on a local machine and authenticate their credentials in order to utilize the AzureRm commands:

We can take this script a step further and integrate it as a new stage in the existing Azure DevOps Release Pipeline, or as a new dedicated Release Pipeline that can be executed independently.

In this example, we will create a new Azure DevOps Release Pipeline.  We'll assume a Service Principle connection already exists (which is likely if you're deploying to your App Services using Azure DevOps already) and you have the proper administrator permissions to create pipelines in Azure DevOps.   We'll also be working with an Inline Azure PowerShell script job instead of including a script file from an artifact.  Steps will slightly differ if you want to go that route, but the concept would remain the same. 

Release Pipeline Setup

Head over to the Pipelines > Release dashboard, click the New dropdown and select New release pipeline.

In the 'Select a template' menu, click 'Empty job'.

Modify the Pipeline name, then click on Stage 1 and click the plus sign on Agent job to add a new agent.  Search for 'powershell', find Azure PowerShell task and click the Add button

Set the Azure Subscription to the appropriate service principle, set the Script Type to Inline Script, and set the Azure PowerShell Version to Latest installed version

Save the pipeline and navigate to the Variables section

Variable Setup

Here, we'll add all the variables that we'll consume in the script - allowing for future modification without touching the script code itself.  

In our case, our script calls for the following variables: 
  • - sourceResourceGroupName
  • - sourceSqlServerName
  • - sourceMasterDbName
  • - sourceWebDbName

  • - targetResourceGroupName
  • - targetSqlServerName
  • - targetSqlServerAdminUserName
  • - targetSqlServerAdminUserPassword
  • - targetMasterDbName
  • - targetMasterSqlUserPassword
  • - targetWebDbName
  • - targetWebSqlUserPassword
  • - targetCdServerName
  • - targetCmServerName

Script Modifications

Luckily, our original script doesn't need too much tinkering! Just a bit 😉 

First, we'll want to remove the Login-AzureRmAccount command altogether since the Azure PowerShell task in the pipeline will authenticate off of the service principle.
We'll then replace any hardcoded variables with their new corresponding variables we previously configured throughout the script using the $env:someVariableName format:

We'll finish this off by placing the modified script in the Inline Script field of our Azure PowerShell task.


  1. Nice Post. I think you should be using the Az Powershell modules now since they RM modules will be going out of support soon.