Summary
If you have a multi-array environment, you can freeze database write I/O to take coordinated snapshots across volumes on two FlashArray systems using the T-SQL snapshot backup feature in SQL Server 2022.
This article first appeared on Anthony Nocentino’s blog. It has been republished with the author’s credit and consent.
In this post, the fourth in the series, I want to share an example demonstrating SQL Server 2022’s T-SQL snapshot backup feature in a scenario where a database spans multiple storage arrays. If you’re dealing with multi-array environments, you’ll appreciate how this technique freezes database write I/O to take coordinated snapshots across volumes on two FlashArray™ systems. In this post, I’ll walk you through the process, point out some of the script’s key elements, and show you how long the write I/O pause takes.
The Scenario
Imagine you have a database called MultiArraySnapshot that is distributed across two separate volumes, each located on its own Pure Storage® FlashArray system. This setup can be beneficial for managing large, mission-critical environments where performance and data consistency are essential. The challenge lies in coordinating a snapshot backup that allows both arrays to capture a consistent view of the data simultaneously. And using SQL Server 2022’s T-SQL snapshot backup feature we can do just that…let’s dig in.
Figure 1: A database with files on two FlashArray systems.
Overview of the Process
The process is straightforward: Freeze the database using T-SQL snapshot, take snapshots of each array, and then execute a metadata-only backup to document the snapshot details. This metadata backup automatically thaws the database.
Setting Up the Environment
First, we import the necessary modules and define connection variables for our SQL Server instance, the two FlashArray systems, and various volumes. Note how we specify details such as the target disk serial numbers and the backup share path:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 |
Import–Module dbatools Import–Module PureStoragePowerShellSDK2 # Define target SQL Server and FlashArrays $TargetSQLServer = ‘SqlServer1’ # SQL Server Name $ArrayName1 = ‘flasharray1.example.com’ # First FlashArray $ArrayName2 = ‘flasharray2.example.com’ # Second FlashArray $PGroupName1 = ‘SqlServer1_Pg’ # Name of the Protection Group on FlashArray1 $PGroupName2 = ‘SqlServer1_Pg’ # Name of the Protection Group on FlashArray2 $DbName = ‘MultiArraySnapshot’ # Name of database $FlashArray1DbVol = ‘Fa1_Sql_Volume_1’ # Volume name on FlashArray1 containing database files $FlashArray2DbVol = ‘Fa2_Sql_Volume_1’ # Volume name on FlashArray2 containing database files $BackupShare = ‘\\FileServer1\SHARE\BACKUP’ # File system location to write the backup metadata file $TargetDisk1 = ‘6000c296dd4362f1a9263c53f2d9d6c1’ # The serial number of the Windows volume containing database files $TargetDisk2 = ‘6000c29ef1396de0dad628b856523709’ # The serial number of the Windows volume containing database files |
We establish a PowerShell remoting session using New-Session and create a persistent SMO connection with Connect-DbaInstance. This connection is essential as it remains open until the backup metadata is written.
|
1 2 |
$SqlServerSession = New–PSSession –ComputerName $TargetSQLServer $SqlInstance = Connect–DbaInstance –SqlInstance $TargetSQLServer –TrustServerCertificate –NonPooledConnection |
Examine the Database File Layout
Next, let’s use Get-DbaDbFile to get detailed information about the database files for the specified database. In our example here P:\SQLDATA1\MultiArraySnapshot.mdf is located on FlashArray1 and Q:\SQLLOG1\MultiArraySnapshot_log.ldf is located on FlashArray2.
|
1 2 3 4 5 6 7 8 |
Get–DbaDbFile –SqlInstance $SqlInstance –Database $DbName | Select–Object Database, LogicalName, PhysicalName | Format–Table Database LogicalName PhysicalName ———— —————– —————— MultiArraySnapshot MultiArraySnapshot P:\SQLDATA1\MultiArraySnapshot.mdf MultiArraySnapshot MultiArraySnapshot_log Q:\SQLLOG1\MultiArraySnapshot_log.ldf |
Connecting to the FlashArray Systems
Now, we authenticate against the REST APIs of our FlashArray systems using credentials stored locally. This enables us to perform snapshot operations on the arrays.
|
1 2 3 |
$Credential = Import–CliXml –Path “$HOME\FA_Cred.xml” $FlashArray1 = Connect–Pfa2Array –EndPoint $ArrayName1 –Credential $Credential –IgnoreCertificateError $FlashArray2 = Connect–Pfa2Array –EndPoint $ArrayName2 –Credential $Credential –IgnoreCertificateError |
Freezing the Database
To create a point-in-time snapshot across the two FlashArray systems, we first temporarily suspend write I/O by executing the command ALTER DATABASE [MultiArraySnapshot] SET SUSPEND_FOR_SNAPSHOT_BACKUP = ON. This action puts the database in a stable state, preparing it for the snapshots from both FlashArray systems. During this process, you can still read from the database.
|
1 2 3 4 5 6 |
$Query = “ALTER DATABASE $DbName SET SUSPEND_FOR_SNAPSHOT_BACKUP = ON” Invoke–DbaQuery –SqlInstance $SqlInstance –Query $Query –Verbose VERBOSE: Database ‘MultiArraySnapshot’ acquired suspend locks in session 58. VERBOSE: I/O is frozen on database MultiArraySnapshot. No user action is required. However, if I/O is not resumed promptly, you could cancel the backup. VERBOSE: Database ‘MultiArraySnapshot’ successfully suspended for snapshot backup in session 58. |
Taking the Snapshots
With the database frozen for write I/O, we now take snapshots on both arrays using New-Pfa2ProtectionGroupSnapshot. These snapshots capture the protection group state.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 |
$SnapshotFlashArray1 = New–Pfa2ProtectionGroupSnapshot –Array $FlashArray1 –SourceName $PGroupName1 $SnapshotFlashArray2 = New–Pfa2ProtectionGroupSnapshot –Array $FlashArray2 –SourceName $PGroupName2 $SnapshotFlashArray1 $SnapshotFlashArray2 Name : SqlServer1_Pg.3044 Created : 2/1/2025 7:39:02 PM ...output omitted Name : SqlServer1_Pg.39 Created : 2/1/2025 7:39:02 PM ...output omitted |
Metadata Backup and Unfreeze
Next, a metadata backup is created. This is the crucial step in generating a backup file (we’ll use this later when restoring this database), automatically unfreezing the database, and recording that snapshot in the database’s backup history.
|
1 2 3 4 5 6 7 8 9 10 |
$Query = “BACKUP DATABASE $DbName TO DISK=’$BackupFile’ WITH METADATA_ONLY” Invoke–DbaQuery –SqlInstance $SqlInstance –Query $Query –Verbose VERBOSE: I/O was resumed on database MultiArraySnapshot. No user action is required. VERBOSE: Database ‘MultiArraySnapshot’ released suspend locks in session 58. VERBOSE: Database ‘MultiArraySnapshot’ originally suspended for snapshot backup in session 58 successfully resumed in session 58. VERBOSE: Processed 0 pages for database ‘MultiArraySnapshot’, file ‘MultiArraySnapshot’ on file 1. VERBOSE: BACKUP DATABASE successfully processed 0 pages in 0.004 seconds (0.000 MB/sec). |
You can check the status of the snapshot backup by reviewing the backup history from the instance using Get-DbaDbBackupHistory.
|
1 2 3 4 5 |
Get–DbaDbBackupHistory –SqlInstance $SqlInstance –Database $DbName –Last SqlInstance Database Type TotalSize DeviceType Start Duration End —————– ———— —— ————– ————— ——– ———— —– aen–sql–22–a MultiArraySnapshot Full 18.65 MB Disk 2025–02–01 19:39:26.000 00:00:00 2025–02–01 19:39:26.000 |
Let’s Do Something Not So Great…
Now, for demonstration purposes, let’s drop a table.
|
1 |
Invoke–DbaQuery –SqlInstance $SqlInstance –Database $DbName –Query “DROP TABLE T1” |
Performing a Restore from a Snapshot Backup on Two Arrays
After dropping the table, let’s restore the whole database from a snapshot. The first thing we need to do is take the database offline.
|
1 2 |
$Query = “ALTER DATABASE $DbName SET OFFLINE WITH ROLLBACK IMMEDIATE” Invoke–DbaQuery –SqlInstance $SqlInstance –Database master –Query $Query |
Next, we take the volumes that contain our database files offline using Set-Disk, which targets the disks by their serial numbers over a PowerShell remoting connection. While you can run this locally on the SQL Server instance, I’m executing this code from my jumpbox.
|
1 2 3 4 5 |
Invoke–Command –Session $SqlServerSession ` –ScriptBlock { Get–Disk | Where–Object { $_.SerialNumber –eq $using:TargetDisk1 } | Set–Disk –IsOffline $True; Get–Disk | Where–Object { $_.SerialNumber –eq $using:TargetDisk2 } | Set–Disk –IsOffline $True; } |
With the database and its volumes offline, we now use the snapshots on each FlashArray to restore the volumes to their state. Before I delete the table from the database, we will use New-Pfa2Volume for this.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 |
New–Pfa2Volume –Array $FlashArray1 ` –Name $FlashArray1DbVol ` –SourceName ($SnapshotFlashArray1.Name + “.$FlashArray1DbVol”) ` –Overwrite $true Id : 002d6261–ed7f–4623–e723–7c47d71468d6 Name : Fa1_Sql_Volume_1 ...output omitted New–Pfa2Volume –Array $FlashArray2 ` –Name $FlashArray2DbVol ` –SourceName ($SnapshotFlashArray2.Name + “.$FlashArray2DbVol”) ` –Overwrite $true Id : f19a7c65–424d–fe41–3448–e83e94cebb9b Name : Fa2_Sql_Volume_1 ...output omitted |
After reverting the volumes on each of the FlashArray systems to their previous state via the snapshots, let’s bring the database’s disks online in Windows.
|
1 2 3 4 5 |
Invoke–Command –Session $SqlServerSession ` –ScriptBlock { Get–Disk | Where–Object { $_.SerialNumber –eq $using:TargetDisk1 } | Set–Disk –IsOffline $False; Get–Disk | Where–Object { $_.SerialNumber –eq $using:TargetDisk2 } | Set–Disk –IsOffline $False; } |
After bringing the disks back online, we restore the database using our metadata backup, using RESTORE DATABASE $DbName FROM DISK = ‘$BackupFile’ WITH METADATA_ONLY, REPLACE.
|
1 2 3 4 |
$Query = “RESTORE DATABASE $DbName FROM DISK = ‘$BackupFile’ WITH METADATA_ONLY, REPLACE” Invoke–DbaQuery –SqlInstance $SqlInstance –Database master –Query $Query –Verbose VERBOSE: RESTORE DATABASE successfully processed 0 pages in 0.478 seconds (0.000 MB/sec). |
Once restored, we check the state of the database MultiArraySnapshot and see that it is ONLINE.
|
1 2 3 4 5 6 7 8 9 |
Get–DbaDbState –SqlInstance $SqlInstance –Database $DbName Access : MULTI_USER ComputerName : aen–sql–22–a DatabaseName : MultiArraySnapshot InstanceName : MSSQLSERVER RW : READ_WRITE SqlInstance : aen–sql–22–a Status : ONLINE |
And finally, we verify that our dropped table is back in place thanks to the nearly instantaneous restore process.
|
1 2 3 4 5 6 |
Get–DbaDbTable –SqlInstance $SqlInstance –Database $DbName –Table ‘T1’ | Format–Table ComputerName InstanceName SqlInstance Database Schema Name IndexSpaceUsed DataSpaceUsed RowCount HasClusteredIndex IsFile Table —————— —————— —————– ———— ——— —— ——————— ——————– ———— ————————– ——— aen–sql–22–a MSSQLSERVER aen–sql–22–a MultiArraySnapshot dbo T1 8.00 3864.00 300000 False False |
Why Am I Doing This?
This script is important because it shows a way to snapshot and restore a database from snapshot across multiple storage arrays in a SQL Server environment. Here’s why it matters:
- Consistent snapshots across arrays: This example above utilizes SQL Server 2022’s T-SQL snapshot backup to pause write I/O on a database with files across two volumes on two different FlashArray systems. By doing this, snapshots taken on two separate FlashArray systems capture the database in a stable state, ensuring that the database is restored reliably.
- Minimized write I/O pause: By coordinating a metadata-only backup that automatically unfreezes the database, this process minimizes the window of downtime, which is a critical factor in high-availability/high-performance environments.
- Modern SQL Server capabilities: This section showcases new capabilities in SQL Server 2022 and provides a real-world example of how to use these features to enhance backup strategies and disaster recovery plans in complex deployment scenarios, such as a database spanning two or more arrays.
How Long Does This Take?
This demo runs the code from the freeze until the thaw is around 200ms for the entire snapshot process across both arrays.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 |
$Start = (Get–Date) $Query = “ALTER DATABASE $DbName SET SUSPEND_FOR_SNAPSHOT_BACKUP = ON” Invoke–DbaQuery –SqlInstance $SqlInstance –Query $Query –Verbose $SnapshotFlashArray1 = New–Pfa2ProtectionGroupSnapshot –Array $FlashArray1 –SourceName $PGroupName1 $SnapshotFlashArray2 = New–Pfa2ProtectionGroupSnapshot –Array $FlashArray2 –SourceName $PGroupName2 $BackupFile = “$BackupShare\$DbName_$(Get-Date -Format FileDateTime).bkm” $Query = “BACKUP DATABASE $DbName TO DISK=’$BackupFile’ WITH METADATA_ONLY” Invoke–DbaQuery –SqlInstance $SqlInstance –Query $Query –Verbose $Stop = (Get–Date) Write–Output “The snapshot time takes…$(($Stop – $Start).Milliseconds)ms!” The snapshot time takes...208ms! |
Read More from This Series
Using T-SQL Snapshot Backup: Are Snapshots Backups?
Using T-SQL Snapshot Backup: Point-in-time Recovery
Using T-SQL Snapshot Backup: Point-in-time Recovery – Azure Edition
Using T-SQL Snapshot Backup: Multi-array Database Snapshots
Wrapping Things Up
In this post, the fourth in our series, we used the T-SQL snapshot backup feature in a multi-array environment.
You saw how you can:
- Restore quickly using metadata-only backups
- Freeze write I/O on a database
- Coordinate snapshots across multiple storage arrays
You can grab the whole script for this blog post on GitHub.
White Paper, 7 pages
Perfecting Cyber Resilience: The CISO Blueprint for Success
Go Deeper
Learn more in the video “Are Snapshots Backups?”
