One way to back up and recover databases natively with Pure Storage® FlashArray™ is by using crash-consistent snapshots. While this is an efficient method for recovering an entire volume of data, Pure’s 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.
Microsoft SQL Server Always On Availability Groups
SQL Server uses AGs to replicate databases to a single instance or multiple instances of SQL Server:
- A clustered instance utilizes Windows Server Failover Clustering (WSFC) as a failover clustering instance (FCI)
- A single standalone instance achieves high availability and provides disaster recovery
Depending on how you’ve configured your environment, the failover process can be manual or automated. Data movement can be asynchronous or synchronous. Asynchronous works best as a disaster-recovery solution while synchronous is best suited for high availability. (Read more about AGs.)
Figure 1: Using SQL Server availability groups with Pure Storage.
Tools for Availability Group Recovery
Why would you need a recovery tool like the Pure Storage SSMS extension to recover a database that’s already part of a native highly available solution? There are a few scenarios in which you might benefit from the added flexibility and resilience of storing SQL Server data on a Pure FlashArray device and using automation to perform the needed functions, including:
- Refreshing a test/dev database using a crash-consistent snapshot
- Creating a new test/dev database with an application-consistent snapshot of production
- Refreshing a test/dev database with a SQL Server backup of production
- Restoring a production replica from an AG backup snapshot
Microsoft SQL Server Always On AG provides many ways to create backups of the primary AG database, including on the primary or secondary databases or another SQL Server. With the SSMS extension, you can host the backup snapshots on the array and restore them much faster than if you were using traditional SQL Server backups. SSMS extension also utilizes a third-party PowerShell SQL Server module called DBATools. This widely used, universal PowerShell module for administration and management extends the capability of the Microsoft native SQL Server PowerShell module.
Next Steps
Dig deeper in an article on Pure’s Microsoft Platform Guide. In it, we examine a solution using a FlashArray native crash-consistent snapshot, as well as an application-consistent snapshot that utilizes the SSMS extension, Pure Storage Backup SDK, and the DBATools module. Explore the scenarios to see how simple and efficient they can be when you automate your AG recovery with Pure Storage data-protection solutions.
