Example YAML Pipeline for a SQL Change Automation Project

trigger:
- main

# This pipeline uses a self-hosted agent which has access to the
# SQL Server we are deploying to as well as to the SQL Clone service
# https://docs.microsoft.com/en-us/azure/devops/pipelines/agents/v2-windows
pool: YourPoolName

steps:

# First we build our project
- task: RedgateSqlChangeAutomationBuild@4
  displayName: SQL Change Automation Build
  inputs:
    operation: 'Build SQL Change Automation Project'
    sqlProj: 'LiveDemo/LiveDemo.sqlproj'
    packageName: 'LD'
    tempServerTypeBuild: 'sqlServer'
    tempServerNameBuild: '.'
    authMethodBuild: 'windowsAuth'
    nugetPackageVersionSelector: 'Specific'
    nugetPackageVersion: '1.0'
    nugetPackageVersionUseBuildId: true
    requiredVersionOfDlma: 'latestInstalled'

# The build publishes an artifact. We now download that
# to our self-hosted agent
- task: DownloadPipelineArtifact@2
  displayName: Download build artifact
  inputs:
    buildType: 'current'
    artifactName: 'Database Build Artifact'
    targetPath: '$(System.DefaultWorkingDirectory)'

# This PowerShell reads the name of the source branch,
# replaces any / characters with _'s, and sets this as a variable.
# We will be able to reference $(PRString) in future steps which
# we will use to name a database. This is simply an example of
# how it may be useful to use variables.
- task: PowerShell@2
  displayName: Set $(PRString) variable
  inputs:
    targetType: 'inline'
    script: |
      $path="$(Build.SourceBranch)"
      # Write-Host "$path"
      $path= $path -replace "/", "_"
      Write-Host "##vso[task.setvariable variable=PRString]$path"

# We call SQL Clone to create a database clone just for this PR
# The clone name contains the source branch for this PR
- task: RedgateSqlCloneClone@4
  displayName: Clone database
  inputs:
    cloneServer: 'sqlclone'
    imageNameForClone: 'LiveDemo'
    cloneSqlServer: 'YourServerName'
    cloneName: 'LiveDemo_$(PRString)'
    deleteClone: true

# We call SQL Change Automation to create a Release Artifact 
# against the clone. We export the Release Artifact, which may
# be useful for reference by reviewers of the PR.
# In real-world cases, a highly-available fileshare
# is recommended for Release Artifacts.
- task: RedgateSqlChangeAutomationRelease@4
  displayName: Create Release Artifact with SQL Change Automation
  inputs:
    Operation: 'Create'
    NuGetFile: '$(System.DefaultWorkingDirectory)'
    ExportPath: 'C:\DBAutomation\ReleaseArtifacts\$(PRString)\$(Build.BuildNumber)'
    TargetDatabaseConnectionType: 'explicit'
    TargetDatabaseServer: '.'
    TargetDatabaseName: 'LiveDemo_$(PRString)'
    TargetAuthMethod: 'windowsAuth'
    AbortOnWarning: 'none'
    requiredVersionOfDlma: 'latestInstalled'


# This PowerShell checks the deployment script in our Release Artifact for 
# TSQL commands which we want to warn or throw an error on
# A pipeline variable is used to allow for this to be re-run with an 
# override/bypass if an exception is to be made 

- task: PowerShell@2
  displayName: Check TargetedDeploymentScript.sql for problem commands
  inputs:
    targetType: 'inline'
    script: |
      If  (${env:BYPASS_CHECK_OF_TARGETEDDEPLOYMENTSCRIPT_SQL_FOR_PROBLEM_COMMANDS} -eq 'True')
      {
        Write-Output "Bypassing check of TargetedDeploymentScript.sql based on pipeline variables."
        exit 0
      }
      
      $targetedDeploymentScript = 'C:\DBAutomation\ReleaseArtifacts\$(PRString)\$(Build.BuildNumber)\TargetedDeploymentScript.sql'
      $searchPatternWarnings = @(
          "CREATE",
          "EXEC[\W]+\(.*",
          "sp_executesql"
      )
      
      $searchPatternErrors = @(
          "GRANT"
          "TRUNCATE",
          "DROP",
          "DELETE",
          "sp_rename"
      )
      
      
      $warnings = @( 
          Foreach ($s in $searchPatternWarnings)
          {
              Select-String -Path $targetedDeploymentScript -Pattern "(^|\s)$s($|\s)" -AllMatches | `
                  # Exclude lines specific to __SchemaSnapshot objects
                  Select-String -Pattern "[__SchemaSnapshot]" -NotMatch -SimpleMatch |
                  Select-Object LineNumber, Line
          }
      )
      
      $ct = $warnings.Count
      if ($ct -gt 0)
      {
          Write-Output "##vso[task.logissue type=warning;code=100;] $($ct) warnings found"
          Write-Output $warnings | Format-Table -RepeatHeader
          
      } else 
      {
         Write-Output "No warnings found."
      }
      
      $errors = @( 
          Foreach ($s in $searchPatternErrors)
          {
              Select-String -Path $targetedDeploymentScript -Pattern "(^|\s)$s($|\s)" -AllMatches | `
                  # Exclude lines specific to __SchemaSnapshot objects
                  Select-String -Pattern "[__SchemaSnapshot]" -NotMatch -SimpleMatch |
                  Select-Object LineNumber, Line
          }
      )
      
      $ct = $errors.Count
      if ($ct -gt 0)
      {
          Write-Output "##vso[task.logissue type=error;code=100;] $($ct) errors found"
          Write-Output $errors | Format-Table -RepeatHeader
          Exit 1
      } else 
      {
         Write-Output "No errors found."
      }

# We call SQL Change Automation to deploy the Release Artifact
# to the clone.
- task: RedgateSqlChangeAutomationRelease@4
  displayName: Deploy Release Artifact with SQL Change Automation
  inputs:
    Operation: 'DeployFromResources'
    ImportPath: 'C:\DBAutomation\ReleaseArtifacts\$(PRString)\$(Build.BuildNumber)'
    TargetDatabaseConnectionType: 'explicit'
    TargetDatabaseServer: '.'
    TargetDatabaseName: 'LiveDemo_$(PRString)'
    TargetAuthMethod: 'windowsAuth'
    requiredVersionOfDlma: 'latestInstalled'