Skip to main content
Pure Technical Services

Pure Storage FlashArray Management Extension for Microsoft SQL Server Management Studio

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

Overview

The Pure Storage® FlashArray Management Extension for Microsoft SQL Server Management Studio (aka FA SSMS Extension) is a database backup and restore extension for Microsoft SQL Server Management Studio (SSMS). 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 the SQL Server Management as a top level menu item and also as an individual database context menu.

clipboard_e0e5f39a2b84f41938a07903d8b8eea92.png

clipboard_ee99c46f933e68c8e3bc33cb0e4639b74.png

Releases

Click on the release version to download

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.

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

  • 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).
  • 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

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 or above. The extension also works with Windows 10 (10.0.18362) or above.

If Windows Server 2012 is used:

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) both physical and virtualized with VMware (RDMs).
  • 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?
No. In order for the extension to work with VVol we need to add that support to our VSS HW Provider.

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'