Using FlashArray Volume Snapshots with SQL Server

1. Create a protection group by clicking the + icon for Source Protection Groups. The Create Protection Group dialogue will appear.

   Figure 1. The Protection Groups tab in the FlashArray user interface.
2. Name the Protection Group, and then click Create.

   Figure 2. The Create Protection Group dialogue.
3. To add volumes to the protection group, click the ellipsis icon for Members, and then select Add Volumes.

   Figure 3. Adding volumes to a protection group.
4. Select all volumes relevant to the database. This should be done for both application- and crash-consistent snapshots.

   Figure 4. The Add Members dialogue.
5. After adding selected members to the protection group, use the example protection group "UserDB-PG" for all use case samples going forward.

   Figure 5. The UserDB-PG protection group.

1. Enter Optional Suffix information for the snapshot's name (the snapshot name will default to "volume name.snapshot-number" without a suffix being set), and then click Create.

   Figure 7. Naming a snapshot in the Create Snapshot dialogue.
2. The snapshot name (with optional suffix) is displayed for Source Protection Group Snapshots.

   Figure 8. The snapshot created for the source protection group.

1. If the database being restored is available, online, and usable, ensure that it is set offline using the following command:ALTER DATABASE [UserDB] OFFLINE WITH ROLLBACK IMMEDIATE;

2. The Windows disks that the volumes correspond to also need to be offline prior to restoring the volume. snapshot. To offline the disks, in the Disk Management application on the host, right-click on the disk, and then select Offline.

   Figure 9. Volumes attached to the SQL Server host as offline drives.
3. In the FlashArray user interface, select the protection group snapshot from the previously created snapshot, and then select it to open the detail view for the volume snapshots contained within it.

   Figure 10. Selecting the protection group snapshot.
4. To restore the snapshots to the original volumes, click the overwrite icon.

   Figure 11. Restoring a snapshot using the overwrite icon.
5. In the Restore Volume from Snapshot prompt, review the volume name, and then select Restore to confirm.

   Figure 12. The Restore Volume from Snapshot dialogue.

6. Repeat steps 4–5 for each volume in the protection group snapshot.

7. Overwriting volumes with snapshots does not immediately destroy the state of a volume when it is overwritten. Instead, a snapshot of the state of the volume is taken and then placed in the Destroyed Volume Snapshots section, which is then retained for a set period prior to being eradicated.

   Figure 13. The Destroyed Volume Snapshots section of the Volumes tab.
8. If the volumes are in an offline state, they need to be set online. In Disk Management, right-click each offline disk, and then select Online.

   Figure 14. Bringing the disks online in the Windows Disk Manager.
9. If the disk does not have a drive letter assigned, right-click the disk, and then select Change Drive Letter and Paths.

   Figure 15. Volumes attached as drives on the SQL Server host.

10. If the disks come online in a read-only state, right-click each disk and set it to read/write.

11. If the database was set offline in step 1, and the database paths remain consistent, then set the database to online by using the following command:ALTER DATABASE [UserDB] ONLINE;

12. If the database was deleted or is being recovered to a different set of paths, attach the database to the current instance by running the CREATE DATABASE T-SQL command with the FOR ATTACH property and the appropriate paths to each datafile.

    CREATE DATABASE [UserDB_temp] ON ( FILENAME = 'E:\SQL\UserDB.mdf' ),( FILENAME = 'F:\SQL\UserDB_log.ldf' ) FOR ATTACH

1. Prior to creating a Protection Group snapshot, the write operations on the database need to be suspended and the database prepared for a storage snapshot backup. The database will be frozen for write input with no impact to read output, until the BACKUP DATABASE [UserDB] TO DISK...WITH METADATA_ONLY command is run. This step should be automated to reduce the duration of the write input/output freeze. To do this, execute the following T-SQL command, substituting the database name for [UserDB]:ALTER DATABASE [UserDB] SET SUSPEND_FOR_SNAPSHOT_BACKUP = ON

2. Go to Source Protection Group Snapshots, click the ellipsis, and then select Create to create a snapshot.

   Figure 16. Creating a snapshot.
3. Enter Optional Suffix information for the snapshot's name, and then click Create. The snapshot name (with optional suffix) is displayed for Source Protection Group Snapshots. Take note of this snapshot name for reference in other examples in this document.

   Figure 17. Naming a snapshot in the Create Snapshot dialogue.

4. After the protection group snapshot has been created, finish the snapshot backup process by executing the following com-mand, substituting the database name and location for the metadata file (the backup file is required to put the database into restoring mode for point in time recovery):BACKUP DATABASE [UserDB] TO DISK='C:\SQL\UserDB.bkm' WITH METADATA_ONLY

1. See the section on Creating a Crash-consistent Snapshot to create a crash-consistent snapshot.

2. To copy the volumes, select the protection group snapshot from the previously created snapshot, and then select the copy icon to copy to a new volume.

   Figure 25. Volumes attached to the SQL Server host as offline drives.
3. Copy the selected snapshot to a new volume by creating a name and then clicking Copy.

   Figure 26. The snapshot created for the source protection group.
4. Repeat steps 3 and 4 for each volume in the protection group, providing a unique name for each volume.

   Figure 27. Restoring a snapshot using the overwrite icon.
5. Once the snapshot is cloned/copied (a data reduced copy), verify that a new host or hosts are available. These hosts can be part of a dev or test environment or a reporting server. In the FlashArray user interface, select Storage in the navigation pane, and then click the Hosts tab.

   Figure 28. The Restore Volume from Snapshot dialogue.

6. Verify that the new host appears in the Hosts group, and then attach the host from the Hosts tab.

7. On the new host, open the Disk Management application. The volumes appear as offline disks.

   Figure 29. The Destroyed Volume Snapshots section of the Volumes tab.
