This article originally appeared on Anthony Nocentino’s blog. It has been republished with the author’s credit and consent.
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 on GitHub.
Why Is This Important?
If you’ve been working with AGs, 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 AG. 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:
- An 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, not in the AG and not in the secondary.
- Storage volumes hosted on a Pure Storage FlashArray system. Each SQL Server has a volume allocated on a FlashArray system.
- 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 FlashArray systems.
- Asynchronous replication between FlashArray systems. 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 coordinate 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 FlashArray systems 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.
[crayon-67f526ea85953768835068/]
[crayon-67f526ea8596a815963822/]
[crayon-67f526ea8596d294729848/]
[crayon-67f526ea85970241126172/]
[crayon-67f526ea85973076805622/]
[crayon-67f526ea85975416470886/]
[crayon-67f526ea85978938272383/]
[crayon-67f526ea8597a065659514/]
[crayon-67f526ea8597d264315823/]
[crayon-67f526ea85980798948620/]
[crayon-67f526ea85982111814872/]
[crayon-67f526ea85988294967750/]
[crayon-67f526ea8598b053531630/]
[crayon-67f526ea8598d082155662/]
[crayon-67f526ea85990131157576/]
[crayon-67f526ea85993108951983/]
[crayon-67f526ea85995855217854/]
[crayon-67f526ea85998192771315/]
[crayon-67f526ea8599a746380811/]
[crayon-67f526ea8599d777180086/]
[crayon-67f526ea8599f686146135/]
[crayon-67f526ea859a2798087167/]
[crayon-67f526ea859a4473827331/]
[crayon-67f526ea859a7134937518/]
[crayon-67f526ea859a9221317089/]
[crayon-67f526ea859ac492605660/]
[crayon-67f526ea859ae714080130/]
[crayon-67f526ea859b1519469539/]
[crayon-67f526ea859b4169462856/]
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:
[crayon-67f526ea859b6149400566/]
[crayon-67f526ea859b9267863607/]
Take a snapshot of the protection group and replicate it to our other array:
[crayon-67f526ea859bc473892939/]
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.
[crayon-67f526ea859be621461682/]
[crayon-67f526ea859c4057201407/]
[crayon-67f526ea859c6831071344/]
[crayon-67f526ea859c9230788720/]
[crayon-67f526ea859cb439916344/]
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. This dramatically reduces 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 FlashArray systems 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.
[crayon-67f526ea859ce104123095/]
[crayon-67f526ea859d6363566407/]
[crayon-67f526ea859d9307182817/]
[crayon-67f526ea859db660165050/]
[crayon-67f526ea859de234766906/]
[crayon-67f526ea859e0231501234/]
[crayon-67f526ea859e3695648785/]
[crayon-67f526ea859e7943597401/]
[crayon-67f526ea859ea210991378/]
[crayon-67f526ea859ee056243808/]
[crayon-67f526ea859f1897478978/]
[crayon-67f526ea859f3685892788/]
[crayon-67f526ea859f6574886504/]
[crayon-67f526ea859f9645933956/]
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:
[crayon-67f526ea859fb596473768/]
Overwrite the volumes on the secondary from the protection group snapshot with New-Pfa2Volume:
[crayon-67f526ea859fe936568094/]
Online the volume(s) on the secondary:
[crayon-67f526ea85a01772670357/]
You’ll want to ensure your volume names and drive letters/mount points match the primary’s layout. If you’re using the 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 AG, the database state needs to be RESTORING.
[crayon-67f526ea85a03451330030/]
[crayon-67f526ea85a06542109963/]
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:
[crayon-67f526ea85a08349031224/]
[crayon-67f526ea85a0b249822841/]
Restore it on the secondary:
[crayon-67f526ea85a0e704393561/]
[crayon-67f526ea85a10295935194/]
Set the seeding mode on the secondary to manual:
[crayon-67f526ea85a13459359662/]
[crayon-67f526ea85a15445357601/]
Add the database to the availability group:
[crayon-67f526ea85a18093048174/]
[crayon-67f526ea85a1b199681431/]
Start data movement:
[crayon-67f526ea85a1d547470771/]
Now let’s check the status of the AG. Check to see if the SynchronizationState is Synchronized:
[crayon-67f526ea85a20562966557/]
[crayon-67f526ea85a23751793217/]
[crayon-67f526ea85a25901543932/]
[crayon-67f526ea85a28521692538/]
[crayon-67f526ea85a2a950632944/]
[crayon-67f526ea85a2d684663312/]
[crayon-67f526ea85a2f745541905/]
[crayon-67f526ea85a32075152405/]
[crayon-67f526ea85a34630921577/]
[crayon-67f526ea85a37433966742/]
[crayon-67f526ea85a39472443779/]
[crayon-67f526ea85a3c004199611/]
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 on GitHub.