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.
[…] Kevin Chant shows how to use Azure DevOps to create a dacpac for an Azure Synapse Analytics dedicate…: […]
[…] Create a dacpac for an Azure Synapse Analytics dedicated SQL Pool using Azure DevOpsThis post demonstrates how to create a dapac for a dedicated SQL pool using Azure Pipelines. […]
[…] Create a dacpac for an Azure Synapse Analytics dedicated SQL Pool using Azure DevOpsThis post demonstrates how to create a dapac for a dedicated SQL pool using Azure Pipelines. […]
[…] then did a follow up in another post about how to create a dacpac for an Azure Synapse Analytics dedicated SQL Pool using Azure DevOps. With this in mind, I thought it was only fair that I showed how to do the same thing using GitHub […]
[…] Create a dacpac for an Azure Synapse Analytics dedicated SQL Pool using Azure DevOps This post demonstrates how to create a dapac for a dedicated SQL pool using Azure Pipelines. […]
[…] As some of you know, I have done a fair few posts and sessions about using Azure DevOps for Data Platform deployments. For example, my post about how to create a dacpac for an Azure Synapse Analytics dedicated SQL Pool using Azure DevOps. […]
[…] related to my post about how you can create a dacpac for an Azure Synapse Analytics dedicated SQL pool using Azure DevOps. Since they are both based in the same […]
[…] For example, say you were working with a new YAML pipeline in Azure Pipelines. Similar to the one that I covered in a previous post about creating a dacpac for an Azure Synapse Analytics dedicated SQL Pool using Azure DevOps. […]
[…] my last post on how to database project for use with Azure Synapse Link for SQL Server 2022 I showed how to create a database project for use with Azure Synapse Link for SQL Server 2022. […]
[…] 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. […]
[…] 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. […]
[…] From there, initialize the repository and synchronize it with one in Azure DevOps. I covered how you can do this in detail in the below sections of a previous post about how to create a dacpac for an Azure Synapse Analytics dedicated SQL Pool using Azure DevOps. […]
[…] From there, initialize the repository and synchronize it with one in Azure DevOps. I covered how you can do this in detail in the below sections of a previous post about how to create a dacpac for an Azure Synapse Analytics dedicated SQL Pool using Azure DevOps. […]
[…] covered how you can do this in detail in a previous post about how to create a dacpac for an Azure Synapse Analytics dedicated SQL Pool using Azure DevOps. You can find the relevant details in the below sections of that […]