Azure DevOps with Azure Synapse Analytics SQL Pools experiments

Reading Time: 7 minutes

In this post I want to cover some Azure DevOps with Azure Synapse Analytics SQL Pools experiments. Because spent some time working on it.

By the end of this post, you will have a better idea of some of the things that are possible with both Azure Synapse dedicated and serverless SQL pools.

If you are going to follow any of the examples or experiments below, be aware of the costs involved. You can find out more about the Azure Synapse Analytics pricing here.

Update for this post

After publishing this post I figured out an alternative way to do CI/CD for serverless SQL pools using Azure DevOps. A lot of this post is still relevant, I just wanted to make everybody aware.

Deploying to a Azure Synapse dedicated SQL pool

Even though I have deployed to a dedicated SQL Pool before like I mentioned in a previous post here, I thought I would try a few new things.

Creating an Azure Synapse dedicated SQL Pool in advance

I tested a few things relating to deploying a new database to a dedicated SQL Pool using Azure DevOps. One thing I must stress here is that you must make sure you have a dedicated SQL Pool first before trying to create a database. Otherwise, you will get the below error in Azure DevOps.

Error SQL72014: .Net SqlClient Data Provider: Msg 49974, Level 16, State 1, Line 1 CREATE DATABASE statement is not supported in a Synapse workspace. To create a SQL pool, use the Azure Synapse Portal or the Synapse REST API.

In reality, you can simply create a new Pool using the portal. However, I decided to test using the New-AzSynapseSqlPool PowerShell module to create the pool. It’s one of the new PowerShell cmdlets for Azure Synapse Analytics. You can find out more about them all here.

Once I had created the dedicated SQL Pool, I tested deploying to it in an Azure DevOps pipeline. Using the ‘Azure SQL Database deployment’ task with the below YAML syntax.

      - task: SqlAzureDacpacDeployment@1
        displayName: 'Install in dedicated SQL Pool'
        inputs:
          azureSubscription: '{MY SUBSCRIPTION}'
          AuthenticationType: 'server'
          ServerName: '{WORKSPACE NAME}.sql.azuresynapse.net'
          DatabaseName: 'kcdedicatedPool'
          SqlUsername: '{SQL ADMIN USERNAME}'
          SqlPassword: '{MY PASSWORD}'
          deployType: 'DacpacTask'
          DeploymentAction: 'Publish'
          DacpacFile: '$(datadacpacfile)'

Using the ‘Azure SQL Database deployment’ task to deploy a dedicated pool is possible. However, the database name you use above has to match the name of your pool. From there it will create the database under your dedicated Pool in the workspace as below.

You can see an example of an Azure Pipelines YAML file for this in a GitHub repository that I have made public. Which is called AzureDevOps-AzureSynapseSQLPool.

Deploying to Azure Synapse serverless SQL pool

I thought I would try deploying to a serverless pool as well. Because I enjoy a challenge. First thing I did was to change the YAML code that worked above to cater for the Built-in serverless pool.

- task: SqlAzureDacpacDeployment@1
        displayName: 'Install in dedicated SQL Pool'
        inputs:
          azureSubscription: '{MY SUBSCRIPTION}'
          AuthenticationType: 'server'
          ServerName: '{WORKSPACE NAME}-ondemand.sql.azuresynapse.net'
          DatabaseName: 'Built-in'
          SqlUsername: '{SQL ADMIN USERNAME}'
          SqlPassword: '{MY PASSWORD}'
          deployType: 'DacpacTask'
          DeploymentAction: 'Publish'
          DacpacFile: '$(datadacpacfile)'

However, I got the below error.

FUNCTION 'SESSIONPROPERTY' is not supported

So, I thought I would create a database in the Built-in pool first and then try this again.

Creating database in serverless pool

I tried creating a database in the serverless pool a few ways. Purely as part of my experiments to use Azure DevOps with Azure Synapse Analytics SQL Pools. In reality, it is easy to create a database in the serverless pool if you are using the Synapse studio.

You just go to the Data section, click the plus sign and select ‘SQL database’ From there just leave the serverless pool selected and enter the name of the database you wish to create.

From there just leave the serverless pool selected and enter the name of the database you wish to create.

Another easy way

