deploy-synapse.yml

parameters:
  group: variable-group
  azureResourceManagerConnection: 'Service_Connection_Name'
  deploymentName: stage_deployment
  displayName: SynapseDeployment
  environment: Development

jobs:

- deployment: ${{ parameters.deploymentName }}
  displayName: ${{ parameters.displayName }}
  environment: ${{ parameters.environment }}

- job: DeployARM
  displayName: Deploy ARM templates

  steps:
  
  - task: AzureResourceManagerTemplateDeployment@3
    displayName: 'Synapse Workspace Deployment'
    inputs:
      azureResourceManagerConnection: ${{ parameters.azureResourceManagerConnection }}
      subscriptionId: '$(SubscriptionId)'
      resourceGroupName: '$(RGName)'
      location: '$(Location)'
      csmFile: '$(System.DefaultWorkingDirectory)/AzureResources/ARMTemplates/synapse_azuredeploy.json'
      csmParametersFile: '$(System.DefaultWorkingDirectory)/AzureResources/Parameters/$(Environment)/synapse_azuredeploy.parameters.json'
      deploymentOutputs: 'armOutputs'

  - task: AzurePowerShell@5
    displayName: Assign Workspace Admins
    env:
      ARM_OUTPUTS: $(armOutputs)
    inputs:
      azureSubscription: ${{ parameters.azureResourceManagerConnection }}
      ScriptType: 'InlineScript'
      Inline: |
        $output = $env:ARM_OUTPUTS | ConvertFrom-Json
        $workspaceName = $output.synapseWorkspace.value
        Install-Module -Name "Az.Synapse" -Confirm:$false  -Scope CurrentUser  -Force;
        Import-Module Az.Synapse
        try{
          New-AzSynapseRoleAssignment -WorkspaceName $workspaceName -RoleDefinitionName "Synapse Administrator" -ObjectId "00000000-0000-0000-0000-000000000000" 
        }
        catch{
          Write-Host "An error occurred:"
          Write-Host $_
        }
        
      azurePowerShellVersion: latestVersion
      
      
- job: DeploySQLObjects
  dependsOn: 
     - DeployARM
  displayName: Deploy SQL Objects for SQL Serverless

  steps:

  - task: AzureKeyVault@1
    inputs:
      azureSubscription: ${{ parameters.azureSubscription }}
      KeyVaultName: ${{ parameters.keyVaultName }}
      SecretsFilter: '*'
      RunAsPreJob: true

  - task: CmdLine@2
    displayName: Create Serverless Database
    inputs:
      script: 'sqlcmd -S $(SynapseServerless)-ondemand.sql.azuresynapse.net -U $(SynapseUser) -P $(SynapsePassword) -i "SynapseObjects/shared/CreateDatabase.sql"' 
      workingDirectory: $(System.DefaultWorkingDirectory)

  - task: CmdLine@2
    displayName: Create Credentials
    inputs:
      script: 'sqlcmd -S $(SynapseServerless)-ondemand.sql.azuresynapse.net -d ServerlessSQL -U $(SynapseUser) -P $(SynapsePassword) -i "SynapseObjects/$(Environment)/SynapseDatabaseObjects.sql"'
      workingDirectory: $(System.DefaultWorkingDirectory)

  - task: CmdLine@2
    displayName: Create Schema
    inputs:
      script: 'sqlcmd -S $(SynapseServerless)-ondemand.sql.azuresynapse.net -d ServerlessSQL -U $(SynapseUser) -P $(SynapsePassword) -i "SynapseObjects/shared/CreateSchema.sql"'
      workingDirectory: $(System.DefaultWorkingDirectory)

  - task: CmdLine@2
    displayName: Create Schema Proc
    inputs:
      script: 'sqlcmd -S $(SynapseServerless)-ondemand.sql.azuresynapse.net -d ServerlessSQL -U $(SynapseUser) -P $(SynapsePassword) -i "SynapseObjects/shared/CreateSchemaProc.sql"'
      workingDirectory: $(System.DefaultWorkingDirectory)

  - task: CmdLine@2
    displayName: Create View Proc
    inputs:
      script: 'sqlcmd -S $(SynapseServerless)-ondemand.sql.azuresynapse.net -d ServerlessSQL -U $(SynapseUser) -P $(SynapsePassword) -i "SynapseObjects/shared/CreateDeltaViewProc.sql"'
      workingDirectory: $(System.DefaultWorkingDirectory)