Pure Storage FlashArray Management Extension for Microsoft SQL Server Management Studio
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.
Releases
Click on the release version to download
v2.5.521.0 9/29/2022 |
Release Notes & User Guide (included in archive) Fixes:
|
v2.5.510.0 12/13/2021 |
Release Notes & User Guide (included in archive) Improvements:
|
v2.0.433.0 11/2/2021 |
Release Notes & User Guide (included in archive) Fixes:
Improvements:
Note: See Release Notes for more details. |
v1.1.100.0 10/25/2021 |
Release Notes & User Guide (included in archive) Fixes:
Note: See Release Notes for more details. |
v1.1.96.0 9/17/2021 |
Release Notes & User Guide (included in archive) Fixes:
Note: See Release Notes for more details. |
v1.1.76.0 |
Release Notes & User Guide (included in archive) Fixes:
Note: See Release Notes for more details. |
v1.0.444.0 |
Release Notes & User Guide (included in archive) Fixes:
Known Issues:
|
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:
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.
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.