Functions/Invoke-DatabaseDacpacDeploy.ps1
|
<#
.SYNOPSIS Executes a DACPAC deployment against a SQL Server database using SqlPackage.exe. .DESCRIPTION The Invoke-DatabaseDacpacDeploy function orchestrates the deployment of a DACPAC file to a target SQL Server database. It supports various authentication methods, deployment actions, and can generate deployment scripts. The function handles both individual server/database parameters and connection strings. .PARAMETER dacpacfile Specifies the path to the DACPAC file to deploy. This is a mandatory parameter. .PARAMETER sqlpackagePath Specifies the path to the SqlPackage.exe utility that will perform the deployment. This is a mandatory parameter. .PARAMETER action Specifies the SqlPackage action to perform (e.g., Publish, Script, Report). This is a mandatory parameter. .PARAMETER scriptParentPath Specifies the parent directory where deployment scripts will be generated. This is a mandatory parameter. .PARAMETER TargetServerName Specifies the target SQL Server name. Required when using the IndividualTarget parameter set. .PARAMETER TargetDatabaseName Specifies the target database name. Required when using the IndividualTarget parameter set. .PARAMETER TargetUser Optional. Specifies the username for SQL Server authentication. If not provided, integrated security will be used. .PARAMETER TargetPasswordSecure Optional. Specifies the secure password for SQL Server authentication. Used with TargetUser parameter. .PARAMETER TargetIntegratedSecurity Optional. Specifies whether to use integrated security for the connection. .PARAMETER TargetTrustServerCert Optional switch. When specified, trusts the server certificate without validation. .PARAMETER TargetConnectionString Specifies a complete connection string for the target database. Required when using the ConnectionStringTarget parameter set. .PARAMETER ServiceObjective Optional. Specifies the Azure SQL Database service objective (performance level). .PARAMETER AccessToken Optional. Specifies an access token for Azure Active Directory authentication. .PARAMETER AccessTokenSecure Optional. Specifies a secure access token for Azure Active Directory authentication. .PARAMETER TenantId Optional. Specifies the tenant ID for Azure Active Directory authentication. .PARAMETER ClientId Optional. Specifies the client ID for Azure Active Directory authentication. .PARAMETER ClientSecret Optional. Specifies the client secret for Azure Active Directory authentication. .PARAMETER ClientSecretSecure Optional. Specifies the secure client secret for Azure Active Directory authentication. .PARAMETER PublishFile Optional. Specifies the path to a publish profile file with additional deployment settings. .PARAMETER OutputDeployScript Optional switch. When specified, generates a deployment script instead of executing the deployment directly. .PARAMETER Variables Specifies SQLCMD variables to use during deployment. This is a mandatory parameter. .PARAMETER TargetTimeout Specifies the timeout value for the target connection. This is a mandatory parameter. .PARAMETER CommandTimeout Specifies the timeout value for SQL commands during deployment. This is a mandatory parameter. .PARAMETER SettingsToCheck Optional. Specifies specific deployment settings to monitor and store for future comparisons. .PARAMETER DBScriptPrefix Optional. Specifies a prefix for generated deployment script files. If not provided, the DACPAC filename is used. .OUTPUTS None This function performs deployment actions but does not return objects. Status and results are logged to the console. .EXAMPLE Invoke-DatabaseDacpacDeploy -dacpacfile "MyApp.dacpac" -sqlpackagePath "C:\SqlPackage\SqlPackage.exe" -action "Publish" -scriptParentPath "C:\Deploy" -TargetServerName "localhost" -TargetDatabaseName "MyDB" -Variables @() -TargetTimeout 60 -CommandTimeout 300 Deploys MyApp.dacpac to MyDB on localhost using integrated security. .EXAMPLE Invoke-DatabaseDacpacDeploy -dacpacfile "MyApp.dacpac" -sqlpackagePath "SqlPackage.exe" -action "Script" -scriptParentPath "C:\Scripts" -TargetConnectionString "Server=localhost;Database=MyDB;Integrated Security=true;" -OutputDeployScript -Variables @() -TargetTimeout 60 -CommandTimeout 300 Generates a deployment script for MyApp.dacpac without executing the deployment. .EXAMPLE Invoke-DatabaseDacpacDeploy -dacpacfile "MyApp.dacpac" -sqlpackagePath "SqlPackage.exe" -action "Publish" -scriptParentPath "C:\Deploy" -TargetServerName "myserver.database.windows.net" -TargetDatabaseName "MyDB" -AccessToken $accessToken -Variables @("Env=Production") -TargetTimeout 120 -CommandTimeout 600 Deploys to Azure SQL Database using an access token with custom variables. .NOTES The function supports multiple authentication methods: - Windows Integrated Security (default when no credentials provided) - SQL Server Authentication (username/password) - Azure Active Directory Authentication (access token or service principal) The function automatically determines the authentication method based on provided parameters. Generated scripts and logs are placed in subdirectories under scriptParentPath. Deployment settings are stored in the target database for future comparison to optimize deployment decisions. #> Function Invoke-DatabaseDacpacDeploy { [Diagnostics.CodeAnalysis.SuppressMessageAttribute("PSReviewUnusedParameter", "", Justification = "OutputDeployScript is reported as not being used but it is")] [CmdletBinding()] param ( [Parameter(Mandatory = $true, HelpMessage = "Path to the DACPAC file to deploy")] [string]$dacpacfile, [Parameter(Mandatory = $true, HelpMessage = "Path to SqlPackage.exe utility")] [string]$sqlpackagePath, [Parameter(Mandatory = $true, HelpMessage = "SqlPackage action to perform")] [string]$action, [Parameter(Mandatory = $true, HelpMessage = "Parent directory for deployment scripts")] [string]$scriptParentPath, #Parameter set TargetServer and Database or ConnectionString [Parameter(Mandatory = $true, ParameterSetName = "IndividualTarget", HelpMessage = "Target SQL Server name")] [string]$TargetServerName, [Parameter(Mandatory = $true, ParameterSetName = "IndividualTarget", HelpMessage = "Target database name")] [string]$TargetDatabaseName, [Parameter(HelpMessage = "Username for SQL Server authentication")] [string]$TargetUser, [Parameter(HelpMessage = "Secure password for SQL Server authentication")] [securestring]$TargetPasswordSecure, [Parameter(HelpMessage = "Whether to use integrated security")] [string]$TargetIntegratedSecurity, [Parameter(HelpMessage = "Trust server certificate without validation")] [switch]$TargetTrustServerCert, [Parameter(Mandatory = $true, ParameterSetName = "ConnectionStringTarget", HelpMessage = "Complete connection string")] [string]$TargetConnectionString, [Parameter(HelpMessage = "Azure SQL Database service objective")] $ServiceObjective, [Parameter(HelpMessage = "Access token for Azure AD authentication")] [String]$AccessToken, [Parameter(HelpMessage = "Secure access token for Azure AD authentication")] [SecureString]$AccessTokenSecure, [Parameter(HelpMessage = "Tenant ID for Azure AD authentication")] [string]$TenantId, [Parameter(HelpMessage = "Client ID for Azure AD authentication")] [string]$ClientId, [Parameter(HelpMessage = "Client secret for Azure AD authentication")] [string]$ClientSecret, [Parameter(HelpMessage = "Secure client secret for Azure AD authentication")] [securestring]$ClientSecretSecure, [Parameter(HelpMessage = "Path to publish profile file")] [string]$PublishFile, [Parameter(HelpMessage = "Generate deployment script instead of executing")] [Switch]$OutputDeployScript, [Parameter(Mandatory = $true, HelpMessage = "SQLCMD variables for deployment")] $Variables, [Parameter(Mandatory = $true, HelpMessage = "Target connection timeout")] $TargetTimeout, [Parameter(Mandatory = $true, HelpMessage = "SQL command timeout")] $CommandTimeout, [Parameter(HelpMessage = "Deployment settings to monitor")] $SettingsToCheck, [Parameter(HelpMessage = "Prefix for generated script files")] [string]$DBScriptPrefix ) try { $EntraSecurity = $false if ($TargetUser) { #Used to be able to extract the password and pass to SQLPackage $TargetCredentials = New-Object System.Management.Automation.PSCredential($TargetUser, $TargetPasswordSecure ) $Security = "/TargetUser:$($TargetCredentials.UserName)", "/TargetPassword:`$(`$TargetCredentials.GetNetworkCredential().Password)" } else { if ($AccessToken -or $AccessTokenSecure -or ( $ClientId -and ( $ClientSecret -or $ClientSecretSecure) -and $TenantId ) ) { $EntraSecurity = $true } else { $TargetIntegratedSecurity = $true } $Security = @() } Write-DBDeployParameterLog -dacpacfile $dacpacfile ` -action $action ` -TargetConnectionString $TargetConnectionString ` -TargetServerName $TargetServerName ` -TargetDatabaseName $TargetDatabaseName ` -TargetIntegratedSecurity $TargetIntegratedSecurity ` -EntraSecurity $EntraSecurity ` -ServiceObjective $ServiceObjective ` -PublishFile $PublishFile ` -Variables $Variables ` -TargetTimeout $TargetTimeout ` -CommandTimeout $CommandTimeout ` -sqlpackagePath $sqlpackagePath ` -Username $TargetUser ` -scriptParentPath $scriptParentPath -ErrorAction Stop $DeployProperties = Get-DeployPropertiesHash -action $action ` -TargetServerName $TargetServerName ` -TargetDatabaseName $TargetDatabaseName ` -TargetUser $TargetUser ` -TargetPasswordSecure $TargetPasswordSecure ` -TargetIntegratedSecurity $TargetIntegratedSecurity ` -ServiceObjective $ServiceObjective ` -PublishFile $PublishFile ` -Variables $Variables ` -TargetTimeout $TargetTimeout ` -dacpacfile $dacpacfile ` -SettingsToCheck $SettingsToCheck $sqlPackageCommand = New-Object Collections.Generic.List[String] Add-ToList $sqlPackageCommand "/Action:$Action" Add-ToList $sqlPackageCommand "/TargetTimeout:$TargetTimeout" if ([string]::IsNullOrWhiteSpace($DBScriptPrefix) ) { $DBScriptPrefix = [io.path]::GetFileNameWithoutExtension($dacpacfile) } # Handle database name extraction for path construction - extract database name from connection string if needed $DatabaseNameForPath = $TargetDatabaseName if ($TargetConnectionString -and [string]::IsNullOrWhiteSpace($TargetDatabaseName)) { # Extract database name from connection string if ($TargetConnectionString -match "Database=([^;]+)") { $DatabaseNameForPath = $matches[1] } elseif ($TargetConnectionString -match "Initial Catalog=([^;]+)") { $DatabaseNameForPath = $matches[1] } else{ throw "Unable to extract database name from connection string. Please ensure the connection string contains 'Database' or 'Initial Catalog' parameter." } } if ($TargetConnectionString) { Add-ToList $sqlPackageCommand "/TargetConnectionString:$TargetConnectionString" } else { $TargetDatabase = "/TargetServerName:$TargetServerName", "/TargetDatabaseName:$TargetDatabaseName" } If ($Action -eq "DriftReport") { if ($Variables -Contains "/TargetTrustServerCertificate:true"){ Add-ToList $sqlPackageCommand ("/TargetTrustServerCertificate:True") } Add-ToList $sqlPackageCommand ("/OutputPath:{0}" -f [IO.Path]::Combine($ScriptParentPath, $DatabaseNameForPath, "$DBScriptPrefix`_drift.xml")) } else { if ($PublishFile) { Add-ToList $sqlPackageCommand "/Profile:$publishFile" } if ($ServiceObjective) { Add-ToList $sqlPackageCommand "/p:DatabaseServiceObjective=$ServiceObjective" } Add-ToList $sqlPackageCommand "/SourceFile:$dacpacFile" Add-ToList $sqlPackageCommand "/v:DeployProperties=`"$(Convert-ToSQLPackageSafeString $DeployProperties)`"" Add-ToList $sqlPackageCommand -items $Variables Add-ToList $sqlPackageCommand "/p:CommandTimeout=$CommandTimeout" if ($Action -eq "Publish") { Add-ToList $sqlPackageCommand ("/DeployScriptPath:{0}" -f [IO.Path]::Combine($ScriptParentPath, $DatabaseNameForPath, "$DBScriptPrefix`_db.sql")) } elseif ($Action -eq "Script") { Add-ToList $sqlPackageCommand ("/DeployScriptPath:{0}" -f [IO.Path]::Combine($ScriptParentPath, $DatabaseNameForPath, "$DBScriptPrefix`_db.sql")) } } if (-not $TargetConnectionString) { Add-ToList $sqlPackageCommand -items $TargetDatabase } if ($TargetTrustServerCert) { Add-ToList $sqlPackageCommand "/TargetTrustServerCertificate:True" } if ($AccessToken -or $AccessTokenSecure -or ( $ClientId -and ( $ClientSecret -or $ClientSecretSecure) -and $TenantId ) ) { if ($AccessTokenSecure -or $AccessToken) { Write-Host "Using Access Token provided clientId and secret being ignored" } else{ Write-Host "Getting Access Token using ClientId and ClientSecret" $AccessToken = (Get-EntraAccessToken -ClientId $ClientId -ClientSecret $ClientSecret -ClientSecretSecure $ClientSecretSecure -TenantId $TenantId).access_token Write-Host $AccessToken } if ($AccessTokenSecure -is [System.Security.SecureString]) { Write-Host "Extracting Access Token from SecureString" $cred = New-Object System.Management.Automation.PSCredential ("user", $AccessTokenSecure) $AccessToken = $cred.GetNetworkCredential().Password } write-host "Access token first 10 and last 10 characters: $($AccessToken.Substring(0,10))...$($AccessToken.Substring($AccessToken.Length - 10))" $Security += "/AccessToken:$AccessToken" } # $sqlPackageCommand +="/p:CommentOutSetVarDeclarations=true New-Item $ScriptParentPath\$DatabaseNameForPath -ItemType "Directory" -Force | Out-null if ($env:SYSTEM_DEBUG) { $sqlPackageCommand } Add-ToList $sqlPackageCommand -items $Security Function Get-SqlPackageArgument { $sqlPackageCommand | ForEach-Object { if ($_ -like "*/v:DeployProperties*" ) { $_ } else { $ExecutionContext.InvokeCommand.ExpandString($_) } } } (Get-SqlPackageArgument) | ForEach-Object { Write-Verbose $_ } $ErrorActionPreference = "Continue" $LASTEXITCODE = 0 [Diagnostics.CodeAnalysis.SuppressMessageAttribute('PSUseDeclaredVarsMoreThanAssignments', 'PS variable used internally by PS not by our code')] $PSNativeCommandArgumentPassing ="legacy" invoke-command -ScriptBlock { &$sqlpackagePath (Get-SqlPackageArgument) #Ensure errors are sent to the errorvariable } -ev sqlpackageerror -OutVariable SqlPackageExitCode $ErrorActionPreference = "Stop" $LEC = $Global:LASTEXITCODE $result = [PscustomObject]@{Scripts = Get-ChildItem "$ScriptParentPath\$DatabaseNameForPath" -File -Recurse } if ($OutputDeployScript) { $result.Scripts | ForEach-Object { Write-Host "######################################### DB Deploy Script ######################################" Write-Host "Produced file $_" Write-Host "-------------------------------------------------------------------------------------------------" Get-Content $_.FullName | ForEach-Object { if ($_ -notlike '*:setvar DeployProperties*') { Write-Host $_ } else { Write-host ":setvar DeployProperties ### masked ####" } } } } IF ($LEC -ne 0 ) { #Write error with the exit code from SQLPackage, this will be used by the calling function to determine if the deployment succeeded or failed #Proper writeError $PSCmdlet.WriteError((New-Object System.Management.Automation.ErrorRecord( (New-Object System.Exception("SqlPackage returned non-zero exit code $LEC")), "SqlPackageDeploymentFailed", [System.Management.Automation.ErrorCategory]::NotSpecified, $null))) } return $result } Catch { Throw $_ } } |