Deploying a dacpac to a serverless SQL pool

Reading Time: 5 minutes

In this post I want to cover deploying a dacpac to a serverless SQL pool using Azure DevOps. By showing you examples based on both classic pipelines and YAML pipelines.

Yes, you are reading that right. It is now possible to do achieve this with database projects thanks to a SqlPackage update.

You can download the SqlPackage update on the ‘Download and install SqlPackage‘ page. To download it onto an Azure Pipelines Agent during deployment you can read a newer post which covers how to install SqlPackage to work with dacpacs for serverless SQL Pools.

To clarify, a dacpac file is a special file that you can use to deploy database schema updates to SQL Server related databases using a state-based deployment. Plus, when I say serverless SQL pool I mean an Azure Synapse Analytics serverless SQL Pool.

Just to be clear, you can only deploy a dacpac that has been created especially for serverless SQL Pools. Because serverless SQL Pools only support a limited number of objects.

You can read more about this in detail in the Microsoft page about Transact-SQL features supported in Azure Synapse SQL.

As always, this post contains plenty of links. Plus, a link to a GitHub repository that you can use as a template for YAML pipelines in Azure DevOps.

SqlPackage update

A long requested feature is for there to be support to deploy the contents of a dacpac file to databases in serverless SQL Pools. So that state-based deployments can be done.

Which led to Johannes Vink (l/t) raising a GitHub issue about Synapse Serverless SQL Pool support.

It is now finally possible thanks to the latest SqlPackage update that was announced this week. You can read more about this and other updates in a post by Microsoft about updates to DacFx and Microsoft.Build.Sql.

As soon as I found out about the SqlPackage update I downloaded and tested it. I first ran the below code to extract the contents of a database in a serverless SQL Pool into a dacpac file.

SqlPackage /Action:Extract /TargetFile:sqlpackagetest.dacpac /p:VerifyExtraction=true /SourceServerName:"{MY SERVERLESS SQL POOL ENDPOINT}.sql.azuresynapse.net" /SourceDatabaseName:sqlpackagetest /SourceUser:{A SQL POOL LOGIN} /SourcePassword:{REDACTED P/W}

Which created a dacpac on my local machine. So far so good. From there, I ran the below code to publish the contents of the dacpac to another database in the serverless SQL Pool.

SqlPackage /Action:Publish /SourceFile:sqlpackagetest.dacpac /TargetConnectionString:"Server=tcp:{MY SERVERLESS SQL POOL ENDPOINT}.sql.azuresynapse.net;Initial Catalog=destinationdb;Persist Security Info=False;User ID={A SQL POOL LOGIN};Password={REDACTED P/W}

Which worked, as you can see below.

Deploying a dacpac to a serverless SQL pool using Azure DevOps

In reality, you can figure out how to do the above yourselves by reading the SqlPackage documentation about the SqlPackage extract parameters and publish parameters.

Which is why I decided to take this post a bit further and cover how to deploy a dacpac to a serverless SQL pool using Azure DevOps in this post.

Plus, I wanted to share a link to a new GitHub repository called AzureDevOps-SynpaseServerlessSQLPool-dacpac.

Which is available for anybody to download and use as a template to deploy a dacpac to a serverless SQL pool using Azure DevOps. If you find it useful please give it a star in GitHub.

Note that for both of the methods that I show below I am using a self-hosted agent that has the latest version of SqlPackage installed. In addition, they both use the same variable group.

I will show how to do this using the Classic Editor and Releases feature in Azure Pipelines first. Which is the GUI-based way to do this.

Deploying a dacpac to a serverless SQL Pool using classic pipelines

I first use the Classic Editor in Azure Pipelines to extract the schema from a database in a serverless SQL Pool.

As you can see below, it first uses a PowerShell task to extract the schema into a dacpac file. From there it publishes the dacpac back into Azure DevOps.

For those wondering, the above PowerShell task uses the below code.

# PowerShell to extract contents of a database in a serverless SQLPool into a dacpac file
            
