Open in app

Sign in

Write

Sign in

How to start and stop Azure Synapse Triggers using GitHub Actions

Prashanth Kumar
8 min readJun 29, 2023

What is Azure Synapse Analytics triggers?

Triggers in Synapse pipelines determine when a particular pipeline(s) should be run. In an Azure Synapse environment, pipeline runs are typically instantiated by passing arguments to parameters that you define in the pipeline. You can execute a pipeline either manually or by using a trigger in a JSON definition

Problem Statement

Recently we started working on Azure Synapse Analytics and then started Creating/Managing

  • sql pools
  • linked services
  • creating dataflows etc.

As the environment gets bigger and bigger and then if you have a multiple environments you will get into maintenance issues. So how do i control my versions and how do I move my changes from 1 environment to another one. Thats the place we will be using Github as a version control to check-in my changes and then we will be moving from 1 environment to another.

Now the next Scenario is when you move your changes & when you have dependencies with triggers will code move will happen seamlessly? Answer is NO.

So how do I promote my code from 1 environment to Another one.

Solution

In order to achieve Stop and Start of triggers in Azure Synapse we went with GitHub Actions.

Lets look into workflow, before you deploy any Pipelines or LinkedServices you need to stop Triggers.

  1. First lets create a new PowerShell script and start defining all required parameters. Start defining parameters such as
  • $SynapseWorkspace
  • $ResourceGroupName
  • $ApplicationId
  • $TenantId
  • ServicePrincipalCertificate

2. In a parameter block we will just define SynapseWorkspace and ResourceGroup details.

# Input parameters
param(
[string]$SynapseWorkspace = “SynapseWorkspaceName”,
[string]$ResourceGroup = “ResourceGroupName”,
[string]$Action = “start”,
[string]$NameFilter = “”,
[switch]$WhatIf
)

Next we have to connect to Azure portal via PowerShell and connect to specific Subscription.

$plainText = $env:TEST_PWD_CERTIFICATE
$secureString = ConvertTo-SecureString $plainText -AsPlainText -Force

Connect-AzAccount -ServicePrincipal -ApplicationId “$ApplicationId” -TenantId “$TenantId” -CertificatePath “certificate.pfx” -CertificatePassword $secureString -WarningAction Ignore | Out-Null
Set-AzContext -Subscription “$SubscriptionName”

Once you login you need to connect to specific Workspace instance

# Get the specified workspace
Write-Output (“Getting workspace {0} in resource group {1}” -f $synapseworkspace, $resourcegroup)
$workspace = Get-AzSynapseWorkspace -ResourceGroupName $resourcegroup -Name $synapseworkspace
if (-not($workspace)) { throw “Could not find workspace” }
Write-Output $workspace

Now we need to know how many triggers exist and if you want to display their names individually

# Get the list of triggers if the workspace
Write-Output “Getting triggers”
$triggers = Get-AzSynapseTrigger -WorkspaceObject $workspace
if ($namefilter -ne ‘-’) { $triggers = $triggers | Where-Object { $_.Name -match $namefilter } } # filter out names if the filter is specified
Write-Output (“Found {0} triggers” -f $triggers.Count)
if (-not($triggers)) { exit }

Now you can see we found 6 triggers which are in running state.

Once you get all available triggers you may need to stop them 1 by 1

# Continue only if there are triggers to be found
if ($action -eq “stop”) {
# Stop the triggers
Write-Output “Looping through triggers that are started …”
$startedtriggers = $triggers | Where-Object { $_.Properties.RuntimeState -eq “Started” }
Write-Output (“Found {0} started triggers” -f $startedtriggers.Count)

foreach ($t in $startedtriggers) {
Write-Output (“Stopping {0} …” -f $t.Name);
try {
$result = Stop-AzSynapseTrigger -WorkspaceName $synapseworkspace -Name $t.name -WhatIf:$WhatIf.IsPresent -PassThru
Write-Output (“Result of stopping trigger {0}: {1}” -f $t.Name, $result)
}
catch {
Write-Output (“Something went wrong with {0}” -f $t.Name)
Write-Output $_
}
}
}

