Azure Synapse Dedicated SQL CICD - Specific Use Case!

Hi Friends,

Today's topic is about the challenge we recently faced while deploying azure synapse dedicated SQL pool to higher environment. I amnot going to explain the end-to-ed deployment of dedicated SQL pool, instead just a specific small use case and the solution for that.

So, let's start, when we started working on this task of deploying SQL to other environment, as a first step we wanted to create new project from the source database. We realized we had restricted access to the source database itself. Because of which we were getting below error-

Obviously, we went to clients and asked for the required permission to create the project. As per the organization norms the access has to be restricted.

So, we decided to follow below steps to create database project with access restrictions at source database-

1. Create blank SQL server database project in visual studio.
2. Important point - Make sure you change the target platform of the newly create SQL database project (Go to properties-> project settings-> target platform) to Microsoft Azure SQL Data Warehouse.

3. Do schema compare - Connect to the source azure synapse dedicated SQL pool server -> select the database -> right click and select schema compare. Make sure you are selecting the target as the newly created database project.

4. Update the project with the changes and do a commit to the Azure repo.

After this, create DACPAC file and deploy to higher environments. One of the best article for this is Create dacpac for Azure Synapse Analytics SQL Pool using Azure DevOps (kevinrchant.com).

Dedicated SQL pool CICD process - CI/CD for Dedicated SQL Pools - YouTube

Conclusion

This is a short article for specific use case, but if you are looking for any help or guidelines on this topic. Please IM me directly, I will love to help.

Enjoy learning and sharing 😊

Thank You All 😊