Last month we released the Pure Storage® FlashArray™ Management Extension for Microsoft SQL Server Management Studio, also known as the FA SSMS Extension, which enables Volume Shadow Copy Service (VSS) application consistent snapshots of SQL databases. You have probably dealt with the differences between crash and application consistent snapshots for use cases such as data protection and database cloning. Now you can speed and simplify those tasks with the new extension.

You can install the extension once on your admin client that has version 18.x of Microsoft’s SQL Server Management Studio installed. When VSS actions such as Backup, Mount, Unmount, and Restore are performed, the extension automatically checks component versions and pushes any missing or out of date components to the target SQL Servers. 

Components

The VSS components include the Requestor, Writer, and Provider. The Writer is provided by the application, such as SQL Server, Exchange Server, and Active Directory (NTDS). The provider, in this case the Pure Storage VSS Hardware Provider, acts as a proxy between the VSS service and the Pure FlashArray. The Requestor, or backup initiator, is usually the backup application.

The FA SSMS Extension is composed of three parts; PureSQLSnap the VSS Requestor, PureProvider the VSS Hardware Provider, and PureStorageBackupSDK which is our PowerShell Module that enables automation.

Automation

Today I want to focus on how to leverage the FA SSMS Extension and our PowerShell module to enable the automation of the refresh of a test and dev environment with a recent snapshot of production. One of the ways to get the automation syntax, is by clicking the ‘View PowerShell Script’ button on any of the Pure Storage windows in SSMS.

Simplify SQL server data protection

Another way is to use the help that is built-in to PowerShell. If you are unsure of the available commands in the PureStorageBackupSDK, query them with: 

Get-Command -Module PureStorageBackupSDK

The help for each function, or cmdlet, is retrievable with Get-Help. This is the command to see the full help for Invoke-PFABackupJob.

Get-Help Invoke-PfaBackupJob -Full

The automation scenario involves a production database that must be refreshed in a test and dev (testdev) environment at an automated interval. The following are the list of steps involved to do this in under a minute irrespective of the size of the database.

  • Create a new VSS snapshot of the production database.
  • Offline the database in testdev using TSQL and the SQLServer PowerShell module.
  • Dismount the older snapshot of the production database on the testdev server.
  • Mount the new VSS snapshot of the production database on the testdev server.
  • Mount the database in testdev using TSQL and the SQLServer PowerShell module.

Here is an example script that does just that:

#create new production snapshot
Invoke-PfaBackupJob -ConfigName "dbprod"

#offline testdev db
Invoke-Sqlcmd -inputfile c:\ps\dismount.sql -QueryTimeout 3600
-ServerInstance 'ch9-robert-02'

#dismount snapshot in testdev
$DismountJob = Get-PfaBackupHistory | Where-Object component -eq dbprod | Where-Object MountDrive -Match "P:"
Dismount-PFADrive -HistoryId $DismountJob.HistoryId

#mount newest snapshot
$BackupJob = Get-PfaBackupHistory | Where-Object component -eq dbprod | Sort-Object HistoryId -Descending
Mount-PfaBackupJob -HistoryId $BackupJob[0].HistoryId -DriveLetter P:
-MountComputer ch9-robert-02

#mount database in testdev
Invoke-Sqlcmd -inputfile c:\ps\mount.sql -QueryTimeout 3600
-ServerInstance 'ch9-robert-02'

Keep in mind that the above example uses a very elementary sorting of the backup history, simply finding the most recent snapshot. If other snapshots are being taken on the Pure Volumes, more sorting could be required. Unlike example scripts involving crash consistent snapshots, the target disk does not need to be taken offline, since that is taken care of in the unmount process. The mount process takes care of a few things that would have to be performed, if for instance, the VSS snapshot is used as a source to overwrite an existing Pure Volume outside of the PureStorageBackupSDK.

  • Online the Disk.
  • Remove the shadowcopy, readonly, and hidden volume flags.

Pure is focused on simplifying storage management for our IT customers as your technology environments and businesses grow ever-more complex. This new extension is another step in that direction, helping you do more with less and free-up time for your most critical projects and operations. 

Learn more about how Pure helps customers simplify, automate and scale Microsoft SQL Server. 

For more technical details go to the Pure Storage Microsoft Platform Guide

0 Responses to Simplify SQL Server Data Protection and Cloning with FlashArray SSMS Extension

Leave a Reply

Your email address will not be published. Required fields are marked *