Skip to content

Homemade serverless SQL Pool Database Project

Reading Time: 5 minutes

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.

GitHub repository available
GitHub repository available

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 files 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 browse the files and work with them natively. Like in the below example that uses Explorer in Azure Data Studio:

Working with the SQL files in Azure Data Studio
Working with the SQL files in Azure Data Studio

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 ‘’.

I then extracted the zip file which left me with the below four files:

  • [Content_Types].xml
  • DacMetadata.xml
  • model.xml
  • Origin.xml

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="">

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.Sqlfunctionality 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.

dotnet build succeeded for homemade database project for serverless SQL Pool
dotnet build succeeded

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'
          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.

New objects in serverless SQL Pool database
New objects in serverless SQL Pool database

Final words

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.

Published inAzure DevOpsAzure Synapse Analytics


  1. Jesse Jesse

    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?

      • Jesse Jesse

        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!

        • Kevin Chant Kevin Chant

          Hi Jesse

          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.


  2. […] 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. […]

  3. Hi Kevin. Nice blog posts and great session on SQLBits.

    I tried making a new Database Project and change the provider to Microsoft.Data.Tools.Schema.Sql.SqlServerlessDatabaseSchemaProvider, but then I can’t load the project in Azure Data Studio. I installed the latest version of SqlPackage and also tried restarting my computer, but that didn’t help.

    This article don’t mention the new provider as valid:

    • Kevin Chant Kevin Chant

      Support for working with the projects is expected in Visual Studio and Azure Data Studio in the future. In the meantime, best option is to explore the files instead in one of these applications instead.

Leave a Reply

Your email address will not be published. Required fields are marked *