Using T-SQL Snapshot Backup - Seeding Availability Groups

In this post, the fifth in our series, I want to illustrate an example of using the T-SQL Snapshot Backup feature in SQL Server 2022 to seed Availability Groups (AGs) with storage-based snapshots. Efficiently seeding an Availability Group is essential for maintaining high availability and ensuring effective disaster recovery. With the introduction of T-SQL Snapshot Backup in SQL Server 2022, snapshots can now be created at the storage layer. This advancement significantly speeds up the initialization of secondary replicas, particularly in environments that handle large databases.

This post will walk through a PowerShell script that effectively seeds an AG using T-SQL Snapshot Backup, dbatools, and Pure Storage FlashArray.

You can find the complete script for this blog post here.

Why is this important?

If you’ve been working with Availability Groups, you’re likely familiar with replica seeding, sometimes referred to as initializing, preparing, or data synchronization. Seeding is a data operation that involves copying data from a primary replica to one or more secondary replicas. This process is necessary before a database can join an Availability Group. Typically, you can seed a replica through backup and restore or automatic seeding, each of which comes with its own challenges. Regardless of the method you choose, the seeding operation can be time-consuming. The duration of the seeding process depends on several factors, including the size of the database, network speed, and storage capabilities. If you have multiple replicas to seed, the time involved multiplies accordingly!

But what if I told you that you could seed your Availability Group from a storage-based snapshot and that the re-seeding process could be nearly instantaneous?

This method saves time and reduces the CPU, network, and disk resources consumed by traditional direct seeding and backup-and-restore processes.

The Scenario

We have two SQL Server 2022 instances, each with:

Availability Group configured with two Replicas - I will not cover creating an Availability Group for this post. So, you already have the AG up and running, with both instances configured as replicas, and the database is online on the Primary and not in the AG, not on the secondary.
Storage volumes hosted on a Pure Storage FlashArray - each SQL Server has a volume allocated on a FlashArray.
Protection Groups ensuring consistent snapshots - Most databases are spread across multiple volumes. For snapshot backup to work correctly, the volumes must be snapshot at the exact time. A Protection Group guarantees that a snapshot happens simultaneously across all volumes in the Protection Group. This is also required to replicate snapshots between FlashArrays.
Asynchronous replication between FlashArrays - You can perform this process on a single array, but I want the data on two separate arrays if we’re talking about availability. So, we want to replicate the snapshot between two storage arrays, and that replicated snapshot will be used to seed the replica. Cool sidebar here; if you need to scale out read replicas… clone the snapshot to several AG replicas on the same array, and you’ll benefit from data reduction for your data.
dbatools and Pure Storage PowerShell SDK2 installed - we’re using these modules to coodinate the work in this script

Setting Up the Environment

The code below defines key infrastructure components, including primary and secondary SQL Server replicas, AG details, and FlashArray volumes. Using PowerShell remoting, we establish a session with the secondary replica, create persistent SMO connections to both SQL Server instances, and also build REST API Sessions with the FlashArrays our SQL Servers’ volumes are on. This setup lays the foundation for automating AG tasks later in the script. Like in the previous posts in this series, I’m again implementing this using the PureStoragePowerShellSDK2 and dbatools PowerShell modules.

Import-Module dbatools
Import-Module PureStoragePowerShellSDK2

$PrimarySqlServer   = 'SqlServer1'                          # SQL Server Name - Primary Replica
$SecondarySqlServer = 'SqlServer2'                          # SQL Server Name - Secondary Replica
$AgName             = 'ag1'                                 # Name of availability group
$DbName             = 'AgTestDb1'                           # Name of database to place in AG
$BackupShare        = '\\FileServer1\SHARE\BACKUP'          # File location for metadata backup file.
$PrimaryArrayName   = 'flasharray1.example.com'             # FlashArray containing the volumes for our primary replica
$SecondaryArrayName = 'flasharray2.example.com'             # FlashArray containing the volumes for our secondary replica
$SourcePGroupName   = 'SqlServer1_Pg'                       # Name of the Protection Group on FlashArray1
$TargetPGroupName   = 'flasharray1:SqlServer1_Pg'           # Name of the Protection Group replicated from FlashArray1 to FlashArray2, in the format of ArrayName:ProtectionGroupName
$PrimaryFaDbVol     = 'Fa1_Sql_Volume_1'                    # Volume name on FlashArray containing database files of the primary replica
$SecondaryFaDbVol   = 'Fa2_Sql_Volume_1'                    # Volume name on FlashArray containing database files of the secondary replica
$TargetDisk         = '6000c29668589f61a386218139e21bb0'    # The serial number if the Windows volume containing database files


# Build a PowerShell Remoting Session to the secondary replica
$SecondarySession = New-PSSession -ComputerName $SecondarySqlServer


