Skip to content

Instantly share code, notes, and snippets.

@nocentino
Created May 26, 2022 18:07
Show Gist options
  • Save nocentino/c1b6c097a7d046aaaaf2be23aa11a507 to your computer and use it in GitHub Desktop.
Save nocentino/c1b6c097a7d046aaaaf2be23aa11a507 to your computer and use it in GitHub Desktop.
Seeding an Availability Group Replica from Snapshots
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];'
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment