Install SqlPackage to work with dacpacs for serverless SQL Pools

Reading Time: 4 minutes

In this post I want to cover how to install SqlPackage to work with dacpacs for serverless SQL Pools.

I decided to do this post after some feedback I received about SqlPackage after a series of posts about deploying dacpacs to serverless SQL Pools. For example, my post about deploying a dacpac to a serverless SQL pool.

Because in order to deploy dacpacs to serverless SQL Pools you must update SqlPackage.

Those who work with dedicated SQL Pools will find this useful as well. Due to the fact that the latest version of SqlPackage now supports the DW_COMPATIBILITY_LEVEL setting. Which allows you to work with Multi-Column Distribution (MCD).

With this in mind, I thought I better go through various ways to update SqlPackage if intending to use it to deploy dacpacs to serverless SQL Pools.

Install or upgrade SqlPackage locally

You must install or upgrade SqlPackage locally for the following scenarios when looking to use SqlPackage to extract from a serverless SQL Pool:

• You are extracting from a serverless SQL Pool and deploying the dacpacs manually.
• Your local machine is a self-hosted Azure Pipelines agent.
• Your local machine is a self-hosted GitHub Runner.

To save me re-inventing the wheel, Microsoft provides various ways to install SqlPackage on their page about how to download and install SqlPackage.

However, the simplest way to install it and to get it to work with either an Azure Pipelines agent or GitHub Runner locally is to issue the below command:

dotnet tool install -g microsoft.sqlpackage

Upgrade or install SqlPackage for Azure Pipeline deployments

When you are looking to deploy a dacpac using Azure Pipelines and you are using a remote Azure Pipelines agent you must install or upgrade SqlPackage on there.

To clarify, when I say remote Azure Pipelines agent, I mean either:

• A self-hosted agent running on another computer.
• A Microsoft-hosted agent

In reality, there are various ways you can do this.

For example, say I decided to change the classic editor pipeline that I used in my post about deploying a dacpac to a serverless SQL pool. So that it used a Microsoft-hosted agent instead.

First, I change the agent settings to use a Microsoft-hosted agent based on the latest image of Windows.

I then add a new PowerShell task called ‘Install SqlPackage’.

Which contains the below line of code to install SqlPackage as a dotnet tool. This makes it easy for me to re-use the existing code I created before in the other PowerShell task.

dotnet tool install -g microsoft.sqlpackage

Afterwards, I select ‘Save & queue’ and the extract completes and again I have an artifact that contains a dacpac.

To check all is well I download the dacpac and view the contents of the model.xml file inside it. To check that it has the same Database Schema Provider (DSP) that I mentioned in my homemade serverless SQL Pool database project post.

After confirming that is is the same one, I go over to the Releases feature in Azure Pipelines. First, I change my release to use a Microsoft-hosted agent using the same image.

From there, I create a new release. Which completes fine, as you can see below.

Installing SqlPackage to work with dacpacs in a YAML pipeline

To do the above in a YAML pipeline you can add the new task into an existing stage.

For example, to get it to work in my public AzureDevOps-SynpaseServerlessSQLPool-dacpac repository I can add the below code to my Build dacpac stage.

You can see this for yourself in the new workflow I created in the repository called serverlessSQLPool-sqlpackage-install-first.yml.

- task: PowerShell@2
        inputs:
          targetType: 'inline'
          script: |
            dotnet tool install -g microsoft.sqlpackage

Final words about installing SqlPackage to work with dacpacs for serverless SQL Pools

I hope this post about how to install SqlPackage to work with dacpacs for serverless SQL Pools helps some of you. As aware some of you found have found this an obstacle.

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