SQL Server Always On Availability Groups Refresh and Restore Solutions with FlashArray

Prerequisites:

• Ensure that SQL Management Studio version 18.5 or later is installed on the management machine.

• If not already installed, install the Pure Storage SSMS Extension using these instructions. This will install the SSMS extension, The Pure Storage PowerShell Backup SDK module, and the Pure Storage Volume Shadow copy Service (VSS) components.

• Ensure the Databases and associated primary and secondary AG replicas are properly synchronized. Refer to this Microsoft article for more information on AG synchronization states.

• Ensure that PowerShell Remoting is enabled on the SQL Servers that the scripts will be run against. This document shows how to enable this PowerShell feature.

Using SSMS and the Pure SSMS Extension with the PowerShell Backup SDK:

1. In SSMS on the management machine, connect to the primary AG replica source server and the intended test/dev target server.

2. In the Pure Storage SSMS Extension, create a backup configuration for the primary database. Ensure that "Copy-only" is selected.
   

3. Backup the secondary database using the SSMS extension.

4. Mount the secondary backup on the test/dev target SQL server using the Pure Storage SSMS extension.
   
   

5. You now have the option to copy the database files to a different volume, or use it as-is in a mounted drive.

6. Do an Attach in SSMS to add the database to the Test/Dev target SQL Server. If the database name already exists on the server, you must choose a new name for the database.

Using PowerShell and the Pure Storage PowerShell Backup SDK:

This script loads the SQLServer and Pure Storage PowerShell Backup SDK modules on startup. Ensure that these modules are installed before executing this script. 

# Begin script
# Run from a SQL management machine with SSMS and the PureStorage SSMS extension installed.
# PowerShell remoting must also be enabled on the source and target servers

Import-Module PureStorageBackupSDK
[Net.ServicePointManager]::SecurityProtocol = [Net.SecurityProtocolType]::Tls12
if ((Get-InstalledModule -Name "SQLServer" -ErrorAction SilentlyContinue) -eq $null) {
    Install-Module -Name SQLServer
    Import-Module SQLServer
}

$sourceServer = 'server1' # Change to your source server
$targetServer = 'server2' # Change to your target server
$sourcePSSession = New-PSSession -ComputerName $sourceServer
$targetPSSession = New-PSSession -ComputerName $targetServer

# Enter remoting session on source server
Enter-PSSession $sourcePSSession

# Check for SDK Module on source server. If not present, install it.
[Net.ServicePointManager]::SecurityProtocol = [Net.SecurityProtocolType]::Tls12
if ((Get-InstalledModule -Name "SQLServer" -ErrorAction SilentlyContinue) -eq $null) {
    Install-Module -Name SQLServer
    Import-Module SQLServer
}

# Backup the secondary with Copy Only
# Change parameters to match your environment
Add-PfaBackupJob -ConfigName "snapAG" -Component "newdb2" -ComputerName "server1" -FAName "array1" -MetadataDir "C:\Users\administrator\AppData\Roaming" -CopyOnly
Invoke-PfaBackupJob -ConfigName "snapAG"
Remove-PfaBackupJob -ConfigName "snapAG"

# Retrieve the snapshot and mount it to the target as S:
# Change DB name & drive letter if necessary
$getSnapshot = Get-PfaBackupHistory | Where-Object component -eq newdb2 | Sort-Object HistoryId -Descending 
Mount-PfaBackupJob -Historyid $getSnapshot[0].historyid -driveletter "s:" -mountcomputer $targetServer

# Enter remoting session on targer server
Enter-PSSession $targetPSSession

# Attach the mounted DB to the target SQL instance
# Change DB name & drive letter if necessary
$attachDB = @"
USE [master]
GO
CREATE DATABASE [testdevDB] ON (FILENAME = 's:\newdb2.mdf'),(FILENAME = 's:\newdb2_log.ldf') for ATTACH
GO
"@

# Change server name
[Net.ServicePointManager]::SecurityProtocol = [Net.SecurityProtocolType]::Tls12
if ((Get-InstalledModule -Name "SQLServer" -ErrorAction SilentlyContinue) -eq $null) {
    Install-Module -Name SQLServer
    Import-Module SQLServer
}
Invoke-Sqlcmd $attachDB -QueryTimeout 3600 -ServerInstance 'server2' -erroraction 'silentlycontinue'

Exit-PSSession
Remove-PSSession $targetPSSession
Remove-PSSession $sourcePSSession

Using the SQL Server and DBATools PowerShell Modules:

<#
Requires a manual backup of the Production database. You must specify a UNC share folder of the Production backup (*.bak) files as a parameter.
This script requires SQL 2016 or later to utilize automatic seeding.
This script requires the DBATools and SQLServer PowerShell modules.
The DBATools and SQLServer mnodukes are available from the PowerShell Gallery. This script will attempt to install it if it does not exist.
#>

<#
.SYNOPSIS
  This script is will restore the latest SQL Server database backup and restore it to an Availability Group in development.

.DESCRIPTION
  This script is will restore the latest SQL Server database backup and restore it to an Availability Group in development with a specific time rollback of data. If the database exists in the Availability Group, it will be removed from the Availability Group and dropped on all secondaries. Once the database is restored on the primary development replica, it will be added to the secondaries. Automatic seeding will be used to initialize the secondaries.

.PARAMETER prodServer
  The production database server to obtain the backup from.

.PARAMETER prodBackupfolder
  The folder on the production database server to obtain the backup from. This must be a share accessible via a UNC share from the Development SQL server.

.PARAMETER devServer
  The development database server to restore the database to.

.PARAMETER devInstance
  The development database instance name.

.PARAMETER devDatabase
  The development database name to be restored.

.PARAMETER devFolder
  The location of the development database files to be restored.

.PARAMETER Ag
  The development Availability Group the database will be restored to.

.PARAMETER Primary
  The primary replica for the development Availability Group.

.PARAMETER Secondaries
  The secondary replica(s) for the development Availability Group. If there is more than one secondary, define parameter values as an array.
  Example - $secondaries = @("devsql1","devsql2");

.NOTES
  None

.EXAMPLE
  RefreshProdToDev -Ag MyAgname -prodServer MyServer -prodBackupfolder MyBackupfolder -devServer MyDevserver -devInstance MyDevInstance -devDatabase MyDevdatabase -Primary MyPrimary -Secondaries "Secondary1","Secondary2";

#>

function RefreshProdTodev {
    [CmdletBinding()]

    PARAM (
        [Parameter(Mandatory = $true)]
        [string]
        $Ag,
        [Parameter(Mandatory = $true)]
        [string]
        $prodServer,
        [Parameter(Mandatory = $true)]
        [string]
        $prodBackupfolder,
        [Parameter(Mandatory = $true)]
        [string]
        $devServer,
        [Parameter(Mandatory = $true)]
        [string]
        $devInstance,
        [Parameter(Mandatory = $true)]
        [string]
        $devDatabase,
        [Parameter(Mandatory = $true)]
        [string]
        $Primary,
        [Parameter(Mandatory = $true)]
        [string[]]
        $Secondaries
    )

 Begin{
        <# Check for DBATools and SQLServer. If DBATools or SQLServer modules are not present, install and import them. #>
        [Net.ServicePointManager]::SecurityProtocol = [Net.SecurityProtocolType]::Tls12
        if ((Get-InstalledModule -Name "DBATools" -ErrorAction SilentlyContinue) -eq $null) {
            Install-Module -Name DBATools -Force
            Import-Module DBATools
        }
        if ((Get-InstalledModule -Name "SQLServer" -ErrorAction SilentlyContinue) -eq $null) {
            Install-Module -Name SQLServer -Force
            Import-Module SQLServer
        }
        }

 Process {
   Try {
    $exists = Get-DbaDatabase -SqlInstance $primary -Database $devDatabase;

            if ($exists) {
                <# Remove the database to be restored from the Dev Availability Group #>
                Remove-DbaAgDatabase -SqlInstance $Primary -Database $devDatabase -Ag $Ag -Confirm:$false;
                <# Drop the database from all secondary replicas #>
                Remove-DbaDatabase -SqlInstance $Secondaries -Database $devDatabase -Confirm:$false;
            }

            <# Place the dev database in single user mode #>
            Get-DbaDatabase -SqlInstance $devInstance -Database $devDatabase | Set-DbaDbState -SingleUser -Force;

            <# Restore the database to the primary replica #>
            Restore-DbaDatabase -SqlInstance $devServer -Path \\$prodServer\$prodBackupfolder -DatabaseName $devDatabase -NoRecovery -DestinationDataDirectory $devFolder;

            # Set to MultiUser
            Get-DbaDatabase -SqlInstance $Primary -Database $devDatabase | Set-DbaDbState -MultiUser -Force;

            <# Automatic seeding to initialize the secondaries. #>
            Add-DbaAgDatabase -SqlInstance $Primary -Ag $Ag -Database $devDatabase -SeedingMode Automatic;

            <# Sync the AG properties to secondaries #>
            Sync-DbaAvailabilityGroup -Primary $devServer -AvailabilityGroup $Ag

  }
    Catch {
        "Something went wrong. $_"
        Break
    }

}

End {
    If ($?) {
        Write-Host "Completed Restore."
    }
}
}

