Create a dacpac for an Azure Synapse Analytics dedicated SQL Pool using Azure DevOps

Reading Time: 7 minutes

In this post I want to cover how you can create a dacpac for an Azure Synapse Analytics dedicated SQL Pool using Azure DevOps.

By the end of this post, you will know how to create a dacpac for a dedicated SQL Pool within Azure Pipelines for your CI/CD deployments. Plus, how you can synchronize a database project created in Azure Data Studio with a Git repository in Azure DevOps.

Please note that this post applies to dedicated SQL Pools. To learn one way you can do CI/CD for serverless SQL Pools read my other post on how to do CI/CD for serverless SQL pools using Azure DevOps.

In a previous post I covered how you can create a dacpac for an Azure Synapse Analytics dedicated SQL Pool using Azure Data Studio. In that post I stated that you could create a dacpac for the database project using Azure DevOps.

With this in mind, I will use the same database project that I created in that post.

Azure DevOps template

You can find a template that shows how to do the below in a GitHub repository that I have made public. It is called AzureDevOps-AzureSynapseSQLPool.

You can find a sample YAML file that you can use in Azure Pipelines in the subfolder AzureDevOpsTemplates.

Create Git repository for SQL Pool in Azure DevOps

To use the database project that I had created on my laptop in Azure DevOps I first needed to synchronize (copy) the database project I had created there.

First of all, I created a new Git repository within the Azure Repos service in Azure DevOps. Note, I decided not click the ‘Add a README’ option. So that I initialize the repository that I create using Azure Data Studio and synchronize it easier.

I left the Azure DevOps screen on the repository page and went back into the database project that I had created in Azure Data Studio.

In reality, there are many ways you can initialize the folder the database project is in so that it becomes a Git repository. However, I decided to do it in Azure Data Studio this time around. To show others how it can be done there.

I did this in Azure Data Studio by clicking on the source control icon and than the ‘Initialize Repository’ button.

Followed by choosing the relevant workspace folder.

From there I was able to do my initial commit to complete the process.

Synchronize Azure Synapse SQL Pool repository to Azure DevOps

Once I did this commit, I had a clone(copy) of the repository on my local computer. To synchronize it with Azure DevOps I had to add the Git repository I had created in Azure DevOps as a remote.

I did this right after the commit as you by selecting Add Remote in Azure Data Studio.

I then got asked to enter a repository URL. To get this I went back into the new Git repository in Azure DevOps. I then copied the clone URL to the clipboard.

Within Azure Data Studio I pasted the URL that I had copied. I was then asked to enter a name for the Remote repository. You can call this anything you want. To keep it simple I stuck with origin. Because a lot of you will see this used in other examples online.

Once that was done it was time to test if the sync would work. To save a bit of work Azure Data Studio has a handy option called ‘Sync’.

So, I clicked it to sync the repository that I had created on my laptop with the one in Azure DevOps. Clicking ‘OK’ when prompted afterwards to publish the main branch.

Afterwards, I went back into Azure DevOps and clicked refresh on the repository screen.

Create dacpac for Azure Synapse SQL Pool using Azure DevOps

To deploy to a dedicated SQL Pool, I had to create a new Pipeline. For the benefit of those who are new to Azure Pipelines you go down to ‘Pipelines’ in Azure DevOps. From there you select ‘Pipelines’ again.

Once there I clicked on the ‘New Pipeline’ button. When asked where my code was, I selected ‘Azure Repos Git’. Because that’s where my database project was, and I wanted to create a YAML based pipeline.

From there, I selected the Git repository and chose a ‘Starter pipeline’. However, this is not an introduction to YAML post. So, I will be skipping some parts here.

Note: If following along from here make sure you have your Azure Synapse Analytics dedicated SQL Pool created first.

