Deploy.template.ps1
|
function Get-SqlScalarValue($variableName, $ConnectionString, $scalarQuery) { try { $SqlConnection = New-Object System.Data.SqlClient.SqlConnection $SqlConnection.ConnectionString = $ConnectionString $SqlConnection.Open() $SqlCmd = New-Object System.Data.SqlClient.SqlCommand $SqlCmd.CommandText = $scalarQuery $SqlCmd.Connection = $sqlConnection $scalarValue = [string]$SqlCmd.ExecuteScalar() if ($scalarValue -eq '') { Write-Warning "Could not determine a value for $variableName variable. An empty string will be supplied to the deployment." } $SqlConnection.Close() return $scalarValue } catch { Write-Warning "Could not retrieve a value for ${variableName}: $_ " return "" } } function Get-ScriptDirectory { $Invocation = (Get-Variable MyInvocation -Scope 1).Value Split-Path $Invocation.MyCommand.Path } try { if ($ReleaseVersion -eq $null) { $ReleaseVersion = ''; if ($OctopusEnvironmentName -eq $null) { Write-Warning 'As the ReleaseVersion variable is not set, the [__MigrationLog].[release_version] column will be set to NULL for any pending migrations.' } } if ($OctopusReleaseNumber -ne $null) { $ReleaseVersion = $OctopusReleaseNumber } if ($DeployPath -eq $null) { $DeployPath = (Get-ScriptDirectory).TrimEnd('\') + '\' } if ($SkipOctopusVariableValidation -ne $null) { $SkipVariableValidation = $SkipOctopusVariableValidation } if ($UseSqlCmdVariableDefaults -eq $null) { $UseSqlCmdVariableDefaults = "true" } if ($UseSqlCmdVariableDefaults -eq "true") { Write-Output 'If you require that all SqlCmd variable values be passed in explicitly, specify UseSqlCmdVariableDefaults=False.' foreach ($kvp in $SqlCmdVariables.GetEnumerator()) { $identity = $kvp.Name $default = $kvp.Value $currentValue = Get-Variable $identity -ValueOnly -ErrorAction SilentlyContinue if ($identity -ne '') { if ($currentValue -eq $null) { Write-Output "Using default value for $identity variable: $default" New-Variable $identity $default } } } if ($TargetDatabase -ne '') { if ($DatabaseName -eq $null) { Write-Output "Using default value for DatabaseName variable: $TargetDatabase" $DatabaseName=$TargetDatabase.Replace("'", "''") } } if ($ForceDeployWithoutBaseline -eq $null) { Write-Output 'Using default value for ForceDeployWithoutBaseline variable: False' $ForceDeployWithoutBaseline = 'False' } if ($OctoOptionDeployToLocalInstance) { if ($DatabaseServer -eq $null -and $OctoOptionDeployToLocalInstance -ne "false") { Write-Output '**Deploying to (local) because OctoOptionDeployToLocalInstance=True' $DatabaseServer='(local)' } } } if ($SkipVariableValidation -ne $true) { if ($DatabaseServer -eq $null) { Throw 'DatabaseServer variable was not provided.' } if ($DatabaseName -eq $null) { Throw 'DatabaseName variable was not provided.' } if ($ForceDeployWithoutBaseline -eq $null) { Throw 'ForceDeployWithoutBaseline variable was not provided.' } foreach ($kvp in $SqlCmdVariables.GetEnumerator()) { $identity = $kvp.Name $currentValue = Get-Variable $identity -ValueOnly -ErrorAction SilentlyContinue if ($currentValue -eq $null) { Throw "$identity variable was not provided" } } } if ($__isAzurePlatformTarget -eq $false) { if ($UseWindowsAuth -eq $null) { $UseWindowsAuth = $true } } if ($UseWindowsAuth -eq $true) { Write-Output 'Using Windows Authentication' $SqlCmdAuth = '-E' $ConnectionString = 'Data Source=' + $DatabaseServer + ';Integrated Security=SSPI'; } else { if ($DatabaseUserName -eq $null) { Throw 'As SQL Server Authentication is to be used, please specify values for the DatabaseUserName and DatabasePassword variables. Alternately, specify UseWindowsAuth=True to use Windows Authentication instead.' } if ($DatabasePassword -eq $null) { Throw 'If a DatabaseUserName is specified, the DatabasePassword variable must also be provided.' } Write-Output 'Using SQL Server Authentication' $SqlCmdAuth = '-U "' + $DatabaseUserName.Replace('"', '""') + '" '; $env:SQLCMDPASSWORD=$DatabasePassword; $ConnectionString = 'Data Source=' + $DatabaseServer + ';User Id=' + $DatabaseUserName + ';Password=' + $DatabasePassword; } if ($__isAzurePlatformTarget -eq $true) { $databaseExists = Get-SqlScalarValue 'databaseExists' $ConnectionString ('select count(*) from sys.databases where name = ''' + $DatabaseName.Replace("'", "''") + "'") $DefaultFilePrefix = "" $DefaultDataPath = "" $DefaultLogPath = "" $DefaultBackupPath = "" } else { if ($DefaultFilePrefix -eq $null) { Write-Output "Using default value for DefaultFilePrefix variable: $TargetDatabase" $DefaultFilePrefix = $TargetDatabase.Replace("'", "''") } if ($DefaultDataPath -eq $null) { $DefaultDataPath = Get-SqlScalarValue "DefaultDataPath" $ConnectionString "declare @DefaultPath nvarchar(512); exec master.dbo.xp_instance_regread N'HKEY_LOCAL_MACHINE', N'Software\Microsoft\MSSQLServer\MSSQLServer', N'DefaultData', @DefaultPath output; if (@DefaultPath is null) begin set @DefaultPath = (select F.physical_name from sys.master_files F where F.database_id=db_id('master') and F.type = 0); select @DefaultPath=substring(@DefaultPath, 1, len(@DefaultPath) - charindex('\', reverse(@DefaultPath))); end select isnull(@DefaultPath + '\', '') DefaultData" Write-Output "Using default value for DefaultDataPath variable: $DefaultDataPath" } if ($DefaultLogPath -eq $null) { $DefaultLogPath = Get-SqlScalarValue "DefaultLogPath" $ConnectionString "declare @DefaultPath nvarchar(512); exec master.dbo.xp_instance_regread N'HKEY_LOCAL_MACHINE', N'Software\Microsoft\MSSQLServer\MSSQLServer', N'DefaultLog', @DefaultPath output; if (@DefaultPath is null) begin set @DefaultPath = (select F.physical_name from sys.master_files F where F.database_id=db_id('master') and F.type = 1); select @DefaultPath=substring(@DefaultPath, 1, len(@DefaultPath) - charindex('\', reverse(@DefaultPath))); end select isnull(@DefaultPath + '\', '') DefaultData" Write-Output "Using default value for DefaultLogPath variable: $DefaultLogPath" } if ($DefaultBackupPath -eq $null) { $DefaultBackupPath = Get-SqlScalarValue "DefaultBackupPath" $ConnectionString "declare @DefaultBackup nvarchar(512); exec master.dbo.xp_instance_regread N'HKEY_LOCAL_MACHINE', N'Software\Microsoft\MSSQLServer\MSSQLServer', N'BackupDirectory', @DefaultBackup output; select isnull(@DefaultBackup + '\', '') DefaultBackup;" Write-Output "Using default value for DefaultBackupPath variable: $DefaultBackupPath" } } Write-Output "Starting '$DatabaseName' Database Deployment to '$DatabaseServer'" $SqlCmdVarArguments = 'DatabaseName="' + $DatabaseName.Replace('"', '""') + '"' $SqlCmdVarArguments += ' ReleaseVersion="' + $ReleaseVersion.Replace('"', '""') + '"' $SqlCmdVarArguments += ' DeployPath="' + $DeployPath.Replace('"', '""') + '"' $SqlCmdVarArguments += ' ForceDeployWithoutBaseline="' + $ForceDeployWithoutBaseline.Replace('"', '""') + '"' $SqlCmdVarArguments += ' DefaultFilePrefix="' + $DefaultFilePrefix.Replace('"', '""') + '"' $SqlCmdVarArguments += ' DefaultDataPath="' + $DefaultDataPath.Replace('"', '""') + '"' $SqlCmdVarArguments += ' DefaultLogPath="' + $DefaultLogPath.Replace('"', '""') + '"' $SqlCmdVarArguments += ' DefaultBackupPath="' + $DefaultBackupPath.Replace('"', '""') + '"' foreach ($kvp in $SqlCmdVariables.GetEnumerator()) { $identity = $kvp.Name $currentValue = Get-Variable $identity -ValueOnly -ErrorAction SilentlyContinue $SqlCmdVarArguments += " $Identity=""" + $currentValue.Replace('"', '""') + '"' } $SqlCmdBase = 'sqlcmd.exe -b -S "' + $DatabaseServer + '" -v ' + $SqlCmdVarArguments if ($__isAzurePlatformTarget -eq $false) { $SqlCmd = $SqlCmdBase } if ($__isAzurePlatformTarget -eq $true) { $SqlCmd = $SqlCmdBase + ' -d "' + $DatabaseName.Replace('"', '""') + '"' } $SqlCmd = $SqlCmd + ' -i "' + (Get-ScriptDirectory) + "\$CreateScriptFileName" + '"' $SqlCmdWithAuth = $SqlCmd + ' ' + $SqlCmdAuth Write-Output $SqlCmdWithAuth } catch { Write-Error "A validation error occurred: $_ " if ($SkipVariableValidation) { Write-Error 'To bypass variable validation, pass this property value to MSBuild: SkipVariableValidation=True' } if ($OctopusEnvironmentName -ne $null) { [Environment]::Exit(1) } throw } # SQLCMD package deployment if ($__isAzurePlatformTarget -eq $true) { $SqlCmdCreateDatabase = $SqlCmdBase + ' ' + $SqlCmdAuth + ' -Q "CREATE DATABASE [' + $DatabaseName.Replace('"', '""') + ']"' if ($databaseExists -eq "0") { try { Write-Output "Creating database $DatabaseName..." cmd /Q /C $SqlCmdCreateDatabase if ($lastexitcode) { throw 'sqlcmd.exe exited with a non-zero exit code.' } } catch { Write-Error "A deployment error occurred: $_ " if ($OctopusEnvironmentName -ne $null) { [Environment]::Exit(1) } throw } } else { Write-Output "The database already exists. An incremental deployment will be performed." } } try { cmd /Q /C $SqlCmdWithAuth if ($lastexitcode) { throw 'sqlcmd.exe exited with a non-zero exit code.' } } catch { Write-Error "A deployment error occurred: $_ " if ($OctopusEnvironmentName -ne $null) { [Environment]::Exit(1) } throw } function Read-Snapshot($ScriptDirectory) { try{ $SnapshotName = (Get-Item "$ScriptDirectory\$SnapshotPackageFileName" -ErrorAction Stop)[0].Name $SnapshotPath = Join-Path $ScriptDirectory $SnapshotName $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-Snapshot($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-Warning $WriteFailedMessage return } $SchemaSnapshotTableName = "__SchemaSnapshot" $SqlConnection = New-Object System.Data.SqlClient.SqlConnection $SqlConnection.ConnectionString = "$ConnectionString;Database=$DatabaseName" $SnapshotSqlCmd = New-Object System.Data.SqlClient.SqlCommand $SnapshotSqlCmd.Connection = $SqlConnection $InsertQuery = "INSERT INTO [$MigrationLogSchemaName].[$SchemaSnapshotTableName] (Snapshot) VALUES (0x$HexString)" $SnapshotSqlCmd.CommandText = "$InsertQuery" try { $SqlConnection.Open() $SnapshotSqlCmd.ExecuteScalar() } catch { Write-Warning "Failed to write schema snapshot to database: $_" Write-Warning $WriteFailedMessage } finally{ $SqlConnection.Close() $SnapshotSqlCmd.Dispose() } } $ScriptDirectory = Get-ScriptDirectory if (Test-Path "$ScriptDirectory\$SnapshotPackageFileName") { Write-Output "Reading schema snapshot" $HexString = Read-Snapshot(Get-ScriptDirectory) if ($HexString -ne $null) { Write-Output "Writing schema snapshot to target database" Write-Snapshot($HexString) } } else { Write-Output 'Skipping schema snapshot deployment as a snapshot file could not be found. As a result, preview/drift reports will be unavailable for the next deployment. To enable schema snapshot creation, specify the ShadowServer property in your build configuration https://www.red-gate.com/sca/continuous-integration' } |