A while ago we created a script to clean up the Synapse Workspace before deploying new pipelines, datasets, etc. to Synapse. This helps you to remove old parts like for example triggers because the Synapse deployment is incremental and does not do any deletes. That script works fine, however for workspaces with a lot of items it could take up to 30 minutes to clean up everything. In that post we already mentioned some future improvements...
 |
| Cleanup Synapse Workspace after deployment |
Update: use DeleteArtifactsNotInTemplate: true in deployment task to avoid powershell
This version of the cleanup script compares the items in the Synapse Workspace against the Artifact that you just deployed. Everything that is in the workspace but not in the artifact will now be deleted (afterwards). This will significantly shorten the cleanup period during deployment.
You need to add this YAML step to your Synapse Deployment pipeline (preferably after the Deployment step) and change the path of the PowerShell script to your setup. The first parameter is the Synapse Workspace Name of the environment you are deploying to. The second one is the corresponding Resource Group Name. The last one is the location of the artifact. In this case the Pipeline.Workspace variable + "s\Synapse" (see screenshot below of the treeview on the agent).
###################################
# 5 Cleanup Synapse
###################################
- task: AzurePowerShell@5
displayName: '5 Cleanup Synapse'
inputs:
azureSubscription: ${{ parameters.ServiceConnnection }}
scriptType: filePath
scriptPath: $(Pipeline.Workspace)\s\CICD\Powershell\ClearSynapse.ps1
scriptArguments:
-WorkspaceName ${{ parameters.TargetWorkspaceName }} `
-ResourceGroupName ${{ parameters.ResourceGroupName }} `
-ArtifactDirectory $(Pipeline.Workspace)\s\Synapse
azurePowerShellVersion: latestVersion
pwsh: true
 |
| Showing the path of the Artifact root folder |
Save the PowerShell Script below in your repository and change the path in the above YAML script. We like to have a CICD folder in the root to store everything deployment related.
 |