if ($triggers.Count -gt 0) {
if ($action -eq “start”) {
# Start triggers
Write-Output “Looping through triggers that are stopped …”
$stoppedtriggers = $triggers | Where-Object { $_.Properties.RuntimeState -eq “Stopped” }
Write-Output (“Found {0} stopped triggers” -f $stoppedtriggers.Count)

foreach ($t in $stoppedtriggers) {
Write-Output (“Starting {0} …” -f $t.Name);
try {
$result = Start-AzSynapseTrigger -WorkspaceName $synapseworkspace -Name $t.name -WhatIf:$WhatIf.IsPresent -PassThru
Write-Output (“Result of starting trigger {0}: {1}” -f $t.Name, $result)
}
catch {
Write-Output (“Something went wrong with {0}” -f $t.Name)
Write-Output $_
}
}
}

Write-Output “… done”
}

After execution final state will be as mentioned

Entire PowerShell Script is added below.

Now lets look into Github Actions to see how we can integrate and make it fully automated.

  1. Login to your Github account → Go back to your Github repository → Click on Actions → Click on “New workflow”.
  2. It will open a blank yml file and start adding below elements.
  • Branch (if any specific)
  • Jobs Build with specific agent
  • Login to specific Azure Subscription
  • Checkout of repo
  • Installing Azure Modules (if there is any specific module of Synapse needs to be installed, currently we are installing explicitely as there is a open bug which Microsoft is working on it. Once they fix we dont this command).
  • finally executing trigger start and stop script.

And entire workflow is

Stop Azure Synapse trigger(s) → Deployment of Synapse Components → Start Azure Synapse Trigger(s)

PowerShell scripts

Stop trigger PowerShell script

# Input parameters
param(
    [string]$SynapseWorkspace = "SynapseWorkspaceName",
    [string]$ResourceGroup = "ResourceGroupName",
    [string]$Action = "start",
    [string]$NameFilter = "",
    [switch]$WhatIf
)



$plainText = $env:TEST_PWD_CERTIFICATE
$secureString = ConvertTo-SecureString $plainText -AsPlainText -Force

Connect-AzAccount -ServicePrincipal -ApplicationId "$ApplicationId" -TenantId "$TenantId" -CertificatePath "certificate.pfx" -CertificatePassword $secureString -WarningAction Ignore | Out-Null
Set-AzContext -Subscription "$SubscriptionName"

# Get the specified workspace
Write-Output ("Getting workspace {0} in resource group {1}" -f $synapseworkspace, $resourcegroup)
$workspace = Get-AzSynapseWorkspace -ResourceGroupName $resourcegroup -Name $synapseworkspace
if (-not($workspace)) { throw "Could not find workspace" }
Write-Output $workspace


# Get the list of triggers if the workspace 
Write-Output "Getting triggers"
$triggers = Get-AzSynapseTrigger -WorkspaceObject $workspace
if ($namefilter -ne '-') { $triggers = $triggers | Where-Object { $_.Name -match $namefilter } } # filter out names if the filter is specified
Write-Output ("Found {0} triggers" -f $triggers.Count)
if (-not($triggers)) { exit }


# Continue only if there are triggers to be found
if ($action -eq "stop") {
    # Stop the triggers
    Write-Output "Looping through triggers that are started ..."
    $startedtriggers = $triggers | Where-Object { $_.Properties.RuntimeState -eq "Started" }
    Write-Output ("Found {0} started triggers" -f $startedtriggers.Count)

    foreach ($t in $startedtriggers) {
        Write-Output ("Stopping {0} ..." -f $t.Name);
        try {
            $result = Stop-AzSynapseTrigger -WorkspaceName $synapseworkspace -Name $t.name -WhatIf:$WhatIf.IsPresent -PassThru
            Write-Output ("Result of stopping trigger {0}: {1}" -f $t.Name, $result)
        }
        catch {
            Write-Output ("Something went wrong with {0}" -f $t.Name)
            Write-Output $_
        }
    }
}


if ($triggers.Count -gt 0) {
    if ($action -eq "start") {
        # Start triggers
        Write-Output "Looping through triggers that are stopped ..."
        $stoppedtriggers = $triggers | Where-Object { $_.Properties.RuntimeState -eq "Stopped" }
        Write-Output ("Found {0} stopped triggers" -f $stoppedtriggers.Count)

        foreach ($t in $stoppedtriggers) {
            Write-Output ("Starting {0} ..." -f $t.Name);
            try {
                $result = Start-AzSynapseTrigger -WorkspaceName $synapseworkspace -Name $t.name -WhatIf:$WhatIf.IsPresent -PassThru
                Write-Output ("Result of starting trigger {0}: {1}" -f $t.Name, $result)
            }
            catch {
                Write-Output ("Something went wrong with {0}" -f $t.Name)
                Write-Output $_
            }
        }
    }

    Write-Output "... done"
}

