Redgate Deploy related code

BuildCmd.ps1

Import-Module $PSScriptRoot\RedgateDeployFunctions.ps1

# Take a snapshot of the baseline? Set to true if testing first deployments.
[bool]$snapshotBaseline = $true

<#########################################################
BUILD VALIDATION (deploy to new empty database)
#########################################################>


$dbHost= $($env:BUILDDB_HOST) 
$dbPort= $($env:BUILDDB_PORT) 
$buildDBName = $($env:BUILDDB_NAME) 
$workingDirectory = $($env:AGENT_BUILD_DIRECTORY)

$buildConnectionString = "Data Source= $dbHost, $dbPort ;Integrated Security=true; Database=master"
$flywayLocations = (Resolve-Path "$PSScriptRoot\..\migrations").ToString()
$FlywayConfig = (Resolve-Path "$PSScriptRoot\..\").ToString() + 'flyway.conf'


Write-Output "### Create (or recreate) database $buildDBName on $dbContainerName at $dbHost, $dbPort to perform build ###"
New-BuildDatabase -ConnectionString $buildConnectionString -BuildDatabaseName $buildDBName


if ($snapshotBaseline) {
    #Flyway Migrate to baseline 
    Write-Output "### Flyway migrating to the baseline ###"
    Invoke-FlywayCmd -TargetHost $dbHost -Port $dbPort -DatabaseName $buildDBName -FlywayLocations $flywayLocations -FlywayConfig $FlywayConfig -FlywayCommand 'migrate' -Target '1'

    Write-Output "### Snapshotting the baseline schema ###"
    New-SQLCompareSnapshotCmd -Src $workingDirectory -TargetHost $dbHost -Port  $dbPort -DatabaseName $buildDBName -SnapshotName 'BuildBaseline.snp'
}


#Migrate flyway to latest (no target specified)
Write-Output "### Flyway migrate against build database ###"
Invoke-FlywayCmd -TargetHost $dbHost -Port $dbPort -DatabaseName $buildDBName -FlywayLocations $flywayLocations -FlywayConfig $FlywayConfig -FlywayCommand 'migrate'

Write-Output "### Snapshot build database to $workingDirectory ###"
#Take a snapshot after the end of deployment (the build snapshot)
New-SQLCompareSnapshotCmd -Src $workingDirectory -TargetHost $dbHost -Port  $dbPort -DatabaseName $buildDBName -SnapshotName 'BuildResult.snp'

Write-Output "### All done! ###"

DeployCmd.ps1

Import-Module $PSScriptRoot\RedgateDeployFunctions.ps1

$dbHost= $($env:DEPLOYDB_HOST) 
$dbPort= $($env:DEPLOYDB_PORT) 
$deployDBName =  $($env:DEPLOYDB_NAME) 
$reportOutputPath =  $($env:REPORT_OUPUT_PATH) 
$workingDirectory = $($env:WORKING_DIRECTORY)
$artifactAlias = $($env:ARTIFACT_ALIAS)

$deployConnectionString = "Data Source= $dbHost, $dbPort ;Integrated Security=true; Database=$deployDBName"
$flywayLocations = (Resolve-Path "$PSScriptRoot\..\migrations").ToString()
$FlywayConfig = (Resolve-Path "$PSScriptRoot\..\").ToString() + 'flyway.conf'

# Use a snapshot of the baseline from the build? Set to true if testing first deployments.
[bool]$snapshotBaseline = $true


if ($snapshotBaseline) {
    #Flyway baseline 
    Write-Output "### Flyway baseline ###"
    Invoke-FlywayCmd -TargetHost $dbHost -Port $dbPort -DatabaseName $deployDBName -FlywayLocations $flywayLocations -FlywayConfig $FlywayConfig -FlywayCommand 'baseline'
}

#Flyway Dry run
Write-Output '### Flyway Dry Run ###'
Invoke-FlywayCmd -TargetHost $dbHost -Port $dbPort -DatabaseName $deployDBName -User $dbUser -Password $dbPassword -FlywayLocations $flywayLocations -FlywayConfig $FlywayConfig -FlywayCommand 'migrate' -DryRunFileName "$reportOutputPath\DryRun.sql"

New-SQLCompareSnapshotCmd -Src $workingDirectory -TargetHost $dbHost -Port $dbPort -DatabaseName  $deployDBName -User $dbUser -Password $dbPassword -SnapshotName "BeforeMigrate.snp"

Write-Output "### Reading schema snapshot from target database ###"

[string]$SnapshotPackageFileName = 'PriorDeployment.snp'
[string]$PriorDeploymentSnapshotPath =  Join-Path -Path $workingDirectory -ChildPath $SnapshotPackageFileName

Read-SnapshotFromDatabase -ConnectionString $deployConnectionString -SnapshotPath "$PriorDeploymentSnapshotPath"

#Drift check
#Compare snapshot from prior deployment to target database's current state
#If we don't have a snapshot from prior deployment, use snapshot of the baseline
Write-Output '### Drift Check ###'

if (Test-Path $PriorDeploymentSnapshotPath)  {
    New-SQLCompareReportCmd -WorkingDirectory $workingDirectory -Snapshot1FileName 'PriorDeployment.snp' -Snapshot2FileName  'BeforeMigrate.snp' -ReportPathAndFileName "$reportOutputPath\DriftReport.html"
} else {
    if ($snapshotBaseline) {
        New-SQLCompareReportCmd -WorkingDirectory $workingDirectory -Snapshot1FileName "\$artifactAlias\baselinesnapshot\BuildBaseline.snp" -Snapshot2FileName  'BeforeMigrate.snp' -ReportPathAndFileName "$reportOutputPath\DriftReport.html"
    } else {
        Write-Output "No snapshot from prior deployment found and no snapshot of baseline, skipping Drift Report."
    }
}


#Change report
# Compare snapshot after end of build to snapshot of target database's current state
Write-Output '### Change Report ###'
New-SQLCompareReportCmd -WorkingDirectory $workingDirectory -Snapshot1FileName "\$artifactAlias\buildsnapshot\BuildResult.snp" -Snapshot2FileName  "BeforeMigrate.snp" -ReportPathAndFileName "$reportOutputPath\ChangeReport.html"


#Migrate flyway to latest (no target specified)
Write-Output '### Flyway Migrate ###'
Invoke-FlywayCmd -TargetHost $dbHost -Port $dbPort -DatabaseName $deployDBName -User $dbUser -Password $dbPassword -FlywayLocations $flywayLocations -FlywayConfig $FlywayConfig -FlywayCommand 'migrate'

[string]$SnapshotPath =  Join-Path -Path $workingDirectory -ChildPath "\$artifactAlias\buildsnapshot\BuildResult.snp"

if (Test-Path $SnapshotPath) {
    Write-Output "Reading schema snapshot from file system"
    $HexString = Read-SnapshotFromDisk -SnapshotPath $SnapshotPath

    if ($HexString -ne $null) {
        Write-Output "### Writing schema snapshot to target database ###"
        Write-SnapshotToDatabase -ConnectionString $deployConnectionString -HexString $HexString 
    } else {
        Write-Output "### Found schema snapshot file but failed to read it successfully ###"
    }
} else {
    Write-Output "Schema snapshot not found, could not write snapshot to database."
}

RedgateDeployFunctions.ps1

function New-SpawnDataContainer {
  Param (
    [Parameter(Mandatory=$true, Position=0)] [string]$dataImageName,
    [Parameter(Mandatory=$false, Position=0)] [string]$lifetimeMinutes

  )

  try {

    Write-Output "Creating a new Spawn data container from $dataImageName"

    # Build parameters to run with Docker
    $params = 'run', 
      '--rm',
      'redgatefoundry/spawnctl',
      'create', 'data-container',
      '--image', "$dataImageName",
      '-q',
      '--accessToken', "$($env:SPAWNCTL_ACCESS_TOKEN)"


      # Add lifetime if specified
      if (-not([string]::IsNullOrEmpty($lifetimeMinutes) )) 
      { 
          $params += "--lifetime", "${lifetimeMinutes}m" 
      }

    $dataContainer = & 'docker' @params


    Write-Output "Get metadata for Spawn data container"
    $params = 'run', 
      '--rm',
      'redgatefoundry/spawnctl',
      'get', 'data-container', "$dataContainer",
      '-o', 'json',
      '--accessToken', "$($env:SPAWNCTL_ACCESS_TOKEN)"

    $dataContainerInfo = & 'docker' @params | ConvertFrom-Json

    Write-Output "Returning info for data container $dataContainerInfo.host,$dataContainerInfo.port"
    return $dataContainerInfo

  }
  catch {
    Write-Error "Error invoking spawn docker container: $_"
    return $null
  }
}



function New-BuildDatabase {
  
  [CmdletBinding()]
  Param
  (
    [Parameter(Mandatory=$true, Position=0)]
      [string]$ConnectionString,

    [Parameter(Mandatory=$true, Position=1)]
      [string]$BuildDatabaseName
  )

 
  $SqlConnection = New-Object System.Data.SqlClient.SqlConnection
  $SqlConnection.ConnectionString = $ConnectionString
  $SqlCmd = New-Object System.Data.SqlClient.SqlCommand
  $SqlCmd.Connection = $SqlConnection

  $Query = "
    IF DB_ID('$BuildDatabaseName') IS NOT NULL
    BEGIN
        ALTER DATABASE $BuildDatabaseName
            SET
                SINGLE_USER
            WITH ROLLBACK IMMEDIATE;
        DROP DATABASE $BuildDatabaseName;
    END;  
  
    CREATE DATABASE [$BuildDatabaseName];"
  $SqlCmd.CommandText = "$Query"

  try {
    $SqlConnection.Open()
    $SqlCmd.ExecuteScalar()
  }
  catch {
    Write-Error "Failed create build database: $_"
  }
  finally{
    $SqlConnection.Close()
    $SqlCmd.Dispose()
  }

}

function Invoke-FlywayCmd {
  [CmdletBinding()]
  Param (
    [Parameter(Mandatory=$true, Position=1)] [string]$TargetHost,
    [Parameter(Mandatory=$true, Position=2)] [string]$Port,
    [Parameter(Mandatory=$true, Position=3)] [string]$DatabaseName,
    [Parameter(Mandatory=$false, Position=4)] [string]$User,
    [Parameter(Mandatory=$false, Position=5)] [string]$Password,
    [Parameter(Mandatory=$true, Position=6)] [string]$FlywayLocations,
    [Parameter(Mandatory=$true, Position=7)] [string]$FlywayConfig,
    [Parameter(Mandatory=$true, Position=8)] [string]$FlywayCommand,
    [Parameter(Mandatory=$false, Position=9)] [string]$Target,
    [Parameter(Mandatory=$false, Position=10)] [string]$DryRunFileName
  )

  try {
      # Build parameters r
      $params = 
        '-teams', 
        "-licenseKey=$($env:FLYWAY_LICENSE_KEY)",
        "-locations=filesystem:$FlywayLocations",
        "-configFiles=$FlywayConfig",
        "-url=jdbc:sqlserver://${TargetHost}:$Port;databaseName=$DatabaseName;integratedSecurity=true",
        # "-user=$User",
        # "-password=$Password",
        "$FlywayCommand" 

      # Add target migration number if specified
      if (-not([string]::IsNullOrEmpty($Target) )) 
        { $params += "-target=$Target" }

      # Make it a DryRun if a filename is specified
      if (-not([string]::IsNullOrEmpty($DryRunFileName) )) 
        { $params += "-dryRunOutput=$DryRunFileName" }

      & 'flyway' @params
  }
  catch {
    Write-Error "Failed to run Flyway command line: $_"
    return $null
  }  
}


function Invoke-FlywayDocker {
  [CmdletBinding()]
  Param (
    [Parameter(Mandatory=$true, Position=0)] [string]$Src,
    [Parameter(Mandatory=$true, Position=1)] [string]$TargetHost,
    [Parameter(Mandatory=$true, Position=2)] [string]$Port,
    [Parameter(Mandatory=$true, Position=3)] [string]$DatabaseName,
    [Parameter(Mandatory=$true, Position=4)] [string]$User,
    [Parameter(Mandatory=$true, Position=5)] [string]$Password,
    [Parameter(Mandatory=$true, Position=6)] [string]$FlywayLocations,
    [Parameter(Mandatory=$true, Position=7)] [string]$FlywayConfig,
    [Parameter(Mandatory=$true, Position=8)] [string]$FlywayCommand,
    [Parameter(Mandatory=$false, Position=9)] [string]$Target,
    [Parameter(Mandatory=$false, Position=10)] [string]$DryRunFileName
  )

  try {
      # Build parameters to run with Docker
      $params = 'run', 
        '-e', 'FLYWAY_EDITION=enterprise',
        '-e', "FLYWAY_LICENSE_KEY=$($env:FLYWAY_LICENSE_KEY)",
        '-v', "${Src}:/scripts",
        '-v', "$FlywayConfig/:/flyway/conf",
        '-v', "$FlywayLocations/:/flyway/sql",
        'flyway/flyway',
#        'flyway/flyway-azure:latest-alpine', 'flyway',
        "$FlywayCommand" , 
        "-url=jdbc:sqlserver://${TargetHost}:$Port;databaseName=$DatabaseName;",
        "-user=$User",
        "-password=$Password"

      # Add target if specified
      if (-not([string]::IsNullOrEmpty($Target) )) 
        { $params += "-target=$Target" }

      # Make it a DryRun if a filename is specified
      if (-not([string]::IsNullOrEmpty($DryRunFileName) )) 
        { $params += "-dryRunOutput=/scripts/$DryRunFileName" }

      & 'docker' @params
  }
  catch {
    Write-Error "Failed to run Flyway Docker container: $_"
    return $null
  }  
}

function New-SQLCompareSnapshotCmd {

  [CmdletBinding()]
  Param (
    [Parameter(Mandatory=$true, Position=0)] [string]$Src,
    [Parameter(Mandatory=$true, Position=1)] [string]$TargetHost,
    [Parameter(Mandatory=$true, Position=2)] [string]$Port,
    [Parameter(Mandatory=$true, Position=3)] [string]$DatabaseName,
    [Parameter(Mandatory=$false, Position=4)] [string]$User,
    [Parameter(Mandatory=$false, Position=5)] [string]$Password,
    [Parameter(Mandatory=$true, Position=6)] [string]$SnapshotName
  )  
  try {

    # Build parameters to run with sql compare command line
    $params = 
      "/activateserial=$($env:SQLCOMPARE_LICENSE_KEY)",
      "/s1:$TargetHost,$Port",
      "/db1:$DatabaseName",
      # "/u1:$User",
      # "/p1:$Password",
      "/makeSnapshot:$Src\$SnapshotName"

#    &"${env:ProgramFiles(x86)}\Red Gate\SQL Compare 14\sqlcompare.exe" @params
     &"C:\Program Files (x86)\Red Gate\SQL Change Automation PowerShell\SC\sqlcompare.exe" @params

  }
  catch {
    Write-Error "Error invoking SQLCompare command line: $_"
    return $null
  }

}


function New-SQLCompareSnapshotDocker {

  [CmdletBinding()]
  Param (
    [Parameter(Mandatory=$true, Position=0)] [string]$Src,
    [Parameter(Mandatory=$true, Position=1)] [string]$TargetHost,
    [Parameter(Mandatory=$true, Position=2)] [string]$Port,
    [Parameter(Mandatory=$true, Position=3)] [string]$DatabaseName,
    [Parameter(Mandatory=$true, Position=4)] [string]$User,
    [Parameter(Mandatory=$true, Position=5)] [string]$Password,
    [Parameter(Mandatory=$true, Position=6)] [string]$SnapshotName
  )  
  try {

    # Build parameters to run with Docker
    $params = 'run', 
      '--rm',
      '-v', "${Src}:/scripts",
      'redgate/sqlcompare:14', 
      '/IAgreeToTheEULA' , 
      "/s1:$TargetHost,$Port",
      "/db1:$DatabaseName",
      "/u1:$User",
      "/p1:$Password",
      "/makeSnapshot:scripts/$SnapshotName"

      & 'docker' @params

  }
  catch {
    Write-Error "Error invoking SQLCompare Docker container: $_"
    return $null
  }

}


function New-SQLCompareReportCmd {

  [CmdletBinding()]
  Param (
    [Parameter(Mandatory=$true, Position=0)] [string]$WorkingDirectory,
    [Parameter(Mandatory=$true, Position=1)] [string]$Snapshot1FileName,
    [Parameter(Mandatory=$true, Position=2)] [string]$Snapshot2FileName,
    [Parameter(Mandatory=$true, Position=3)] [string]$ReportPathAndFileName
  )  
  try {

    # Build parameters to run with SQLCompare command line
    $params = 
      "/activateserial=$($env:SQLCOMPARE_LICENSE_KEY)",
      "/Snapshot1:$WorkingDirectory/$Snapshot1FileName",                   
      "/Snapshot2:$WorkingDirectory/$Snapshot2FileName" ,                  
      "/Report:$ReportPathAndFileName",                         
      '/Options:IgnoreWithNocheck',
      '/ReportType:Html',
      '/exclude:table:flyway_schema_history',
      '/exclude:table:__SchemaSnapshot'

      &"C:\Program Files (x86)\Red Gate\SQL Change Automation PowerShell\SC\sqlcompare.exe" @params

  }
  catch {
    Write-Error "Error invoking SQLCompare command line: $_"
    return $null
  }

}

function New-SQLCompareReportDocker {

  [CmdletBinding()]
  Param (
    [Parameter(Mandatory=$true, Position=0)] [string]$Src,
    [Parameter(Mandatory=$true, Position=1)] [string]$Snapshot1FileName,
    [Parameter(Mandatory=$true, Position=2)] [string]$Snapshot2FileName,
    [Parameter(Mandatory=$true, Position=3)] [string]$ReportFileName
  )  
  try {

    # Build parameters to run with Docker
    $params = 'run', 
      '--rm',
      '-v', "${Src}:/scripts",
      'redgate/sqlcompare:14', 
      '/IAgreeToTheEULA' , 
      "/Snapshot1:scripts/$Snapshot1FileName",                   
      "/Snapshot2:scripts/$Snapshot2FileName" ,                  
      "/Report:scripts/$ReportFileName",                         
      '/ReportType:Html',
      '/Options:IgnoreWithNocheck',
      '/exclude:table:flyway_schema_history',
      '/exclude:table:__SchemaSnapshot'

      & 'docker' @params

  }
  catch {
    Write-Error "Error invoking SQLCompare Docker container: $_"
    return $null
  }

}

function Read-SnapshotFromDisk {

    [CmdletBinding()]
    Param (
      [Parameter(Mandatory=$true, Position=0)]
      [string]$SnapshotPath
    )
  
    try {
          # Write-Output "Snapshot path is: $SnapshotPath"
  
        $HexString = [System.BitConverter]::ToString([System.IO.File]::ReadAllBytes($SnapshotPath)).Replace('-', '')
        if ([string]::IsNullOrEmpty($HexString)) {
          Throw "File [$SnapshotPackageFileName] contained no data."
        }
        return $HexString
      }
      catch {
        Write-Warning "Failed to read schema snapshot from file. As a result, preview/drift reports will be unavailable for the next deployment: $_"
        return $null
      }
  }
  
  function Write-SnapshotToDatabase {

    [CmdletBinding()]
    Param
    (
        [Parameter(Mandatory=$true, Position=0)]
        [string]$ConnectionString,
  
        [Parameter(Mandatory=$true, Position=1)]
        [string]$HexString
    )
  
    $WriteFailedMessage = "No schema snapshot will be written to the target database. As a result, preview/drift reports will be unavailable for the next deployment." 
  
      if ([string]::IsNullOrEmpty($HexString)) {
        Write-Error $WriteFailedMessage
        return
      }
  
      $SchemaSnapshotTableName = "__SchemaSnapshot"
    
      $SqlConnection = New-Object System.Data.SqlClient.SqlConnection
      $SqlConnection.ConnectionString = $ConnectionString
      $SnapshotSqlCmd = New-Object System.Data.SqlClient.SqlCommand
      $SnapshotSqlCmd.Connection = $SqlConnection
  
      $CreateTableQuery = "IF (NOT EXISTS (SELECT * FROM sys.objects WHERE [object_id] = OBJECT_ID(N'[dbo].[$SchemaSnapshotTableName]')))
      BEGIN
          PRINT '# Setting up dbo.$SchemaSnapshotTableName table';
  
          CREATE TABLE [dbo].[$SchemaSnapshotTableName] (
          [SnapshotId] INT IDENTITY NOT NULL,
          [Snapshot] VARBINARY (MAX),
          [LastUpdateDate] DATETIME2 CONSTRAINT [__SchemaSnapshotDateDefault] DEFAULT SYSDATETIME(),
          CONSTRAINT PK__$SchemaSnapshotTableName_SnapshotId PRIMARY KEY CLUSTERED (SnapshotId)
          );
  
          IF OBJECT_ID(N'sp_addextendedproperty', 'P') IS NOT NULL
          BEGIN
              EXECUTE sp_addextendedproperty N'MS_Description', N'This table stores a snapshot of the schema at the time of the last deployment. Please do not alter or remove this table from the database.', 'SCHEMA', N'dbo', 'TABLE', N'$SchemaSnapshotTableName', NULL, NULL;
          END
      END
      "
      $InsertQuery = "BEGIN TRAN; TRUNCATE TABLE [dbo].[$SchemaSnapshotTableName]; INSERT INTO [dbo].[$SchemaSnapshotTableName] ([Snapshot]) VALUES (0x$HexString); COMMIT;"
    
      try {
        $SqlConnection.Open()
 
        Write-Output "Ensuring dbo.$SchemaSnapshotTableName exists"
        $SnapshotSqlCmd.CommandText = "$CreateTableQuery"
        $SnapshotSqlCmd.ExecuteScalar()
  
        Write-Output "Inserting snapshot into dbo.$SchemaSnapshotTableName"
        $SnapshotSqlCmd.CommandText = "$InsertQuery"
        $SnapshotSqlCmd.ExecuteScalar()
      }
      catch {
        Write-Error "Failed to write schema snapshot to database: $_"
        Write-Error $WriteFailedMessage
      }
      finally{
        $SqlConnection.Close()
        $SnapshotSqlCmd.Dispose()
      }
      
  
  }
  
  
  
  function Read-SnapshotFromDatabase {
  
    [CmdletBinding()]
    Param
    (
      [Parameter(Mandatory=$true, Position=0)]
        [string]$ConnectionString,
  
      [Parameter(Mandatory=$true, Position=1)]
        [string]$SnapshotPath
    )
  
  
    $SchemaSnapshotTableName = "__SchemaSnapshot"
    
    $SqlConnection = New-Object System.Data.SqlClient.SqlConnection
    $SqlConnection.ConnectionString = $ConnectionString
    $SnapshotSqlCmd = New-Object System.Data.SqlClient.SqlCommand
    $SnapshotSqlCmd.Connection = $SqlConnection
  
    $SelectQuery = "SELECT TOP (1) [Snapshot] FROM [dbo].[$SchemaSnapshotTableName] ORDER BY [LastUpdateDate] DESC;"
    $SnapshotSqlCmd.CommandText = "$SelectQuery"
  
    try {
      $SqlConnection.Open()
      $snapshot = $SnapshotSqlCmd.ExecuteScalar()
  
  #    $decoded = [System.Convert]::FromBase64CharArray($snapshot, 0, $snapshot.Length)
      [io.file]::WriteAllBytes($SnapshotPath, $snapshot)
  
    }
    catch {
      Write-Warning "Failed to read schema snapshot from database: $_"
    }
    finally{
      $SqlConnection.Close()
      $SnapshotSqlCmd.Dispose()
    }
  
  }