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.
[…] Kevin Chant uses a deployment tool to install a deployment tool for his deployment tools: […]
agentpool – The name of the agent pool you want to use (ideally a self-hosted one with latest sqlpackage installed). Otherwise you must put additional logic in this pipeline to deploy latest version of sqlpackage onto the agent
How to implement this logic in YAML?
Hi, I stated which agent to use and the additional task in the below YAML pipeline:
However, you can do more complex logic if needed. One option is to use expressions:
[…] I show a couple of examples on how to do this in a post I wrote, Which showed how to install SqlPackage to work with dacpacs for serverless SQL Pools. […]