Serverless db creation using ci cd in synapse.

Question

Hi ,

I have synapse workspace already available. And I want to create one serverless db and run some scripts , stored procedure using ci/cd azure devops , without running it manually as we don't get access for higher environments. How to achieve this.

Thanks in advance.

Regards,
Sandeep

Answer 1

Hello @NAGA SANDEEP KUMAR KAPA ,

Please follow the below steps for the serverless DB creation using CICD in the synapse.

Release Pipeline contains the Azure Synapse Workspace Deployment task which is followed by a PowerShell task responsible to deploy the Serverless SQL Objects. The PowerShell Script can deploy/migrate the following SQL Objects- Database, Database Master Key Encryption, Database Scoped Credential, Schema, External Data Sources, External File Formats, External Tables, Views, and Stored Procedures.

Prerequisites:

1. Azure Synapse Analytics Workspace to be created in the target environment to which Serverless SQL Pool is to be deployed.
2. GIT Configuration to be enabled for the Azure Synapse Analytics Workspace in the current environment by configuring the Azure DevOps Organization, Project, Repository, and the Root Folder. This facilitates the check-in to the Repos (Continuous Integration) for all the SQL objects to be migrated and deployed in the target environment.
   Note- Make sure to include 'GO' at the end of every SQL Script committed to the Repos.
3. After configuring GIT and checking in the required SQL objects, publish the changes through the main branch in the Synapse Studio. This will generate the following ARM Template Files in the Publish Branch (default- workspace_publish) which will be used for Synapse Workspace Deployment in the Release Pipeline-

o TemplateForWorkspace.json and
o TemplateParametersForWorkspace.json

4. Grant the Synapse Administrator Role to the Service Principal being used to deploy the Synapse Workspace through DevOps CD Pipeline.
5. Install the “Synapse Workspace Deployment” extension from the Visual Studio Marketplace in the Organizational Settings.
6. This article uses Access Token Authentication to connect to the Azure Synapse Serverless SQL Pool while deploying the SQL Objects. Create the following secrets in the Azure Key Vault which can be fetched from a Variable Group linked to the Release Pipeline-

o Client ID
o Tenant ID
o Client Secret

7. Similarly add the “SynapseDatabaseMasterKeyEncryptionKeyPassword” as an Azure Key Vault Secret which will be used to create the master key encryption for Serverless SQL Database.

Azure Synapse Serverless SQL Continuous Deployment/ Release Pipeline:

a) After the Synapse Workspace GIT Configuration, verify the repository folder structure as shown-

b) Add the PowerShell Script under the synapse folder as shown-

c) Create a New Release Pipeline with an empty job.

d) Link the following artifacts to the release pipeline:

1. SynapsePublishArtifacts

2.SynapseSQLObjectsArtifacts

e) Create and link the Variable Groups to the Release Pipeline with appropriate values for the variables– Resource Group, Synapse Workspace, SQL Server Instance, SQL Database, Database Schema, Database Scoped Credential, Master Key Encryption Password, ClientID, TenantID, and Client Secret.

F) In the Release Pipeline Stages, add the Synapse Workspace Deployment task to deploy the Synapse Workspace.

Note: Pay attention to the “Delete Artifacts Not in Template”- if enabled this will delete any artifacts from the previous releases not present in the current release publish artifacts.

g) Now add the PowerShell task to deploy the Synapse Serverless SQL Objects to the Synapse Workspace.

h) Create the pipeline release to verify the Synapse Serverless SQL Deployment.

I have attached the executeSqlscripts in txt format. please use it in .ps1 format

• Please don't forget to click on or upvote button whenever the information provided helps you. Original posters help the community find answers faster by identifying the correct answer. Here is how
• Want a reminder to come back and check responses? Here is how to subscribe to a notification
• If you are interested in joining the VM program and help shape the future of Q&A: Here is how you can be part of Q&A Volunteer Moderators

233782-executesqlscripts.txt

Answer 2

We want to use this approach to deploy and run sql scripts which create views in a serverless slq pool based on delta tables in a adls gen 2 storage account. We are using different storage accounts for dev and prd environment. Is there a way to dynamically change the storage account where the sql scripts creates the views from during deployment?