Functions/PublishDatabaseDeployment.ps1

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
Function Publish-DatabaseDeployment {
    [cmdletbinding()]
    param(
        $dacfxPath
        , $dacpac
        , $publishXml
        , $targetConnectionString
        , $targetDatabaseName
        , [Switch] $getSqlCmdVars
        , [Switch] $FailOnMissingVars
        , [bool] $GenerateDeploymentScript
        , [bool] $GenerateDeploymentReport 
        , [bool] $GenerateDeploymentSummary
        , $ScriptPath 
        , [Switch] $ScriptOnly
        , [Switch] $FailOnAlerts
    )

    if (($GenerateDeploymentReport -eq $false) -and ($GenerateDeploymentSummary -eq $true)) {
        throw "To include the summary report (`$GenerateDeploymentSummary) you need to include `$GenerateDeploymentReport"
    }
    
    Write-Verbose 'Testing if DACfx was installed...'
    if (-not (Test-Path $dacfxPath)) { throw "No usable version of Dac Fx found at $dacfxPath" }
    if (-not (Test-Path $dacpac)) { throw "$dacpac not found!" }
    if (-not (Test-Path $publishXml)) { throw "$publishXml not found!" }
    if (-not (Test-Path $ScriptPath)) { Throw "Script Path Invalid!" }

    $ScriptPath = Resolve-Path $ScriptPath

    try {
        Write-Verbose 'DacFX found, attempting to load DAC assembly...'
        Add-Type -Path $dacfxPath
        Write-Verbose 'Loaded DAC assembly.'
    }
    catch [System.Management.Automation.RuntimeException] {
        throw ("Exception caught: {0}" -f $_.Exception.GetType().FullName)
    }
    
    $dacPackage = [Microsoft.SqlServer.Dac.DacPackage]::Load($Dacpac)
    Write-Host ("Loaded dacpac '{0}'." -f $Dacpac) -ForegroundColor White -BackgroundColor DarkMagenta
    
    $dacProfile = [Microsoft.SqlServer.Dac.DacProfile]::Load($publishXml)
    Write-Host ("Loaded publish profile '{0}'." -f $publishXml) -ForegroundColor White -BackgroundColor DarkMagenta

    if ($PSBoundParameters.ContainsKey('targetConnectionString') -eq $false) {
        $publishXmlName = Split-Path $publishXml -leaf
        Write-Verbose "No TargetConnectionString specified, loading value from $publishXmlName" -Verbose
        $TargetConnectionString = $dacProfile.TargetConnectionString 
        $TargetConnectionStringLoadedFromPublishXml = $true
    }
    else {
        $TargetConnectionStringLoadedFromPublishXml = $false
    }
    if ($getSqlCmdVars) {
        if ($PSBoundParameters.ContainsKey('FailOnMissingVars') -eq $true) { 
            Get-SqlCmdVars $dacProfile.DeployOptions.SqlCommandVariableValues -FailOnMissingVariables
        }
        else {
            Get-SqlCmdVars $($dacProfile.DeployOptions.SqlCommandVariableValues)
        }
    }
    $now = Get-Date 
    $timeStamp = Get-Date $now -Format "yyMMdd_HHmmss_f"
    $DatabaseScriptPath = Join-Path $ScriptPath "$($targetDatabaseName).Result.DeployScript_$timeStamp.sql"
    $MasterDbScriptPath = Join-Path $ScriptPath "($targetDatabaseName)_Master.DeployScript_$timeStamp.sql"
    $DeploymentReport = Join-Path $ScriptPath "$targetDatabaseName.Result.DeploymentReport_$timeStamp.xml"
    $DeploymentSummary = Join-Path $ScriptPath "$targetDatabaseName.Result.DeploymentSummary_$timeStamp.txt"

    $dacServices = New-Object Microsoft.SqlServer.Dac.DacServices $targetConnectionString
    $options = @{
        GenerateDeploymentScript = $GenerateDeploymentScript
        GenerateDeploymentReport = $GenerateDeploymentReport
        DatabaseScriptPath       = $DatabaseScriptPath
        MasterDbScriptPath       = $MasterDbScriptPath
        DeployOptions            = $dacProfile.DeployOptions
    }
    try {
        Register-ObjectEvent -InputObject $dacServices -EventName "Message" -Source "msg" -Action { Write-Host $EventArgs.Message.Message } | Out-Null  
        if ($ScriptOnly) {
            if (($GenerateDeploymentScript -eq $false) -and ($GenerateDeploymentReport -eq $false)) {
                throw "Specify at least one of GenerateDeploymentScript or GenerateDeploymentReport to be true when using ScriptOnly!"
            }
            Write-Host "Generating script..." -ForegroundColor Yellow
            $result = $dacServices.script($dacPackage, $targetDatabaseName, $options)
            Write-Host "Script created!" -ForegroundColor DarkGreen
        }
        else {
            Write-Host "Executing Deployment..." -ForegroundColor Yellow     
            $result = $dacServices.publish($dacPackage, $targetDatabaseName, $options)
            Write-Host "Deployment successful!" -ForegroundColor DarkGreen
        }
    }  
    catch {
        $e = $_.Exception
        $toThrow = $e.Message
        while ($e.InnerException) {
            $e = $e.InnerException
            $toThrow += "`n" + $e.Message
        }
    }
    finally {
        Unregister-Event -SourceIdentifier "msg"
        if ($ToThrow) {
            Throw $ToThrow
        }
        if ($GenerateDeploymentReport -eq $true) {
            $result.DeploymentReport | Out-File $DeploymentReport
            Write-Host "Deployment Report - $DeploymentReport" -ForegroundColor DarkGreen -BackgroundColor White
            $deprep = [xml] (Get-Content -Path $DeploymentReport)
            if ($GenerateDeploymentSummary -eq $true) {
                $OperationSummary = Get-OperationSummary -deprep $deprep
                $OperationTotal = Get-OperationTotal -deprep $deprep
                $Alerts = Get-Alerts -deprep $deprep
                if ($null -ne $Alerts) {
                    $JoinTables = Join-Object -left $OperationSummary -Right $alerts -LeftJoinProperty IssueId -RightJoinProperty IssueId -Type AllInRight -RightProperties IssueValue
                }
                "Deployment for database $targetDatabaseName on $now `n" | Out-File $DeploymentSummary
                $OperationTotal | Out-String | Add-Content $DeploymentSummary
                $OperationSummary | Out-String | Add-Content $DeploymentSummary
                $Alerts | Out-String | Add-Content $DeploymentSummary
                $JoinTables | Out-String | Where-Object {$null -ne $_.IssueId} | Add-Content $DeploymentSummary
            }
        }
        if ($GenerateDeploymentScript -eq $true) {
            Write-Host "Database change script - $DatabaseScriptPath" -ForegroundColor White -BackgroundColor DarkCyan
            if ((Test-Path $MasterDbScriptPath) -eq $true) {
                Write-Host "Master database change script - $($result.MasterDbScript)" -ForegroundColor White -BackgroundColor DarkGreen
            }
        }    
        $deployOptions = $dacProfile.DeployOptions | Select-Object -Property * -ExcludeProperty "SqlCommandVariableValues"
        [pscustomobject]@{
            Dacpac                                     = $dacpac
            PublishXml                                 = $PublishXml
            DatabaseScriptPath                         = $DatabaseScriptPath
            MasterDbScriptPath                         = $($result.MasterDbScript)
            DeploymentReport                           = $DeploymentReport
            DeploymentSummary                          = $DeploymentSummary
            DeployOptions                              = $deployOptions
            SqlCmdVariableValues                       = $dacProfile.DeployOptions.SqlCommandVariableValues.Keys
            TargetConnectionStringLoadedFromPublishXml = $TargetConnectionStringLoadedFromPublishXml
        }
        if ($GenerateDeplymentSummary -eq $true) {
            [pscustomobject]$OperationTotal | Format-Table
            [pscustomobject]$OperationSummary | Format-Table
            [pscustomobject]$Alerts | Format-Table
            [pscustomobject]$JoinTables | Where-Object {$null -ne $_.IssueId}  | Format-Table
        
            if ($PSBoundParameters.ContainsKey('FailOnAlerts') -eq $true) { 
                if ($Alerts.Count -gt 0) {
                    Write-Error "Alerts found, failing. Consult tables above."
                }
            }
        }
    }
}