Once done I created a stage which builds a dacpac and then publishes it as a dacpac. Which you can see below.

  jobs:
    - job: 'Builddacpac'
      displayName: 'Build SQL Pool dacpac'
      
      steps:
      - task: VSBuild@1
        displayName: 'Builds the dacpac'
        inputs:
          solution: Create dacpac from ADS test.sqlproj
          configuration: $(BuildConfiguration)

      - task: PublishBuildArtifacts@1
        displayName: 'Publishes dacpac as an artifact'
        # Publishes the dacpac as part of an artifact within Azure DevOps
        inputs:
          PathtoPublish: 'bin\$(BuildConfiguration)'
          ArtifactName: SQLPool
          publishLocation: 'Container'

In reality, this code might look familiar to those of you who have used Azure Pipelines to create dacpacs for SQL Server databases before.

Deploying the dacpac using Azure DevOps

Because I had already had a dedicated SQL Pool in place, I was then able to deploy the dacpac using the below code. Notice that I use the ‘Azure SQL Database deployment’ task to deploy to a dedicated SQL Pool.

  jobs:
    - job: 'DeployDedicated'
      displayName: 'Deploy to dedicated SQL Pool'

      steps:
      - task: DownloadBuildArtifacts@0
        inputs:
          buildType: 'current'
          downloadType: 'single'
          artifactName: 'SQLPool'
          downloadPath: '$(System.ArtifactsDirectory)'

      - task: SqlAzureDacpacDeployment@1
        displayName: 'Deploy DACPAC to dedicated SQL Pool'
        inputs:
          azureSubscription: '$(EntSub)'
          AuthenticationType: 'server'
          ServerName: '{YOUR SYNAPSE DEDICATED SQL ENDPOINT}'
          DatabaseName: 'DedicatedPool'
          SqlUsername: '$(SynapseUser)'
          SqlPassword: '$(Synapsepw)'
          deployType: 'DacpacTask'
          DeploymentAction: 'Publish'
          DacpacFile: '$(System.ArtifactsDirectory)\SQLPool\Create dacpac from ADS test.dacpac'
          AdditionalArguments: '/p:TreatVerificationErrorsAsWarnings=True'

You will also notice in the above code that I specify {YOUR SYNAPSE DEDICATED SQL ENDPOINT}. To clarify, this is the dedicated SQL endpoint that you can find in your Synapse workspace overview page. I showed the overview page before in a post where I did a five-minute crash course about Synapse Studio.

In reality, you should use variables outside your pipeline to hide values you don’t want others to see. I did this for this example by clicking the ‘Variables’ button in the top right-hand corner.

To make your code more readable you should also add variables within your YAML code as well. To avoid repeating yourself like I have done above.

For example, I could add a variable called SQLPool to save myself repeating it in the above pipeline and specify it throughout my code. Specifying the variable, the same way as I did my secure ones.

variables:
  SQLPool: SQLPool

In reality, there are various ways to use variables in your pipelines. Including variables containing secrets from Azure Key Vault. For other ways to add variables I recommend reading one of my other post which covers how to keep your Azure Synapse secrets secret in Azure DevOps.

Completed pipeline in Azure DevOps

I split the pipeline into two separate stages. You can view the completed pipeline below.

In reality, your final pipeline can be more complex than this. In addition, you might want to deploy the database project to a SQL Server instance first to perform unit testing on there. Like I discussed in another post about how you can do unit tests for an Azure Synapse Analytics dedicated SQL Pool.

Using GitHub Actions

In reality, you can use a similar technique to deploy to GitHub Actions as well. In fact, this Saturday myself and Sander show a GitHub Actions demo that deploys to dedicated SQL Pool.

You can see that demo at SQL Saturday Los Angeles this Saturday.

Azure Synapse workspaces

If you want more details about doing CI/CD with Synapse Workspaces, Craig Porteous will be covering it during the next Data Toboggan conference.

Somebody asked me a while back about doing CI/CD from a development workspace to a production one. So I know this will be of useful to some of you.

Final word

I hope this post about how you can create a dacpac for an Azure Synapse Analytics dedicated SQL Pool using Azure DevOps proves useful.

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