I’m sure the majority of you are aware of this, but I want to highlight this for people who are new to Azure Synapse Analytics.

When you create your Azure Synapse workspace you have a Serverless SQL endpoint created. Which you can find in the overview page of the portal. You can use this endpoint to connect up to the pool the same way as you can do with Azure SQL Database.

However, what you are able to do is a lot more limited. Of course, you can also connect to your dedicated Pools using the dedicated SQL endpoint that gets created for the workspace as well. Only difference being that the serverless endpoint has a -ondemand reference at the end of your workspace name.

To be able to connect to the endpoint properly you will have to add your client IP address to the firewall settings for your workspace. Once done you can connect up to the pool using your SQL application of choice and use the ‘CREATE DATABASE’ command.

Afterwards, you can view the database like the example below using Azure Data Studio.

PowerShell

In addition, I also tested creating a database using the New-AzSynapseSqlDatabase PowerShell cmdlet. I kept getting the below error message about a bad request no matter what I tried.

New-AzSynapseSqlDatabase : Operation returned an invalid status code 'BadRequest' No registered resource provider
found for location 'westeurope' and API version '2020-04-01-preview' for type 'workspaces'. The supported api-versions
are '2019-06-01-preview, 2020-12-01'

Eventually, I found a post in GitHub here which suggests that this cmdlet might be designed for something else.

With this in mind, I briefly tested using the older New-AzSqlDatabase syntax instead. However, I suspected I would not get far with it unless I wanted to go down the route of using the older logical server solution and I wanted to have this deployed the newer way.

For the benefit of time, I stuck to creating the database using one of the easy methods.

Creating a Database Project

Obviously, creating the database in the serverless pool was fine if I used one of the easy methods. However, I cannot say the same for my attempts to create a database project based on it.

I will provide an overview of what I have tried so far to save others some time.

First, I tried importing the database as a database as a project in Azure Data Studio. Because it had worked fine when I had done it for a database in a dedicated SQL Pool. However, it failed with the familiar error below:

Extract project files: An error occurred while attempting to connect to the server: FUNCTION 'SESSIONPROPERTY' is not supported..

In reality, I tried a few different things to work in Azure Data Studio to no avail. So, I then tried to do it in using Visual Studio 2019.

I first created a Database Project in Visual Studio and tried to import it from there. Which caused something interesting to happen. Every time the same thing happened, first it would state an invalid logon attempt, and then the second attempt would cause Visual Studio to crash.

However, I have had this issue before on one of the laptops, so I already knew a workaround. I opened up Visual Studio again and this time around connected to the pool using SQL Server Object Explorer.

I was surprised to see that I could not even explore the contents of the serverless pool this way. Because it had worked before with the dedicated pool.

Existing Database Project

Since I already had an existing Database Project based on a dedicated SQL Pool, I thought I would test creating an Azure DevOps pipeline to update the database that I had created in the serverless pool.

It was a simple pipeline, based on one I had covered in a previous post here. However, no matter what I tried I got the familiar error message below.

FUNCTION 'SESSIONPROPERTY' is not supported

Recommendations

With this in mind, I recommend some alternative options for now if you wish to do schema updates between multiple databases in serverless pools.

First option is to create to keep the SQL scripts with all the schema updates in Synapse studio. If you have your workspace under source control and you are replicating your objects to another workspace your scripts will move along with it. From there simply run them when required for the new databases.

Second option is to keep the schema scripts in source control somewhere. For example, a Git repository in Azure DevOps. From there, you can use them with any of the standard SQL tools locally after creating a local clone of the scripts.

Third option is to see if it’s worth using dedicated pools instead. That way, you can get your pools under source control easier. However, it can introduce a cost factor.

In reality, there are potentially a few other options as well. However, I have yet to explore them. If you have experimented using other methods feel free to let me know.

Final word

Well, I hope my post about my experiments to use Azure DevOps with Azure Synapse Analytics SQL Pools has been an interesting read.

In reality, it feels strange posting about some of the experiments that did not work as I had hoped. However, at least for now those of you have tried them know that you are not alone or missing something.

Of course, if you have figured out a way to do deployments to the serverless pools more gracefully feel free to let me know. In addition, if you have any comments or queries about this post feel free to reach out to me.