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 |
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!" } $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) $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." } } } } |