SkyWire-PSDatabase.psm1
|
<#
.SYNOPSIS Creates a new database for a SkyWire Point of Sale product. .DESCRIPTION Publishes the dacpac for a specified app and tag (as well as optional bacpac to include test data) to a specified server. .PARAMETER app The app to establish a database for. Valid values: POS, SRDM, Reporting. .PARAMETER tag The tag (ticket number) for the build to download and deploy. This tag will be used in the db name. .PARAMETER dbServer The database server (default: localhost). .PARAMETER dbUser The database user id. .PARAMETER dbPassword The database password. .PARAMETER rootDir Specify the root directory where all data will reside for the current deployment (default: %PROGRAMDATA%\SkyWire-PSInstaller). .PARAMETER bacpacUrl If the app parameter is "BACPAC", the URL to download the .bacpac file from. .PARAMETER force If a bacpac is supplied, and a database for the specified tag already exists, force it to be dropped and recreated without prompting. .PARAMETER refresh Indicates that any currently cached copy of this file should be refreshed. .NOTES This script requires the use of the PublishDacPac module. Run Install-Module -Name PublishDacPac if you do not have this. By default, the database will use Trusted Connection. Provide dbUser and dbPassword if user/password need to be used. .EXAMPLE New-SWDatabase -app POS -tag POS-0000-beta .EXAMPLE New-SWDatabase -app POS -tag POS-0000-beta -dbServer remoteSqlHost -dbUser sa -dbPassword password -bacpac "C:\MyStartingData.bacpac" -force #> function New-SWDatabase { param( [Parameter(Mandatory=$true)] [ValidateSet("POS","SRDM","Reporting")] [string] $app, [Parameter(Mandatory=$true)] [string] $tag, [Parameter(Mandatory=$false)] [string] $dbServer = "localhost", [Parameter(Mandatory=$false)] [string] $dbName = "$tag-$app", [Parameter(Mandatory=$false)] [string] $dbUser = "", [Parameter(Mandatory=$false)] [string] $dbPassword = "", [Parameter(Mandatory=$false)] [string] $rootDir = "$env:PROGRAMDATA\SkyWire-PSInstaller", [Parameter(Mandatory=$false)] [string] $bacpacUrl, [Parameter(Mandatory=$false)] [switch] $force, [Parameter(Mandatory=$false)] [switch] $refresh ) $trustedConnection = !$dbUser -and !$dbPassword $connectionString = "Data Source=$dbServer;Initial Catalog=$dbName;Integrated Security=$trustedConnection;" if (!($trustedConnection)) { $connectionString = "$($connectionString)User Id=$dbUser;Password=$dbPassword;" } if ($bacpacUrl) { $bacpac = Get-SWInstallPackage -app "BACPAC" -bacpacUrl $bacpacUrl -tag $tag -rootDir $rootDir -refresh:$refresh try { if (Test-Path $bacpac) { $version = Select-SqlPackageVersion -PreferredVersion "120"; $sqlPackageExe = Get-SqlPackagePath -Version $version; if (!(Test-Path $sqlPackageExe)) { Write-Error "Could not find SqlPackage.exe in order to import the database BacPac!" Write-Warning "For install instructions, see https://www.microsoft.com/en-us/download/details.aspx?id=57784/" throw "Could not find SqlPackage.exe in order to import the database BacPac!" } #See if the database already exists: $sqlDbCheck = "SELECT [create_date] FROM [Sys].[databases] WHERE [Name] = '$dbName'" if ($trustedConnection) { $sqlDbCheckResult = Invoke-SqlCmd -Query $sqlDbCheck -ServerInstance $dbServer } else { $sqlDbCheckResult = Invoke-SqlCmd -Query $sqlDbCheck -ServerInstance $dbServer -Username $dbUser -Password $dbPassword } $dbCreatedDate = $sqlDbCheckResult.create_date $applyBacpac = 1 if ($dbCreatedDate) { Write-Warning "The database $dbName already exists!" if (!($force)) { $newDbNo = New-Object System.Management.Automation.Host.ChoiceDescription "&No", "Uses the existing database and ignores the specified bacpac" $newDbYes = New-Object System.Management.Automation.Host.ChoiceDescription "&Yes", "Deletes the existing database and creates a new one from the specified bacpac" $options = [System.Management.Automation.Host.ChoiceDescription[]]($newDbNo, $newDbYes) $promptMessage = "A database named '$dbName' was previously created at $dbCreatedDate. Would you like to delete it and create a new version from your bacpac?" $applyBacpac = $host.ui.PromptForChoice("Delete Previous Database", $promptMessage, $options, 0) } if ($applyBacpac -eq 1) { if ($trustedConnection) { Remove-SWDatabase -dbName $dbName -dbServer $dbServer } else { Remove-SWDatabase -dbName $dbName -dbServer $dbServer -dbUser $dbUser -dbPassword $dbPassword } } } if ($applyBacpac -eq 1) { #Create database from bacpac, then apply dacpac to update $argList = @( "/Action:Import", "/SourceFile:$bacpac", "/TargetConnectionString:$connectionString" ); Invoke-ExternalCommand -Command "$sqlPackageExe" -Arguments $argList; } } } catch { Write-Error "An error occurred while attempting to apply the bacpac. Please verify the URL is correct and try restarting the SQL Service, verifying yoru credentials, and running this script again." throw $_.Exception } } #Apply the dacpac to create a new DB, or upgrade the one we just imported try { $dacpac = Get-SWInstallPackage -app "$($app)DAC" -tag $tag -rootDir $rootDir -refresh $refresh $tempPublishProfile = New-TemporaryFile Rename-Item -Path $tempPublishProfile.FullName -NewName "$tempPublishProfile.publish.xml" $publishProfile = "$($tempPublishProfile.FullName).publish.xml" $xml = New-Object System.Collections.ArrayList $xml.Add('<?xml version="1.0" encoding="utf-8"?>') > $null $xml.Add('<Project ToolsVersion="Current" xmlns="http://schemas.microsoft.com/developer/msbuild/2003">') > $null $xml.Add(" <PropertyGroup>") > $null $xml.Add(" <IncludeCompositeObjects>True</IncludeCompositeObjects>") > $null $xml.Add(" <TargetDatabaseName>$tag-$app</TargetDatabaseName>") > $null $xml.Add(" <TargetConnectionString>$($connectionString)Pooling=False;Connect Timeout=60;Encrypt=False;TrustServerCertificate=False;</TargetConnectionString>") > $null $xml.Add(" <BlockOnPossibleDataLoss>False</BlockOnPossibleDataLoss>") > $null $xml.Add(" <ProfileVersionNumber>1</ProfileVersionNumber>") > $null $xml.Add(" </PropertyGroup>") > $null $xml.Add("</Project>") > $null $xml|Out-File $publishProfile #Create database from dacpac Publish-DacPac -DacPacPath $dacpac -DacPublishProfile $publishProfile -Server $dbServer -Database $dbName Remove-Item $publishProfile -Force } catch { Write-Error "An error occurred while attempting to apply the dacpac. This typically happens when the database is currently in use or the authentication is incorrect. Try restarting the SQL Service, verifying yoru credentials, and running this script again." throw $_.Exception } } <# .SYNOPSIS Removes a SkyWire database. .DESCRIPTION Removes the specified database from the specified server using the provided credentials (or windows account, if no credentials are provided). .PARAMETER dbName The name of the database to remove. .PARAMETER dbServer The database server (default: localhost). .PARAMETER dbUser The SQL user. Leave blank for integrated security. .PARAMETER dbPassword The SQL password. Leave blank for integrated security. .NOTES This script requires the use of the PublishDacPac module. Run Install-Module -Name PublishDacPac if you do not have this. By default, the database will use Trusted Connection. Provide dbUser and dbPassword if user/password need to be used. .EXAMPLE Remove-SWDatabase -dbName SkyWirePOS #> function Remove-SWDatabase { param ( [Parameter(Mandatory=$true)] [string] $dbName, [Parameter(Mandatory=$false)] [string] $dbServer = "localhost", [Parameter(Mandatory=$false)] [string] $dbUser = "", [Parameter(Mandatory=$false)] [string] $dbPassword = "" ) try { Write-Warning "Dropping existing $dbName database..." if (!$dbUser -and !$dbPassword) { Invoke-SqlCmd -Query "DROP DATABASE [$dbName]" -ServerInstance $dbServer } else { Invoke-SqlCmd -Query "DROP DATABASE [$dbName]" -ServerInstance $dbServer -Username $dbUser -Password $dbPassword } } catch { Write-Error "An error occurred while attempting to remove the previous database. This typically happens when the database is currently in use. Try restarting the SQL Service and running this script again." throw $_.Exception } } |