SqlPackage /Action:Extract /TargetFile:$(Build.ArtifactStagingDirectory)\$(TargetFile) /p:VerifyExtraction=true /SourceServerName:$(SQLPoolEndPoint) /SourceDatabaseName:$(SourceDB) /SourceUser:$(SQLPooluser) /SourcePassword:$(SQLPoolpw)

Once the pipeline had completed, I created the below Release in Azure Pipelines. With the artifact being the artifact published by the Classic Editor.

In the stage to deploy the dacpac I created the below tasks. Which is where things get interesting,

As you can see, I used the Azure SQL Database deployment task to deploy the dacpac instead of using a PowerShell task. This is because this behind the scenes this task uses SqlPackage.

As you can see below, this release succeeded.

Deploying a dacpac to a serverless SQL Pool using a YAML pipeline

Now I want to cover how to deploy this using a YAML pipeline.

You can see the code shown below in the AzureDevOps-SynpaseServerlessSQLPool-dacpac repository. Which you can download and use as you see fit.

Basically, this is a code-based version of the above. I first extracted the contents of the database into a dacpac and then published the dacpac using the below code.

steps:
 - task: PowerShell@2
 inputs:
   targetType: 'inline'
   script: |
       # PowerShell to extract contents of a database in a serverless SQLPool into a dacpac file
            
 SqlPackage /Action:Extract /TargetFile:$(Build.ArtifactStagingDirectory)\$(TargetFile) /p:VerifyExtraction=true /SourceServerName:$(SQLPoolEndPoint) /SourceDatabaseName:$(SourceDB) /SourceUser:$(SQLPooluser) /SourcePassword:$(SQLPoolpw)

 - task: PublishBuildArtifacts@1
 displayName: 'Publishes dacpac as an artifact'
  # Publishes the dacpac as part of an artifact within Azure DevOps
        inputs:
          PathtoPublish: '$(Build.ArtifactStagingDirectory)'
          ArtifactName: $(SQLPoolartifactname)
          publishLocation: 'Container'

Afterwards, I deployed the contents of the dacpac using the below code.

 jobs:
  - deployment: 'SQLPool'
  displayName: 'Serverless SQL Pool'
  environment: Production

  pool: 
  name: $(agentpool)

  strategy:
   runOnce:
      deploy:
       steps:
          - task: DownloadBuildArtifacts@0
          displayName: 'Download Artifacts'
          inputs:
          buildType: 'current'
           downloadType: 'specific'
           artifactName:  '$(SQLPoolartifactname)'
           downloadPath: '$(System.ArtifactsDirectory)'

          - task: SqlAzureDacpacDeployment@1
          displayName: 'Install DACPAC on serverless SQL Pool'
          inputs:
           azureSubscription: $(AzureSubscription)
           AuthenticationType: 'server'
           ServerName: $(SQLPoolEndPoint)
           DatabaseName: '$(DestinationDB)'
           SqlUsername: '$(SQLPooluser)'
           SqlPassword: '$(SQLPoolpw)'
           deployType: 'DacpacTask'
           DeploymentAction: 'Publish'
           DacpacFile: '$(System.ArtifactsDirectory)\$(SQLPoolartifactname)\$(Targetfile)'

One key point I want to highlight is that I am using a deployment job in the above code. This is so that I can specify an environment. Because in the real-world you might want to consider approving deployments to production.

As you can see below, the pipeline completed with an artifact published. Which contains the dacpac file.

Final words

I hope this post about deploying a dacpac to a serverless SQL pool helps some you set this up. In addition, I hope the same goes for the AzureDevOps-SynpaseServerlessSQLPool-dacpac that is now available.

If you prefer For migration-based deployments you can still use the method I covered in my post about CI/CD for serverless SQL Pools.

Since publishing this post I have also shared how to deploy a dacpac to a serverless SQL pool using GitHub Actions. Plus, how you can create a database project which you can use to create dacpac files in a post about my homemade serverless SQL Pool database project.

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