Skip to main content
Contact Us
Register
Pure Technical Services

Pure Storage FlashArray Management Extension for SQL Server Management Studio

  1. Last updated
  2. Save as PDF

Currently viewing public documentation. Please login to access the full scope of documentation.

Overview

The Pure Storage® FlashArray Management Extension for SQL Server Management Studio (aka FA SSMS Extension) is a database backup and restore extension for SQL Server Management Studio (SSMS). SSMS is the main administration tool for SQL Server. The extension supports creating Application Consistent snapshots using the Pure Storage Volume Shadow Copy Service (VSS) Hardware Provider. The extension can be used from the SSMS GUI or automated using the included Pure Storage Backup SDK Windows PowerShell module. Database Administrators (DBAs) can manage backup and restore operations on local and remote databases from a centralized SSMS deployment.

The FA SSMS Extension will appear in SSMS as a top level menu item and also as an individual database context menu.

clipboard_e42cb27db5374d316508bf2c79029f8f2.png

clipboard_e5ce03010f5246ef8fa582ac2fb5b448a.png

Releases

Click on the release version to download

v2.5.521.0
9/29/2022

Release Notes & User Guide (included in archive)

Fixes: 

  • Fixed a problem where VSS was crashing when there is a disk with invalid or incomplete Vendor information.
v2.5.510.0
12/13/2021

Release Notes & User Guide (included in archive)

Improvements: 

  • Support databases stored in vVol disks
  • Validate backup configuration before saving
  • Request confirmation in PowerShell commands before deleting all backup configurations or all history items
  • Allow specifying Datastore to use when mounting RDM disks
  • Invoke-BackupJob returns detailed information instead of $true/$false to indicate success/failure 
  • Dependent component (PureStorageRequester) installed by the SDK can be uninstalled using built in Windows "Add/Remove Programs"
v2.0.433.0
11/2/2021

Release Notes & User Guide (included in archive)

Fixes: 

  • Fixed issue where mounting to a remote server failed when using mount points.

Improvements:

  • When removing an entry from the Backup History, in both the GUI and Remove-PFABackupHistory cmdlet, an option is available to also destroy the snapshot on the FlashArray.
  • A new Credential Manager is added, where you can list all of the credentials, add new credentials, edit credentials, remove credentials, and test credentials. 

  • AllowPartial (during restore only): If a backup set includes more than 1 pure volume, and 1 or more of the pure volumes is missing the snapshot, the restore will fail. If the administrator prefers to allow a partial restore on the volumes where the snapshots exist, an allowpartial override can be used during the restore.

  • Pods: In ActiveCluster and ActiveDR configurations that utilize FlashArray Pods, snapshots that are mounted will be created inside of the POD.

Note: See Release Notes for more details.
v1.1.100.0
10/25/2021

Release Notes & User Guide (included in archive)

Fixes: 

  • In some configurations during restore, the "norecovery" flag was not honored.

Note: See Release Notes for more details.
v1.1.96.0
9/17/2021

Release Notes & User Guide (included in archive)

Fixes: 

  • Mounting a snapshot to a server connected to the FlashArray with Fibre Channel would fail. Fixed a null termination issue that showed up as a bad HostID from the provider. Fixed an issue where the HostId returned by the provider had undefined characters. 

  • Fixed assertion in GetVDSLunInfo() 

  • Improved build numbers in various binaries. 

  • Keep original registry entries after updating or installing the VSS provider. 

  • Show Install-VSS window if a newer VSS Provider is available in SSMS. This prompts the user to update the provider version. 

  • Code sign all files. Some files were unsigned. 

Note: See Release Notes for more details.
v1.1.76.0

Release Notes & User Guide (included in archive)

Fixes: 

  • DB enumeration succeeds with named instances
  • Changed the string matching to exit code checking, to check if the requestor is returning a good result which fixes some workflows where the language is not set to English.
  • Support for Protection Group snapshots. Volumes can only be part of a single, or no Protection Group. If any volume belongs to more than 1 Protection Group, the backup will fail.

Note: See Release Notes for more details.
v1.0.444.0

Release Notes & User Guide (included in archive)

Fixes: 

  • Fixes an issue in v1.0.440 where some configurations will fail to honor the "no recovery" flag when a restore is performed in SSMS. Invoking the restore with PowerShell honors the "no recovery" flag in both versions.

Known Issues:

  • DB enumeration fails with named instances. 
v1.0.440.0 Original release (no longer available for download)

Resources