# Build persistent SMO connections to each SQL Server that will participate in the availability group
$SqlInstancePrimary = Connect-DbaInstance -SqlInstance $PrimarySqlServer -TrustServerCertificate -NonPooledConnection 
$SqlInstanceSecondary = Connect-DbaInstance -SqlInstance $SecondarySqlServer -TrustServerCertificate -NonPooledConnection 


# Connect to the FlashArray with for the AG Primary
$Credential = Get-Credential
$FlashArrayPrimary = Connect-Pfa2Array –EndPoint $PrimaryArrayName -Credential $Credential -IgnoreCertificateError


# Connect to the FlashArray's REST API where the secondary's data is located
$FlashArraySecondary = Connect-Pfa2Array –EndPoint $SecondaryArrayName -Credential $Credential -IgnoreCertificateError

Take the Snapshot Backup on the Primary’s FlashArray

Now, we’re ready to take an application-consistent snapshot of our database. First, we freeze write I/O with SUSPEND_FOR_SNAPSHOT_BACKUP, then trigger a Protection Group snapshot and replicate it. Finally, we take a metadata-only backup, embedding snapshot details for seamless recovery, ensuring consistency and integration with FlashArray replication. Let’s walk through the code block below.

On the primary replica’s FlashArray…

Freeze write IO on the database using ALTER DATABASE [$DbName] SET SUSPEND_FOR_SNAPSHOT_BACKUP = ON

$Query = "ALTER DATABASE [$DbName] SET SUSPEND_FOR_SNAPSHOT_BACKUP = ON"
Invoke-DbaQuery -SqlInstance $SqlInstancePrimary -Query $Query -Verbose

Take a snapshot of the Protection Group and replicate it to our other array

$SourceSnapshot = New-Pfa2ProtectionGroupSnapshot -Array $FlashArrayPrimary -SourceName $SourcePGroupName -ForReplication $true -ReplicateNow $true

Execute the BACKUP DATABASE TestDB1 TO DISK='\\FILESERVER\BACKUP\'$BackupFile'' WITH METADATA_ONLY command. This takes a metadata backup of the database; this will automatically unfreeze if successful. We’ll use the MEDIADESCRIPTION parameter to hold information about our snapshot.

$BackupFile = "$BackupShare\$DbName$(Get-Date -Format FileDateTime).bkm"
$Query = "BACKUP DATABASE $DbName 
TO DISK='$BackupFile' 
WITH METADATA_ONLY, MEDIADESCRIPTION='$($SourceSnapshot.Name)|$($FlashArrayPrimary.ArrayName)'"
Invoke-DbaQuery -SqlInstance $SqlInstancePrimary -Query $Query -Verbose

The BACKUP command generates a metadata file that describes what’s in the backup. We’ll need this later to restore the database on the Secondary replica.

Let’s talk about snapshot replication for a second.

The first time FlashArray replicates a snapshot between the arrays, it moves the data-reduced data. On SQL Server, FlashArray generally sees a 3.58:1 data reduction. This reduces the time needed to seed the secondary replica on the secondary array since less data has to be replicated. This technique is immensely helpful in scenarios where you have to seed a replica in a DR site or cloud over a WAN or VPN link.

Now, if this was a re-seed of a replica…when we take a snapshot of the Primary replica’s array and replicate it to the Secondary’s array…only data that has changed on the Primary’s array and not yet on the Secondary’s array will be copied over the wire. Dramatically reducing the amount of data that needs to be replicated and the time it takes to re-seed that Secondary replica. If this is a multi-terabyte database or set of databases, the time savings here is enormous.

Get the Snapshot on the Secondary’s Array

This loop ensures the snapshot is fully replicated between the FlashArrays before proceeding. It continuously checks replication progress, logging updates and pausing as needed until completion. This guarantees that the snapshot is on the target array before proceeding.

Write-Warning "Obtaining the most recent snapshot for the protection group..."
$TargetSnapshot = $null
do {
 Write-Warning "Waiting for snapshot to replicate to target array..."
 $TargetSnapshot = Get-Pfa2ProtectionGroupSnapshotTransfer -Array $FlashArraySecondary -Name $TargetPGroupName | 
 Where-Object { $_.Name -eq "$TargetPGroupName.$($SourceSnapshot.Suffix)" } 

 if ( $TargetSnapshot -and $TargetSnapshot.Progress -ne 1.0 ){
 Write-Warning "Snapshot $($TargetSnapshot.Name) found on Target Array...replication progress is $($TargetSnapshot.Progress)"
 Start-Sleep 3
 }

} while ( [string]::IsNullOrEmpty($TargetSnapshot.Completed) -or ($TargetSnapshot.Progress -ne 1.0) )
Write-Warning "Snapshot $($TargetSnapshot.Name) replicated to Target Array. Completed at $($TargetSnapshot.Completed)"

