In this post I want to share how I created a homemade serverless SQL Pool database project.
Because I know people are keen to work this way right now. Mostly due to the comments I received when I covered how to deploy a dacpac to a serverless SQL pool.
By the end of this post you will know how I created a database project for it. Plus, how you can deploy the contents of the database project with Azure DevOps. I also share plenty of links along the way.
You can download a sample repository that I shared in GitHub that shows all of this. Which is called AzureDevOps-ServerlessSQLPool-HomemadeDBProject.
You can use this repository as a template to test both the database project itself and performing CI/CD for it using Azure DevOps.
To use it in Azure Pipelines you can select the YAML file I created in the AzureDevOpsTemplates folder. I covered how to do this in my post about how to connect a database project in Azure Repos to Azure Pipelines.
Building homemade serverless SQL Pool Database Project
I started building my homemade serverless SQL Pool database project by running a variation of the below command.
dotnet new sqlproj -n "ServerlessSQLPool-HomemadeProject"
Which allowed me to create a SDK-style database project using the ‘Microsoft.Build.Sql‘ .NET SDK for database projects. In other words, a newer way to create efficient database projects.
This command created a new folder which contained the database project. It contained a sqlproj file and a Readme file. Which are the objects that get created by default.
You can find out more about how to do this on the ‘Microsoft.Build.Sql.Templates‘ page in GitHub. Alternatively, you can view the details in nuget documentation for ‘MSBuild.Sdk.SqlProj‘.
Working with the Database Project in Azure Data Studio
Currently, this type of Database Project is not supported in Azure Data Studio or Visual Studio Code. However, you can still work the files natively as you can see below:
Changing the Database Project
Once I had created my database project I wanted to make some changes to make sure it was compatible with a serverless SQL Pool.
To do this, I first downloaded the dacpac file that I had initially created in my post on how to deploy a dacpac to a serverless SQL pool. I then renamed it from ‘sqlpackagetest.dacpac’ to ‘sqlpackagetest.zip’.
I then extracted the zip file which left me with the below four files:
When I opened up ‘model.xml’ file I saw the below line at the top.
<DataSchemaModel FileFormatVersion="1.2" SchemaVersion="3.1" DspName="Microsoft.Data.Tools.Schema.Sql.SqlServerlessDatabaseSchemaProvider" CollationLcid="1033" CollationCaseSensitive="False" xmlns="http://schemas.microsoft.com/sqlserver/dac/Serialization/2012/02">
Which means that the new Database Schema Provider (DSP) for serverless SQL Pools is currently ‘Microsoft.Data.Tools.Schema.Sql.SqlServerlessDatabaseSchemaProvider’. In addition, it currently uses SchemaVersion 3.1.
With this in mind, I added the below two lines to my sqlproj file.
I also added the latest .NET framework that I had installed on my laptop. Since I ran my Azure DevOps self-hosted agent on there.
It is also worth noting that my laptop has the latest version of SqlPackage installed. Since that supports the update I covered in the previous post.
If you are using a Microsoft-hosted agent you might want to consider installing these updates first as part of your pipeline.
Anyway, once the above was done I was ready to add files to the project.
Testing homemade serverless SQL Pool Database Project
If you read the ‘Microsoft.Build.Sql‘ functionality page you can see that it has a default globbing pattern for SQL files.
Which means that you can put SQL files anywhere you want to in the project. Without having to specify them in the sqlproj file like you had to in the traditional structure.
However, I did decide to keep the schema/object folder structure. To test my builds, I simply used the ‘dotnet build’ command.
To cut a long story short I tested creating various objects in the serverless SQL Pool. However, I encountered issues every time I tried to create an external file format. Which was a bit of an issue considering I wanted to create an external table.
Now, you can test possible workarounds yourself. For example, you can look to deploy external file formats and tables using a post-deployment script.
However, I was still able to create an external data source. Which meant that I could create views. So, one user case where this database project can be useful is if you want to perform CI/CD for views.
Deploying homemade serverless SQL Pool Database Project using Azure DevOps
Once I had my homemade serverless SQL Pool database project working I looked to deploy it using Azure DevOps. So I cloned (copied) the database project into Azure Repos.
I opted for a YAML pipeline so that I can share it easily with others. You can view the ‘AzureDevOps-serverless-sql-pool.yml‘ YAML file that I created in the GitHub repository that I have made available.
Anyway, I used a similar method that you can use to deploy a dedicated SQL Pool. Like in the ‘azure-pipelines-Single-SQL-Pool.yml‘ file that is in my public AzureDevOps-AzureSynapseSQLPool repository.
However, the main difference is that within my pipeline I built my new project using a .NET core task instead of Visual Studio build task. In other words, I created my dacpac file based on the contents of the database project in a different way. As you can see below.
- task: DotNetCoreCLI@2 displayName: 'Builds the dacpac using dotnet' inputs: command: 'build' projects: 'ServerlessSQLPool-HomemadeProject.sqlproj' arguments: '--configuration $(BuildConfiguration)'
I used the same deployment method, which deploys the contents of the dacpac.
Once the pipeline was finished I was able to see the new objects in the destination database.
I hope my personal project to create a homemade serverless SQL Pool Database Project was useful for some of you.
Because I wanted show how you can build an efficient one for serverless SQL Pools. Plus, I wanted to show how you can perform CI/CD with this database project using Azure DevOps.
Of course, if you have any comments or queries about this post feel free to reach out to me.
[…] Kevin Chant doesn’t let the lack of support for a product limit him: […]
[…] in GitHub repositories so that anybody can download them. Like I did for my last post about my homemade serverless SQL Pool Database Project. As you can see […]
Awesome work Kevin, and exactly what I’m trying to achieve. However, my sqlproj is a bit less homemade and should be able to open in visual studio as well. I’m thinking of adding a find and replace task in the pipeline to add the correct DSP. Could you imagine that working? And preferably building the dacpac with VSBuild instead of .Net. Why did you create your dacpac differently this time?
I can definitely imagine it working because I wrote a post on how to do it:
Be aware that you will still only be able to deploy compatible items.
I created my dacpac differently this time because the SDK-style method is supported by the dotnet build task in the pipeline, whereas the VS Build task did not support it.
Hi Kevin, thanks for your answer. My use-case is deploying a (views-only) database project to a synapse serverless sql pool and doing this in a ci/cd fashion to our different environments. This means not only replacing that DSP, but also the actual SQL scripts in the project (e.g. replacing data sources per environment). Ideally, I would like to do this after building the dacpac, but it doesn’t seem possible to execute a find and replace on a .dacpac file, does it?
Besides, I’m still having some issues with the DotNetCore build task. Maybe a bit of a strange question, but on the agent pool, do you have to install SS Data Tools separately for .Net? Somewhere on your github I noticed that in the .sqlproj you use a parameter $NetCoreTargetPath to find the path to import Microsoft.Data.Tools.Schema.SqlTasks.targets. This doesn’t work for me, and I haven’t figured out yet how to get the same done as with VSBuild.
Thanks again, very interested in how you solved this issue with deploying to a synapse serverless endpoint!
I am running a self-hosted Azure Pipelines agent locally. In addition, I have the latest versions of the .NET framework and SqlPackage installed.
I have updated my post to reflect this. I hope it helps.
[…] You can read more about this in detail in the Microsoft page about Transact-SQL features supported in Azure Synapse SQL. Plus, I show how you can create your own one in a post about my homemade serverless SQL Pool Database Project. […]
[…] Since I covered how I had created one for my homemade serverless SQL Pool Database Project. […]
[…] For example, one created with the method that I show in this post. Alternatively, one based on a custom database project like in one of my previous posts. […]