-
-
Save nocentino/c1b6c097a7d046aaaaf2be23aa11a507 to your computer and use it in GitHub Desktop.
Seeding an Availability Group Replica from Snapshots
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
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