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

Reading Time: 9 minutes

In this post I want to show how a GitHub CI/CD experience for Azure Synapse Link for SQL Server 2022 can look. Which uses GitHub Actions. Including how to automatically stop and start it in the pipeline.

In my last post I showed a complete CI/CD experience for Azure Synapse Link for SQL Server 2022 using Azure DevOps.

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

Plus, I cover how you can use a pull request as a trigger for the first time ever in a post.

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 the video for the March 2023 edition of the Azure Synapse Analytics and MVP series.

Repository for GitHub Actions template

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 GitHub. It is called GitHub-AzureSynapseLinkforSQLServer2022 and contains a database project based on the WideWorldImporters sample database.

All the magic in this post can be found in the azure-synapse-link-with-automated-link-start.yml file within that repository. Which is in the /.github/workflows/ subfolder.

One a side note, for self-hosted runners I recommend enabling long file names as discussed in this thread.

Prerequisites for a GitHub 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 have something setup to authenticate in Azure for the Azure Login action to work.

For this example, I created a service principal based on the below code.

az ad sp create-for-rbac --name "{Name you want to give service principal} " --role contributor --scopes /subscriptions/{your Azure Subscription ID}/resourceGroups/{Resource Group you want access to} --sdk-auth

Afterwards, you must add the role you add for your Azure credentials to the correct Synapse RBAC role. So that the PowerShell commands in GitHub Actions 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 "{Object ID of service principal}"

In reality, there are a few different ways to get the Object Id value for your service principal in Azure Active Directory. For example, you can go to the Azure Portal and look for the service principal you created in Azure Active Directory.

Alternatively, you can force the issue by trying to run a PowerShell command against your workspace. You can find out more about creating the service principal and adding the secret in the Azure Login action repository.

GitHub repository for Azure Synapse Link

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. 

However, I have modified it by adding a YAML file for the GitHub Actions workflow.

I synchronized this repository with GitHub. 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 GitHub Actions.

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

• Creating database project for Azure Synapse dedicated SQL Pool
• Synchronize to GitHub instead to create dacpac using GitHub Actions

Creating the GitHub Actions workflow for Azure Synapse Link

To help with some jargon here, you can think of a GitHub Actions workflow as the definition of your pipeline. It is based on YAML. You can read about the YAML syntax in detail in the GitHub guide about the YAML syntax for workflows.

First, I created the initial workflow in the /.github/workflows subfolder and added a trigger. So that the workflow started when a pull request had completed for the main branch.

on:
  pull_request:
    types:
      - closed
    branches:
      - main

Stopping Azure Synapse Link for SQL Server 2022

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

jobs:

  # Job to stop Azure Synapse Link for SQL Server 2022
  StopLink:
    # Easier to use Github-hosted runner to stop Azure Synapse
    runs-on: self-hosted
    # Alternatively can use a GitHub-Hosted agent instead of your own runner
    # runs-on: windows-latest

    # Steps represent a sequence of tasks that will be executed as part of the job
    steps:
      # Run migration-based scripts
      - name: Install Azure Synapse Module
        run: 'Install-Module -Name Az.Synapse -Force'

      - name: Login via Az module
        uses: azure/login@v1
        with:
          creds: ${{secrets.AZURE_CREDENTIALS}}
          enable-AzPSSession: true 

      - name: Stop Azure Synapse Link
        uses: azure/powershell@v1
        with:
          inlineScript: |
            Stop-AzSynapseLinkConnection -WorkspaceName ${{secrets.WORKSPACENAME}} -Name ${{secrets.LINKNAME}}
          
            $status = Get-AzSynapseLinkConnectionDetailedStatus -WorkspaceName ${{secrets.WORKSPACENAME}} -Name ${{secrets.LINKNAME}}
            do{
              $status = Get-AzSynapseLinkConnectionDetailedStatus -WorkspaceName ${{secrets.WORKSPACENAME}} -Name ${{secrets.LINKNAME}}
              Start-Sleep -Seconds 20
            } Until ($status.Status -eq "Stopped")
          azPSVersion: "latest"

Basically, the above stage does the below steps:

1. Installs the Az.Synapse PowerShell module.
2. Connects up to your Azure resource using the service principal you provided.
3. Issues the Stop-AzSynapseLinkConnection command using the Azure PowerShell Action.
4. 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 job to build (create) a SQL Server 2022 dacpac and publish it within GitHub.

