Forum Discussion

zmkarakas's avatar
zmkarakas
Copper Contributor
Jul 03, 2023

An automation and templating question with Azure Pipelines

Hello, we are working on a project where we have to automate resources across different environments. We are using Azure Pipelines

 

What we have:

 

An original environment where we would publish straight from the collaboration branch to workspace_publish. This env contains:

- A Synapse workspace (Dedicated SQL Pool)
- Azure Functions used in the Synapse pipelines through Linked Services and also one function that works as an API and should be callable always.
- Blobstorage (StorageV2)
- Key vault
- Logic Apps (sending emails)

- A environment deploy pipeline in DevOps. 

 

What we want:

- An automated way of deploying the Synapse Workspace, Linked Services, Pipelines, Stored Procedures and tables from the dev environment to the others (qa, stage, prod)
- Templating of connection strings, function URLs, linked services so that all connections work in each environment and any other interaction with resources outside Synapse.
- Definition of the SQL Pool to run Stored Procedures
- Database migration: If we create a table/column in dev how can we push it to prod without affecting the data?
- Templating variables and resources in Azure Functions
- Template permissions/access, specifically between Azure Functions and Database, Blob,

 

How far we've gotten: Following this tutorial: https://www.youtube.com/watch?v=d7wsEh8Vr34

- We have tried to expose all the parameters that need templating using a template-parameters-definition.json file placed in the main branch of the repo (CICD-Test).
- Then using override parameters in the Release pipeline in DevOps change the parameters to point at the correct resources for example:
   Changing the blobstorage connection string from AccountName=stgnkdwdatalakedev to AccountName=stgnkdwdatalakeqa

 

How can we proceed with this?

 

Since we do a lot of adhoc analysis in the DW and also with the live data coming from the event hub we require at least the dev and prod environments to be running.

 

azure devops
Synapse dedicated pool

1 Reply

Sort By
  • Kidd_Ip's avatar
    Kidd_Ip
    MVP
    Sep 10, 2025

    How about this:

     

    1. Synapse Workspace Automation
    Use ARM templates or Bicep to define your Synapse workspace and its artifacts (pipelines, datasets, linked services, etc.).
    •    You can extract the workspace template via Synapse Studio > Manage > Workspace > Export template.
    •    Use the template-parameters-definition.json to expose environment-specific values like connection strings, URLs, etc.
    •    In your pipeline, use az deployment group create or New-AzResourceGroupDeployment to deploy the template with overridden parameters.

    2. Templating Across Environments
    To make this scalable:
    •    Create a parameter file per environment (e.g., dev.parameters.json, qa.parameters.json, etc.).
    •    Use pipeline variables or variable groups in Azure DevOps to inject values dynamically.
    •    Use YAML templates to reuse pipeline logic across environments.

    parameters:
  - name: environment
    type: string

steps:
  - task: AzureResourceManagerTemplateDeployment@3
    inputs:
      deploymentScope: 'Resource Group'
      templateLocation: 'Linked artifact'
      csmFile: 'synapse-template.json'
      csmParametersFile: '${{ parameters.environment }}.parameters.json'


    3. SQL Pool & Stored Procedure Deployment
    For database schema changes:
    •    Use SQL Server Data Tools (SSDT) or DACPACs to manage schema.
    •    Use SqlPackage.exe in your pipeline to deploy schema changes: 

    SqlPackage.exe /Action:Publish /SourceFile:MyDW.dacpac /TargetConnectionString:"..." /p:BlockOnPossibleDataLoss=false


    •    Set BlockOnPossibleDataLoss=false to avoid dropping columns with data—but use with caution.
    Consider using Flyway or Liquibase if you want versioned migrations with rollback support.

    4. Azure Functions Templating
    Use Bicep or ARM templates to deploy Azure Functions with environment-specific settings:
    •    Define appSettings in your template and override them per environment.
    •    Use Key Vault references for secrets: 

    "AzureWebJobsStorage": "@Microsoft.KeyVault(SecretUri=https://myvault.vault.azure.net/secrets/storageConnection)"


    5. Permissions & Access Control
    Use role assignments in your templates or scripts:
    •    Assign Managed Identity of Azure Functions access to Blob, SQL, etc.
    •    Use az role assignment create or ARM roleAssignments to automate this.

    {
  "type": "Microsoft.Authorization/roleAssignments",
  "properties": {
    "roleDefinitionId": "...",
    "principalId": "[reference(resourceId('Microsoft.Web/sites', parameters('functionAppName')), '2018-11-01', 'Full').identity.principalId]"
  }
}

     

Resources