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 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 parameters:

$sourceEnvironment = ${{ parameters.source_env }}
$sourceResourceGroup = ${{ parameters.source_rg }}
$sourceWorkspace = ${{ parameters.source_ws }}
$sourceSqlPool = ${{ parameters.source_sql_pool }}
$targetEnvironment = ${{ parameters.target_env }}
$targetResourceGroup = ${{ parameters.target_rg }}
$targetWorkspace = ${{ parameters.target_ws }}
$targetSqlPool = ${{ parameters.target_sql_pool }}
$keepTargetSqlPool = ${{ parameters.keep_target_sql_pool }}
$databaseScale = ${{ parameters.database_scale }}

YAML variables:

Key Vault URL Prefix= ${{ variables.kv_url_prefix }}

Deletes the workspace lock

Query Azure locks for specific name (1)
Set lock exists variable for later resource lock reinstatement task (2)
Delete lock (3)

- task: AzureCLI@2
  name: delete_lock
  displayName: Delete workspace lock
  inputs:
    azureSubscription: ${{ variables.azure_connection }}
    ScriptType: ps
    ScriptLocation: inlineScript
    inlineScript: |
      #1
      $resourceLocks = $(az lock list `
                            --resource-group ${{ parameters.target_rg }} `
                            --query "[?name=='lock-${{ parameters.target_rg }}']" `
                            | ConvertFrom-Json)

      $resourceLockExists = $resourceLocks.count -eq 1
      
      #2
      Write-Host "##vso[task.setvariable variable=resource_lock_exists;isOutput=true]$resourceLockExists"
      
      #3
      if ( $resourceLockExists ) {
        Write-Host "Removing workspace lock 'lock-${{ parameters.target_rg }}'"
        az lock delete `
          --name lock-${{ variables.target_resource_group }} `
          --resource-group ${{ parameters.target_rg }}
      }

Change encryption key

Set key vault key name for source workspace (1)
Delete inactive keys from target workspace (2)
Create source workspace key in target workspace (3)
Update workspace key (4)

- task: AzureCLI@2
  name: change_key1
  displayName: Change encryption key
  inputs:
    azureSubscription: ${{ variables.azure_connection }}
    scriptType: ps
    scriptLocation: inlineScript
    inlineScript: |
      #1
      if ("${{ parameters.source_ws }}" -eq "ws-prd") {
          $SourceWsKvKeyName = "key-prd"
      }
      else
          $SourceWsKvKeyName = "key-test"
      }
  
      #2
      $Keys = az synapse workspace key list `
                --workspace-name ${{ parameters.target_ws }} `
                --resource-group ${{ parameters.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 ${{ parameters.target_ws }} `
                --resource-group ${{ parameters.target_rg }} `
                --yes --only-show-errors | Out-Null
          }
      }
      #3
      az synapse workspace key create `
          --name $SourceWsKvKeyName `
          --workspace-name ${{ parameters.target_ws }} `
          --resource-group ${{ parameters.target_rg }} `
          --key-identifier ${{ variables.kv_url_prefix }}$SourceWsKvKeyName `
          --only-show-errors | Out-Null
          
      #4
      az synapse workspace update `
          --name ${{ parameters.target_ws }} `
          --resource-group ${{ parameters.target_rg }} `
          --key-name $SourceWsKvKeyName `
          --only-show-errors | Out-Null

Delete existing target SQL pool

Check target SQL pool exists (1)
Check status of target SQL pool and resume if paused (2)
Create restore point name for retained target SQL pool if parameter ‘Keep target workspace SQL pool if it exists?’ is checked (3)
Create restore point of target SQL pool (4)
Get database id of restore point (5)
Clone SQL pool (6)
Pause cloned SQL pool (7)
Delete SQL pool (8)

- task: AzurePowerShell@5
  name: manage_database
  displayName: Manage existing database
  inputs:
    azureSubscription: ${{ variables.azure_connection }}
    azurePowerShellVersion: LatestVersion
    ScriptType: 'inlineScript'
    inline: |
      #1
      $targetPoolExists = Test-AzSynapseSqlPool `
                            -WorkspaceName ${{ parameters.target_ws }} `
                            -Name ${{ parameters.target_sql_pool }}
                            
      if( $targetPoolExists ) {
        if( "${{ parameters.keep_target_sql_pool }}" -eq $true ) {
  
            #2
            $pool = Get-AzSynapseSqlPool `
                      -ResourceGroupName ${{ parameters.target_rg }} `
                      -WorkspaceName ${{ parameters.target_ws }} `
                      -Name ${{ parameters.target_sql_pool }} | Select-Object Status
            $poolStatus = $pool.Status
            
            if ("$poolStatus" -eq "Paused") {
                Resume-AzSynapseSqlPool `
                    -ResourceGroupName ${{ parameters.target_rg }} `
                    -WorkspaceName ${{ parameters.target_ws }} `
                    -Name ${{ parameters.target_sql_pool }} | Out-Null
            }
  
            #3
            $restoreDatetime = Get-Date -Format "dd_MM_yyyy_HH.mm"
            $backedUpSqlPool = '${{ variables.target_sql_pool }}' + "_" + $restoreDatetime
            
            #4
            New-AzSynapseSqlPoolRestorePoint `
                -ResourceGroupName ${{ parameters.target_rg }} `
                -WorkspaceName ${{ parameters.target_ws }} `
                -Name ${{ parameters.target_sql_pool }} `
                -RestorePointLabel $backedUpSqlPool | Out-Null
            
            #5
            $pool = Get-AzSynapseSqlPool `
                        -ResourceGroupName ${{ parameters.target_rg }} `
                        -WorkspaceName ${{ parameters.target_ws }} `
                        -Name ${{ parameters.target_sql_pool }}
                        
            $databaseId = $pool.Id -replace "Microsoft.Synapse", "Microsoft.Sql" `
                -replace "workspaces", "servers" `
                -replace "sqlPools", "databases"
            
            #6
            $restorePoint = $pool | Get-AzSynapseSqlPoolRestorePoint |
                                       Where-Object {$PSItem.RestorePointLabel -eq $backedUpSqlPool}
                               
            Restore-AzSynapseSqlPool `
                -FromRestorePoint `
                -RestorePoint $restore_point.RestorePointCreationDate `
                -TargetSqlPoolName $az_retained_target_sql_pool `
                -ResourceGroupName ${{ parameters.target_rg }} `
                -WorkspaceName ${{ parameters.target_ws }} `
                -ResourceId $databaseId `
                -PerformanceLevel ${{ parameters.database_scale }} | Out-Null
            
            #7
            Suspend-AzSynapseSqlPool `
                -ResourceGroupName ${{ parameters.target_rg }} `
                -WorkspaceName ${{ parameters.target_ws }} `
                -Name $backedUpSqlPool | Out-Null  
        }
        #8
        Remove-AzSynapseSqlPool `
            -WorkspaceName ${{ parameters.target_ws }} `
            -Name ${{ parameters.target_sql_pool }} -Force
      }

Clone database

Check target SQL pool status (1)
Resume SQL pool if paused (2)
Create restore point of source SQL pool (3)
Get database id of restore point (4)
Get restore point (5)
Clone SQL pool (6)

- task: AzurePowerShell@5
  name: clone_database
  displayName: Clone database
  inputs:
    azureSubscription: ${{ variables.azure_connection }}
    azurePowerShellVersion: 'LatestVersion'
    ScriptType: 'inlineScript'
    inline: |
      #1
      $pool = Get-AzSynapseSqlPool `
                  -ResourceGroupName ${{ parameters.source_rg }} `
                  -WorkspaceName ${{ parameters.source_ws }} `
                  -Name ${{ parameters.source_sql_pool }} | Select-Object Status
      $poolStatus = $pool.Status
      
      #2
      if ("$poolStatus" -eq "Paused") {
          Resume-AzSynapseSqlPool `
            -ResourceGroupName ${{ parameters.source_rg }} `
            -WorkspaceName ${{ parameters.source_ws }} `
            -Name ${{ parameters.source_sql_pool }} | Out-Null
      }
  
      #3
      New-AzSynapseSqlPoolRestorePoint `
          -ResourceGroupName ${{ parameters.source_rg }} `
          -WorkspaceName ${{ parameters.source_ws }} `
          -Name ${{ parameters.source_sql_pool }} `
          -RestorePointLabel ${{ parameters.source_sql_pool }} | Out-Null
      
      #4
      $pool = Get-AzSynapseSqlPool `
                -ResourceGroupName ${{ parameters.source_rg }} `
                -WorkspaceName ${{ parameters.source_ws }} `
                -Name ${{ parameters.source_sql_pool }}
                
      $ws = Get-AzSynapseWorkspace `
                -name ${{ parameters.source_ws }} `
                -ResourceGroupName ${{ parameters.source_rg }}
                
      $databaseId = $pool.Id -replace "Microsoft.Synapse", "Microsoft.Sql" `
          -replace "workspaces", "servers" `
          -replace "sqlPools", "databases" `
          -replace "${{ parameters.source_rg }}", $ws.ManagedResourceGroupName
      
      #5
      $restorePoint = $Pool | Get-AzSynapseSqlPoolRestorePoint | Select-Object -Last 1
      Restore-AzSynapseSqlPool `
          -FromRestorePoint `
          -RestorePoint
          
      #6
      $restorePoint.RestorePointCreationDate `
          -TargetSqlPoolName ${{ parameters.target_sql_pool }} `
          -ResourceGroupName ${{ parameters.target_rg }} `
          -WorkspaceName ${{ parameters.target_ws }} `
          -ResourceId $databaseId `
          -PerformanceLevel ${{ parameters.database_scale }} | Out-Null

Change encryption key

Set key vault key name for source workspace (1)
Delete inactive keys from target workspace (2)
Create source workspace key in target workspace (3)
Update workspace key (4)

- task: AzureCLI@2
  name: change_key2
  displayName: Change encryption key
  inputs:
    azureSubscription: ${{ variables.azure_connection }}
    scriptType: ps
    scriptLocation: inlineScript
    inlineScript: |
      #1
      if ( "${{ parameters.target_workspace }}" -eq "ws-prod") {
          $TargetWsKvKeyName = "key-synapse-ws-prod"
      }
      else {
          $TargetWsKvKeyName = "key-synapse-ws-test"
      }
      
      #2
      $Keys = az synapse workspace key list `
                --workspace-name ${{ variables.target_workspace_full_name }} `
                --resource-group ${{ variables.target_resource_group }} `
                --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_workspace_full_name }} `
                --resource-group ${{ variables.target_resource_group }} `
                --yes --only-show-errors | Out-Null
          }
      }
      
      #3
      az synapse workspace key create `
          --name $TargetWsKvKeyName `
          --workspace-name ${{ variables.target_workspace_full_name }} `
          --resource-group ${{ variables.target_resource_group }} `
          --key-identifier ${{ variables.kv_url_prefix }}$TargetWsKvKeyName `
          --only-show-errors | Out-Null
      
      #4    
      az synapse workspace update `
          --name ${{ variables.target_workspace_full_name }} `
          --resource-group ${{ variables.target_resource_group }} `
          --key-name $TargetWsKvKeyName `
          --only-show-errors | Out-Null

Create resource lock if one was in place

Create resource lock (1)

- task: AzureCLI@2
    name: reinstate_lock
    displayName: Reinstate workspace lock
    inputs:
      azureSubscription: ${{ variables.azure_connection }}
      ScriptType: ps
      ScriptLocation: inlineScript
      inlineScript: |
        #1
        if ( $$(resourceLockExists) ) {
            az lock create `
                  --lock-type CanNotDelete `
                  --name lock-${{ variables.target_rg }} `
                  --notes "Managed By Azure DevOps Pipelines" `
                  --resource-group ${{ parameters.target_rg }}
        }