# Job to build SQL Server 2022 dacpac
  CreateDacpac:
  # Set the dependency for job to stop the link
    needs: StopLink
    # Recommend use self-hosted runner
    runs-on: self-hosted

    steps:
      # Checks-out your repository under $GITHUB_WORKSPACE, so your job can access it
      # Note that I am using latest version of action
      - uses: actions/checkout@v3 
      
      # Find latest version of msbuild on runner
      - name: Add msbuild to PATH
        uses: microsoft/setup-msbuild@v1.1

      # create dacpac using MSBUILD
      - name: Build Database project
        run: |
          msbuild.exe WideWorldImportersDBProject.sqlproj /p:Configuration=Release
      # Publish artifact
      - uses: actions/upload-artifact@v3.0.0
        with:
          name: SQLDB
          path: ${{ github.workspace }}/bin/Release/ 

Deploying SQL Server dacpac to SQL Server 2022

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

# Deploy dacpac job
  DeployDacpac:
    # Set the dependency for the build job
    needs: CreateDacpac
    # Easier to use Github-hosted runner if updating in GitHub
    runs-on: self-hosted

    # Steps to deploy the updates to Azure SQL Database
    # To keep this example simple, the Firewall settings in the logical SQL Server allows access from Azure services and resources
    # An alternative is to use the Azure Login GitHub Action https://github.com/marketplace/actions/azure-login
    steps:
      - name: download artifact containing dacpac
        # Dowloads Data artifact to dacpac folder
        uses: actions/download-artifact@v3.0.0
        with:
          name: SQLDB

      # Installs dacpac
      # For this to work you have a secret specified, default in my repo are blank
      # Another called AZURESQLDB_CONNECTION_STRING which contains connection string to your Azure SQL Database
      - name: SQL DB Deploy
        uses: Azure/sql-action@v1.3
        with:
          # The SQL Server name
          server-name: '${{ secrets.SQL_SERVER }}'
          # The connection string, including authentication information, for the SQL Database.
          connection-string: '${{ secrets.SQL_CONNECTION_STRING }}'
          # Name of the dacpac file in the artifact
          dacpac-package: 'WideWorldImportersDBProject.dacpac'
          # Added argument to allow data loss for testing purposes
          # You might want to change this
          arguments: '/p:BlockOnPossibleDataLoss=false'

In reality, you can extend this stage in many ways. For example, you can add additional sqlpackage arguments to the sql-action GitHub Action.

Note that I am using version 1.3 of the action instead of the newer version 2. Due to fact that I had issues with version 2 and SQL Server instances on virtual machines.

Stopping Azure Synapse Link for SQL Server 2022

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

# Job to start Azure Synapse Link for SQL Server 2022
  StartLink:
    # Set the dependency for the deployment job
    needs: DeployDacpac
    # Easier to use Github-hosted runner to stop Azure Synapse
    runs-on: self-hosted
    # Alternatively, can use a GitHub-Hosted agent instead of your own runner
    # runs-on: windows-latest

    # Steps represent a sequence of tasks that will be executed as part of the job
    steps:
      # Run migration-based scripts
      - name: Install Azure Synapse Module
        run: 'Install-Module -Name Az.Synapse -Force'

      - name: Login via Az module
        uses: azure/login@v1
        with:
          creds: ${{secrets.AZURE_CREDENTIALS}}
          enable-AzPSSession: true 

      - name: Stop Azure Synapse Link
        uses: azure/powershell@v1
        with:
          inlineScript: |
            Start-AzSynapseLinkConnection -WorkspaceName ${{secrets.WORKSPACENAME}} -Name ${{secrets.LINKNAME}}

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 the GitHub Actions workflow for Azure Synapse Link

After creating the workflow I tested that it worked. By checking that it ran when a pull request to the main branch had completed. Just like I specified in the trigger.

To test that the workflow worked fully I opened Azure Data Studio on my laptop. I created a branch called new-feature and 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 published my changes to GitHub.

When I went back to the repository in GitHub, I was invited to compare the changes in the new branch and create a pull request.

I clicked on the ‘Compare & pull request’ and created a new pull request. So that the changes in the new branch would be pulled into the main branch.

After clicking on ‘Create pull request’ I waited for the checks to complete before clicking the ‘Merge pull request’ button.

I clicked on the ‘Merge pull request’ button and then clicked the ‘Confirm merge’ button. Once the merge had completed I clicked on the ‘Delete branch’ button to delete the new-feature branch.

Once I had closed the pull request and the change in the new-feature branch had been merged I clicked on the Actions tab. As you can see below, the workflow was triggered by the pull request and completed successfully.

Checking Azure Synapse Link for SQL Server 2022 in Synapse Studio

My final test was to check the status of the link in Synapse Studio. I did this by going into Synapse Studio, 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 GitHub CI/CD experience for Azure Synapse Link

I really hope that this complete GitHub CI/CD experience for Azure Synapse Link for SQL Server 2022 helps some of you. Along with the template that is available to everybody in GitHub.

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

You can extend this workflow as well. For example, you can change the trigger to fit your needs. However, I wanted to keep this template as basic as possible and at the same time introduce the pull request trigger.

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