The following resources are attached to this article:

  • Overview Presentation -- This presentation provides an overview of the different features and a quick slide walkthrough of the extension experience. 

  • Demo Videos -- These voice over demonstrations showcase a few use cases for the FA SSMS Extension.

    • Overview (4m 35s)
      This video shows the main features of how the management extension works.

    • Mount (1m 31s)
      This video shows taking a backup of a database and mounting to a local and remote SQL Server instance. 

    • Raw Device Map (pRDM) support (1m 48s)
      This video shows how to backup and restore a SQL Server Failover Cluster Instance (FCI) database running in VMware using a physical Raw Device Map.

    • Dev/Test Refresh (48s)
      This video shows how to refresh a production database into a test/dev environment using the PureStorageBackupSDK.

    • ActiveCluster Protection Group Backup (5m 59s)
      This video shows how to configure pgroup or volume snapshots, ActiveCluster configuration, and backup regardless of which FlashArray the SQL FCI is running on. 

Components

The components that are included with the FA SSMS Extension:

  • FA SSMS Extension for SQL Server Management Studio.
  • Volume Shadow Copy Service (VSS) hardware provider.
  • Pure Storage Backup SDK Windows PowerShell module for automation of all operations that can be performed in the SSMS extension.

Supported Features

The Microsoft Support Matrix lists recently delivered versions of the FA SSMS Extension and what is supported in a table view.

  • Application Consistent Snapshots using the VSS hardware provider.
  • Support for Full or Copy-Only backups.
    • Full Backups — A full database backup backs up the entire database. This includes part of the transaction log so that the full database can be recovered after a full database backup is restored. Full database backups represent the database at the time the backup finished. For more details review Full Database Backups (SQL Server).
    • Copy-Only Backups — This is a SQL Server backup that is independent of the sequence of conventional SQL Server backups. In a Full Backup the database is alerted and affects how later backups are restored. Copy-Only Backups provide the ability to use a database for scenarios such as dev/test/QA. For more details review Copy-Only Backups (SQL Server).
  • Support for Point-in-Time Recovery using the No-Recovery option when restoring a database.
  • Support for Microsoft SQL Server physical deployments.
  • Support for VMware ESXi physical Raw Device Maps (pRDMs) and virtual volumes (vVols).
  • Support for Protection 
  • Restoring databases can be performed in the following use cases:
    • Database Restore: Import a backup (snapshot), then restore a database to the original location. This operation will perform an overwrite of the original database volumes in place by restoring Pure Volumes that hold database or transaction log files. Other data, including other SQL data files located on those Pure Volumes will also be impacted by this point-in-time restore.
    • Database Restore with No-Recovery: Import a backup (snapshot), then restore a database in No-Recovery Mode. Additional Transaction Logs can be applied manually as the database is left in the Recovering state.
    • Database Mount: Import a backup (snapshot), then restore a database to a different drive letter or mount point on the original or a different server, using the Mount option. The database files can be copied to the original location, or the database can be directly attached. If attached to the original server be sure to make the "Attach as" name unique.
  • Support for scheduling using SQL Server Agents & Jobs with the CmdExec proxy option.
  • Support for automating backup and restore using the Pure Storage Backup SDK. As part of creating a backup configuration or restore in SSMS a PowerShell Script button is provided for cut & paste of the PowerShell cmdlet details.
    • Example:

clipboard_e45f69d126f95b9cbc40d337c7a9a5b98.png

Frequently Asked Questions

Do databases have to be isolated from each other?

User databases must be isolated from system databases (master, model, tempdb), and cannot be placed on the system drive (C:).

User databases do not have to be isolated from each other. When a backup configuration is saved, 1 database is declared. When a backup is attempted the SQL writer looks for all database files and creates a backup set of every drive containing files that are part of that database such as MDF, NDF, and LDF files. The SQL writer then looks to see if any other database, in every SQL instance on the server, has any files on the backup set of drives. If so, and only if all files that are part of these other databases exist within the backup set of drives, will backup complete. If any database, that has any file on the backup set of drives, but not all files, the backup will fail.

clipboard_e7ee9bc5d41f9a8c7aec8883fd77ff3d2.png

In the image above, the database "rdm" on the instance "RSTANCE" is being backed up which has all of its files on the R: drive. The SQL Writer finds another database "splitdb" which has it's MDF file on the R: drive, but its LDF file is on the C: drive. VSS fails the backup.  Either move the database "splitdb" off of the R: drive, or move its log file from C: to the R: drive.

What version of SQL Server Management Studio is supported?
We only support SSMS 18.x. Between SSMS 17.x and 18.x there where implementation changes which required us to move to the new release branch of SSMS. For full details on Microsoft's support policy with SSMS please refer to https://docs.microsoft.com/en-us/sql/ssms/support-policy.

Does FA SSMS Extension run inside of Purity//Run?
No. FA SSMS Extension is a host based integration that executes on Windows Server to manage SQL Server backup and restore operations.

Does FA SSMS Extension support all versions of SQL Server?
The following versions of Microsoft SQL Server are supported; 2012, 2014, 2016, 2017, and 2019.

What is the required minimum version of Windows?
The FA SSMS Extension requires a minimum of Windows Server 2012 R2 or above. The extension also works with Windows 10 (10.0.18362) or above.