8. Right-click each offline disk, and then select Online.

   Figure 30. Bringing the disks online in Windows Disk Manager.
9. If the disk does not have a drive letter assigned, right-click the disk, and then choose Change Drive Letter and Paths.

   Figure 31. Volumes attached as drives on the SQL Server host.

10. If the disks come online in a read-only state, right-click each disk and set it to read/write.

11. Attach the database to the current instances by running the CREATE DATABASE T-SQL command with the FOR ATTACH property and the appropriate paths to each datafile.

    CREATE DATABASE [UserDB] ON ( FILENAME = 'E:\SQL\UserDB.mdf' ),( FILENAME = 'F:\SQL\UserDB_log.ldf' ) FOR ATTACH

    The database is now available on the new host. The snapshot can now be cloned to other dev, test, or reporting SQL Server hosts.

Import-Module dbatools
Import-Module PureStoragePowerShellSDK2
$Credential              = Get-Credential                 # Get the user credentials
$PrimarySqlServer                = 'SqlServer1.example.com'      # SQL Server primary replica             
$SecondarySqlServer      = 'SqlServer2.example.com'      # SQL Server secondary replica
$AgName                  = 'ag1'                  # Name of the availability group
$DbName                  = 'UserDB'               # Name of database to place in the                                                                    
#availability        group
$BackupShare     = '\\FileServer1\SHARE\BACKUP'  # File location for metadata backup file.
$PrimaryArrayName = 'flasharray1.example.com' # FlashArray containing #the primary replica
$SecondaryArrayName      = 'flasharray2.example.com' # FlashArray containing the volumes
for      # secondary replica
$SourcePGroupName        = 'SqlServer1_Pg'          # Name of the protection group on
                                                    #primary array
$TargetPGroupName = 'flasharray1:SqlServer1_Pg'    # Name of the protection group replicated
     FlashArray1        to        FlashArray
formatted        as      #ArrayName:ProtectionGroupName
$PrimaryFaDbVol          = 'Fa1_Sql_Volume_1'              # Volume name on FlashArray containing
# the primary replica database files
$SecondaryFaDbVol                 = 'Fa2_Sql_Volume_1'   # Volume name on FlashArray containing
# the primary replica database files
$TargetDisk              = 'serialnum'            # The serial number of the Windows                  # volume containing database files

# Connect to the primary replica FlashArray 
$FlashArrayPrimary = Connect-Pfa2Array –EndPoint $PrimaryArrayName -Credential $Credential -IgnoreCertifica-teError 
#Suspend write operations to the database on the primary replica
$Query = "ALTER DATABASE [$DbName] SET SUSPEND_FOR_SNAPSHOT_BACKUP = ON"
Invoke-DbaQuery -SqlInstance $SqlInstancePrimary -Query $Query -Verbose
#Create a snapshot of the primary replica's protection group
$SourceSnapshot = New-Pfa2ProtectionGroupSnapshot -Array $FlashArrayPrimary -SourceName $SourcePGroupName 
-ForReplication $true -ReplicateNow $true 
#Create a metadata backup, which will unfreeze the database if successful
$BackupFile = "$BackupShare\$DbName$(Get-Date -Format FileDateTime).bkm"
$Query = "BACKUP DATABASE $DbName 
	TO DISK='$BackupFile' 
   WITHMETADATA_ONLY,MEDIADESCRIPTION='$($SourceSnapshot.Name)|$($FlashArrayPrimary.ArrayName)'"
Invoke-DbaQuery -SqlInstance $SqlInstancePrimary -Query $Query -Verbose

# Connect to the secondary replica FlashArray 
$FlashArraySecondary = Connect-Pfa2Array –EndPoint $SecondaryArrayName -Credential $Credential -IgnoreCer-tificateError 
#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 $TargetP-GroupName |       
	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 $($Tar-getSnapshot.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)"

# Connect to the secondary replica FlashArray and take the volume offline
Invoke-Command -Session $SecondarySession -ScriptBlock { Get-Disk | Where-Object { $_.SerialNumber -eq $us-ing:TargetDisk } | Set-Disk -IsOffline $True }
# Overwrite the volumes on the secondary replica FlashArray with the snapshot
New-Pfa2Volume -Array $FlashArraySecondary -Name $SecondaryFaDbVol -SourceName ($TargetSnapshot.Name + ".$PrimaryFaDbVol") -Overwrite $true 
#Bring the volume online on the secondary replica FlashArrayInvoke-Command -Session $SecondarySession -ScriptBlock { Get-Disk | Where-Object { $_.SerialNumber -eq $us-ing:TargetDisk } | Set-Disk -IsOffline $False }

# Restore the database with the NORECOVERY option
$Query = "RESTORE DATABASE [$DbName] FROM DISK = '$BackupFile' WITH METADATA_ONLY, REPLACE, NORECOVERY" 
Invoke-DbaQuery -SqlInstance $SqlInstanceSecondary -Database master -Query $Query -Verbose 
#Take a log backup from the primary replica
$Query = "BACKUP LOG [$DbName] TO DISK = '$BackupShare\$DbName-seed.trn' WITH FORMAT, INIT" 
Invoke-DbaQuery -SqlInstance $SqlInstancePrimary -Database master -Query $Query -Verbose
#Restore the log backup to the secondary replica
$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 replica 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 the availability group replication process
Invoke-DbaQuery -SqlInstance $SecondarySqlInstance -Database master -Query 'ALTER DATABASE [$DbName] SET HARD AVAILABILITY GROUP = [ag1];'
#Check the status of the availability group to see if the primary and secondary replicas are synchronizedGet-DbaAgDatabase -SqlInstance $SqlInstancePrimary -AvailabilityGroup $AgName