Start trigger PowerShell script (technically its same script but we have saved with 2 different names)

# Input parameters
param(
    [string]$SynapseWorkspace = "SynapseWorkspaceName",
    [string]$ResourceGroup = "ResourceGroupName",
    [string]$Action = "start",
    [string]$NameFilter = "",
    [switch]$WhatIf
)



$plainText = $env:TEST_PWD_CERTIFICATE
$secureString = ConvertTo-SecureString $plainText -AsPlainText -Force

Connect-AzAccount -ServicePrincipal -ApplicationId "$ApplicationId" -TenantId "$TenantId" -CertificatePath "certificate.pfx" -CertificatePassword $secureString -WarningAction Ignore | Out-Null
Set-AzContext -Subscription "$SubscriptionName"

# Get the specified workspace
Write-Output ("Getting workspace {0} in resource group {1}" -f $synapseworkspace, $resourcegroup)
$workspace = Get-AzSynapseWorkspace -ResourceGroupName $resourcegroup -Name $synapseworkspace
if (-not($workspace)) { throw "Could not find workspace" }
Write-Output $workspace

# Get the list of triggers if the workspace 
Write-Output "Getting triggers"
$triggers = Get-AzSynapseTrigger -WorkspaceObject $workspace
if ($namefilter -ne '-') { $triggers = $triggers | Where-Object { $_.Name -match $namefilter } } # filter out names if the filter is specified
Write-Output ("Found {0} triggers" -f $triggers.Count)
if (-not($triggers)) { exit }


# Continue only if there are triggers to be found
if ($action -eq "stop") {
    # Stop the triggers
    Write-Output "Looping through triggers that are started ..."
    $startedtriggers = $triggers | Where-Object { $_.Properties.RuntimeState -eq "Started" }
    Write-Output ("Found {0} started triggers" -f $startedtriggers.Count)

    foreach ($t in $startedtriggers) {
        Write-Output ("Stopping {0} ..." -f $t.Name);
        try {
            $result = Stop-AzSynapseTrigger -WorkspaceName $synapseworkspace -Name $t.name -WhatIf:$WhatIf.IsPresent -PassThru 
            Write-Output ("Result of stopping trigger {0}: {1}" -f $t.Name, $result)
        }
        catch {
            Write-Output ("Something went wrong with {0}" -f $t.Name)
            Write-Output $_
        }
    }
}


if ($triggers.Count -gt 0) {
    if ($action -eq "start") {
        # Start triggers
        Write-Output "Looping through triggers that are stopped ..."
        $stoppedtriggers = $triggers | Where-Object { $_.Properties.RuntimeState -eq "Stopped" }
        Write-Output ("Found {0} stopped triggers" -f $stoppedtriggers.Count)

        foreach ($t in $stoppedtriggers) {
            Write-Output ("Starting {0} ..." -f $t.Name);
            try {
                $result = Start-AzSynapseTrigger -WorkspaceName $synapseworkspace -Name $t.name -WhatIf:$WhatIf.IsPresent -PassThru
                Write-Output ("Result of starting trigger {0}: {1}" -f $t.Name, $result)
            }
            catch {
                Write-Output ("Something went wrong with {0}" -f $t.Name)
                Write-Output $_
            }
        }
    }

    Write-Output "... done"
}

GitHub action workflow file.


name: AzureSynapse_Worflow_deployment_with_trigger_start_stop

on:
  push:

  workflow_dispatch:

  ############### Start: Code for BUILD Job #################

