Clone a dedicated SQL pool between encrypted and resource locked Synapse workspaces

by Jon Shah | Aug 22, 2024 | Az Synapse, Az DevOps, PowerShell

This post covers the automation of cloning or restoring a Synapse dedicated SQL pool from one workspace to another where locks and encryption exist.

The steps required by the code are as follows:
Variables:

$AzTargetWorkspace
$TargetResourceGroup
$SourceSqlPool
$TargetSqlPool

YAML variables:

Target Resource Group = ${{ variables.target_rg }}
Target Workspace Full Name = ${{ variables.target_ws_full }}

Delete Resource Lock

$resourceLocks = $(az lock list `
                      --resource-group ${{ variables.target_rg }} `
                      --query "[?name=='lock-${{ variables.target_rg }}']" `
                      | ConvertFrom-Json)
                      
$resourceLockExists = $resourceLocks.count -eq 1

if ( $resourceLockExists ) {
    az lock delete `
      --name lock-${{ variables.target_rg }} `
      --resource-group ${{ variables.target_rg }}
}

Change Encryption Key

if ( "${{ parameters.target_ws }}" -ne "${{ parameters.source_ws }}" ) {
    #set key vault key name for source workspace
    if ("${{ parameters.source_ws }}" -eq "ws-prod") {
        $sourceWsKvKeyName = "key-synapse-prod"
    }
    elseif ("${{ parameters.source_ws }}" -eq "ws-test") {
        $sourceWsKvKeyName = "key-synapse-test"
    }

    #delete inactive keys from target workspace
    $keys = az synapse workspace key list `
                --workspace-name ${{ variables.target_ws_full }} `
                --resource-group ${{ variables.target_rg }} `
                --only-show-errors | ConvertFrom-Json
    
    for ($i=0; $i -lt $keys.Count; $i++) {
        $wsKeyName = $keys[$i].name
        $isActive = $keys[$i].isActiveCmk

        if ($IsActive -eq $false) {
            az synapse workspace key delete `
                    --name $WsKeyName `
                    --workspace-name ${{ variables.target_ws_full }} `
                    --resource-group ${{ variables.target_rg }} `
                    --yes `
                    --only-show-errors | Out-Null
        }
    }
    
    #create source workspace key in target workspace
    az synapse workspace key create `
        --name $SourceWsKvKeyName `
        --workspace-name ${{ variables.target_ws_full }} `
        --resource-group ${{ variables.target_rg }} `
        --key-identifier ${{ variables.kv_url_prefix }}$sourceWsKvKeyName `
        --only-show-errors | Out-Null
    
    #update workspace key
    az synapse workspace update `
        --name ${{ variables.target_ws_full }} `
        --resource-group ${{ variables.target_rg }} `
        --key-name $sourceWsKvKeyName `
        --only-show-errors | Out-Null
}

Manage existing target database

$targetPoolExists = Test-AzSynapseSqlPool `
                        -WorkspaceName ${{ variables.target_workspace_full_name }} `
                        -Name ${{ variables.target_sql_pool }}
if( $targetPoolExists ) {
  if( "${{ parameters.keep_target_sql_pool }}" -eq $true ) {
      #check status of target sql pool and resume if paused
      $pool = Get-AzSynapseSqlPool `
                  -ResourceGroupName ${{ variables.target_rg }} `
                  -WorkspaceName ${{ variables.target_ws_full }} `
                  -Name ${{ variables.target_sql_pool }}
      if ($pool.Status -eq "Paused") {

          Resume-AzSynapseSqlPool `
              -ResourceGroupName ${{ variables.target_rg }} `
              -WorkspaceName ${{ variables.target_ws_full }} `
              -Name ${{ variables.target_sql_pool }} | Out-Null
      }
      #create restore point name for retained target sql pool
      $restoreDateTime = Get-Date -Format "dd_MM_yyyy_HH.mm"
      $retainedSqlPool = '${{ variables.target_sql_pool }}' + "_" + $restoreDateTime
      
      #create restore point of target sql pool
      New-AzSynapseSqlPoolRestorePoint `
            -ResourceGroupName ${{ variables.target_rg }} `
            -WorkspaceName ${{ variables.target_ws_full }} `
            -Name ${{ variables.target_sql_pool }} `
            -RestorePointLabel $az_retained_target_sql_pool | Out-Null
      
      #get database id of restore point
      $pool = Get-AzSynapseSqlPool `
                -ResourceGroupName ${{ variables.target_rg }} `
                -WorkspaceName ${{ variables.target_ws_full }} `
                -Name ${{ variables.target_sql_pool }}
      $databaseId = $pool.Id -replace "Microsoft.Synapse", "Microsoft.Sql" `
          -replace "workspaces", "servers" `
          -replace "sqlPools", "databases"
      
      #clone sql pool
      $restorePoint = $pool |
                        Get-AzSynapseSqlPoolRestorePoint |
                        Where-Object {$PSItem.RestorePointLabel -eq $retainedSqlPool}  
                        
      Restore-AzSynapseSqlPool `
          -FromRestorePoint `
          -RestorePoint $restore_point.RestorePointCreationDate 
          -TargetSqlPoolName $retainedSqlPool `
          -ResourceGroupName ${{ variables.target_rg }} `
          -WorkspaceName ${{ variables.target_ws_full }} `
          -ResourceId $databaseId `
          -PerformanceLevel ${{ parameters.database_scale }} | Out-Null
      
      #suspend cloned sql pool
      Suspend-AzSynapseSqlPool `
          -ResourceGroupName ${{ variables.target_rg }} `
          -WorkspaceName ${{ variables.target_ws_full }} `
          -Name $retained_sql_pool | Out-Null  
  }
  #delete target sql pool
  Remove-AzSynapseSqlPool `
      -WorkspaceName ${{ variables.target_ws_full }} `
      -Name ${{ variables.target_sql_pool }} `
      -Force
}

Clone Database

if ("$pool.Status" -eq "Paused") {
        Resume-AzSynapseSqlPool `
            -ResourceGroupName ${{ variables.source_rg }} `
            -WorkspaceName ${{ variables.source_ws_full }} `
            -Name ${{ variables.source_sql_pool }} | Out-Null
}

#create restore point of source sql pool
New-AzSynapseSqlPoolRestorePoint `
    -ResourceGroupName ${{ variables.source_rg }} `
    -WorkspaceName ${{ variables.source_ws_full }} `
    -Name ${{ variables.source_sql_pool }} `
    -RestorePointLabel ${{ variables.source_sql_pool }} | Out-Null

#get database id of restore point
$pool = Get-AzSynapseSqlPool `
            -ResourceGroupName ${{ variables.source_rg }} `
            -WorkspaceName ${{ variables.source_ws_full }} `
            -Name ${{ variables.source_sql_pool }}
            
$databaseId = $pool.Id -replace "Microsoft.Synapse", "Microsoft.Sql" `
    -replace "workspaces", "servers" `
    -replace "sqlPools", "databases"

#get restore point
$restorePoint = $Pool | Get-AzSynapseSqlPoolRestorePoint | Select-Object -Last 1

#clone sql pool
Restore-AzSynapseSqlPool `
    -FromRestorePoint `
    -RestorePoint $restorePoint.RestorePointCreationDate `
    -TargetSqlPoolName ${{ variables.target_sql_pool }} `
    -ResourceGroupName ${{ variables.target_rg }} `
    -WorkspaceName ${{ variables.target_ws_full }} `
    -ResourceId $databaseId `
    -PerformanceLevel ${{ parameters.database_scale }} | Out-Null

Change Encryption Key

if ( "${{ parameters.target_ws }}" -ne "${{ parameters.source_ws }}" ) {
    #set key vault key name for target workspace
    if ("${{ parameters.target_ws }}" -eq "ws-prod") {
        $targetWsKvKeyName = "key-synapse-prod"
    }
    elseif ("${{ parameters.target_ws }}" -eq "ws-test") {
        $targetWsKvKeyName = "key-synapse-test"
    }
    
    #delete inactive keys from target workspace
    $Keys = az synapse workspace key list `
                --workspace-name ${{ variables.target_ws_full }} `
                --resource-group ${{ variables.target_rg }} `
                --only-show-errors | ConvertFrom-Json

    for ($i=0; $i -lt $Keys.Count; $i++) {
        $wsKeyName = $Keys[$i].name
        $isActive = $Keys[$i].isActiveCmk

        if ($isActive -eq $false) {
            az synapse workspace key delete `
                --name $WsKeyName `
                --workspace-name ${{ variables.target_ws_full }} `
                --resource-group ${{ variables.target_rg }} `
                --yes `
                --only-show-errors | Out-Null
        }
    }
    
    #create source workspace key in target workspace
    az synapse workspace key create `
          --name $targetWsKvKeyName `
          --workspace-name ${{ variables.target_ws_full }} `
          --resource-group ${{ variables.target_rg }} `
          --key-identifier ${{ variables.kv_url_prefix }}$targetWsKvKeyName `
          --only-show-errors | Out-Null
    
    #update workspace key      
    az synapse workspace update `
          --name ${{ variables.target_ws_full }} `
          --resource-group ${{ variables.target_rg }} `
          --key-name $targetWsKvKeyName `
          --only-show-errors | Out-Null
}

Reinstate resource lock

if ( $$(resourceLockExists) ) {
    az lock create --lock-type CanNotDelete `
          --name lock-${{ variables.target_rg }} `
          --notes "Managed By Azure DevOps Pipelines" `
          --resource-group ${{ variables.target_rg }}
}