When mounting, if VMware Tools is not installed, available drive letters will not be enumerated. VMware Tools can be installed on Server Core.

If Windows 10 is used:

  • Windows 10 can be the client where SSMS and the FA SSMS Extension is installed, but a snapshot cannot be mounted to that Windows 10 client because the Pure VSS Hardware Provider requires a Windows Server operating system.
  • Other Windows 10 client operating systems that have SQL Server installed, both physical and virtual, are not supported by the FA SSMS Extension because the Pure VSS Hardware Provider requires a Windows Server operating system.
  • Hardware providers are only supported on Windows Server operating systems.

Does Windows PowerShell 7 work with the Pure Storage Backup SDK?
Yes, the Backup SDK does support the use of Windows PowerShell 7.

What use cases does FA SSMS Extension cover?
FA SSMS Extension addresses these core use cases:

  • Application consistent backup (snapshot) of SQL Server database(s); physical and virtualized with VMware raw device mapped (pRDMs) and virtual volumes (vVols).
  • Restore application consistent backups as follows:
    • Import a backup (snapshot), then restore database(s) to the original location. This operation will perform an overwrite operation of the original database.
    • Import a backup (snapshot), then restore database(s) in No-Recovery Mode for applying transaction logs.
    • Import a backup (snapshot), then restore database(s) to a different drive letter(s) using the Mount option which allows for a new drive to be selected for restore.

Does the FA SSMS Extension work with Windows Server Failover Cluster?
Yes. When deploying a database it should be isolated onto its own Clustered Disk and not be included with the SQL Server master, model or msdb databases. Master, model and msdb are typically installed on the root drive (C:\) but will be installed on the shared disk assigned to the SQL Server role for the SQL Instance in a SQL FCI. The new user database disk will need to be added to the cluster, added to the SQL role, and then made dependent of the SQL Server resource. The Computer credential in the Backup Configuration should be set to the SQL FCI Network Name that is resource of the SQL FCI role. Otherwise backup will fail, if the SQL FCI is moved to a different cluster node.

Does the FA SSMS Extension support SQL Server Availability Groups?
Yes. The SQL Server Management Studio (SSMS) extension, which integrates with the Volume Shadow Copy Service (VSS) hardware provider, enables you to recover database volumes, including those within a Microsoft SQL Server Always On Availability Group (AG). Using the VSS provider, you can create application-consistent snapshots and use them to restore data in various scenarios when working in production, development, and test environments. See this blog for more details.

Is the FA SSMS Extension limited to only use SQL Server Management Studio?
No. Using the Windows PowerShell Pure Storage Backup SDK, any of the operations that can be performed in SSMS can also be executed using PowerShell. Every page of the FA SSMS Extension has a 'View PowerShell Script' button which can be used to copy the PowerShell command for automation.

Does the FA SSMS Extension need to be configured manually on each individual SQL Server instance?
No. When creating a new backup configuration and specifying the SQL Server default or named instance the required components will be automatically deployed using PowerShell Remoting.

Does the FA SSMS Extension work with ActiveCluster?
Yes. Support was added in SSMS 1.1.  Both FlashArray's should be added to the Backup Configuration.

Does the FA SSMS Extension work with Protection Groups?
Yes. Support was added in SSMS 1.1.  By default, a Protection Group snapshot will be taken. If it is preferred to only take volume snapshots, this can be configured on each SQL Server by enabling volume snapshot only in the pureproviderconfig.exe tool located in "C:\Program Files\Pure Storage\VSS\Provider" folder. The commands are:

  • pureproviderconfig volumesnapshotonly --status
  • pureproviderconfig volumesnapshotonly --enable
  • pureproviderconfig volumesnapshotonly --disable

Does the FA SSMS Extension work with Cloud Block Store?
Yes. Testing has been completed with Cloud Block Store for AWS and with Cloud Block Store for Azure. 

Does the FA SSMS Extension support VMware vVols?
Yes. vSphere 7.0 is the minimum version supporting vVols that is not end of life. In order for the extension to work with vVol upgrade to the latest 2.5.x version os the FA SSMS Extension.

How critical is the metadata (cab file)?
The metadata contains all information about the snapshot including which volumes are part of the backup set. If the metadata file is lost, mounting or restoring a snapshot through the extension will be impossible.  The snapshots still exist, so a manual volume copy with or without overwrite will be required to access the data. When manually copying VSS snapshots, the readonly, shadowcopy, and hidden flags need to be removed. Using diskpart, select the disk, partition, and then volume. To list the attributes run 'attribute volume'.  To clear the attributes, run 'attribute volume clear readonly', 'attribute volume clear shadowcopy', and 'attribute volume clear hidden'

Can I use mount points instead of drive letters?
Yes. Mount points must be created on empty folders in the root of the drive letter. D:\mount1 is valid, whereas d:\snaps\mount1 is not.