PureStorageDbaTools.psm1
|
function New-PfaDbSnapshot { param( [parameter(mandatory=$true)] [string] $Database ,[parameter(mandatory=$true)] [string] $SqlInstance ,[parameter(mandatory=$true)] [string] $PfaEndpoint ,[parameter(mandatory=$true)] [string] $PfaUser ,[parameter(mandatory=$true)] [string] $PfaPassword ) $StartMs = Get-Date Write-Host "Connecting to array endpoint" -ForegroundColor Yellow try { $FlashArray = New-PfaArray �EndPoint $PfaEndpoint -UserName $PfaUser -Password (ConvertTo-SecureString -AsPlainText $PfaPassword -Force) -IgnoreCertificateError } catch { $ExceptionMessage = $_.Exception.Message Write-Error "Failed to connect to FlashArray endpoint $PfaEndpoint with: $ExceptionMessage" Return } Write-Host "Connecting to snapshot target SQL Server instance" -ForegroundColor Yellow try { $DestDb = Get-DbaDatabase -sqlinstance $SqlInstance -Database $Database } catch { $ExceptionMessage = $_.Exception.Message Write-Error "Failed to connect to destination database $SqlInstance.$Database with: $ExceptionMessage" Return } $GetDbDisk = { param ( $Db ) $DbDisk = Get-partition -DriveLetter $Db.PrimaryFilePath.Split(':')[0]| Get-Disk return $DbDisk } Write-Host "Creating snapshot from windows drive < " $DestDb.PrimaryFilePath.Split(':')[0] ">" try { $TargetDisk = Invoke-Command -ScriptBlock $GetDbDisk -ArgumentList $DestDb } catch { $ExceptionMessage = $_.Exception.Message Write-Error "Failed to determine the windows disk snapshot target with: $ExceptionMessage" Return } Write-Host "Determining snapshot target FlashArray volume" -ForegroundColor Yellow try { $TargetVolume = Get-PfaVolume -Array $FlashArray | Where-Object { $_.serial -eq $TargetDisk.SerialNumber } | Select name } catch { $ExceptionMessage = $_.Exception.Message Write-Error "Failed to determine snapshot FlashArray volume with: $ExceptionMessage" Return } Write-Host "Target volume for snapshot is < " $TargetVolume ">" try { New-PfaVolumeSnapshots -Array $FlashArray -Sources $TargetVolume } catch { $ExceptionMessage = $_.Exception.Message Write-Error "Failed to create snapshot for target database FlashArray volume with: $ExceptionMessage" Return } } function Invoke-PfaDbRefresh { <# .SYNOPSIS A PowerShell function to refresh one or more SQL Server databases (the destination) from either a snapshot or database. .DESCRIPTION This PowerShell function uses calls to the PowerShell SDK and dbatools module functions to refresh one or more SQL Server server databases either from a snapshot (either supplied explicitly or from a list associated with a volume) or from a source database directly. .EXAMPLE # Refresh a single database from a snapshot selected from a list of snapshots associated with the volume specified by the # -RefreshSource parameter. $Targets = @("Z-STN-WIN2016-A\DEVOPSTST") Invoke-PfaDbRefresh -RefreshDatabase Invoke-PfaDbRefresh ` -RefreshSource z-sql2016-devops-prd ` -DestSqlInstance $Targets ` -PfaEndpoint 10.225.112.10 ` -PfaUser pureuser ` -PfaPassword pureuser ` -PromptForSnapshot .EXAMPLE # Refresh multiple databases from a snapshot selected from a list of snapshots associated with the volume specified by the # -RefreshSource parameter.$Targets = @("Z-STN-WIN2016-A\DEVOPSTST", "Z-STN-WIN2016-A\DEVOPSDEV") Invoke-PfaDbRefresh -RefreshDatabase Invoke-PfaDbRefresh ` -RefreshSource z-sql2016-devops-prd ` -DestSqlInstance $Targets ` -PfaEndpoint 10.225.112.10 ` -PfaUser pureuser ` -PfaPassword pureuser ` -PromptForSnapshot .EXAMPLE # Refresh a single database using the snapshot specified by the RefreshSource parameter: $Targets = @("Z-STN-WIN2016-A\DEVOPSTST") Invoke-PfaDbRefresh -RefreshDatabase Invoke-PfaDbRefresh ` -RefreshSource source-snap ` -DestSqlInstance $Targets ` -PfaEndpoint 10.225.112.10 ` -PfaUser pureuser ` -PfaPassword pureuser ` -RefreshFromSnapshot .EXAMPLE # Refresh multiple databases using the snapshot specified by the RefreshSource parameter: $Targets = @("Z-STN-WIN2016-A\DEVOPSTST", "Z-STN-WIN2016-A\DEVOPSDEV") Invoke-PfaDbRefresh -RefreshDatabase Invoke-PfaDbRefresh -RefreshSource source-snap ` -DestSqlInstance $Targets ` -PfaEndpoint 10.225.112.10 ` -PfaUser pureuser ` -PfaPassword pureuser ` -RefreshFromSnapshot #.EXAMPLE # Refresh a single database from the database specified by the SourceDatabase parameter residing on the instance specified by RefreshSource $Targets = @("Z-STN-WIN2016-A\DEVOPSTST") Invoke-PfaDbRefresh -$RefreshDatabase Invoke-PfaDbRefresh ` -RefreshSource z-sql-prd ` -DestSqlInstance $Targets ` -PfaEndpoint 10.225.112.10 ` -PfaUser pureuser ` -PfaPassword pureuser .EXAMPLE # Refresh multiple databases from the database specified by the SourceDatabase parameter residing on the instance specified by RefreshSource $Targets = @("Z-STN-WIN2016-A\DEVOPSTST", "Z-STN-WIN2016-A\DEVOPSDEV") Invoke-PfaDbRefresh -$RefreshDatabase Invoke-PfaDbRefresh ` -RefreshSource z-sql-prd ` -DestSqlInstance $Targets ` -PfaEndpoint 10.225.112.10 ` -PfaUser pureuser ` -PfaPassword pureuser .NOTES This function is part of the PureStorageDbaTools module, it is recommend that the module is always obtained from the PowerShell gallery: https://www.powershellgallery.com/packages/PureStorageDbaTools Note that it has dependencies on the dbatools and PureStoragePowerShellSDK modules which are installed as part of the installation of this module. This function is available under the Apache 2.0 license, stipulated as follows: Copyright 2017 Pure Storage, Inc. Licensed under the Apache License, Version 2.0 (the "License"); you may not use this file except in compliance with the License. You may obtain a copy of the License at http://www.apache.org/licenses/LICENSE-2.0 Unless required by applicable law or agreed to in writing, software distributed under the License is distributed on an "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. See the License for the specific language governing permissions and limitations under the License. .LINK TBD #> param( [parameter(mandatory=$true)] [string] $RefreshDatabase ,[parameter(mandatory=$true)] [string] $RefreshSource ,[parameter(mandatory=$true)] [string[]] $DestSqlInstances ,[parameter(mandatory=$true)] [string] $PfaEndpoint ,[parameter(mandatory=$true)] [string] $PfaUser ,[parameter(mandatory=$true)] [string] $PfaPassword ,[parameter(mandatory=$false)] [switch] $PromptForSnapshot ,[parameter(mandatory=$false)] [switch] $RefreshFromSnapshot ,[parameter(mandatory=$false)] [switch] $NoPsRemoting ) $StartMs = Get-Date if ( $PromptForSnapshot.IsPresent.Equals($false) -And $RefreshFromSnapshot.IsPresent.Equals($false) ) { Write-Host "Connecting to source SQL Server instance" -ForegroundColor Yellow try { $SourceDb = Get-DbaDatabase -sqlinstance $RefreshSource -Database $RefreshDatabase } catch { $ExceptionMessage = $_.Exception.Message Write-Error "Failed to connect to source database $RefreshSource.$Database with: $ExceptionMessage" Return } try { $SourceServer = (Connect-DbaInstance -SqlInstance $RefreshSource).ComputerNamePhysicalNetBIOS } catch { Write-Error "Failed to determine target server name with: $ExceptionMessage" } } Write-Host "Connecting to array endpoint" -ForegroundColor Yellow try { $FlashArray = New-PfaArray �EndPoint $PfaEndpoint -UserName $PfaUser -Password (ConvertTo-SecureString -AsPlainText $PfaPassword -Force) -IgnoreCertificateError } catch { $ExceptionMessage = $_.Exception.Message Write-Error "Failed to connect to FlashArray endpoint $PfaEndpoint with: $ExceptionMessage" Return } $GetDbDisk = { param ( $Db ) $DbDisk = Get-partition -DriveLetter $Db.PrimaryFilePath.Split(':')[0]| Get-Disk return $DbDisk } $Snapshots = $(Get-PfaAllVolumeSnapshots $FlashArray) $FilteredSnapshots = $Snapshots.where({ ([string]$_.Source) -eq $RefreshSource }) if ( $PromptForSnapshot.IsPresent ) { Write-Host ' ' for ($i=0; $i -lt $FilteredSnapshots.Count; $i++) { Write-Host 'Snapshot ' $i.ToString() $FilteredSnapshots[$i] } $SnapshotId = Read-Host -Prompt 'Enter the number of the snapshot to be used for the database refresh' } elseif ( $RefreshFromSnapshot.IsPresent.Equals( $false ) ) { try { if ( $NoPsRemoting.IsPresent ) { $SourceDisk = Invoke-Command -ScriptBlock $GetDbDisk -ArgumentList $SourceDb } else { $SourceDisk = Invoke-Command -ComputerName $SourceServer -ScriptBlock $GetDbDisk -ArgumentList $SourceDb } } catch { $ExceptionMessage = $_.Exception.Message Write-Error "Failed to determine source disk with: $ExceptionMessage" Return } try { $SourceVolume = Get-PfaVolumes -Array $FlashArray | Where-Object { $_.serial -eq $SourceDisk.SerialNumber } | Select name } catch { $ExceptionMessage = $_.Exception.Message Write-Error "Failed to determine source volume with: $ExceptionMessage" Return } } Foreach($DestSqlInstance in $DestSqlInstances) { Write-Host "Connecting to destination SQL Server instance" -ForegroundColor Yellow try { $DestDb = Get-DbaDatabase -sqlinstance $DestSqlInstance -Database $RefreshDatabase } catch { $ExceptionMessage = $_.Exception.Message Write-Error "Failed to connect to destination database $DestSqlInstance.$Database with: $ExceptionMessage" Return } try { $TargetServer = (Connect-DbaInstance -SqlInstance $DestSqlInstance).ComputerNamePhysicalNetBIOS } catch { Write-Error "Failed to determine target server name with: $ExceptionMessage" } $OfflineDestDisk = { param ( $DiskNumber, $Status ) Set-Disk -Number $DiskNumber -IsOffline $Status } try { if ( $NoPsRemoting.IsPresent ) { $DestDisk = Invoke-Command -ScriptBlock $GetDbDisk -ArgumentList $DestDb } else { $DestDisk = Invoke-Command -ComputerName $TargetServer -ScriptBlock $GetDbDisk -ArgumentList $DestDb } } catch { $ExceptionMessage = $_.Exception.Message Write-Error "Failed to determine destination database disk with: $ExceptionMessage" Return } try { $DestVolume = Get-PfaVolumes -Array $FlashArray | Where-Object { $_.serial -eq $DestDisk.SerialNumber } | Select name } catch { $ExceptionMessage = $_.Exception.Message Write-Error "Failed to determine destination FlashArray volume with: $ExceptionMessage" Return } $OfflineDestDisk = { param ( $DiskNumber, $Status ) Set-Disk -Number $DiskNumber -IsOffline $Status } Write-Host "Offlining destination database" -ForegroundColor Yellow try { $DestDb.SetOffline() } catch { $ExceptionMessage = $_.Exception.Message Write-Error "Failed to offline database $Database with: $ExceptionMessage" Return } Write-Host "Offlining destination Windows volume" -ForegroundColor Yellow try { if ( $NoPsRemoting.IsPresent ) { Invoke-Command -ScriptBlock $OfflineDestDisk -ArgumentList $DestDisk.Number, $True } else { Invoke-Command -ComputerName $TargetServer -ScriptBlock $OfflineDestDisk -ArgumentList $DestDisk.Number, $True } } catch { $ExceptionMessage = $_.Exception.Message Write-Error "Failed to offline disk with : $ExceptionMessage" Return } $StartCopyVolMs = Get-Date Write-Host ' ' try { if ( $PromptForSnapshot.IsPresent ) { Write-Host "Snap -> DB refresh: Overwriting destination FlashArray volume <" $DestVolume.name "> with snapshot <" $FilteredSnapshots[$SnapshotId].name ">" -ForegroundColor Yellow New-PfaVolume -Array $FlashArray -VolumeName $DestVolume.name -Source $FilteredSnapshots[$SnapshotId].name -Overwrite } elseif ( $RefreshFromSnapshot.IsPresent ) { Write-Host "DB -> DB refresh : Overwriting destination FlashArray volume <" $DestVolume.name "> with snapshot <" $RefreshSource ">" -ForegroundColor Yellow New-PfaVolume -Array $FlashArray -VolumeName $DestVolume.name -Source $RefreshSource -Overwrite } else { Write-Host "DB -> DB refresh : Overwriting destination FlashArray volume <" $DestVolume.name "> with a copy of the source volume <" $SourceVolume.name ">" -ForegroundColor Yellow New-PfaVolume -Array $FlashArray -VolumeName $DestVolume.name -Source $SourceVolume.name -Overwrite } } catch { $ExceptionMessage = $_.Exception.Message Write-Error "Failed to refresh test database volume with : $ExceptionMessage" Set-Disk -Number $DestDisk.Number -IsOffline $False $DestDb.SetOnline() Return } $EndCopyVolMs = Get-Date Write-Host "Volume overwrite duration (ms) = " ($EndCopyVolMs - $StartCopyVolMs).TotalMilliseconds -ForegroundColor Yellow Write-Host " " Write-Host "Onlining destination Windows volume" -ForegroundColor Yellow try { if ( $NoPsRemoting.IsPresent.Equals( $true ) ) { Invoke-Command -ScriptBlock $OfflineDestDisk -ArgumentList $DestDisk.Number, $False } else { Invoke-Command -ComputerName $TargetServer -ScriptBlock $OfflineDestDisk -ArgumentList $DestDisk.Number, $False } } catch { $ExceptionMessage = $_.Exception.Message Write-Error "Failed to online disk with : $ExceptionMessage" Return } Write-Host "Onlining destination database" -ForegroundColor Yellow try { $DestDb.SetOnline() } catch { $ExceptionMessage = $_.Exception.Message Write-Error "Failed to online database $Database with: $ExceptionMessage" Return } } $EndMs = Get-Date Write-Host " " Write-Host "-------------------------------------------------------" -ForegroundColor Green Write-Host " " Write-Host "D A T A B A S E R E F R E S H C O M P L E T E" -ForegroundColor Green Write-Host " " Write-Host " Duration (s) = " ($EndMs - $StartMs).TotalSeconds -ForegroundColor White Write-Host " " Write-Host "-------------------------------------------------------" -ForegroundColor Green } Export-ModuleMember -Function @('Invoke-PfaDbRefresh', 'New-PfaDbSnapshot') |