Seeding an Availability Group Replica from Snapshots

AGSeeding.ps1

Import-Module dbatools
Import-Module PureStoragePowerShellSDK

$Primary = 'aen-sql-22-a'
$Secondary = 'aen-sql-22-b'
$PrimarySqlInstance = Connect-DbaInstance -SqlInstance $Primary
$SecondarySqlInstance = Connect-DbaInstance -SqlInstance $Secondary
$SecondaryPsSession = New-PSSession -ComputerName $Secondary
$Credential = Get-Credential -UserName "$env:USERNAME" -Message 'Enter your credential information...'

#Connect to the Primary replicas FlashArray
$PrimaryFlashArray = New-PfaArray –EndPoint primaryarray.yourdatacenter.local -Credentials $Credential -IgnoreCertificateError

#Freeze the database
Invoke-DbaQuery -SqlInstance $PrimarySqlInstance -Database master -Query 'ALTER DATABASE TestDB1 SET SUSPEND_FOR_SNAPSHOT_BACKUP ON' 

#Take a snapshot of the Protection Group
$PrimarySnapshot = New-PfaProtectionGroupSnapshot -Array $PrimaryFlashArray -Protectiongroupname 'aen-sql-22-pg' -ApplyRetention -ReplicateNow

#Take a metadata backup of the database, this will automatically unfreeze if successful
$Query = "BACKUP DATABASE TestDB1 TO DISK='\\FILESERVER\BACKUP\TestDB1-Replica.bkm' WITH METADATA_ONLY"
Invoke-DbaQuery -SqlInstance $PrimarySqlInstance -Database master -Query $Query


#Connect to the seconary's array
$SecondaryFlashArray = New-PfaArray –EndPoint secondaryarray.yourdatacenter.local -Credentials $Credential -IgnoreCertificateError

#Get the replicated snapshot on the Secondary's array...it has to be THAT same exact snapshot
$TargetSnapshot = Get-PfaProtectionGroupSnapshots -Array $SecondaryFlashArray -Name 'primaryarray:aen-sql-22-pg' | Where-Object { ($_.Name).EndsWith($PrimarySnapshot.name) }

#Offline the disks
Invoke-Command -Session $SecondaryPsSession -ScriptBlock { Get-Disk | Where-Object { $_.SerialNumber -eq '6000c29f238200118f86219b90d11fe6' } | Set-Disk -IsOffline $True }
Invoke-Command -Session $SecondaryPsSession -ScriptBlock { Get-Disk | Where-Object { $_.SerialNumber -eq '6000c296fdfa73527a65de55eb6db3de' } | Set-Disk -IsOffline $True }

#Overwrite the volumes on the Secondary from the protection group snapshot
New-PfaVolume -Array $SecondaryFlashArray -VolumeName 'vvol-aen-sql-22-b-1-1b3811f2-vg/Data-9a615973' -Source ($TargetSnapshot.name + ".vvol-aen-sql-22-a-1-74fc3caf-vg/Data-618800dc") -Overwrite
New-PfaVolume -Array $SecondaryFlashArray -VolumeName 'vvol-aen-sql-22-b-1-1b3811f2-vg/Data-8ef07fb5' -Source ($TargetSnapshot.name + ".vvol-aen-sql-22-a-1-74fc3caf-vg/Data-e9bb1e31") -Overwrite

#Online the disks on the Secondary
Invoke-Command -Session $SecondaryPsSession -ScriptBlock { Get-Disk | Where-Object { $_.SerialNumber -eq '6000c29f238200118f86219b90d11fe6' } | Set-Disk -IsOffline $False }
Invoke-Command -Session $SecondaryPsSession -ScriptBlock { Get-Disk | Where-Object { $_.SerialNumber -eq '6000c296fdfa73527a65de55eb6db3de' } | Set-Disk -IsOffline $False }

#Restore the snapshot backup
$Query = "RESTORE DATABASE TestDB1 FROM DISK = '\\FILESERVER\BACKUP\TestDB1-Replica.bkm' WITH METADATA_ONLY, REPLACE, NORECOVERY" 
Invoke-DbaQuery -SqlInstance $SecondarySqlInstance -Database master -Query $Query

#Finalize the seeding of the AG
$Query = "BACKUP LOG TestDB1 TO DISK = '\\FILESERVER\BACKUP\TestDB1-seed.trn' WITH FORMAT" 
Invoke-DbaQuery -SqlInstance $PrimarySqlInstance -Database master -Query $Query

#Restore it on the Secondary
$Query = "RESTORE LOG TestDB1 FROM DISK = '\\FILESERVER\SHARE\BACKUP\TestDB1-seed.trn' WITH NORECOVERY" 
Invoke-DbaQuery -SqlInstance $SecondarySqlInstance -Database master -Query $Query

### Set the seeding mode on the Seconary to manual
Invoke-DbaQuery -SqlInstance $PrimarySqlInstance   -Database master -Query 'ALTER AVAILABILITY GROUP [ag1] MODIFY REPLICA ON N''aen-sql-22-b'' WITH (SEEDING_MODE = MANUAL)'

#Add the database to the AG
Invoke-DbaQuery -SqlInstance $PrimarySqlInstance   -Database master -Query 'ALTER AVAILABILITY GROUP [ag1] ADD DATABASE [TestDB1];'

#Enable replication
Invoke-DbaQuery -SqlInstance $SecondarySqlInstance -Database master -Query 'ALTER DATABASE [TestDB1] SET HADR AVAILABILITY GROUP = [ag1];'