SqlPackageOnTargetMachines.ps1
|
function Import-SqlPs { push-location Import-Module SqlPS -ErrorAction 'SilentlyContinue' | out-null pop-location } function RunCommand { param( [string]$command, [bool] $failOnErr = $true ) $ErrorActionPreference = 'Continue' if( $psversiontable.PSVersion.Major -le 4) { $result = cmd.exe /c "`"$command`"" 2>&1 } else { Write-Verbose -Verbose $command $result = cmd.exe /c "$command" 2>&1 } $ErrorActionPreference = 'Stop' if($failOnErr -and $LASTEXITCODE -ne 0) { throw $result } return $result } function Get-SqlPackageOnTargetMachine { try { $sqlDacPath, $sqlVersion = LocateHighestVersionSqlPackageWithSql $sqlVersionNumber = [decimal] $sqlVersion } catch [System.Exception] { Write-Verbose ("Failed to get Dac Framework (installed with SQL Server) location with exception: " + $_.Exception.Message) $sqlVersionNumber = 0 } try { $sqlMsiDacPath, $sqlMsiVersion = LocateHighestVersionSqlPackageWithDacMsi $sqlMsiVersionNumber = [decimal] $sqlMsiVersion } catch [System.Exception] { Write-Verbose ("Failed to get Dac Framework (installed with DAC Framework) location with exception: " + $_.Exception.Message) $sqlMsiVersionNumber = 0 } try { $vsDacPath, $vsVersion = LocateHighestVersionSqlPackageInVS $vsVersionNumber = [decimal] $vsVersion } catch [System.Exception] { Write-Verbose ("Failed to get Dac Framework (installed with Visual Studio) location with exception: " + $_.Exception.Message) $vsVersionNumber = 0 } $maximumVersion = [decimal]$(@($vsVersionNumber, $sqlVersionNumber, $sqlMsiVersionNumber) | Measure-Object -Maximum).Maximum if ($sqlMsiVersionNumber -eq $maximumVersion) { $dacPath = $sqlMsiDacPath } elseif ($vsVersionNumber -eq $maximumVersion) { $dacPath = $vsDacPath } elseif ($sqlVersionNumber -eq $maximumVersion) { $dacPath = $sqlDacPath } if ($dacPath -eq $null) { throw "Unable to find the location of Dac Framework (SqlPackage.exe) from registry on machine $env:COMPUTERNAME" } else { return $dacPath } } function Get-RegistryValueIgnoreError { param ( [parameter(Mandatory = $true)] [Microsoft.Win32.RegistryHive] $RegistryHive, [parameter(Mandatory = $true)] [System.String] $Key, [parameter(Mandatory = $true)] [System.String] $Value, [parameter(Mandatory = $true)] [Microsoft.Win32.RegistryView] $RegistryView ) try { $baseKey = [Microsoft.Win32.RegistryKey]::OpenBaseKey($RegistryHive, $RegistryView) $subKey = $baseKey.OpenSubKey($Key) if($subKey -ne $null) { return $subKey.GetValue($Value) } } catch { } return $null } function Get-SubKeysInFloatFormat($keys) { $targetKeys = @() foreach ($key in $keys) { try { $targetKeys += [decimal] $key } catch {} } $targetKeys } function Get-SqlPackageForSqlVersion([int] $majorVersion, [bool] $wow6432Node) { $sqlInstallRootRegKey = "SOFTWARE", "Microsoft", "Microsoft SQL Server", "$majorVersion" -join [System.IO.Path]::DirectorySeparatorChar if ($wow6432Node -eq $true) { $sqlInstallRootPath = Get-RegistryValueIgnoreError LocalMachine "$sqlInstallRootRegKey" "VerSpecificRootDir" Registry64 } else { $sqlInstallRootPath = Get-RegistryValueIgnoreError LocalMachine "$sqlInstallRootRegKey" "VerSpecificRootDir" Registry32 } if ($sqlInstallRootPath -eq $null) { return $null } Write-Verbose "Sql Version Specific Root Dir for version $majorVersion as read from registry: $sqlInstallRootPath" $DacInstallPath = [System.IO.Path]::Combine($sqlInstallRootPath, "Dac", "bin", "SqlPackage.exe") if (Test-Path $DacInstallPath) { Write-Verbose "Dac Framework installed with SQL Version $majorVersion found at $DacInstallPath on machine $env:COMPUTERNAME" return $DacInstallPath } else { return $null } } function LocateHighestVersionSqlPackageWithSql() { $sqlRegKey = "HKLM:", "SOFTWARE", "Wow6432Node", "Microsoft", "Microsoft SQL Server"-join [System.IO.Path]::DirectorySeparatorChar $sqlRegKey64 = "HKLM:", "SOFTWARE", "Microsoft", "Microsoft SQL Server"-join [System.IO.Path]::DirectorySeparatorChar if (-not (Test-Path $sqlRegKey)) { $sqlRegKey = $sqlRegKey64 } if (-not (Test-Path $sqlRegKey)) { return $null, 0 } $keys = Get-Item $sqlRegKey | %{$_.GetSubKeyNames()} $versions = Get-SubKeysInFloatFormat $keys | Sort-Object -Descending Write-Verbose "Sql Versions installed on machine $env:COMPUTERNAME as read from registry: $versions" foreach ($majorVersion in $versions) { $DacInstallPathWow6432Node = Get-SqlPackageForSqlVersion $majorVersion $true $DacInstallPath = Get-SqlPackageForSqlVersion $majorVersion $false if ($DacInstallPathWow6432Node -ne $null) { return $DacInstallPathWow6432Node, $majorVersion } elseif ($DacInstallPath -ne $null) { return $DacInstallPath, $majorVersion } } Write-Verbose "Dac Framework (installed with SQL) not found on machine $env:COMPUTERNAME" return $null, 0 } function LocateHighestVersionSqlPackageWithDacMsi() { $sqlDataTierFrameworkRegKeyWow = "HKLM:", "SOFTWARE", "Wow6432Node", "Microsoft", "Microsoft SQL Server", "Data-Tier Application Framework" -join [System.IO.Path]::DirectorySeparatorChar $sqlDataTierFrameworkRegKey = "HKLM:", "SOFTWARE", "Microsoft", "Microsoft SQL Server", "Data-Tier Application Framework" -join [System.IO.Path]::DirectorySeparatorChar if (-not (Test-Path $sqlDataTierFrameworkRegKey)) { $sqlDataTierFrameworkRegKey = $sqlDataTierFrameworkRegKeyWow } if ((Test-Path $sqlDataTierFrameworkRegKey)) { $keys = Get-Item $sqlDataTierFrameworkRegKey | %{$_.GetSubKeyNames()} $versions = Get-SubKeysInFloatFormat $keys | Sort-Object -Descending $installedMajorVersion = 0 foreach ($majorVersion in $versions) { $sqlInstallRootRegKey = "SOFTWARE", "Microsoft", "Microsoft SQL Server", "Data-Tier Application Framework", "$majorVersion" -join [System.IO.Path]::DirectorySeparatorChar $sqlInstallRootPath64 = Get-RegistryValueIgnoreError LocalMachine "$sqlInstallRootRegKey" "InstallDir" Registry64 $sqlInstallRootPath32 = Get-RegistryValueIgnoreError LocalMachine "$sqlInstallRootRegKey" "InstallDir" Registry32 if ($sqlInstallRootPath64 -ne $null) { $sqlInstallRootPath = $sqlInstallRootPath64 break } if ($sqlInstallRootPath32 -ne $null) { $sqlInstallRootPath = $sqlInstallRootPath32 break } } $DacInstallPath = [System.IO.Path]::Combine($sqlInstallRootPath, "SqlPackage.exe") if (Test-Path $DacInstallPath) { Write-Verbose "Dac Framework installed with SQL Version $majorVersion found at $DacInstallPath on machine $env:COMPUTERNAME" return $DacInstallPath, $majorVersion } } $sqlRegKeyWow = "HKLM:", "SOFTWARE", "Wow6432Node", "Microsoft", "Microsoft SQL Server", "DACFramework", "CurrentVersion" -join [System.IO.Path]::DirectorySeparatorChar $sqlRegKey = "HKLM:", "SOFTWARE", "Microsoft", "Microsoft SQL Server", "DACFramework", "CurrentVersion" -join [System.IO.Path]::DirectorySeparatorChar $sqlKey = "SOFTWARE", "Microsoft", "Microsoft SQL Server", "DACFramework", "CurrentVersion" -join [System.IO.Path]::DirectorySeparatorChar if (Test-Path $sqlRegKey) { $dacVersion = Get-RegistryValueIgnoreError LocalMachine "$sqlKey" "Version" Registry64 $majorVersion = $dacVersion.Substring(0, $dacVersion.IndexOf(".")) + "0" } if (Test-Path $sqlRegKeyWow) { $dacVersionX86 = Get-RegistryValueIgnoreError LocalMachine "$sqlKey" "Version" Registry32 $majorVersionX86 = $dacVersionX86.Substring(0, $dacVersionX86.IndexOf(".")) + "0" } if ((-not($dacVersion)) -and (-not($dacVersionX86))) { Write-Verbose "Dac Framework (installed with DAC Framework) not found on machine $env:COMPUTERNAME" return $null, 0 } if ($majorVersionX86 -gt $majorVersion) { $majorVersion = $majorVersionX86 } $dacRelativePath = "Microsoft SQL Server", "$majorVersion", "DAC", "bin", "SqlPackage.exe" -join [System.IO.Path]::DirectorySeparatorChar $programFiles = $env:ProgramFiles $programFilesX86 = "${env:ProgramFiles(x86)}" if (-not ($programFilesX86 -eq $null)) { $dacPath = $programFilesX86, $dacRelativePath -join [System.IO.Path]::DirectorySeparatorChar if (Test-Path("$dacPath")) { Write-Verbose "Dac Framework (installed with DAC Framework Msi) found on machine $env:COMPUTERNAME at $dacPath" return $dacPath, $majorVersion } } if (-not ($programFiles -eq $null)) { $dacPath = $programFiles, $dacRelativePath -join [System.IO.Path]::DirectorySeparatorChar if (Test-Path($dacPath)) { Write-Verbose "Dac Framework (installed with DAC Framework Msi) found on machine $env:COMPUTERNAME at $dacPath" return $dacPath, $majorVersion } } return $null, 0 } function LocateSqlPackageFromVSInstallationRoot { [CmdletBinding()] Param ( [string] $VSInstallRoot ) Write-Verbose "Visual Studio install location: $VSInstallRoot" $sqlDacRoot = [System.IO.Path]::Combine($VSInstallRoot, "Extensions", "Microsoft", "SQLDB", "DAC") if (Test-Path $sqlDacRoot) { $sqlDacLocations = Get-ChildItem $sqlDacRoot | Sort-Object @{e={$_.Name -as [int]}} -Descending foreach ($sqlDacLocation in $sqlDacLocations) { $dacVersion = $sqlDacLocation.Name $dacFullPath = [System.IO.Path]::Combine($sqlDacLocation.FullName, "SqlPackage.exe") if(Test-Path $dacFullPath -pathtype leaf) { Write-Verbose "Dac Framework installed with Visual Studio found at $dacFullPath on machine $env:COMPUTERNAME" return $dacFullPath, $dacVersion } else { Write-Verbose "Unable to find Dac framework installed with Visual Studio at $($dacVersionDir.FullName) on machine $env:COMPUTERNAME" } } } return $null, 0 } function LocateSqlPackageInVS([string] $version) { $vsRegKeyForVersion = "SOFTWARE", "Microsoft", "VisualStudio", $version -join [System.IO.Path]::DirectorySeparatorChar $vsInstallDir = Get-RegistryValueIgnoreError LocalMachine "$vsRegKeyForVersion" "InstallDir" Registry64 if ($vsInstallDir -eq $null) { $vsInstallDir = Get-RegistryValueIgnoreError LocalMachine "$vsRegKeyForVersion" "InstallDir" Registry32 } if ($vsInstallDir) { return (LocateSqlPackageFromVSInstallationRoot -VSInstallRoot $vsInstallDir) } return $null, 0 } function Find-VSWhere { $vsWhereLocation = [System.IO.Path]::Combine(${env:ProgramFiles(x86)}, 'Microsoft Visual Studio', 'Installer', 'vswhere.exe') if (Test-Path $vsWhereLocation) { Write-Verbose "vswhere.exe location:'$vsWhereLocation'" return $vsWhereLocation } return $null } function LocateLatestVSVersionUsingVSWhere { [CmdletBinding()] Param ([string] $VSWherePath) Remove-Item variable:\LASTEXITCODE -ErrorAction 'SilentlyContinue' $vsInstallations = & $VSWherePath "-legacy" "-prerelease" "-format" "json" $vsInstallations = $($vsInstallations -join '').Trim() if ($LASTEXITCODE -ne 0) { # if lastexitcode is not 0, then vsinstallations variable will contain the error string throw "VSWhere exitcode: '$LASTEXITCODE', error: '$vsInstallations'" } if (![string]::IsNullOrEmpty($vsInstallations)) { $vsInstallations = ConvertFrom-Json $vsInstallations $maxVersion = [version]::new('0.0.0.0') $vsPath = '' foreach ($vsInstallation in $vsInstallations) { $version = [version]::new($vsInstallation.installationVersion) if ($version -gt $maxVersion) { $maxVersion = $version $vsPath = $vsInstallation.installationPath } } Write-Verbose "Latest Visual Studio (version: '$($maxVersion.ToString()))' found at: '$vsPath'" return $vsPath } Write-Verbose "Cannot locate any Visual Studio installation using vswhere.exe". return $null } function LocateHighestVersionSqlPackageInVS() { $vsWherePath = Find-VSWhere if (![string]::IsNullOrEmpty($vsWherePath)) { try { $vsPath = LocateLatestVSVersionUsingVSWhere -VSWherePath $vsWherePath -ErrorAction 'Stop' if (![string]::IsNullOrEmpty($vsPath)) { $vsPath = [System.IO.Path]::Combine($vsPath, 'Common7', 'IDE') $dacFullPath, $dacVersion = LocateSqlPackageFromVSInstallationRoot -VSInstallRoot $vsPath if ($dacFullPath -ne $null) { Write-Verbose "Detected sqlpackage.exe from Visual Studio installation using vswhere.exe. SqlPackage location: $dacFullPath, version: $dacVersion" return $dacFullPath, $dacVersion } } } catch { Write-Verbose "Unable to locate sqlpackage.exe from Visual Studio installation using vswhere. Error: $($_.Exception.Message)" } } # fallback to detecting sqlpackage using the registry method if no vswhere is found or if an error was encountered $vsRegKey = "HKLM:", "SOFTWARE", "Wow6432Node", "Microsoft", "VisualStudio" -join [System.IO.Path]::DirectorySeparatorChar $vsRegKey64 = "HKLM:", "SOFTWARE", "Microsoft", "VisualStudio" -join [System.IO.Path]::DirectorySeparatorChar if (-not (Test-Path $vsRegKey)) { $vsRegKey = $vsRegKey64 } if (-not (Test-Path $vsRegKey)) { Write-Verbose "Visual Studio not found on machine $env:COMPUTERNAME" return $null, 0 } $keys = Get-Item $vsRegKey | %{$_.GetSubKeyNames()} $versions = Get-SubKeysInFloatFormat $keys | Sort-Object -Descending Write-Verbose "Visual Studio versions found on machine $env:COMPUTERNAME as read from registry: $versions" foreach ($majorVersion in $versions) { $dacFullPath, $dacVersion = LocateSqlPackageInVS $majorVersion if ($dacFullPath -ne $null) { return $dacFullPath, $dacVersion } } Write-Verbose "Dac Framework (installed with Visual Studio) not found on machine $env:COMPUTERNAME" return $null, 0 } function Get-SqlPackageCmdArgs { param ( [string]$dacpacFile, [string]$targetMethod, [string]$serverName, [string]$databaseName, [string]$authscheme, [System.Management.Automation.PSCredential]$sqlServerCredentials, [string]$connectionString, [string]$publishProfile, [string]$additionalArguments ) Write-Verbose -Verbose "File is $dacpacFile" # validate dacpac file if ([System.IO.Path]::GetExtension($dacpacFile) -ne ".dacpac") { throw "Invalid Dacpac file [ $dacpacFile ] provided" } $sqlPkgCmdArgs = [string]::Format(' /SourceFile:"{0}" /Action:Publish', $dacpacFile) if($targetMethod -eq "server") { $sqlPkgCmdArgs = [string]::Format('{0} /TargetServerName:"{1}"', $sqlPkgCmdArgs, $serverName) if ($databaseName) { $sqlPkgCmdArgs = [string]::Format('{0} /TargetDatabaseName:"{1}"', $sqlPkgCmdArgs, $databaseName) } if($authscheme -eq "sqlServerAuthentication") { if($sqlServerCredentials) { $sqlUsername = $sqlServerCredentials.UserName $sqlPassword = $sqlServerCredentials.GetNetworkCredential().password $sqlPkgCmdArgs = [string]::Format('{0} /TargetUser:"{1}" /TargetPassword:"{2}"', $sqlPkgCmdArgs, $sqlUsername, $sqlPassword) } } } elseif($targetMethod -eq "connectionString") { $sqlPkgCmdArgs = [string]::Format('{0} /TargetConnectionString:"{1}"', $sqlPkgCmdArgs, $connectionString) } if( ![string]::IsNullOrWhiteSpace($publishProfile) ) { # validate publish profile if ([System.IO.Path]::GetExtension($publishProfile) -ne ".xml") { throw "Invalid Publish Profile [ $publishProfile ] provided" } $sqlPkgCmdArgs = [string]::Format('{0} /Profile:"{1}"', $sqlPkgCmdArgs, $publishProfile) } $sqlPkgCmdArgs = [string]::Format('{0} {1}', $sqlPkgCmdArgs, $additionalArguments) Write-Verbose "Sqlpackage.exe arguments : $sqlPkgCmdArgs" return $sqlPkgCmdArgs } function Invoke-DacpacDeployment { param ( [string]$dacpacFile, [string]$targetMethod, [string]$serverName, [string]$databaseName, [string]$authscheme, [System.Management.Automation.PSCredential]$sqlServerCredentials, [string]$connectionString, [string]$publishProfile, [string]$additionalArguments ) Write-Verbose "Entering script SqlPackageOnTargetMachines.ps1" Import-SqlPs $sqlPackage = Get-SqlPackageOnTargetMachine $sqlPackageArguments = Get-SqlPackageCmdArgs -dacpacFile $dacpacFile -targetMethod $targetMethod -serverName $serverName -databaseName $databaseName -authscheme $authscheme -sqlServerCredentials $sqlServerCredentials -connectionString $connectionString -publishProfile $publishProfile -additionalArguments $additionalArguments if($databaseName -Split "" -Contains "*") { $databaseNamePattern = $databaseName -Replace "*", "%" -Replace "_", "[_]" $dbNames=Invoke-Sqlcmd -Query "SELECT name FROM sys.databases WHERE state=0 AND name LIKE '$dbNamePattern' ORDER BY name" -ConnectionString $connectionString foreach($dbName in $dbNames) { Write-Verbose "Found Database $($dbName)"; } } $sqlInvokeCmd = Invoke-SqlCmd -ConnectionString $connectionString Write-Verbose -Verbose $sqlPackageArguments Write-Verbose "Executing command: $sqlPackage $sqlPackageArguments" ExecuteCommand -FileName "$sqlPackage" -Arguments $sqlPackageArguments } function ExecuteCommand { param( [String][Parameter(Mandatory=$true)] $FileName, [String][Parameter(Mandatory=$true)] $Arguments ) if( $psversiontable.PSVersion.Major -lt 4) { $ErrorActionPreference = 'Continue' $command = "`"$FileName`" $Arguments" $result = cmd.exe /c "`"$command`"" } else { $ErrorActionPreference = 'SilentlyContinue' $result = "" Invoke-Expression "& '$FileName' --% $Arguments" -ErrorVariable errors | ForEach-Object { $result += ("$_ " + [Environment]::NewLine) } foreach($errorMsg in $errors){ $result += "$errorMsg " } } $ErrorActionPreference = 'Stop' if($LASTEXITCODE -ne 0) { Write-Verbose "Deployment failed with error : $result" throw $result } return $result } |