Using PowerShell, the Pure Storage PowerShell Backup SDK, and DBATools:

<#
This script requires SQL 2016 or later to utilize automatic seeding.
This script requires the DBATools and the Pure Storage SSMS extension.
The DBATools module is available from the PowerShell Gallery. This script will attempt to install it if it does not exist.
The Pure Storage SSMS Extension is available here - https://github.com/PureStorage-Connect/FlashArray-SSMS-Extension/releases.
This script requires PowerShell 5.1.
#>

<#
.SYNOPSIS
  This script is will restore the latest SQL Server database that was backed up via an application consistent snapshot using the Pure Storage Backup SSMS extension and add it to an Availability Group.

.DESCRIPTION
  This script is will restore the latest SQL Server database application consistent snapshot from a Pure Storage FlashArray and add it to an Availability Group. If the database exists in the Availability Group, it will be removed from the Availability Group and dropped on all secondaries. Once the database is restored on the primary replica, it will be added to the secondaries. Automatic seeding will be used to initialize the secondaries.

.PARAMETER Ag
  The Availability Group the database will be restored to.

.PARAMETER Mountserver
  The database server to mount the restored snapshot to. PowerShell Remoting must be enabled on this server for drive mounting to work.

.PARAMETER Database
  The database to be restored.

.PARAMETER Driveletter
  The drive letter to use to mount the database to the primary. Eg. "S:" (colon must be present).

.PARAMETER Primary
  The primary replica for the Availability Group.

.PARAMETER Secondaries
  The secondary replica(s) for the Availability Group. If there is more than one secondary, define parameter values as an array.
  Example - $secondaries = @("sql2019vm2","sql2019vm3");

.NOTES
  Modified for the Pure Storage FlashArray and SSMS Extension.
  Thanks Frank Gill.

.EXAMPLE
  PSRestoreAgDatabase -Ag MyAgName -Mountserver MyServer -Database MyDatabase -BackupConfigname MyBackupConfig -Primary MyPrimary -Secondaries "Secondary1","Secondary2";

#>

