Publish button on Azure Synapse using code

Question

Hello guys I'm currently working with Azure Synapse studio. My situation could be described in this way:

I have 3 env: Dev, Test and Prod, each of them has a Azure synapse workspace but I can access only to the Dev one. I need to make some changes from Dev also for the other 2 env (sql script, pipelines etc) and then publish them to other env without touching them. So I think Azure DevOps can be the solution. From Dev Syanapse studio Workspace I created 3 branches 1 per env, all of them linked to an Azure DevOps repo. Also Test and Prod are linked to the same repo. The problem is that the code on Test and Prod workspace could be different from the code on Dev. So I can't use the same ARM template (generated by publishing on the publish branch of the workspace) for all the 3 environment. A good way could be find a way to hit the publish button also on the other envs without using the portal, for example by a REST API ? It is possible ?

Now I only set up the 3 branch solution so I can magae the 3 env directly from Dev env but I think that this will not be the right solution, are changes applied on other envs ? Can I run SQL scripts or pipelines manually from other envs ? This is my current situation on the other envs I asked to set collaboration and publish branch with the same value as the env branch name (test-test-test and prod-prod-prod)

Answer 1

This was bothering me as well, so put together the following to be run once any PR is approved to merge into the Synapse collaboration branch, in our case, "main".

For your case, you can modify to target the relevant workspaces.

See below Azure DevOps pipeline code.

What it does is:

1. runs the Synapse workspace validation task, which also generates the workspace template jsons as an artifact that need to be published to the workspace_publish branch.

2. It will then check out your publish branch and commit and push the templates that were generated from the previous task

3. Such that the workspace UI does not think there are any unpublished changes when you click the "Publish" button, we need to update the workspace configuration to reflect the latest commit ID from the workspace COLLABORATION branch (main in this example) that was used to generate what we pushed to the PUBLISH branch in the previous step.

Any suggestions/improvements welcome. Hope this helps.

name: $(TeamProject)_$(Build.DefinitionName)_$(SourceBranchName)_$(Date:yyyyMMdd)$(Rev:.r) # sets Build.BuildNumber