| Repos folder structure |
param (
[Parameter (Mandatory = $true, HelpMessage = 'Synapse name')]
[ValidateNotNullOrEmpty()]
[string] $WorkspaceName,
[Parameter (Mandatory = $true, HelpMessage = 'Resourcegroup name')]
[ValidateNotNullOrEmpty()]
[string] $ResourceGroupName,
[Parameter (Mandatory = $true, HelpMessage = 'Artifact Directory')]
[ValidateNotNullOrEmpty()]
[string] $ArtifactDirectory
)
# Two default Linked Services we cannot remove
[string] $WorkspaceDefaultSqlServer = "$($WorkspaceName)-WorkspaceDefaultSqlServer"
[string] $WorkspaceDefaultSqlStorage = "$($WorkspaceName)-WorkspaceDefaultStorage"
# A little dummy protection to check whether you have configured the right folder
# If these folders are not available there is probably something wrong and we
# don't want to delete everything in the workspace.
if (!(Test-Path -Path (Join-Path $ArtifactDirectory "integrationRuntime")) -AND
!(Test-Path -Path (Join-Path $ArtifactDirectory "linkedService")) -AND
!(Test-Path -Path (Join-Path $ArtifactDirectory "pipeline")))
{
Write-Output "Artifact folder $($ArtifactDirectory) "
throw "Dummy protection - Probably not the right folder that stores your artifact"
}
#######################################################
# 1) Checking for resource locks and removing them
#######################################################
Write-Output "==========================================="
Write-Output "1) Removing resource locks"
Write-Output "==========================================="
# Getting all locks on the Azure Synapse Workspace
$lock = Get-AzResourceLock -ResourceGroupName $ResourceGroupName -ResourceName $WorkspaceName -ResourceType "Microsoft.Synapse/workspaces"
Write-Output "Found $($lock.Count) locks"
# Check if the collection of Azure resource locks is not emtpy
if($null -ne $lock)
{
# Looping through all resource locks to remove them one by one
$lock | ForEach-Object -process {
# Remove lock
Write-Output "Removing Lock Id: $($lock.LockId)"
Remove-AzResourceLock -LockId $_.LockId -Force
}
}
Write-Output "Step 'Removing resource locks' completed`r`n"
#######################################################
# 2) Stop and remove Triggers not in Artifact
#######################################################
Write-Output "==========================================="
Write-Output "2) Stop and remove Triggers not in Artifact"
Write-Output "==========================================="
# Check if the artifact contains any triggers
if (Test-Path -Path (Join-Path $ArtifactDirectory "trigger"))
{
# Getting all Triggers from Artifact
$ArtifactTriggers = Get-ChildItem -Path (Join-Path $ArtifactDirectory "trigger") -Filter "*.json" | Select-Object -ExpandProperty BaseName
# Getting all Triggers from Synapse
$SynapseTriggers = Get-AzSynapseTrigger -WorkspaceName $WorkspaceName | Select-Object -ExpandProperty Name
# Getting Triggers from Synapse that are not in the Artifact
$Triggers = Compare-Object -ReferenceObject $ArtifactTriggers -DifferenceObject $SynapseTriggers | Select-Object -ExpandProperty InputObject
} else {
# Fill collection with all existing triggers to remove them
# because artifact doesn't contain any triggers anymore
Write-Output "Path not found in Artifact, removing all existing Triggers in Synapse Workspace"
$Triggers = Get-AzSynapseTrigger -WorkspaceName $WorkspaceName | Select-Object -ExpandProperty Name
}
Write-Output "Found $($Triggers.Count) Triggers that are not in the Artifact"
# Stopping all Triggers before deleting them
$Triggers | ForEach-Object -process {
Write-Output "Stopping Trigger $($_)"
try {
# Trying to stop each Trigger
Stop-AzSynapseTrigger -WorkspaceName $WorkspaceName -Name $($_) -ErrorAction Stop
}
catch {
if ($_.Exception.Message -eq "{}")
{
# Ignore failures for triggers that are already stopped
Write-Output "Trigger stopped"
}
else {
# Unexpected error
Write-Output "Something went wrong while stopping trigger!"
Throw $_
}
}
# Remove trigger
Write-Output "Removing Trigger $($_)"
Remove-AzSynapseTrigger -Name $_ -WorkspaceName $WorkspaceName -Force
}
Write-Output "Step 'Stop and remove Triggers not in Artifact' completed`r`n"
#######################################################
# 3) Remove Pipelines not in Artifact
#######################################################
Write-Output "==========================================="
Write-Output "3) Remove Pipelines not in Artifact"
Write-Output "==========================================="
# Check if artifact contains any pipelines
if (Test-Path -Path (Join-Path $ArtifactDirectory "pipeline"))
{
# Getting all Pipelines from Artifact
$ArtifactPipelines = Get-ChildItem -Path (Join-Path $ArtifactDirectory "pipeline") -Filter "*.json" | Select-Object -ExpandProperty BaseName
# Getting all Pipelines from Synapse
$SynapsePipelines = Get-AzSynapsePipeline -WorkspaceName $WorkspaceName | Select-Object -ExpandProperty Name
# Getting Pipelines from Synapse that are not in the Artifact
$Pipelines = Compare-Object -ReferenceObject $ArtifactPipelines -DifferenceObject $SynapsePipelines | Select-Object -ExpandProperty InputObject
} else {
# Fill collection with all existing pipelines to remove them
# because artifact doesn't contain any pipelines anymore
Write-Output "Path not found in Artifact, removing all existing Pipelines in Synapse Workspace"
$Pipelines = Get-AzSynapsePipeline -WorkspaceName $WorkspaceName | Select-Object -ExpandProperty Name
}
Write-Output "Found $($Pipelines.Count) Synapse Pipelines that are not in the Artifact"
# Trying to delete all pipelines. If a pipeline is still referenced
# by an other pipeline it will continue to remove other pipelines
# before trying to remove it again... max 100 times. So don't create
# chains of pipelines that are too long
[int] $depthCount = 0
while ($Pipelines.Count -gt 0 -and $depthCount -lt 100)
{
# Loop through collection of pipelines and try to remove them
$Pipelines | ForEach-Object -process {
Write-Output "Trying to delete pipeline $($_)"
Remove-AzSynapsePipeline -Name $_ -WorkspaceName $WorkspaceName -Force -ErrorAction SilentlyContinue
}
# Wait 2 seconds before retry and raise retry counter
Start-Sleep -Seconds 2
$depthCount += 1
# Check if artifact contains any pipelines
if (Test-Path -Path (Join-Path $ArtifactDirectory "pipeline"))
{
# Getting all Pipelines from Synapse
$SynapsePipelines = Get-AzSynapsePipeline -WorkspaceName $WorkspaceName | Select-Object -ExpandProperty Name
# Getting Pipelines from Synapse that are not in the Artifact
$Pipelines = Compare-Object -ReferenceObject $ArtifactPipelines -DifferenceObject $SynapsePipelines | Select-Object -ExpandProperty InputObject
} else {
# Fill collection with all existing pipelines to remove them
# because artifact doesn't contain any pipelines anymore
$Pipelines = Get-AzSynapsePipeline -WorkspaceName $WorkspaceName | Select-Object -ExpandProperty Name
}
# Check if there are any pipelines left for a retry
if ($Pipelines.count -gt 0)
{
Write-Output "Still found $($Pipelines.Count) Synapse Pipelines that are not in the Artifact. Starting next iteration."
} else {
Write-Output "Deletion of Pipelines not in Artifact completed"
}
}
# Error when you have circulair pipeline links or just way to many levels
if ($depthCount -eq 100)
{
throw "Too many levels of child pipelines or circulair relations!"
}
Write-Output "Step 'Remove Pipelines not in Artifact' completed`r`n"
#######################################################
# 4) Remove Notebooks not in Artifact
#######################################################
Write-Output "==========================================="
Write-Output "4) Remove Notebooks not in Artifact"
Write-Output "==========================================="
# Check if artifact contains any pipelines
if (Test-Path -Path (Join-Path $ArtifactDirectory "notebook"))
{
# Getting all Notebooks from Artifact
$ArtifactNotebooks = Get-ChildItem -Path (Join-Path $ArtifactDirectory "notebook") -Filter "*.json" | Select-Object -ExpandProperty BaseName
# Getting all Notebooks from Synapse
$SynapseNotebooks = Get-AzSynapseNotebook -WorkspaceName $WorkspaceName | Select-Object -ExpandProperty Name
# Getting Notebooks from Synapse that are not in the Artifact
$Notebooks = Compare-Object -ReferenceObject $ArtifactNotebooks -DifferenceObject $SynapseNotebooks | Select-Object -ExpandProperty InputObject
} else {
# Fill collection with all existing notebooks to remove them
# because artifact doesn't contain any notebooks anymore
Write-Output "Path not found in Artifact, removing all existing Notebooks in Synapse Workspace"
$Notebooks = Get-AzSynapseNotebook -WorkspaceName $WorkspaceName | Select-Object -ExpandProperty Name
}
Write-Output "Found $($Notebooks.Count) Synapse Notebooks that are not in the Artifact"
# Loop through collection of Notebooks to delete them
$Notebooks | ForEach-Object -process {
Write-Output "Deleting Notebook $($_)"
Remove-AzSynapseNotebook -Name $($_) -WorkspaceName $WorkspaceName -Force
}
Write-Output "Step 'Remove Notebooks not in Artifact' completed`r`n"
#######################################################
# 5) Remove SQL-Scripts not in Artifact
#######################################################
Write-Output "==========================================="
Write-Output "5) Remove SQL-Scripts not in Artifact"
Write-Output "==========================================="
# Check if artifact contains any SQL Scripts
if (Test-Path -Path (Join-Path $ArtifactDirectory "sqlscript"))
{
# Getting all SQL-scripts from Artifact
$ArtifactSQLScripts = Get-ChildItem -Path (Join-Path $ArtifactDirectory "sqlscript") -Filter "*.json" | Select-Object -ExpandProperty BaseName
# Getting all SQL-scripts from Synapse
$SynapseSQLScripts = Get-AzSynapseSqlScript -WorkspaceName $WorkspaceName | Select-Object -ExpandProperty Name
# Getting SQL-scripts from Synapse that are not in the Artifact
$SQLScripts = Compare-Object -ReferenceObject $ArtifactSQLScripts -DifferenceObject $SynapseSQLScripts | Select-Object -ExpandProperty InputObject
} else {
# Fill collection with all existing SQL Scripts to remove them
# because artifact doesn't contain any SQL Scripts anymore
Write-Output "Path not found in Artifact, removing all existing SQL-Scripts in Synapse Workspace"
$SQLScripts = Get-AzSynapseSqlScript -WorkspaceName $WorkspaceName | Select-Object -ExpandProperty Name
}
Write-Output "Found $($SQLScripts.Count) Synapse SQL-Scripts that are not in the Artifact"
# Loop through collection of SQL scripts to delete them
$SQLScripts | ForEach-Object -Process {
Write-Output "Deleting SQL-script $($_)"
Remove-AzSynapseSqlScript -Name $($_) -WorkspaceName $WorkspaceName -Force
}
Write-Output "Step 'Remove SQL-Scripts not in Artifact' completed`r`n"
#######################################################
# 6) Remove Datasets not in Artifact
#######################################################
Write-Output "==========================================="
Write-Output "6) Remove Datasets not in Artifact"
Write-Output "==========================================="
# Check if artifact contains any datasets
if (Test-Path -Path (Join-Path $ArtifactDirectory "dataset"))
{
# Getting all Datasets from Artifact
$ArtifactDatasets = Get-ChildItem -Path (Join-Path $ArtifactDirectory "dataset") -Filter "*.json" | Select-Object -ExpandProperty BaseName
# Getting all Datasets from Synapse
$SynapseDatasets = Get-AzSynapseDataset -WorkspaceName $WorkspaceName | Select-Object -ExpandProperty Name
# Getting Datasets from Synapse that are not in the Artifact
$Datasets = Compare-Object -ReferenceObject $ArtifactDatasets -DifferenceObject $SynapseDatasets | Select-Object -ExpandProperty InputObject
} else {
# Fill collection with all existing Datasets to remove them
# because artifact doesn't contain any Datasets anymore
Write-Output "Path not found in Artifact, removing all existing Datasets in Synapse Workspace"
$Datasets = Get-AzSynapseDataset -WorkspaceName $WorkspaceName | Select-Object -ExpandProperty Name
}
Write-Output "Found $($Datasets.Count) Synapse Datasets that are not in the Artifact"
# Loop through collection of Datasets to delete them
$Datasets | ForEach-Object -process {
Write-Output "Deleting Dataset $($_)"
Remove-AzSynapseDataset -Name $_ -WorkspaceName $WorkspaceName -Force
}
Write-Output "Step 'Remove Datasets not in Artifact' completed`r`n"
#######################################################
# 7) Remove Linked Services not in Artifact
#######################################################
Write-Output "==========================================="
Write-Output "7) Remove Linked Services not in Artifact"
Write-Output "==========================================="
# Check if artifact contains any Linked Services
if (Test-Path -Path (Join-Path $ArtifactDirectory "linkedService"))
{
# Getting all Linked Services from Artifact
$ArtifactLinkedServices = Get-ChildItem -Path (Join-Path $ArtifactDirectory "linkedService") -Filter "*.json" | Where-Object {($_ -NotLike "*WorkspaceDefaultSqlServer.json" -and $_ -NotLike "*WorkspaceDefaultStorage.json") } | Select-Object -ExpandProperty BaseName
# Getting all Linked Services from Synapse
$SynapseLinkedServices = Get-AzSynapseLinkedService -WorkspaceName $WorkspaceName | Where-Object {($_.Name -ne $WorkspaceDefaultSqlServer -and $_.Name -ne $WorkspaceDefaultSqlStorage) } | Select-Object -ExpandProperty Name
# Getting Linked Services from Synapse that are not in the Artifact
$LinkedServices = Compare-Object -ReferenceObject $ArtifactLinkedServices -DifferenceObject $SynapseLinkedServices | Select-Object -ExpandProperty InputObject
} else {
# Fill collection with all existing Linked Services to remove them
# because artifact doesn't contain any Linked Services anymore
Write-Output "Path not found in Artifact, removing all existing Linked Services in Synapse Workspace"
$LinkedServices = Get-AzSynapseLinkedService -WorkspaceName $WorkspaceName | Where-Object {($_.Name -ne $WorkspaceDefaultSqlServer -and $_.Name -ne $WorkspaceDefaultSqlStorage) } | Select-Object -ExpandProperty Name
}
Write-Output "Found $($LinkedServices.Count) Synapse Linked Services that are not in the Artifact"
# Trying to delete all linked services. If a linked service is still
# referenced by an other linked service it will continue to remove
# other linked services before trying to remove it again...
# max 100 times. Example: KeyVault linked services
$depthCount = 0
while ($LinkedServices.Count -gt 0 -and $depthCount -lt 100)
{
# Loop through collection of Linked Services and try to remove them
$LinkedServices | ForEach-Object -process {
Write-Output "Trying to delete Linked Service $($_)"
Remove-AzSynapseLinkedService -Name $_ -WorkspaceName $WorkspaceName -Force -ErrorAction Continue
}
# Wait 2 seconds before retry and raise retry counter
Start-Sleep 2
$depthCount += 1
# Check if artifact contains any Linked Services
if (Test-Path -Path (Join-Path $ArtifactDirectory "linkedService"))
{
# Getting all Linked Services from Synapse
$SynapseLinkedServices = Get-AzSynapseLinkedService -WorkspaceName $WorkspaceName | Where-Object {($_.Name -ne $WorkspaceDefaultSqlServer -and $_.Name -ne $WorkspaceDefaultSqlStorage) } | Select-Object -ExpandProperty Name
# Getting Linked Services from Synapse that are not in the Artifact
$LinkedServices = Compare-Object -ReferenceObject $ArtifactLinkedServices -DifferenceObject $SynapseLinkedServices | Select-Object -ExpandProperty InputObject
} else {
# Fill collection with all existing Linked Services to remove them
# because artifact doesn't contain any Linked Services anymore
$LinkedServices = Get-AzSynapseLinkedService -WorkspaceName $WorkspaceName | Where-Object {($_.Name -ne $WorkspaceDefaultSqlServer -and $_.Name -ne $WorkspaceDefaultSqlStorage) } | Select-Object -ExpandProperty Name
}
# Check if there are any Linked Services left for a retry
if ($LinkedServices.count -gt 0)
{
Write-Output "Still found $($LinkedServices.Count) Synapse Linked Services that are not in the Artifact. Starting next iteration."
} else {
Write-Output "Deletion of Linked Services not in Artifact completed"
}
}
# Error when you have circulair Linked Services links or just way to many levels
if ($depthCount -eq 100)
{
throw "Too many levels of references to other Linked Services!"
}
Write-Output "Step 'Remove Linked Services not in Artifact' completed"
Write-Output "==========================================="
There is a check at the start that will check whether it can find some expected sub folders in the supplied artifact path. Otherwise it will asume that you don't have any datasets, pipelines, etc. and cleanup your entire Synapse Workspace.
 |
If nothing has to be cleaned/removed the script is ready within seconds.
 |
| No cleanup necessary |
If items do have to be removed it is still ready in a few minutes instead of half an hour. Notice the iterations for cleaning up the pipelines. It will try to delete a pipeline, but if it is still used by an other pipeline then it will continue with the next pipeline and try again afterwards. The same construction is used for Linked Services where for example a Key Vault Linked Service can still be used by an other Linked Service.
 |
| Some cleanup was necessary |
Conclusions
In this post you learned how to clean your Synapse Workspace much more efficiently by comparing the Workspace and the Artifact. This way you don't have to delete each item in your workspace, but only the ones you deleted during development.
Note that not all parts of Synapse are available in this clean up script. For example KQL scripts and Dataflows are still missing. They will be added later on. If you want to add those yourself make sure they are on the right place within the script Dataflows should probably added before the pipelines and the KQL scripts in front of or after the SQL Scripts. Feel free to let us know if you have any improvements for this script that you would like the share with the community.
Special thanks to colleague Joan Zandijk for helping out.
