During the Azure DevOps based training day I co-presented at SQLBits I got an interesting question about the CI/CD method that I showed for serverless SQL Pools.
During SQLBits I showed the same method that I covered in a previous post. Where I covered how to perform CI/CD for serverless SQL pools using Azure DevOps. Which uses the DBOps PowerShell module due to the fact that you cannot do deployments with a dacpac file.
I got asked if I thought we were going backwards with CI/CD for serverless SQL Pools. Mostly due to the fact that we had to include defensive logic in the SQL scripts.
My answer was that we did this purely because of the limitations of the SchemaVersions table. Because the DBOps PowerShell module usually creates this table in the target location if it does not exist. Which it then uses it to log which scripts have already run.
However, you cannot easily create and update this table in a serverless SQL Pool.
In addition, I highlighted that the fact that we can do CI/CD on serverless SQL Pools is great. Because it was an issue for some people.
In reality there are potentially other ways to get around the issue with the SchemaVersions table. It does mean a lot of customization to your solution though.
One thing to remember is that DBOps is open source. In reality, there are premium migration-based deployment tools available for SQL Server and I suspect some of them also work with Azure Synapse Analytics serverless SQL Pools.
With this in mind, I suspect some of these premium solutions support logging what scripts have already run in another location.
KevOps video
I thought it would be appropriate to mention that the ‘KevOps for Azure Synapse Analytics‘ session from this years Data Toboggan is now available to view online. Due to the fact that it was discussed by others at SQLBits.
To view the video you can either click on the name of the session above or on the image below.
Templates to perform CI/CD for serverless SQL Pools
I have made a public repository available in GitHub that you can use as a template to do the CI/CD yourself in Azure DevOps. It is called AzureDevOps-SynapseServerlessSQLPool.
Feel free to give it a star if it proves to be useful.
Final words about CI/CD method for Serverless SQL Pools
I am glad that I got asked this question about the CI/CD method for Serverless SQL Pools. Because it allowed me to explain that defensive logic was required purely due the logging issue.
In addition, I am keen to hear from others who have done CI/CD for serverless SQL Pools as I have some questions of my own.
For instance, do you use a similar method, or do you perform CI/CD for serverless SQL Pools another way? Plus, do you also use defensive logic or is it not required due to how you use serverless SQL Pools?
Of course, if you have any other queries or comments about this post feel free to reach out to me.
[…] Kevin Chant answers a question: […]