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 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.
- Download the package for installation. The ZIP archive contains the installation package (MSI) and User Guide.
- View the User Guide online.
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.
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.
- 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).
- 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.
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 and 2017.
What is the required minimum version of Windows?
The FA SSMS Extension requires a minimum of Windows Server 2012 or above. If Windows Server 2012 is used Windows PowerShell 5.0 must be installed. The extension also works with Windows 10 (10.0.18362) or above.
Is Windows PowerShell 6 or 7 Preview work with the Pure Storage Backup SDK?
No. The Backup SDK does not support the use of Windows PowerShell 6 or 7 Preview.
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.
Does the FA SSMS Extension support SQL Server Availability Groups?
Not completely. Application consistent and crash consistent snapshots can be used for databases/volumes that participate in a SQL Server Availability Group, to be mounted as a standalone database outside of the SQL Server Availability Group. At this time you cannot seed a database for joining a database into an existing SQL Server Availability Group. This is under investigation with engineering.
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.
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?
No. In order for the extension to work with ActiveCluster we need to add in our VSS HW Provider support for Protection Groups. This is planned as a stretch goal before GA but we cannot guarantee inclusion.
Is there an internal build that can be used for testing?
We are working on a merge build that has both physical and virtualization (pRDM) support that internal employees can use for testing.
Does the FA SSMS Extension work with Cloud Block Store?
Yes. Testing has been completed with Cloud Block Store for AWS and an early preview of Cloud Block Store for Azure.
Does the FA SSMS Extension support VMware VVols?
No. VVol support is on our roadmap post but no timeline has been committed as of yet.
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.