Availability Groups are awesome but what is not awesome is having to restore all the replicas within an availability group. At minimum you will have to restore two databases instead of one, maybe even more. When you do have to restore an entire availability group, for example when you need to refresh a development environment, leveraging snapshots can be useful and reduce your refresh time to seconds.
Normally you have to offline a database before refreshing it with a snapshot. Below is some example code that does exactly this. The code at a high level takes a database offline, then takes the volume offline and then applies the snapshot, then it reverses everything.
Clear-Host
Import-Module PureStoragePowerShellSDK
Import-Module SQLPS
#DevServer
$Target1 = ‘MyDevServerA’
$Target2 = ‘MyDevServerB’
$TargetPSSession1 = New-PSSession -ComputerName $Target1
$TargetPSSession2 = New-PSSession -ComputerName $Target2
Write-Warning “Target database downtime begins now.”
# Offline the availability group
Write-Warning “Stopping the AG…”
Invoke-Command -Session $TargetPSSession1 -ScriptBlock { Stop-ClusterGroup My_AG}
# Offline the target/copy volume
Write-Warning “Offlining the volume…”
Invoke-Command -Session $TargetPSSession1 -ScriptBlock { Get-Disk | ? { $_.SerialNumber -eq ‘6000c29a3f3454691b7ad753cd4225d1’ } | Set-Disk -IsOffline $True }
Invoke-Command -Session $TargetPSSession1 -ScriptBlock { Get-Disk | ? { $_.SerialNumber -eq ‘6000c29a3f3454691b7ad753cd4225d2’ } | Set-Disk -IsOffline $True }
Invoke-Command -Session $TargetPSSession2 -ScriptBlock { Get-Disk | ? { $_.SerialNumber -eq ‘7000c29a3f3454691b7ad753cd4225d1’ } | Set-Disk -IsOffline $True }
Invoke-Command -Session $TargetPSSession2 -ScriptBlock { Get-Disk | ? { $_.SerialNumber -eq ‘7000c29a3f3454691b7ad753cd4225d2’ } | Set-Disk -IsOffline $True }
# Connect to the FlashArray’s REST API, get a session going – you’re gonna have to excuse my laziness saving the password here
Write-Warning “Establishing a session against the Pure Storage FlashArray…”
$FlashArray = New-PfaArray -EndPoint 10.XX.XX.XX -UserName MyPureUser -Password (ConvertTo-SecureString -AsPlainText ‘MyPurePassword’ -Force) -IgnoreCertificateError
#Get the latest snapshot for yourt Protection Group
$MostRecentSnapshot = New-PFAProtectionGroupSnapshots -Array $FlashArray -Name ‘MyPGGroupName’ | Sort-Object created -Descending | Select -Property name -First 1
# Perform the volume overwrite
Write-Warning “Overwriting the dev instance’s volume with a fresh copy from production…”
New-PfaVolume -Array $FlashArray -VolumeName MyDevServer1-Data01 -Source ($MostRecentSnapshot.name + ‘.sql00-Data01’) -Overwrite
New-PfaVolume -Array $FlashArray -VolumeName MyDevServer1-Log01 -Source ($MostRecentSnapshot.name + ‘.sql00-Log01’) -Overwrite
New-PfaVolume -Array $FlashArray -VolumeName MyDevServer2-Data01 -Source ($MostRecentSnapshot.name + ‘.sql00-Data01’) -Overwrite
New-PfaVolume -Array $FlashArray -VolumeName MyDevServer2-Log01 -Source ($MostRecentSnapshot.name + ‘.sql00-Log01’) -Overwrite
# Online the volume
Write-Warning “Onlining the volume…”
Invoke-Command -Session $TargetPSSession1 -ScriptBlock { Get-Disk | ? { $_.SerialNumber -eq ‘6000c29a3f3454691b7ad753cd4225d1’ } | Set-Disk -IsOffline $False }
Invoke-Command -Session $TargetPSSession1 -ScriptBlock { Get-Disk | ? { $_.SerialNumber -eq ‘6000c29a3f3454691b7ad753cd4225d2’ } | Set-Disk -IsOffline $False }
Invoke-Command -Session $TargetPSSession2 -ScriptBlock { Get-Disk | ? { $_.SerialNumber -eq ‘7000c29a3f3454691b7ad753cd4225d1’ } | Set-Disk -IsOffline $False }
Invoke-Command -Session $TargetPSSession2 -ScriptBlock { Get-Disk | ? { $_.SerialNumber -eq ‘7000c29a3f3454691b7ad753cd4225d2’ } | Set-Disk -IsOffline $False }
# Start the availability group
Write-Warning “Starting the AG…”
Invoke-Command -Session $TargetPSSession1 -ScriptBlock { Start-ClusterGroup My_AG}
Write-Warning “Development database downtime ended.”
# Clean up
Disconnect-PfaArray $FlashArray
Remove-PSSession $TargetPSSession
Remove-PSSession $SourcePSSession
Write-Output “All done.
However when you leverage availability groups this code has to change slightly. You cannot take databases offline that are part if an Availability Group. Instead of taking the database offline you need to stop the service for the availability group in the cluster manager. You then have to apply the snapshot to all replicas in the availability group. Note how there are now two target servers/sessions instead of one. Also the number of drives has doubled, as I had to add in the drives for the replica or secondary server. If you have additional replicas they will also have to be accounted for in the script.
Clear-Host
Import-Module PureStoragePowerShellSDK
Import-Module SQLPS
#DevServer
$Target1 = ‘MyDevServerA’
$Target2 = ‘MyDevServerB’
$TargetPSSession1 = New-PSSession -ComputerName $Target1
$TargetPSSession2 = New-PSSession -ComputerName $Target2
Write-Warning “Target database downtime begins now.”
# Offline the availability group
Write-Warning “Stopping the AG…”
Invoke-Command -Session $TargetPSSession1 -ScriptBlock { Stop-ClusterGroup My_AG}
# Offline the target/copy volume
Write-Warning “Offlining the volume…”
Invoke-Command -Session $TargetPSSession1 -ScriptBlock { Get-Disk | ? { $_.SerialNumber -eq ‘6000c29a3f3454691b7ad753cd4225d1’ } | Set-Disk -IsOffline $True }
Invoke-Command -Session $TargetPSSession1 -ScriptBlock { Get-Disk | ? { $_.SerialNumber -eq ‘6000c29a3f3454691b7ad753cd4225d2’ } | Set-Disk -IsOffline $True }
Invoke-Command -Session $TargetPSSession2 -ScriptBlock { Get-Disk | ? { $_.SerialNumber -eq ‘7000c29a3f3454691b7ad753cd4225d1’ } | Set-Disk -IsOffline $True }
Invoke-Command -Session $TargetPSSession2 -ScriptBlock { Get-Disk | ? { $_.SerialNumber -eq ‘7000c29a3f3454691b7ad753cd4225d2’ } | Set-Disk -IsOffline $True }
# Connect to the FlashArray’s REST API, get a session going – you’re gonna have to excuse my laziness saving the password here
Write-Warning “Establishing a session against the Pure Storage FlashArray…”
$FlashArray = New-PfaArray -EndPoint 10.XX.XX.XX -UserName MyPureUser -Password (ConvertTo-SecureString -AsPlainText ‘MyPurePassword’ -Force) -IgnoreCertificateError
#Get the latest snapshot for yourt Protection Group
$MostRecentSnapshot = New-PFAProtectionGroupSnapshots -Array $FlashArray -Name ‘MyPGGroupName’ | Sort-Object created -Descending | Select -Property name -First 1
# Perform the volume overwrite
Write-Warning “Overwriting the dev instance’s volume with a fresh copy from production…”
New-PfaVolume -Array $FlashArray -VolumeName MyDevServer1-Data01 -Source ($MostRecentSnapshot.name + ‘.sql00-Data01’) -Overwrite
New-PfaVolume -Array $FlashArray -VolumeName MyDevServer1-Log01 -Source ($MostRecentSnapshot.name + ‘.sql00-Log01’) -Overwrite
New-PfaVolume -Array $FlashArray -VolumeName MyDevServer2-Data01 -Source ($MostRecentSnapshot.name + ‘.sql00-Data01’) -Overwrite
New-PfaVolume -Array $FlashArray -VolumeName MyDevServer2-Log01 -Source ($MostRecentSnapshot.name + ‘.sql00-Log01’) -Overwrite
# Online the volume
Write-Warning “Onlining the volume…”
Invoke-Command -Session $TargetPSSession1 -ScriptBlock { Get-Disk | ? { $_.SerialNumber -eq ‘6000c29a3f3454691b7ad753cd4225d1’ } | Set-Disk -IsOffline $False }
Invoke-Command -Session $TargetPSSession1 -ScriptBlock { Get-Disk | ? { $_.SerialNumber -eq ‘6000c29a3f3454691b7ad753cd4225d2’ } | Set-Disk -IsOffline $False }
Invoke-Command -Session $TargetPSSession2 -ScriptBlock { Get-Disk | ? { $_.SerialNumber -eq ‘7000c29a3f3454691b7ad753cd4225d1’ } | Set-Disk -IsOffline $False }
Invoke-Command -Session $TargetPSSession2 -ScriptBlock { Get-Disk | ? { $_.SerialNumber -eq ‘7000c29a3f3454691b7ad753cd4225d2’ } | Set-Disk -IsOffline $False }
# Start the availability group
Write-Warning “Starting the AG…”
Invoke-Command -Session $TargetPSSession1 -ScriptBlock { Start-ClusterGroup My_AG}
Write-Warning “Development database downtime ended.”
# Clean up
Disconnect-PfaArray $FlashArray
Remove-PSSession $TargetPSSession
Remove-PSSession $SourcePSSession
Write-Output “All done.”
Leveraging snapshots can significantly reduce your dev/test refreshes. They can help you quickly recover from a production data loss. Also they can help you negate the storage penalty you pay for your replicas. It’s a good thing!
New Update 1/20/2021
My friend Argenis Fernandez did a great video demonstrating this. This video shows a dev test environment with 4 replicas that are empty being initialized with a single snapshot from production