Announcing the Pure Storage® FlashArray Management Extension for Microsoft® SQL Server® Management Studio

Today marks a significant milestone for Pure Storage as we announce the release of a new extension for Microsoft SQL Server Management Studio (SSMS). This extension provides the capability to create application consistent snapshots of databases directly from SMSS to simplify backup and restore operations for the Pure Storage FlashArray. 

Access the new extension from the main menu in the SSMS GUI or activate it by selecting a specific database and right-clicking as shown below.

Before delivering further details around the SSMS extension, let’s take a step back and review a brief summary of the journey to where we are today with this announcement. 

• In July 2014, Pure released version 1.0 of the Volume Shadow Copy Service (VSS) Hardware Provider and delivered incremental improvements and bug fixes up to the latest 1.6 release in December 2017. Read the full details of our original release on this blog. 
• Prior to the availability of the extension, we offered diskshadow, already integrated into Windows Server. Using diskshadow however was limited and required scripting to automate its effectiveness. 
• Then, we embarked on a plan to create a product that provides native integration with SQL Server Management Studio, the tool that Database Administrators most frequently use to manage SQL Server databases. 

With the release of this SSMS extension, we have now completed the workflow with our own Requester, Hardware Provider, and automation framework. The diagram below illustrates the various steps of how the extension functions. 

The extensions architecture is comprised of several components:

• SQL Server Management Studio integration that provides both top level menu driven actions and database context menu support.
• 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.

The extension supports the following:

• 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:
  • Importing a backup (snapshot), then restoring a database to the original location. This operation will perform an overwrite of the original database in place.
  • Importing a backup (snapshot), then restoring a database to a different drive letter, using the Mount option which allows for a new drive to be selected for restore.
  • Importing a backup (snapshot), then restoring a database in No-Recovery Mode for applying transaction logs.
• Support for scheduling using SQL Server Agents & Jobs with the CmdExec proxy option.
• Support for automating backups and restores using the Pure Storage Backup SDK. As part of creating backup configurations or restores in SSMS a PowerShell Script button is provided for cut & paste of the cmdlet details.
  • Example:

Add-PfaBackupJob -ConfigName "AdventureWorks" -Component "AdventureWorks2017" -MetadataDir "C:\Temp" -ComputerName "localhost" -FAName "sn1-m20-c09-29" -CopyOnly

In summary, the new Pure Storage FlashArray Management Extension for Microsoft SQL Server Management Studio offers a familiar and powerful user experience for database administrator, including PowerShell scripting, and a variety of backup & restore operations. 

Thanks — Barkz