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.

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:

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:

• [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="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.

<SchemaVersion>3.1</SchemaVersion>
<DSP>Microsoft.Data.Tools.Schema.Sql.SqlServerlessDatabaseSchemaProvider</DSP>

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.

<TargetFrameworkVersion>v4.8</TargetFrameworkVersion>

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.

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.