Offline the volumes on the Secondary and Update the Volumes Contents from the Snapshot

Now, on the Secondary replica, we need to update the volumes with clones of the volumes in the snapshot. This refreshes the data on the secondary replica with the data in the snapshot. Here’s the code for that.

Offline the volume(s) supporting the database

Invoke-Command -Session $SecondarySession -ScriptBlock { Get-Disk | Where-Object { $_.SerialNumber -eq $using:TargetDisk } | Set-Disk -IsOffline $True }

Overwrite the volumes on the Secondary from the protection group snapshot with New-Pfa2Volume

New-Pfa2Volume -Array $FlashArraySecondary -Name $SecondaryFaDbVol -SourceName ($TargetSnapshot.Name + ".$PrimaryFaDbVol") -Overwrite $true

Online the volume(s) on the Secondary

Invoke-Command -Session $SecondarySession -ScriptBlock { Get-Disk | Where-Object { $_.SerialNumber -eq $using:TargetDisk } | Set-Disk -IsOffline $False }

You’ll want to ensure your volume names and drive letters/mount points match the Primary’s layout. If you’re using Availability Group, you probably already are. I’m using VMware VMs here with vVols attached. This technique works for RDM and physical servers.

Restore the database from snapshot backup with `NORECOVERY` on the secondary

With the data on the volumes updated and attached to the Secondary replica, you can restore the snapshot backup on the Secondary replica. The critical thing here is the NORECOVERY option since we’re seeding an Availability Group…the database state needs to be RESTORING.

$Query = "RESTORE DATABASE [$DbName] FROM DISK = '$BackupFile' WITH METADATA_ONLY, REPLACE, NORECOVERY" 
Invoke-DbaQuery -SqlInstance $SqlInstanceSecondary -Database master -Query $Query -Verbose

Finalize the Seeding of the Replica and Join the AG

From here on out, since the database is in a RESTORING state on the secondary replica, we’re looking at standard Availability Group manual seeding.

Take a log backup on the primary

$Query = "BACKUP LOG [$DbName] TO DISK = '$BackupShare\$DbName-seed.trn' WITH FORMAT, INIT" 
Invoke-DbaQuery -SqlInstance $SqlInstancePrimary -Database master -Query $Query -Verbose

Restore it on the Secondary

$Query = "RESTORE LOG [$DbName] FROM DISK = '$BackupShare\$DbName-seed.trn' WITH NORECOVERY" 
Invoke-DbaQuery -SqlInstance $SqlInstanceSecondary -Database master -Query $Query -Verbose

Set the seeding mode on the Secondary to manual

$Query = "ALTER AVAILABILITY GROUP [$AgName] MODIFY REPLICA ON N'$PrimarySqlServer' WITH (SEEDING_MODE = MANUAL)"
Invoke-DbaQuery -SqlInstance $SqlInstancePrimary -Database master -Query $Query -Verbose

Add the database to the Availability Group

$Query = "ALTER AVAILABILITY GROUP [$AgName] ADD DATABASE [$DbName];"
Invoke-DbaQuery -SqlInstance $SqlInstancePrimary -Database master -Query $Query -Verbose

Start data movement

Invoke-DbaQuery -SqlInstance $SecondarySqlInstance -Database master -Query 'ALTER DATABASE [$DbName] SET HADR AVAILABILITY GROUP = [$AgName];'

Now let’s check the status of the AG. Check to see if the SynchronizationState is Synchronized

Get-DbaAgDatabase -SqlInstance $SqlInstancePrimary -AvailabilityGroup $AgName

ComputerName         : SqlServer1
InstanceName         : MSSQLSERVER
SqlInstance          : SqlServer1
AvailabilityGroup    : AG1
LocalReplicaRole     : Primary
Name                 : AgTestDb1
SynchronizationState : Synchronized
IsFailoverReady      : True
IsJoined             : True
IsSuspended          : False

Wrapping Things Up

In this post, the fifth in our series, we used the T-SQL Snapshot Backup feature to seed an availability group replica. Well, first, this helps increase the availability of your database systems. If you had a replica failure and it’s offline, your system is vulnerable if another replica fails. Leveraging this technique, you can quickly bring your systems back to full protection. Further, as a DBA, you won’t have to sit around and monitor the re-seeding process so that you can focus on different tasks in your organization.

You can grab the whole script for this blog post here.

Using T-SQL Snapshot Backup Blog Series

This article is part of a blog series covering the Using T-SQL Snapshot Backup. Check out the other posts in this series:

You can watch Bob Ward and me present a deep dive on this feature at PASS Summit 2023 in the session “Are Snapshots Backups?”.