In CI/CD Azure Synapse Analytics – part 1 we have covered:
- Setting up git source control in Synapse Studio
- The difference in main collaboration and workspace publish branch
In this blog we are going to cover:
- How to create build pipelines
- Deploy to Synapse production workspace using DevOps release pipelines
Build Pipeline in DevOps
Go to you https://dev.azure.com/<YourOrgName> i.e Azure DevOps portal and select your Synapse project which you have initialized earlier by configuring git in Synapse Studio.
In DevOps, Under the Pipelines tab select Build Pipelines (#2 ) and create a new pipeline using the classic editor ( we prefer to do this way). Next is to select source, for this case it is Azure Repos Git and do appropriate selection for Team Project, Repository and git branch which will used to create build artifacts that will used to deploy to another Synapse environment ( eg QA, PRD etc)
If your default publish branch is workspace_publish then select workspace_publish instead of main/master branch. Refer to step 3 in the last blog CI/CD Azure Synapse Analytics – part 1. On the next page for template selection, we will select the Empty Job link.
Add a new Powershell script task and use inline scripting to copy synapse publish templates from your git source. Make sure to modify PowerShell as per your development workspace name, in our case it is called pro-sand-dev.
ROBOCOPY.exe $(System.DefaultWorkingDirectory)/pro-sand-dev/ $(build.artifactstagingdirectory)/ /S /R:0 /W:0
exit 0
Add Publish build artifacts task under Powershell scrip and keep default values for the Display name, Path to publish, Artifcate name, etc. Finally, you can click on Save & Queue.
The build is completed and the artifact is published with success. Now, we can use published artifact to create release pipelines for deployment
Release Pipelines for Deployment
Go back to the Azure DevOps project, select Releases under the pipelines tab and create New Release Pipeline. Click on Empty Job during template selection.
We will use artifact which we have published earlier using Build pipelines. Click on Add an artifact and select Build. In the project setting, select your current project and the name of the latest build artifact. Do ensure to select the latest as Default version and click Add.
Now we will add deployment tasks to our release pipeline. Cick Stage 1 ( 1job, 0 task) to add new task.
Click on the + button to add a new task. Search for ‘Synapse workspace’, you can directly add Synapse workspace deployment task if it’s already installed otherwise you can click on Synapse workspace deployment under Marketplace to install it in your Azure DevOps project.
Select the file path for Workspace Template and Template parameters from the published artifact drop folder. Under your Azure subscription select apprpoirate connection type. Select the resource group of the target Synapse workspace. Enter the Synapse workspace name located in the resource group that you specified.
Select the managed service connection type that you created to connect to the Synapse workspace. Click Save
Note by Microsoft on service connection: To configure new a service connection, select the Azure subscription from the list and click ‘Authorize’. If your subscription is not listed or if you want to use an existing service principal, you can setup an Azure service connection using the ‘Add’ or ‘Manage’ button.
Managed Service Identity service connection scope is limited to access granted to the Azure virtual machine running the agent.
Ensure that the VM has access to specified resources. Service connection scoped at Management Group level are visible only in Azure PowerShell task.
The only drawback of using the synapse deployment task is that it copies your Development environment default linked services to QA/PROD environments. But we don’t want to use DEV linked services in QA/PROD. To clean up this, we will add a new task Azure CLI.
Select script type as PowerShell and Script location as Inline script. Use the below script as per your DEV environment.
az synapse linked-service delete --workspace-name <QA/PRD WorkspaceName> --name pro-sand-dev-WorkspaceDefaultSqlServer --yes
az synapse linked-service delete --workspace-name <QA/PRD WorkspaceName> --name pro-sand-dev-WorkspaceDefaultStorage --yes
Once you are happy with all changes, hit Create Release as it will start deployment to QA/PROD environment.
We will cover Override ARM Parameters i.e ConnectionString, Storage account URL etc in a separate blog as it require special attention.
Hi, Deepak!
Thank you for the tutorial. Very informative. However, there’s one step I’m having troubles with: I can’t delete the default linked services, as DevOps tells me “The linked service xxx-WorkspaceDefaultStorage is managed by the workspace xxx and cannot be deleted”. How did you fix this problem?
Thank you in advance!
Hi Latte,
In Azure CLI task, Azure Resource Manage Connection ( its a service should have access to target synapse workspace. You need to open synapse studio and grant access ADMIN to service prinicipal of Service connection used in Azure CLI task.
Thank you for your reply! Trouble is, It’s already registered as Synapse Administrator. It’s the very SP that deployed it in the first place. My personal user is also Synapse Administrator, but I’m also unable to delete it. In the GUI, the delete icon is not even there on these particular linked services.
Hi Latte,
Sorry for late reply. If you can’t see delete icon, I would suggest you to raise technical support ticket with MSFT.
Hi Deepak,
Can you deploy these arm templates that are generated from azure cli? Or is there any other way that you can take the pipelines from one Synapse workspace and deploy them to another Synapse workspace from Azure CLI?
Thank you in advance
After implemnting above i am getting below error, please help –
##[warning]Directory ‘D:\a\1\a’ is empty. Nothing will be added to build artifact ‘drop’.
Added below powershell to Build pipeline , rest everything same
ROBOCOPY.exe $(System.DefaultWorkingDirectory)/dev/$(build.ArtifactStagingDirectory)/ /S /R:0 /W:0
exit 0
ROBOCOPY.exe $(System.DefaultWorkingDirectory)/dev /$(build.ArtifactStagingDirectory)/ /S /R:0 /W:0
exit 0
There is space after ‘dev’
Thanks Deepak.
Still same error , after adding space –
ROBOCOPY.exe $(System.DefaultWorkingDirectory)/dev /$(build.ArtifactStagingDirectory)/ /S /R:0 /W:0
exit 0
——————————————————————————-
ROBOCOPY :: Robust File Copy for Windows
——————————————————————————-
Source : D:\a\1\s\dev\
Dest : D:\D:\a\1\a\
Files : *.*
Options : *.* /S /DCOPY:DA /COPY:DAT /R:0 /W:0
——————————————————————————
2023/08/01 14:19:30 ERROR 2 (0x00000002) Accessing Source Directory D:\a\1\s\dev\
The system cannot find the file specified.
Thanks Deepak for help.
SynapseWorkspace folder has – dataset , linkedservice,pipeline,trigger folder etc and respective jsons, if developer changes any of them then below ROBOCOPY command will get a specifc(changed one) or all folders json and move to drop location ?
ROBOCOPY.exe $(System.DefaultWorkingDirectory)/SynapseWorkspace/ $(build.ArtifactStagingDirectory)/ /S /R:0 /W:0
exit 0
Also in release process if i choose Operation Type – ‘validate & Deploy’ and specify Artifacts root folder – ‘$(System.DefaultWorkingDirectory)/_BI-CI/drop’ will it take all json in drop folder and deploy ?
please confirm.
Yes, it will take all files (from repo) to build drop not just recently changed.
Yes,Validate & deploy is just ensuring if all json files are valid i.e. not missing any expected argument etc.
Super Thanks Deepak.
Have you tried any rollback startegy for Synapse Pipelines through CI CD ?
like we can have snapshot of tables and rollback if needed using CI CD Pipelines !
Any suggestion how to achieve rollback !
Hi Rohit,
Synapse/ADF Pipeline can be rolled back using the Release pipeline.
Go to releases, find a list of all previous releases. You can click on any prior build release version and click on deploy.
Reference: https://i.stack.imgur.com/KPtGX.png
Tables: We have don’t any rollback yet. Alternative options: Create a visual studio database project and add all schema objects to source control.
The second option is to create restore point of SQLDW actively.
Thanks Deepak, much appreciated.
As far as i understood , synapse serverless SQL doesn’t persist any data (only schema for EXTERNAL TABLES and VIEWS) so it relies on ADLS recovery. Schema can be stored and managed using the source control integration feature of Synapse with Azure devops repository for DR purpose.
Is there a way to create restore point for synapse serverless SQL or to backup serverless SQL pool databases, please suggest how to achieve !!
Hello Deepak,
A quick one , in Git I could see all the resources like notebook, sqlscript, pipelines, triggers. But I do not see any folder for SQL database with external tables.
Is this capability is missing of checked-in GIT/AzureDevops for SQL database with external tables ?
Please suggest.
This is synapse serverless SQL database.
Hi
SQL Pool objects don’t go into source control when you add a git repo to synapse workspace settings. Possibly a missing feature at the moment.
There is a blog from Microsoft to add source control integration SQL pool. We haven’t done this but you can give a try.
https://learn.microsoft.com/en-us/azure/synapse-analytics/sql-data-warehouse/sql-data-warehouse-source-control-integration
Thanks Deepak.
I see Sql Script is added to a repo , is it make sense to have individual sql script for all external tables added to git.
like below –
SynapseWorkspace
-dataset
-pipeline
-sqlscript
-ExternalTable 1
-ExternalTable 2
-ExternalTable 3 ………….