One YAML pipeline for many Azure Synapse environments in Azure DevOps

Reading Time: 5 minutes

In this post I want to raise awareness about how you can use just one YAML pipeline for many Azure Synapse environments in Azure DevOps. Instead of creating multiple YAML pipelines for different environments.

To clarify, when I say YAML pipelines I mean the YAML pipelines you work with in Azure Pipelines service within Azure DevOps. Not the pipelines you work with within Azure Synapse pipelines.

I want to do this post for one very good reason.

A while ago I was made aware that some people still deploy the same update to different environments using multiple YAML pipelines. It turned out that the main reason is because they did not know that the Environments feature has improved a lot in Azure DevOps.

With this in mind, I want to raise awareness of what you can do now in environments.

Because ideally you should implement one YAML pipeline. Just like how pipelines are setup when you work with Power BI deployment pipelines.

Of course, there are times where YAML pipelines will need to be setup differently. For example, when you need one YAML pipeline to call others in a logical flow.

By the end of this post, you will know what approvals and checks can currently be done within the environments feature in Azure Pipelines. Which you can than use with YAML pipelines. In addition, how you can use them in combination with variables for Azure Synapse deployments.

Environments in Azure DevOps

You can find the Environments feature within the Azure Pipelines service in Azure DevOps.

When you go into this feature you can create an environment by clicking on the big blue ‘Create environment’ button. One key point to remember is that environments can be also created automatically. Which I cover later in this post.

Once the environment exists you can click the ellipsis in the top right-hand corner. From there select ‘Approvals and checks’.

If this is the first environment I recommend clicking on the ‘See all’ text on the bottom right-hand side of the screen to view the entire list.

Once done you can see this impressive list of approvals and checks that you can now do in the Environments feature. Some of these will look familiar to those of you who have worked with the GUI-based Releases feature in Azure Pipelines. Because you can configure most of these as both pre and post deployment conditions within the Releases feature.

As you can see this opens up a lot of possibilities for your YAML pipelines.

YAML pipeline environment possibilities

For instance, you can set approvals so that people must approve deployments to different Azure Synapse environments.

For example, say you are making a change to something in an Azure Synapse workspace that is in a development environment. Where an approval has been setup in Azure Pipelines for the production environment.

Once you have committed an update for that change the CI/CD process starts. However, because an approval has been setup for the production environment somebody must approve the update before it takes place.

Of course, these days a lot of people are looking to do more automated approvals. Because of the high number of updates that take place. In reality, this can be an option once there is a high level of confidence in your YAML pipeline.

A good example of an automated approval is where Azure Pipelines checks Azure Monitor before it allows an update to be done.

For example, say you work on a dedicated SQL Pool that is monitored by Azure Monitor. All seems well in development. However, when the update is done in an acceptance environment it introduces performance issues due to a poor distribution choice.

After this update Azure Pipelines then goes to deploy to production. Because there is an approval set to check Azure Monitor it notices that there is a performance issue in the acceptance environment. So it stops the update from taking place.

Microsoft shows how you can use Azure Monitor with your Azure Synapse Analytics workspace. So that you can monitor things like dedicated SQL Pools.

Adding the environments to YAML pipelines

Once your environment is setup you can specify it in your YAML pipelines by using a deployment job. You must change the syntax of your job if your existing YAML pipeline contains a standard one.

For example, below is a standard job that I have configured in my AzureDevOps-SynapseServerlessSQLPool GitHub repository. Which is a template to perform CI/CD for a serverless SQL Pool. Notice that it does not specify the term ‘- deployment’ right after the jobs line.

stages:

- stage: RunScripts
  displayName: 'Run Migration-based scripts'

  jobs:
    - job: 'RunScripts'
      displayName: 'Run Migration-based scripts'
      steps:
      - task: PowerShell@2
        displayName: 'Install dbops PoSh Module'
        inputs:
          targetType: 'inline'
          script: 'Install-Module -Name dbops -Force -PassThru'

Whereas, the YAML file in my AzureDevOps-AzureSynapseSQLPool GitHub repository for dedicated SQL Pools it contains a deployment job.

By default, it deploys to an environment called Azure. As you can see in bold below.

- stage: AzureSQLPool
  displayName: 'SQL Pool'
  jobs:
    - deployment: 'SQLPool'
      displayName: 'Dedicated SQL Pool'
      environment: Azure
      pool: 
        name: $(agentpool)

      strategy:
        runOnce:
          deploy:
            steps:
                  - task: DownloadBuildArtifacts@0
                    displayName: 'Download Artifacts'
                    inputs:
                      buildType: 'current'
                      downloadType: 'specific'
                      artifactName:  '$(SQLPoolartifactname)'
                      downloadPath: '$(System.ArtifactsDirectory)'

One key point to remember is that if you specify an environment that does not exist Azure Pipelines can try and create it for you.

If you have already run the above YAML file you can check if the Azure environment already exists in the Environments feature. You can read about recent changes to how this works in their sprint 188 update.

I purposely named the environment in the YAML file to Azure to avoid any confusion with any existing Production environments.

So, if you intend to use this template for dedicated SQL Pools you can change the environment to one you of your choice. Of course, you can also configure the Azure environment instead if you want to keep it.

Now, when you combine this with my other post where I showed how to keep your Azure Synapse secrets secret in Azure DevOps you can see just how powerful a single pipeline can be for deployments.

Because one pipeline can contain multiple environments, plus secrets stored in different Azure Key Vault services for your different environments.

Final word about using one YAML pipeline for many Azure Synapse environments

I do hope this post about using just one YAML pipeline for many Azure Synapse environments in Azure DevOps helps others.

Especially those of you who are thinking about creating multiple pipelines when you can keep things simple and create just one. Which is why I wanted to make everybody aware that you can do a lot in Azure DevOps now.

Of course, if you have any comments or queries about this post feel free to reach out to me.