jobs:
  Build:
    name: BUILD Job
    runs-on: ubuntu-latest
    steps:
      - name: Checkout
        uses: actions/checkout@v2.4.2
      - name: Upload Build Artifacts
        uses: actions/upload-artifact@v3.1.0
        with:
          path: ${{ github.workspace }}/FilesPath/
          name: drop

  ############# End: Code for BUILD Job ####################

  ############################### Start: Azure Login using SPN certificate in PEM format ACC ############################
  
  Azure-Login:
    needs: [Build]
    name: Azure-Login
    runs-on: ubuntu-latest
    environment:
      name: UAT

    steps:
      - name: Step 1. Checkout
        uses: actions/checkout@v2.4.2

      - name: Install the powershell dependencies
        run: |
          Install-Module -Name Az -Scope CurrentUser -Repository PSGallery -Force -AllowClobber
          Import-Module -Name Az
          Import-Module Az.Synapse
          Get-Module Universal -ListAvailable
        shell: pwsh

      - name: Step 2. retrieve SPN Certificate and private key - PEM file - from github secrets and save to file
        env:
          spn_certificate: ${{secrets.AZURE_SPN_CERTIFICATE_ACC}}
        run: |
          touch ./temp_cert_and_private_key_acc.pem
          echo "$spn_certificate" >> ./temp_cert_and_private_key_acc.pem
      - name: Step 3. use Certificate based SPN to login to azure
        uses: azure/CLI@v1
        with:
          azcliversion: 2.36.0
          inlineScript: |
            az login --service-principal -u ${{secrets.AZURE_SPN_APP_ID_ACC}} -p temp_cert_and_private_key_acc.pem --tenant ${{secrets.AZURE_SPN_TENANT}}

  ############################### End: Login using SPN certificate in PEM format ACC #################################################################################

  ############################### Synapse Trigger stop #####################################################################
  Synapse-Trigger-Stop:
    needs: [Azure-Login]
    name: Synapse Trigger Stop
    runs-on: ubuntu-latest
    environment:
      name: UAT

    steps:
      - name: Install the powershell dependencies
        shell: pwsh
        run: |
          Install-Module -Name Az.Synapse -AllowClobber -Scope CurrentUser -Force
          Import-Module Az.Synapse

      - name: Checkout code
        uses: actions/checkout@v2

      - name: Run the script as Adminstrator
        shell: pwsh
        run: |
          $scriptPath = "scripts/trigger_stop_new.ps1"
          pwsh -ExecutionPolicy Bypass -File $scriptPath

    
      ############################### Start: Deployment to UAT environment ################################################################
  DeploySynapse:
    needs: [Synapse-Trigger-Stop]
    name: DeploySynapse
    runs-on: ubuntu-latest
    environment:
      name: UAT
    steps:
      - name: Step 4. Deployment of Synapse workspace artifacts to UAT workspace
        uses: Azure/synapse-workspace-deployment@V1.7.0
        with:
          
          TargetWorkspaceName: "$TargetWorkspaceName"
          ArtifactsFolder: ${{ github.workspace }}/FilesPath/

          OverrideArmParameters: "./parameters.yaml"
          environment: "Azure Public"
          resourceGroup: "$UATresourceGroup"
          clientId: ${{ secrets.AZURE_SPN_KEY_APPID_ACC }}
          clientSecret: ${{ secrets.AZURE_SPN_KEY_SECRET_ACC }}
          subscriptionId: ${{ secrets.SUBID_ACC }}
          tenantId: ${{ secrets.TENANTID }}         
          operation: "validateDeploy"
  ############################# End: Start Triggers ###################################
  Synapse-Trigger-Start:
    needs: [DeploySynapse]
    name: Synapse Trigger Start
    runs-on: ubuntu-latest
    environment:
      name: UAT

    steps:
      - name: Install the powershell dependencies
        shell: pwsh
        run: |
          Install-Module -Name Az.Synapse -AllowClobber -Scope CurrentUser -Force
          Import-Module Az.Synapse

      - name: Checkout code
        uses: actions/checkout@v2

      - uses: actions/checkout@v3
      - name: Run the script as Adminstrator
        shell: pwsh
        run: |
          $scriptPath = "scripts/trigger_start_new.ps1"
          pwsh -ExecutionPolicy Bypass -File $scriptPath

Azure Synapse Analytics
Adf Trigger

--

--

Prashanth Kumar

Written by Prashanth Kumar

123 Followers

IT professional with 20+ years experience, feel free to contact me at: Prashanth.kumar.ms@outlook.com

Help

Status

About

Careers

Press

Blog

Privacy

Terms

Text to speech

Teams