trigger:
 branches:
   include:
   - main
 paths:
   include:
     - synapse/*

resources:
  repositories:
  - repository: 'Synapse-Publish'
    type: git
    name: Synapse # update to the name of your repo
    ref: workspace_publish # update to the name of your synapse PUBLISH branch


variables:
  repoName: $(Build.Repository.Name)
  azureSubscription: your_subscription
  azureTenantId: your_tenant_guid
  adoOrg: your_azure_devops_org_name
  adoProject: your_azure_devops_project_name
  SourceWorkspaceName: your_synapse_workspace_name
  workspacePublishBranch: workspace_publish # should be the same for you but update if not


stages:
- stage: build_stage
  displayName: Build, Run Validations, Publish NonProd if merged to main

  jobs:
  # other jobs excluded from this snippet
  
  - job: publish_workspace_artifacts_job
    displayName: Publish for $(SourceWorkspaceName) $(workspacePublishBranch)
    # only kick off workspace publish job for non-PR builds
    condition: and(not(or(failed(), canceled())), ne(variables['Build.Reason'], 'PullRequest'))
    pool:
      name: 'linux-vmss' # update this for whatever you need
    steps:
      - checkout: self # main
        clean: true
        persistCredentials: true

      - task: Synapse workspace deployment@2
        displayName: Generate workspace artifact templates
        condition: true
        continueOnError: false    
        inputs:
            operation: 'validate' # despite this name, it also generates the templates
            ArtifactsFolder: '$(Build.SourcesDirectory)/$(repoName)/synapse'
            TargetWorkspaceName: $(SourceWorkspaceName)
      
      - checkout: 'Synapse-Publish' # workspace_publish
        clean: true
        persistCredentials: true

      - task: CmdLine@2
        displayName: 'Set git user'
        inputs:
            workingDirectory: '$(System.DefaultWorkingDirectory)'
            failOnStderr: true
            script: |
                git config --global user.email "whatever.you.want@your_org.com"
                git config --global user.name "Whatever You Want"

      - task: AzurePowerShell@5
        displayName: Publish to $(SourceWorkspaceName) $(workspace_publish)
        condition: true
        inputs:
          azureSubscription: '$(azureSubscription)'
          ScriptType: InlineScript
          Inline: |
            # the output from the workspace validate step above are saved here, also published as artifact with name = the synapse workspace name
            # Get-ChildItem $(Build.SourcesDirectory)/ExportedArtifacts -Name

            cd $(Build.SourcesDirectory)/$(repoName)
            git pull origin $(workspacePublishBranch)
            git switch $(workspacePublishBranch)

            Move-Item -Path $(Build.SourcesDirectory)/ExportedArtifacts/*.json -Destination $(Build.SourcesDirectory)/$(repoName)/$(SourceWorkspaceName) -Force -Verbose

            git add $(Build.SourcesDirectory)/$(repoName)/$(SourceWorkspaceName)/*.json
            $diff = git diff --cached
            $status = git status
            if (!($status.ToLower() -like "*nothing to commit*"))
            {
                echo "##[section]git push changes to repo";
                git commit -m "Update $(workspacePublishBranch) for source workspace $(SourceWorkspaceName) [skip ci]";
                git pull --rebase;
                git push origin $(workspacePublishBranch);
            }          
            else
            {
                echo "##[warning]No new changes to push for source workspace $(SourceWorkspaceName) templates";
                git reset –-hard origin/$(workspacePublishBranch)
                git clean -fxd
            }

          azurePowerShellVersion: 'LatestVersion'

      - task: AzurePowerShell@5
        displayName: Update $(SourceWorkspaceName) Git Config # this is required so when you click "Publish" within the workspace it doesn't think there are any changes vs. what's already published
        inputs:
          azureSubscription: '$(azureSubscription)'
          ScriptType: InlineScript
          Inline: |      
            # get latest version of this module which now has the LastCommitId parameter that we need
            Install-Module -Name Az.Synapse -Confirm:$false -RequiredVersion 1.5.0 -Force
            Import-Module -Name Az.Synapse -MinimumVersion 1.5.0
            
            cd $(Build.SourcesDirectory)/$(repoName)
            [String] $latestCommitHash = git log -n 1 origin/main --pretty=format:"%H"  # format to get only the hash value of the latest commit

            $config = New-AzSynapseGitRepositoryConfig `
              -RepositoryType AzureDevOpsGit `
              -TenantId $(azureTenantId) `
              -AccountName $(adoOrg) `
              -ProjectName $(adoProject) `
              -RepositoryName $(repoName) `
              -CollaborationBranch main `
              -RootFolder "/synapse" `
              -LastCommitId $latestCommitHash
              

            echo "##[section] Updating $(SourceWorkspaceName) git configuration to point to the latest main branch commit ID"
            # see https://learn.microsoft.com/en-us/powershell/module/az.synapse/update-azsynapseworkspace?view=azps-8.0.0
            Update-AzSynapseWorkspace -Name $(SourceWorkspaceName) -GitRepository $config
          
          azurePowerShellVersion: 'LatestVersion'  

Answer 2

with the new version (V2) of the Synapse workspace deployment (in Preview 2022-06), it is now possible to deploy from any branch using Azure Devops, so no need for a workspace_publish branch or the Publish button.

Just make the object json files available as artifacts to the release pipeline, and select "Validate and deploy" as the Operation Type.

Answer 3

I am working with Microsoft directly, building a Synapse warehouse myself for a large corporation. We have the same issue, in that the Publish button must be pressed manually for the ARM templates to be generated. Microsoft have confirmed that there is no automatic method for this available right now; we had hoped to receive a Preview AzDevOps deployment task this month, but it turns out that it simply allows us to validate the JSON assets - it still deploys using the ARM template.

We have also looked at using Azure Data Factory tools to deploy from the JSON component files, but we run into issues with the dedicated pool stored procedure tasks being unsupported. :(

Answer 4

The only standard option to achieve this is by creating GitHub repository and then creating Continuous Integration and creating a self-hosted Azure DevOps VM agent or use an Azure DevOps hosted agent.

Then you can setup release pipelines in Azure DevOps to work with different environments. But still you need to commit the changes in the GitHub repository for each environment, there is no Publish button kind of this available.

Refer Continuous integration and delivery for an Azure Synapse Analytics workspace for more details.