A complete CI/CD experience for Azure Synapse Link for SQL Server 2022

Reading Time: 7 minutes

In this post I want to show you a complete CI/CD experience for Azure Synapse Link for SQL Server 2022 tables. Which uses a YAML Pipeline in Azure DevOps. Including how to automatically stop and start it in the pipeline.

In a previous post I showed how an easier way to perform CI/CD for Azure Synapse Link for SQL Server 2022. Where you only need to stop the link, update the SQL Server database and afterwards start the link again.

However, the best CI/CD solutions are the ones where you do not do any manual work at all. This includes stopping and starting the link.

With this in mind, in this post I show a complete CI/CD experience for Azure Synapse Link for SQL Server 2022 in Azure Pipelines. Which includes automatically stopping the link before the database update and starting it again after the update has completed.

By the end of this post, you will learn how to implement this using a YAML pipeline in Azure DevOps.

You can find a recap about Azure Synapse Link for SQL Server 2022 in my post about my initial tests for Azure Synapse Link for SQL Server 2022. Which is now generally available.

CI/CD experience for Azure Synapse Link video

You can see the contents of this post in action in the video for the March 2023 edition of the Azure Synapse Analytics and MVP series.

GitHub repository available

Before I go into too much detail, I have made a public repository available in GitHub that you can use as a template to do this yourself in Azure DevOps.

It is called AzureDevOps-AzureSynapseLinkforSQLServer2022 and contains a database project based on the WideWorldImporters sample database. You can click on its name in this post to go straight to the site.

All the magic in this post can be found in the azure-pipelines-sql-only-with-automated-link-start.yml file within that repository. Which is in the AzureDevOpsTemplates folder.

Feel free to clone (copy) this repository for your own use.

Prerequisites for a complete CI/CD experience

In order for this pipeline to work you must already have Azure Synapse Link for SQL Server 2022 already configured. Like I covered in my post about my initial tests for Azure Synapse Link for SQL Server 2022.

For the benefit of testing, my link only uses the ‘Sales.Orders’ and ‘Sales.OrderLines’ tables.

In addition, you must select the ‘Drop and recreate table on target’ in your link settings in Synapse Studio, as below. So that the tables are recreated in your dedicated SQL Pool every time the link is restarted.

For the below pipeline to work you must add your Azure DevOps organization to the correct Synapse RBAC role. So that the PowerShell commands in the YAML pipeline will work inside your Synapse workspace.

I added the Object Id for my Azure DevOps organization to the Synapse Compute Operator role within my Azure Synapse workspace by running the below PowerShell code in Cloud Shell.

New-AzSynapseRoleAssignment -WorkspaceName {YOUR SYNAPSE WORKSPACE} -RoleDefinitionName "Synapse Compute Operator" -ObjectId "{AZURE DEVOPS ORGANIZATION OBJECTID"

In reality, there are a few different ways to get the Object Id value for your Azure DevOps organization. For example, you can find your Azure DevOps organization in Azure Active Directory.

Alternatively, you can force the issue by running a PowerShell command against your workspace. Which will return an error like the one below. Where the principal returned is the Object Id value you need.

The principal 'xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx' does not have the required Synapse RBAC permission to perform this action. Required permission: Action: Microsoft.Synapse/workspaces/linkConnections/useCompute/action, Scope: workspaces/{YOUR WORKSPACE NAME}.

In addition, you must create a service connection in your Azure DevOps project.

Plus, you must setup the relevant variables mentioned in the pipeline to at least one variable group. In my pipeline I specify two groups for SQL Server and Azure Synapse variables.

For further details, you can read the ‘Before building the pipeline’ section in my update both ends of Azure Synapse Link for SQL Server 2022 using Azure DevOps post.

Azure DevOps repository

For this post I use the same database project that I created in previous post. Where I showed how to database project for use with Azure Synapse Link for SQL Server 2022. 

Which I synchronized with Azure Repos in Azure DevOps. I explained how to do this in detail in a post I wrote about creating a dacpac for an Azure Synapse Analytics dedicated SQL Pool using Azure DevOps.

You can do this yourself by following the below two sections in that post:

• Create Git repository for SQL Pool in Azure DevOps
• Synchronize Azure Synapse SQL Pool repository to Azure DevOps

Creating the YAML pipeline

First, I created a YAML pipeline based on the database project in Azure Repos. I covered how to do this in my post on how to connect a Database Project in Azure Repos to Azure Pipelines.

Once done, I made sure that the variable groups were specified in the YAML pipeline and that it ran when the main branch was updated.

variables:
- group: SQLServer2022
- group: SQLLinkDedicatedSQLPool

# Take note that if this is added as a pipeline any updates of main branch triggers it
trigger:
- main

Stopping Azure Synapse Link for SQL Server 2022

I then added the below stage to stop Azure Synapse Link for SQL Server 2022.

- stage: StopLink
  displayName: 'Stop Link'

  jobs:
  - job: 'StopLink'
    displayName: 'Stop Link'

    steps:

    - task: PowerShell@2
      displayName: 'Install Az.Synapse PoSh Module'
      inputs:
        targetType: 'inline'
        script: 'Install-Module -Name Az.Synapse -Force'

    - task: AzurePowerShell@5
      displayName: 'Stop Link'
      inputs:
        azureSubscription: '$(AzureSubscription)'
        ScriptType: 'InlineScript'
        Inline: |
          Stop-AzSynapseLinkConnection -WorkspaceName $(WorkspaceName) -Name $(LinkName)
          
          $status = Get-AzSynapseLinkConnectionDetailedStatus -WorkspaceName $(WorkspaceName) -Name $(LinkName)

          do{
            $status = Get-AzSynapseLinkConnectionDetailedStatus -WorkspaceName $(WorkspaceName) -Name $(LinkName)

            Start-Sleep -Seconds 20
          } Until ($status.Status -eq "Stopped")
        azurePowerShellVersion: 'LatestVersion'

Basically, the above stage does the below steps:

1. Installs the Az.Synapse PowerShell module.
2. Issues the Stop-AzSynapseLinkConnection command.
3. Checks every twenty seconds if the link has stopped yet before the pipeline can continue.

In reality, you can change the value from twenty seconds to whatever value you want. By changing the value for the Start-Sleep command above.

Building the SQL Server dacpac

I then added the below stage to build (create) a SQL Server 2022 dacpac and publish it within Azure DevOps.

- stage: CreateSQL2022dacpac
  displayName: 'Build SQL dacpac'
      
  jobs:
  - job: 'BuildSQL2022dacpac'
    displayName: 'Build SQL Server 2022 dacpac'

    steps:

    - task: VSBuild@1
      displayName: 'Build Database Project'
      inputs:
        solution: WideWorldImportersDBProject.sqlproj
        configuration: '$(BuildConfiguration)'

    - task: PublishBuildArtifacts@1
      displayName: 'Publish Artifact: SQL 2022'
      inputs:
        PathtoPublish: 'bin\$(BuildConfiguration)'
        ArtifactName: $(SQLartifactname)

Deploying SQL Server dacpac

Once the dacpac had been created I added the below stage to deploy the updates to the database based on the contents of the dacpac.

- stage: DeploySQL2022dacpac
  displayName: 'Deploy SQL dacpac'
      
  jobs:
  - job: 'DeploySQLPooldacpac'
    displayName: 'Deploy SQL 2022 dacpac'

    steps:

    - task: DownloadBuildArtifacts@1
      displayName: 'Download Build Artifacts'
      inputs:
        artifactName: '$(SQLartifactname)'

    - task: SqlDacpacDeploymentOnMachineGroup@0
      displayName: 'SQL 2022 deploy'
      inputs:
        DacpacFile: '$(SQLdacpacfile)'
        DatabaseName: '$(database)'
        AuthScheme: sqlServerAuthentication
        SqlUsername: '$(sqluser)'
        SqlPassword: '$(sqlpw)'
        AdditionalArguments: '/p:BlockOnPossibleDataLoss=false'

In reality, you can extend this stage in many ways. For example, you can add additional arguments to the deployment task.

Stopping Azure Synapse Link for SQL Server 2022

Finally, I added the below stage so that when the deployment has finished the pipeline will start the link again.

- stage: StartLink
  displayName: 'Start Link'
      
  jobs:
  - job: 'StartLink'
    displayName: 'Start Link'

    steps:

    - task: PowerShell@2
      displayName: 'Install Az.Synapse PoSh Module'
      inputs:
        targetType: 'inline'
        script: 'Install-Module -Name Az.Synapse -Force'

    - task: AzurePowerShell@5
      displayName: 'Start Link'
      inputs:
        azureSubscription: '$(AzureSubscription)'
        ScriptType: 'InlineScript'
        Inline: |
          Start-AzSynapseLinkConnection -WorkspaceName $(WorkspaceName) -Name $(LinkName)
          
          $status = Get-AzSynapseLinkConnectionDetailedStatus -WorkspaceName $(WorkspaceName) -Name $(LinkName)

          do{
            $status = Get-AzSynapseLinkConnectionDetailedStatus -WorkspaceName $(WorkspaceName) -Name $(LinkName)

            Start-Sleep -Seconds 20
          } Until ($status.Status -eq "Running")
        azurePowerShellVersion: 'LatestVersion'

It contains similar logic to the first stage. With the main differences being that it uses the Start-AzSynapseLinkConnection PowerShell cmdlet and checks if the status is “Running”.

You can change the value from twenty seconds to whatever value you want. By changing the Start-Sleep value.

Testing YAML pipeline worked

To test that the YAML pipeline worked fully I opened Azure Data Studio on my laptop. I then changed a column in the code for the OrderLines table in my local copy(clone) of the database project.

Afterwards, I committed my changes and synchronized my local Git repository with the one for the database project in Azure Repos.

My pipeline finished as expected. From there I check that the link was back up and running in Synapse Studio.

I than checked that the column change worked in the dedicated SQL Pool. In reality, there are a few ways you can check this.

However, I wanted to show how the database looks in Synapse Studio in this post. You can navigate to the dedicated SQL Pool ‘Data’ hub in Synapse Studio as below. Which I covered in a previous post about Synapse Studio.

My final test was to check the status of the link in Synapse Studio. I did this by going to the Monitor Hub and then selecting “Link connections”.

I strongly recommend that you do this check and make sure that there is a green check box there. Otherwise, you might think the link is OK because it has started when it in fact has issues.

Final words about a complete CI/CD experience for Azure Synapse Link

I really hope that this complete CI/CD experience for Azure Synapse Link for SQL Server 2022 helps some of you. Along with the template that I created.

Because it allows you to implement a fully automated process in Azure DevOps. Without anybody manually stopping or starting the link.

You extend this pipeline as well. For example, you can add a stage to do unit tests once the dacpac has been created. However, I wanted to keep this template as basic as possible.

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