Function Restore-AgDatabase{
  [CmdletBinding()]

    PARAM (
        [Parameter(Mandatory=$true)]
        [string]
        $Ag,
        [Parameter(Mandatory=$true)]
        [string]
        $Mountserver,
        [Parameter(Mandatory=$true)]
        [string]
        $Database,
        [Parameter(Mandatory=$true)]
        [string]
        $Primary,
        [Parameter(Mandatory=$true)]
        [string[]]
        $Secondaries,
        [Parameter(Mandatory=$true)]
        [string[]]
        $Driveletter,
        [Parameter(Mandatory=$true)]
        [string]
        $BackupConfigfile
    )

  Begin{
    Write-Host "Start PSRestore-AgDatabase function..."
    <# Check for DBATools and Pure Storage Backup SDK modules. If DBATools is not present, install it. If Pure Storage
        BackupSDK is not present, stop. If not loaded, import it. #>
        [Net.ServicePointManager]::SecurityProtocol = [Net.SecurityProtocolType]::Tls12
        if ((Get-InstalledModule -Name "DBATools" -ErrorAction SilentlyContinue) -eq $null) {
            Install-Module -Name DBATools -Force
            Import-Module DBATools
        }
        if ((Get-InstalledModule -Name "PureStorageBackupSDK" -ErrorAction SilentlyContinue) -eq $null) {
            Write-Host "The Pure Storage SSMS extension, which includes the Backup SDK, must be installed to use this script."
            Write-Host "Download the latest from https://github.com/PureStorage-Connect/FlashArray-SSMS-Extension/releases"
            Break
        else {
            Import-Module PureStorageBackupSDK
        }
        }
  }

  Process{
    Try{
        Write-Host "Restoring and mounting last available snapshot for primary database from FlashArray"
        $getSnapshot = Get-PfaBackupHistory | Where-Object component -eq $BackupConfigname | Sort-Object HistoryId -Descending;
        Mount-PfaBackupJob -HistoryId $getSnapshot[0].historyid -DriveLetter $Driveletter -MountComputer $Mountserver;

        $TestPSSession = New-PSSession -ComputerName $Mountserver
        Enter-PSSession $TestPSSession
        $backupexists = Test-Path -Path $Driveletter;

        if($backupexists -ne $true)
        {
            throw "The snapshot did not restore.  Please verify the snapshot in the FlashArray UI and try again.";
        else {
            Remove-PSSession
        }}

        $replicas = @();
        $replicas += $Primary;
        $replicas += $Secondaries;
        $version = Invoke-DbaQuery -SqlInstance $Primary -Database master -Query "SELECT SERVERPROPERTY('productversion')";
        $majorversion = $version.Column1.Substring(0,2);

        $primaryinfo = Get-DbaAgReplica -SqlInstance $Primary -AvailabilityGroup $Ag -Replica $Primary;
        $role = $primaryinfo.Role;

        if($role -ne "Primary")
        {
            throw "$Primary was entered as the primary replica and it is $role";
        }

        if($majorversion -lt 13)
        {
                throw "SQL version is less than 2016 and this script cannot be used.";
                Break
            else
            {
                foreach($replica in $replicas)
                {

                    $services = Get-DbaService -Computer $replica;
                    $serviceacct = $services | Select-Object ServiceName, StartName | Where-Object ServiceName -eq MSSQLSERVER;
                    $sqlacct = $serviceacct.StartName;
                }
            }
        }

        $exists = Get-DbaDatabase -SqlInstance $Primary -Database $Database;

        if($exists)
        {
            <# Remove the database to be restored from the Availability Group #>
            Remove-DbaAgDatabase -SqlInstance $Primary -Database $Database -AvailabilityGroup $Ag -Confirm:$false;
            <# Drop the database from all secondary replicas #>
            Remove-DbaDatabase -SqlInstance $Secondaries -Database $Database -Confirm:$false;
        }
        <# Attach the database to the primary replica #>
            Mount-DbaDatabase -SqlInstance $Primary -Path $Driveletter -DatabaseName $Database;

        <# Automatic seeding to initialize the secondaries. #>
            Add-DbaAgDatabase -SqlInstance $Primary -AvailabilityGroup $Ag -Database $Database -SeedingMode Automatic;
    }

    Catch{
      "Something went wrong. $_"
      Break
    }

  }

  End{
    If($?){
      Write-Host "Completed Restore."
    }
  }
}