PureStorageDbaTools.psm1
|
function New-PfaDbSnapshot { <# .SYNOPSIS A PowerShell function to create a FlashArray snapshot of the volume that a database resides on. .DESCRIPTION A PowerShell function to create a FlashArray snapshot of the volume that a database resides on, based in the values of the following parameters: .PARAMETER Database The name of the database to refresh, note that it is assumed that source and target database(s) are named the same. This parameter is MANDATORY. .PARAMETER SqlInstance This can be one or multiple SQL Server instance(s) that host the database(s) to be refreshed, in the case that the function is invoked to refresh databases across more than one instance, the list of target instances should be spedcified as an array of strings, otherwise a single string representing the target instance will suffice. This parameter is MANDATORY. .PARAMETER PfaEndpoint The ip address representing the FlashArray that the volumes for the source and refresh target databases reside on. This parameter is MANDATORY. .PARAMETER PfaCredentials A PSCredential object containing the username and password of the FlashArray to connect to. For instruction on how to store and retrieve these from an encrypted file, refer to this article https://www.purepowershellguy.com/?p=8431 .EXAMPLE New-PfaDbSnapshot -Database tpch-no-compression -SqlInstance z-sql2016-devops-prd -PfaEndpoint 10.225.112.10 -PfaCredentials $Cred Create a snapshot of FlashArray volume that stores the tpch-no-compression database on the z-sql2016-devops-prd instance -RefreshSource parameter. .NOTES Known Restrictions ------------------ 1. This function does not currently work for databases associated with failover cluster instances. 2. This function cannot be used to seed secondary replicas in availability groups using databases in the primary replica. 3. The function assumes that all database files and the transaction log reside on a single FlashArray volume. Obtaining The PureStorageDbaTools Module ---------------------------------------- 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. Licence ------- 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 https://www.powershellgallery.com/packages/PureStorageDbaTools https://www.purepowershellguy.com/?p=8431 Invoke-PfaDbaRefresh Enable-DataMasks #> param( [parameter(mandatory=$true)] [string] $Database ,[parameter(mandatory=$true)] [string] $SqlInstance ,[parameter(mandatory=$true)] [string] $PfaEndpoint ,[parameter(mandatory=$true)] [System.Management.Automation.PSCredential] $PfaCredentials ) $currentPrincipal = New-Object Security.Principal.WindowsPrincipal([Security.Principal.WindowsIdentity]::GetCurrent()) if ( ! $currentPrincipal.IsInRole([Security.Principal.WindowsBuiltInRole]::Administrator) ) { Write-Error "This function needs to be invoked within a PowerShell session with elevated admin rights" Return } try { $FlashArray = New-PfaArray -EndPoint $PfaEndpoint -Credentials $PfaCredentials -IgnoreCertificateError } catch { $ExceptionMessage = $_.Exception.Message Write-Error "Failed to connect to FlashArray endpoint $PfaEndpoint with: $ExceptionMessage" Return } Write-Colour -Text "FlashArray endpoint : ", "CONNECTED" -Color Yellow, Green 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 } Write-Colour -Text "Target SQL Server instance: ", $SqlInstance, " - ", "CONNECTED" -Color Yellow, Green, Green, Green Write-Colour -Text "Target windows drive : ", $DestDb.PrimaryFilePath.Split(':')[0] -Color Yellow, Green try { $TargetServer = (Connect-DbaInstance -SqlInstance $SqlInstance).ComputerNamePhysicalNetBIOS } catch { Write-Error "Failed to determine target server name with: $ExceptionMessage" } Write-Colour -Text "Target SQL Server host : ", $TargetServer -ForegroundColor Yellow, Green $GetDbDisk = { param ( $Db ) $DbDisk = Get-Partition -DriveLetter $Db.PrimaryFilePath.Split(':')[0]| Get-Disk return $DbDisk } try { $TargetDisk = Invoke-Command -ComputerName $TargetServer -ScriptBlock $GetDbDisk -ArgumentList $DestDb } catch { $ExceptionMessage = $_.Exception.Message Write-Error "Failed to determine the windows disk snapshot target with: $ExceptionMessage" Return } Write-Colour -Text "Target disk serial number : ", $TargetDisk.SerialNumber -Color Yellow, Green try { $TargetVolume = Get-PfaVolumes -Array $FlashArray | Where-Object { $_.serial -eq $TargetDisk.SerialNumber } | Select-Object name } catch { $ExceptionMessage = $_.Exception.Message Write-Error "Failed to determine snapshot FlashArray volume with: $ExceptionMessage" Return } $SnapshotSuffix = $SqlInstance.Replace('\', '-') + '-' + $Database + '-' + $(Get-Date).Hour + $(Get-Date).Minute + $(Get-Date).Second Write-Colour -Text "Snapshot target Pfa volume: ", $TargetVolume.name -Color Yellow, Green Write-Colour -Text "Snapshot suffix : ", $SnapshotSuffix -Color Yellow, Green try { New-PfaVolumeSnapshots -Array $FlashArray -Sources $TargetVolume.name -Suffix $SnapshotSuffix } catch { $ExceptionMessage = $_.Exception.Message Write-Error "Failed to create snapshot for target database FlashArray volume with: $ExceptionMessage" Return } } function DbRefresh { param( [parameter(mandatory=$true)] [string] $DestSqlInstance ,[parameter(mandatory=$true)] [string] $RefreshDatabase ,[parameter(mandatory=$true)] [string] $PfaEndpoint ,[parameter(mandatory=$true)] [System.Management.Automation.PSCredential] $PfaCredentials ,[parameter(mandatory=$true)] [string] $SourceVolume ,[parameter(mandatory=$false)] [string] $StaticDataMaskFile ,[parameter(mandatory=$false)] [bool] $ForceDestDbOffline ,[parameter(mandatory=$false)] [bool] $NoPsRemoting ,[parameter(mandatory=$false)] [bool] $PromptForSnapshot ,[parameter(mandatory=$false)] [bool] $ApplyDataMasks ) try { $FlashArray = New-PfaArray -EndPoint $PfaEndpoint -Credentials $PfaCredentials -IgnoreCertificateError } catch { $ExceptionMessage = $_.Exception.Message Write-Error "Failed to connect to FlashArray endpoint $PfaEndpoint with: $ExceptionMessage" Return } 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 } Write-Host " " Write-Colour -Text "Target SQL Server instance: ", $DestSqlInstance, "- CONNECTED" -ForegroundColor Yellow, Green, Green try { $TargetServer = (Connect-DbaInstance -SqlInstance $DestSqlInstance).ComputerNamePhysicalNetBIOS } catch { Write-Error "Failed to determine target server name with: $ExceptionMessage" } Write-Colour -Text "Target SQL Server host : ", $TargetServer -ForegroundColor Yellow, Green $GetDbDisk = { param ( $Db ) $DbDisk = Get-Partition -DriveLetter $Db.PrimaryFilePath.Split(':')[0]| Get-Disk return $DbDisk } $GetVolumeLabel = { param ( $Db ) Write-Verbose "Target database drive letter = $Db.PrimaryFilePath.Split(':')[0]" $VolumeLabel = $(Get-Volume -DriveLetter $Db.PrimaryFilePath.Split(':')[0]).FileSystemLabel Write-Verbose "Target database windows volume label = <$VolumeLabel>" return $VolumeLabel } try { if ( $NoPsRemoting ) { $DestDisk = Invoke-Command -ScriptBlock $GetDbDisk -ArgumentList $DestDb $DestVolumeLabel = Invoke-Command -ScriptBlock $GetVolumeLabel -ArgumentList $DestDb } else { $DestDisk = Invoke-Command -ComputerName $TargetServer -ScriptBlock $GetDbDisk -ArgumentList $DestDb $DestVolumeLabel = Invoke-Command -ComputerName $TargetServer -ScriptBlock $GetVolumeLabel -ArgumentList $DestDb } } catch { $ExceptionMessage = $_.Exception.Message Write-Error "Failed to determine destination database disk with: $ExceptionMessage" Return } Write-Colour -Text "Target drive letter : ", $DestDb.PrimaryFilePath.Split(':')[0] -ForegroundColor Yellow, Green try { $DestVolume = Get-PfaVolumes -Array $FlashArray | Where-Object { $_.serial -eq $DestDisk.SerialNumber } | Select-Object name if (!$DestVolume) { throw "Failed to determine destination FlashArray volume, check that source and destination volumes are on the SAME array" } } catch { $ExceptionMessage = $_.Exception.Message Write-Error "Failed to determine destination FlashArray volume with: $ExceptionMessage" Return } Write-Colour -Text "Target Pfa volume : ", $DestVolume.name -ForegroundColor Yellow, Green $OfflineDestDisk = { param ( $DiskNumber, $Status ) Set-Disk -Number $DiskNumber -IsOffline $Status } try { if ( $ForceDestDbOffline ) { $ForceDatabaseOffline = "ALTER DATABASE [$RefreshDatabase] SET OFFLINE WITH ROLLBACK IMMEDIATE" Invoke-DbaQuery -ServerInstance $DestSqlInstance -Database $RefreshDatabase -Query $ForceDatabaseOffline } else { $DestDb.SetOffline() } } catch { $ExceptionMessage = $_.Exception.Message Write-Error "Failed to offline database $Database with: $ExceptionMessage" Return } Write-Colour -Text "Target database : ", "OFFLINE" -ForegroundColor Yellow, Green try { if ( $NoPsRemoting ) { 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 } Write-Colour -Text "Target windows disk : ", "OFFLINE" -ForegroundColor Yellow, Green $StartCopyVolMs = Get-Date try { Write-Colour -Text "Source Pfa volume : ", $SourceVolume -ForegroundColor Yellow, Green New-PfaVolume -Array $FlashArray -VolumeName $DestVolume.name -Source $SourceVolume -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 } Write-Colour -Text "Volume overwrite : ", "SUCCESSFUL" -ForegroundColor Yellow, Green $EndCopyVolMs = Get-Date Write-Colour -Text "Overwrite duration (ms) : ", ($EndCopyVolMs - $StartCopyVolMs).TotalMilliseconds -Color Yellow, Green $SetVolumeLabel = { param ( $Db, $DestVolumeLabel ) Set-Volume -DriveLetter $Db.PrimaryFilePath.Split(':')[0] -NewFileSystemLabel $DestVolumeLabel } try { if ( $NoPsRemoting ) { Invoke-Command -ScriptBlock $OfflineDestDisk -ArgumentList $DestDisk.Number, $False Invoke-Command -ScriptBlock $SetVolumeLabel -ArgumentList $DestDb, $DestVolumeLabel } else { Invoke-Command -ComputerName $TargetServer -ScriptBlock $OfflineDestDisk -ArgumentList $DestDisk.Number, $False Invoke-Command -ComputerName $TargetServer -ScriptBlock $SetVolumeLabel -ArgumentList $DestDb, $DestVolumeLabel } } catch { $ExceptionMessage = $_.Exception.Message Write-Error "Failed to online disk with : $ExceptionMessage" Return } Write-Colour -Text "Target windows disk : ", "ONLINE" -ForegroundColor Yellow, Green try { $DestDb.SetOnline() } catch { $ExceptionMessage = $_.Exception.Message Write-Error "Failed to online database $Database with: $ExceptionMessage" Return } Write-Colour -Text "Target database : ", "ONLINE" -ForegroundColor Yellow, Green if ( $ApplyDataMasks ) { Write-Host "Applying SQL Server dynamic data masks to $RefreshDatabase on SQL Server instance $DestSqlInstance" -ForegroundColor Yellow try { Invoke-DynamicDataMasking -SqlInstance $DestSqlInstance -Database $RefreshDatabase Write-Host "SQL Server dynamic data masking has been applied" -ForegroundColor Yellow } catch { $ExceptionMessage = $_.Exception.Message Write-Error "Failed to apply SQL Server dynamic data masks to $Database on $DestSqlInstance with: $ExceptionMessage" Return } } elseif ([System.IO.File]::Exists($StaticDataMaskFile)) { Write-Color -Text "Static data mask target : ", $DestSqlInstance, " - ", $RefreshDatabase -Color Yellow, Green, Green, Green try { Invoke-StaticDataMasking -SqlInstance $DestSqlInstance -Database $RefreshDatabase -DataMaskFile $StaticDataMaskFile Write-Color -Text "Static data masking : ", "APPLIED" -ForegroundColor Yellow, Green } catch { $ExceptionMessage = $_.Exception.Message Write-Error "Failed to apply static data masking to $Database on $DestSqlInstance with: $ExceptionMessage" Return } } Repair-DbaDbOrphanUser -SqlInstance $DestSqlInstance -Database $RefreshDatabase | Out-Null Write-Color -Text "Orphaned users : ", "REPAIRED" -ForegroundColor Yellow, Green } function Invoke-PfaDbRefresh { <# .SYNOPSIS A PowerShell function to refresh one or more SQL Server databases (the destination) from either a snapshot or database. .DESCRIPTION A PowerShell function to refresh one or more SQL Server databases either from: - a snapshot specified by its name - a snapshot picked from a list associated with the volume the source database resides on - a source database directly This function will detect and repair orpaned users in refreshed databases and optionally apply data masking, based on either: - the dynamic data masking functionality available in SQL Server version 2016 onwards, - static data masking built into dbatooils from version 0.9.725, refer to https://dbatools.io/mask/ .PARAMETER RefreshDatabase The name of the database to refresh, note that it is assumed that source and target database(s) are named the same. This parameter is MANDATORY. .PARAMETER RefreshSource If the RefreshFromSnapshot flag is specified, this parameter takes the name of a snapshot, otherwise this takes the name of the source SQL Server instance. This parameter is MANDATORY. .PARAMETER DestSqlInstance This can be one or multiple SQL Server instance(s) that host the database(s) to be refreshed, in the case that the function is invoked to refresh databases across more than one instance, the list of target instances should be spedcified as an array of strings, otherwise a single string representing the target instance will suffice. This parameter is MANDATORY. .PARAMETER PfaEndpoint The ip address representing the FlashArray that the volumes for the source and refresh target databases reside on. This parameter is MANDATORY. .PARAMETER PfaCredentials A PSCredential object containing the username and password of the FlashArray to connect to. For instruction on how to store and retrieve these from an encrypted file, refer to this article https://www.purepowershellguy.com/?p=8431 .PARAMETER PollJobInterval Interval at which background job status is poll, if this is ommited polling will not take place. Note that this parameter is not applicable is the PromptForSnapshot switch is specified. .PARAMETER PromptForSnapshot This is an optional flag that if specified will result in a list of snapshots being displayed for the database volume on the FlashArray that the user can select one from. Despite the source of the refresh operation being an existing snapshot , the source instance still has to be specified by the RefreshSource parameter in order that the function can determine which FlashArray volume to list existing snapshots for. .PARAMETER RefreshFromSnapshot This is an optional flag that if specified causes the function to expect the RefreshSource parameter to be supplied with the name of an existing snapshot. .PARAMETER NoPsRemoting The commands that off and online the windows volumes associated with the refresh target databases will use Invoke-Command with powershell remoting unless this flag is specified. Certain tools that can invoke PowerShell, Ansible for example do not permit double-hop authentication unless CredSSP authentication is used. For security purposes Kerberos is recommend over CredSSP, however this does not support double-hop authentication, in which case this flag should be specified. .PARAMETER ApplyDataMasks Specifying this optional masks will cause data masks to be applied , as per the dynamic data masking feature first introduced with SQL Server 2016, this results in this function invoking the Enable-DataMasks function to be invoked. For documentation on Enable-DataMasks, use the command Get-Help Enable-DataMasks [-Detailed]. .PARAMETER ForceDestDbOffline Specifying this switch will cause refresh target databases for be forced offline via WITH ROLLBACK IMMEDIATE. .PARAMETER StaticDataMaskFile If this parameter is present and has a file path associated with it, the data masking available in version 0.9.725 of the dbatools module onwards will be applied to the refreshed database. The use of this is contigent on the data mask file being created and populated in the first place as per this blog post: https://dbatools.io/mask/ . .EXAMPLE Invoke-PfaDbRefresh -RefreshDatabase tpch-no-compression ` -RefreshSource z-sql2016-devops-prd ` -DestSqlInstance z-sql2016-devops-tst ` -PfaEndpoint 10.225.112.10 ` -PfaCredentials $Creds ` -PromptForSnapshot Refresh a single database from a snapshot selected from a list of snapshots associated with the volume specified by the RefreshSource parameter. .EXAMPLE $Targets = @("z-sql2016-devops-tst", "z-sql2016-devops-dev") Invoke-PfaDbRefresh -RefreshDatabase tpch-no-compression ` -RefreshSource z-sql2016-devops-prd ` -DestSqlInstance $Targets ` -PfaEndpoint 10.225.112.10 ` -PfaCredentials $Creds ` -PromptForSnapshot Refresh multiple databases from a snapshot selected from a list of snapshots associated with the volume specified by the RefreshSource parameter. .EXAMPLE Invoke-PfaDbRefresh -RefreshDatabase tpch-no-compression ` -RefreshSource source-snap ` -DestSqlInstance z-sql2016-devops-tst ` -PfaEndpoint 10.225.112.10 ` -PfaCredentials $Creds ` -RefreshFromSnapshot Refresh a single database using the snapshot specified by the RefreshSource parameter. .EXAMPLE $Targets = @("z-sql2016-devops-tst", "z-sql2016-devops-dev") Invoke-PfaDbRefresh -RefreshDatabase tpch-no-compression ` -RefreshSource source-snap ` -DestSqlInstance $Targets ` -PfaEndpoint 10.225.112.10 ` -PfaCredentials $Creds ` -RefreshFromSnapshot Refresh multiple databases using the snapshot specified by the RefreshSource parameter. .EXAMPLE Invoke-PfaDbRefresh -$RefreshDatabase tpch-no-compression ` -RefreshSource z-sql-prd ` -DestSqlInstance z-sql2016-devops-tst ` -PfaEndpoint 10.225.112.10 ` -PfaCredentials $Creds Refresh a single database from the database specified by the SourceDatabase parameter residing on the instance specified by RefreshSource. .EXAMPLE $Targets = @("z-sql2016-devops-tst", "z-sql2016-devops-dev") Invoke-PfaDbRefresh -$RefreshDatabase tpch-no-compression ` -RefreshSource z-sql-prd ` -DestSqlInstance $Targets ` -PfaEndpoint 10.225.112.10 ` -PfaCredentials $Creds Refresh multiple databases from the database specified by the SourceDatabase parameter residing on the instance specified by RefreshSource. .EXAMPLE $Targets = @("z-sql2016-devops-tst", "z-sql2016-devops-dev") Invoke-PfaDbRefresh -$RefreshDatabase tpch-no-compression ` -RefreshSource z-sql-prd ` -DestSqlInstance $Targets ` -PfaEndpoint 10.225.112.10 ` -PfaCredentials $Creds ` -ApplyDataMasks Refresh multiple databases from the database specified by the SourceDatabase parameter residing on the instance specified by RefreshSource. .EXAMPLE $StaticDataMaskFile = "D:\apps\datamasks\z-sql-prd.tpch-no-compression.tables.json" $Targets = @("z-sql2016-devops-tst", "z-sql2016-devops-dev") Invoke-PfaDbRefresh -$RefreshDatabase tpch-no-compression ` -RefreshSource z-sql-prd ` -DestSqlInstance $Targets ` -PfaEndpoint 10.225.112.10 ` -PfaCredentials $Creds ` -StaticDataMaskFile $StaticDataMaskFile Refresh multiple databases from the database specified by the SourceDatabase parameter residing on the instance specified by RefreshSource and apply SQL Server dynamic data masking to each database. .EXAMPLE $StaticDataMaskFile = "D:\apps\datamasks\z-sql-prd.tpch-no-compression.tables.json" $Targets = @("z-sql2016-devops-tst", "z-sql2016-devops-dev") Invoke-PfaDbRefresh -$RefreshDatabase tpch-no-compression ` -RefreshSource z-sql-prd ` -DestSqlInstance $Targets ` -PfaEndpoint 10.225.112.10 ` -PfaCredentials $Creds ` -ForceDestDbOffline ` -StaticDataMaskFile $StaticDataMaskFile Refresh multiple databases from the database specified by the SourceDatabase parameter residing on the instance specified by RefreshSource and apply SQL Server dynamic data masking to each database. All databases to be refreshed are forced offline prior to their underlying FlashArray volumes being overwritten. .EXAMPLE $StaticDataMaskFile = "D:\apps\datamasks\z-sql-prd.tpch-no-compression.tables.json" $Targets = @("z-sql2016-devops-tst", "z-sql2016-devops-dev") Invoke-PfaDbRefresh -$RefreshDatabase tpch-no-compression ` -RefreshSource z-sql-prd ` -DestSqlInstance $Targets ` -PfaEndpoint 10.225.112.10 ` -PfaCredentials $Creds -PollJobInterval 10 ` -ForceDestDbOffline ` -StaticDataMaskFile $StaticDataMaskFile Refresh multiple databases from the database specified by the SourceDatabase parameter residing on the instance specified by RefreshSource and apply SQL Server dynamic data masking to each database. All databases to be refreshed are forced offline prior to their underlying FlashArray volumes being overwritten. Poll the status of the refresh jobs once every 10 seconds. .NOTES Known Restrictions ------------------ 1. This function does not currently work for databases associated with failover cluster instances. 2. This function cannot be used to seed secondary replicas in availability groups using databases in the primary replica. 3. The function assumes that all database files and the transaction log reside on a single FlashArray volume. Obtaining The PureStorageDbaTools Module ---------------------------------------- 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. Licence ------- 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 https://www.powershellgallery.com/packages/PureStorageDbaTools https://www.purepowershellguy.com/?p=8431 https://dbatools.io/mask/ New-PfaDbSnapshot Enable-DataMasks #> param( [parameter(mandatory=$true)] [string] $RefreshDatabase ,[parameter(mandatory=$true)] [string] $RefreshSource ,[parameter(mandatory=$true)] [string[]] $DestSqlInstances ,[parameter(mandatory=$true)] [string] $PfaEndpoint ,[parameter(mandatory=$true)] [System.Management.Automation.PSCredential] $PfaCredentials ,[parameter(mandatory=$false)] [int] $PollJobInterval ,[parameter(mandatory=$false)] [switch] $PromptForSnapshot ,[parameter(mandatory=$false)] [switch] $RefreshFromSnapshot ,[parameter(mandatory=$false)] [switch] $NoPsRemoting ,[parameter(mandatory=$false)] [switch] $ApplyDataMasks ,[parameter(mandatory=$false)] [switch] $ForceDestDbOffline ,[parameter(mandatory=$false)] [string] $StaticDataMaskFile ) $StartMs = Get-Date if ( $PromptForSnapshot.IsPresent.Equals($false) -And $RefreshFromSnapshot.IsPresent.Equals($false) ) { 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 } Write-Color -Text "Source SQL Server instance: ", $RefreshSource, " - CONNECTED" -Color Yellow, Green, Green try { $SourceServer = (Connect-DbaInstance -SqlInstance $RefreshSource).ComputerNamePhysicalNetBIOS } catch { Write-Error "Failed to determine target server name with: $ExceptionMessage" } } try { $FlashArray = New-PfaArray -EndPoint $PfaEndpoint -Credentials $PfaCredentials -IgnoreCertificateError } catch { $ExceptionMessage = $_.Exception.Message Write-Error "Failed to connect to FlashArray endpoint $PfaEndpoint with: $ExceptionMessage" Return } Write-Color -Text "FlashArray endpoint : ", "CONNECTED" -ForegroundColor Yellow, Green $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-Object name } catch { $ExceptionMessage = $_.Exception.Message Write-Error "Failed to determine source volume with: $ExceptionMessage" Return } } if ( $PromptForSnapshot.IsPresent ) { Foreach($DestSqlInstance in $DestSqlInstances) { Invoke-DbRefresh -DestSqlInstance $DestSqlInstance ` -RefreshDatabase $RefreshDatabase ` -PfaEndpoint $PfaEndpoint ` -PfaCredentials $PfaCredentials ` -SourceVolume $FilteredSnapshots[$SnapshotId] } } else { $JobNumber = 1 Foreach($DestSqlInstance in $DestSqlInstances) { $JobName = "DbRefresh" + $JobNumber Write-Colour -Text "Refresh background job : ", $JobName, " - ", "PROCESSING" -Color Yellow, Green, Green, Green If ( $RefreshFromSnapshot.IsPresent ) { Start-Job -Name $JobName -ScriptBlock $Function:DbRefresh -argumentlist $DestSqlInstance , ` $RefreshDatabase , ` $PfaEndpoint , ` $PfaCredentials , ` $RefreshSource , ` $StaticDataMaskFile, ` $ForceDestDbOffline.IsPresent, ` $NoPsRemoting.IsPresent , ` $PromptForSnapshot.IsPresent , ` $ApplyDataMasks.IsPresent | Out-Null } else { Start-Job -Name $JobName -ScriptBlock $Function:DbRefresh -argumentlist $DestSqlInstance , ` $RefreshDatabase , ` $PfaEndpoint , ` $PfaCredentials , ` $SourceVolume.Name , ` $StaticDataMaskFile, ` $ForceDestDbOffline.IsPresent, ` $NoPsRemoting.IsPresent , ` $PromptForSnapshot.IsPresent , ` $ApplyDataMasks.IsPresent | Out-Null } $JobNumber += 1; } While (Get-Job -State Running | Where-Object {$_.Name.Contains("DbRefresh")}) { if ($PSBoundParameters.ContainsKey('PollJobInterval')) { Get-Job -State Running | Where-Object {$_.Name.Contains("DbRefresh")} | Receive-Job Start-Sleep -Seconds $PollJobInterval } else { Start-Sleep -Seconds 1 } } Write-Colour -Text "Refresh background jobs : ", "COMPLETED" -Color Yellow, Green foreach($job in (Get-Job | Where-Object {$_.Name.Contains("DbRefresh")})) { $result = Receive-Job $job Write-Host $result } Remove-Job -State Completed } $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 } function Enable-DataMasks { param( [parameter(mandatory=$true)] [string] $SqlInstance ,[parameter(mandatory=$true)] [string] $Database ) Write-Warning "Enable-DataMasks has been deprecated, use Invoke-DynamicDataMasking instead" } function Invoke-DynamicDataMasking { <# .SYNOPSIS A PowerShell function to apply data masks to database columns using the SQL Server dynamic data masking feature. .DESCRIPTION This function uses the information stored in the extended properties of a database: sys.extended_properties.name = 'DATAMASK' to obtain the dynamic data masking function to apply at column level. Columns of the following data type are currently supported: - int - bigint - char - nchar - varchar - nvarchar Using the c_address column in the tpch customer table as an example, the DATAMASK extended property can be applied to the column as follows: exec sp_addextendedproperty @name = N'DATAMASK' ,@value = N'(FUNCTION = 'partial(0, "XX", 20)'' ,@level0type = N'Schema', @level0name = 'dbo' ,@level1type = N'Table', @level1name = 'customer' ,@level2type = N'Column', @level2name = 'c_address' GO .PARAMETER SqlInstance The SQL Server instance of the database that data masking is to be applied to .PARAMETER Database The database that data masking is to be applied to .EXAMPLE Invoke-DynamicDataMasking -SqlInstance Z-STN-WIN2016-A\DEVOPSDEV ` -Database tpch-no-compression .NOTES Obtaining The PureStorageDbaTools Module ---------------------------------------- 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. Licence ------- 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 https://www.powershellgallery.com/packages/PureStorageDbaTools https://docs.microsoft.com/en-us/sql/relational-databases/security/dynamic-data-masking?view=sql-server-2017 New-PfaDbSnapshot Invoke-PfaDbRefresh #> param( [parameter(mandatory=$true)] [string] $SqlInstance ,[parameter(mandatory=$true)] [string] $Database ) $sql = @" BEGIN DECLARE @sql_statement nvarchar(1024) ,@error_message varchar(1024) DECLARE apply_data_masks CURSOR FOR SELECT 'ALTER TABLE ' + tb.name + ' ALTER COLUMN ' + c.name + + ' ADD MASKED WITH ' + CAST(p.value AS char) + ''')' FROM sys.columns c JOIN sys.types t ON c.user_type_id = t.user_type_id LEFT JOIN sys.index_columns ic ON ic.object_id = c.object_id AND ic.column_id = c.column_id LEFT JOIN sys.indexes i ON ic.object_id = i.object_id AND ic.index_id = i.index_id JOIN sys.tables tb ON tb.object_id = c.object_id JOIN sys.extended_properties AS p ON p.major_id = tb.object_id AND p.minor_id = c.column_id AND p.class = 1 WHERE t.name IN ('int', 'bigint', 'char', 'nchar', 'varchar', 'nvarchar'); OPEN apply_data_masks FETCH NEXT FROM apply_data_masks INTO @sql_statement; WHILE @@FETCH_STATUS = 0 BEGIN PRINT 'Applying data mask: ' + @sql_statement; BEGIN TRY EXEC sp_executesql @stmt = @sql_statement END TRY BEGIN CATCH SELECT @error_message = ERROR_MESSAGE(); PRINT 'Application of data mask failed with: ' + @error_message; END CATCH; FETCH NEXT FROM apply_data_masks INTO @sql_statement END; CLOSE apply_data_masks DEALLOCATE apply_data_masks; END; "@ Invoke-DbaSqlQuery -SqlInstance $SqlInstance -Database $Database -Query $sql } function Invoke-StaticDataMasking { <# .SYNOPSIS A PowerShell function to statically mask data in char, varchar and/or nvarchar columns using a MD5 hashing function. .DESCRIPTION This PowerShell function uses as input a JSON file created by calling the New-DbaDbMaskingConfig PowerShell function. Data in the columns specified in this file which are of the type char, varchar or nvarchar are envrypted using a MD5 hash. .PARAMETER SqlInstance The SQL Server instance of the database that static data masking is to be applied to .PARAMETER Database The database that static data masking is to be applied to .PARAMETER DataMaskFile Absolute path to the JSON file generated by invoking New-DbaDbMaskingConfig. The file can be subsequently editted by hand to suit the data masking requirements of this function's user. Currently, static data masking is only supported for columns with char, varchar, nvarchar, int and bigint data types. .EXAMPLE Invoke-StaticDataMasking -SqlInstance Z-STN-WIN2016-A\DEVOPSDEV ` -Database tpch-no-compression ` -DataMaskFile 'C:\Users\devops\Documents\tpch-no-compression.tables.json' .NOTES Obtaining The PureStorageDbaTools Module ---------------------------------------- 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. Licence ------- 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 https://www.powershellgallery.com/packages/PureStorageDbaTools https://docs.microsoft.com/en-us/sql/relational-databases/security/dynamic-data-masking?view=sql-server-2017 New-PfaDbSnapshot Invoke-PfaDbRefresh #> param( [parameter(mandatory=$true)] [string] $SqlInstance ,[parameter(mandatory=$true)] [string] $Database ,[parameter(mandatory=$true)] [string] $DataMaskFile ) if ($DataMaskFile.ToString().StartsWith('http')) { $tables = Invoke-RestMethod -Uri $DataMaskFile } else { # Check if the destination is accessible if (-not (Test-Path -Path $DataMaskFile)) { Write-Error "Could not find data mask config file $DataMaskFile" Return } } # Get all the items that should be processed try { $tables = Get-Content -Path $DataMaskFile -ErrorAction Stop | ConvertFrom-Json -ErrorAction Stop } catch { Write-Error "Could not parse masking config file: $DataMaskFile" -ErrorRecord $_ } foreach ($tabletest in $tables.Tables) { if ($Table -and $tabletest.Name -notin $Table) { continue } $ColumnIndex = 0 $UpdateStatement = "" foreach ($columntest in $tabletest.Columns) { if ($columntest.ColumnType -in 'varchar', 'char', 'nvarchar') { if ($ColumnIndex -eq 0) { $UpdateStatement = 'UPDATE ' + $tabletest.Name + ' SET ' + $columntest.Name + ' = SUBSTRING(CONVERT(VARCHAR, HASHBYTES(' + '''' + 'MD5' + '''' + ', ' + $columntest.Name + '), 1), 1, ' + $columntest.MaxValue + ')' } else { $UpdateStatement += ', ' + $columntest.Name + ' = SUBSTRING(CONVERT(VARCHAR, HASHBYTES(' + '''' + 'MD5' + '''' + ', ' + $columntest.Name + '), 1), 1, ' + $columntest.MaxValue + ')' } } elseif ($columntest.ColumnType -eq 'int') { if ($ColumnIndex -eq 0) { $UpdateStatement = 'UPDATE ' + $tabletest.Name + ' SET ' + $columntest.Name + ' = ABS(CHECKSUM(NEWID())) % 2147483647' } else { $UpdateStatement += ', ' + $columntest.Name + ' = ABS(CHECKSUM(NEWID())) % 2147483647' } } elseif ($columntest.ColumnType -eq 'bigint') { if ($ColumnIndex -eq 0) { $UpdateStatement = 'UPDATE ' + $tabletest.Name + ' SET ' + $columntest.Name + ' = ABS(CHECKSUM(NEWID()))' } else { $UpdateStatement += ', ' + $columntest.Name + ' = ABS(CHECKSUM(NEWID()))' } } else { Write-Error "$columntest.ColumnType is not supported, please remove the column $columntest.Name from the $tabletest.Name table" Return } $ColumnIndex += 1 } Write-Verbose "Statically masking table $tabletest.Name using $UpdateStatement" Invoke-DbaQuery -ServerInstance $SqlInstance -Database $Database -Query $UpdateStatement -QueryTimeout 999999 } } Export-ModuleMember -Function @('Invoke-PfaDbRefresh', 'New-PfaDbSnapshot', 'Enable-DataMasks', 'Invoke-DynamicDataMasking', 'Invoke-StaticDataMasking') |