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
Function Publish-DatabaseDeployment {
    [cmdletbinding()]
    param(
        $dacfxPath
        , $dacpac
        , $publishXml
        , $targetConnectionString
        , $targetDatabaseName
        , [Switch] $getSqlCmdVars
        , [Switch] $FailOnMissingVars
        , [bool] $GenerateDeploymentScript
        , [bool] $GenerateDeploymentReport 
        , $ScriptPath 
        , [Switch] $ScriptOnly
        , [Switch] $FailOnAlerts
    )
    
    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!" }

    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)
            $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
        }

        [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."
            }
        }
    }
}