Using FlashArray Snapshots for SQL Server

Intelligent data management is at the core of digital transformation and modern infrastructure. According to the ESG report, The Evolution of Intelligent Data Management, “More than three-quarters of organizations are satisfied to some extent with their secondary data reuse strategies.” The report explains eloquently the data gravity issue, how it can be leveraged, and how companies can benefit. However, it doesn’t delve into the challenges faced by this spin-off benefit to data growth.

Data Growth

Data is growing and is expected to continue to grow. Data volumes grew on average at a rate of 27% in 2019 and 35% in 2021. As data continues to grow, there will be challenges in moving it in a timely way beyond backup use cases.

Backups are done at the software layer. As processes move closer and closer to the data, the processes get faster. A faster way to move data is a snapshot, which happens at the storage layer.

Snapshots

A snapshot is a point-in-time image of a volume or protection group, which is a collection of volumes. SQL Server stores data in files on disks, and you can combine snapshots and SQL Server to enable specific functionality when it comes to protecting, replicating, and making data more accessible to developers and other systems in your environment.

Pure Storage® FlashArray™ supports two different types of snapshots: crash-consistent and application-consistent snapshots. Let’s take a closer look at what types of snapshots are available for SQL Server data stored on volumes in FlashArray.

Crash-consistent Snapshots

A crash-consistent snapshot is a point-in-time image of a volume or protection group. No additional software, other than the Purity software that comes with FlashArray, is needed to use crash-consistent snapshots. Crash-consistent snapshots have zero impact on performance and do not require I/O quiescing of a database, which can lead to small periods of frozen I/Os and require user transactions to wait until the snapshot is complete. A crash-consistent snapshot works with SQL Server’s write-ahead logging protocol to provide a recoverable database.

There is an Achilles’ heel to using crash-consistent snapshots, though: They don’t support point-in-time recovery. The granularity of the protection provided is only the snapshot. If you require transaction-level, point-in-time restore of a database, you’ll want to use application-consistent snapshots.

Application-consistent Snapshots

The SSMS Extension for FlashArray implements application-consistent snapshots, which integrate with the volume shadow copy framework on Windows. This gives you the ability to take an application-consistent snapshot of a database and bring that database back online immediately or in a restoring mode, which then can be combined with SQL Server native backups, like differential or transaction log backups, to restore a database to an exact point in time. Since the SSMS Extension is built using the VSS framework, there’s a small I/O freeze while the snapshot is taken.

Snapshots are different from backups, but each has its place in your tool kit when the need to make copies and move data arises.

Traditional restores are expensive for a company. They consume compute resources, network, disk I/O, and, most importantly, time. If you’re using traditional database restores as a primary recovery technique for your databases or to get database data between systems, there’s a better way.

Anytime you find yourself having to move data either via application-level replication or using backup and restore automation, you can leverage snapshots. When using snapshots, you can clone the volumes supporting the databases on a SQL Server instance, clone those volumes, and make those clones available to another SQL Server instance. Since this is an array-based operation, it can significantly reduce compute, networking and storage overhead in your environment, and, most importantly, make that data available to the person or application that needs access to the data nearly instantaneously.

Since FlashArray is a data-reducing array, these snapshots and clones don’t take any additional space until data is changed. Only the changed blocks consume space. When using clones, they won’t impact the performance of your source volumes.

Common Use Cases for Using FlashArray Snapshots for SQL Server

Here are the most common use cases for snapshots for SQL Server on FlashArray:

• Instant data and ransomware protection: With snapshots, you can revert a database back to a snapshot nearly instantaneously and use it to recover a database after a user error or even a ransomware attack. An interesting twist to this technique is that it’s not likely that you’ll need to restore an entire database to recover. It’s more likely that you’ll need to restore a subset of data back into production, perhaps due to a bad update to an application or a deletion where a clause wasn’t executed and is causing issues in a subset of tables. With snapshots, you can clone a previous snap of the volumes supporting your database and present that back to the same instance and nearly immediately get access to the data from the point of the snapshot. This is much better than the alternative of having to perform a whole database restore, which takes time and additional space for the restored database just to get to the subset of data you need.
• Dev/test refreshes in seconds: Do you find yourself taking backups and restoring them to other SQL instances in your environment? With snapshots, you can do this nearly instantaneously by taking a snapshot of the volumes supporting your source databases and then cloning those volumes to another instance of SQL Server in your environment. This scenario is extremely handy for developers who need quick access to data for testing and development. The data in these snapshots and clones is data reduced, so only the change blocks are needed. Your developers and testers can have access to many variations of your databases without consuming the full capacity needed for each copy.
• In-place application and database upgrades: Have you ever had to fill out change management requests for an application upgrade and put in a recovery time that’s multiple hours? What if it were just a few minutes instead? Using snapshots, you can revert a SQL Server instance, several SQL Server instances, or even the application servers supporting your environment back to the state prior to an upgrade nearly instantaneously. This reduces risk and recovery time in the event of a failed application or database upgrade and brings your systems back online for your users faster.
• Intra-instance ETL: Do you have large and complicated ETL jobs that pull rows from production to get data into your data warehouse? This process can compete for resources on your production systems. Are you using complicated replication techniques to offload this process or other reporting processes to other SQL Server instances? Does that make your systems more complex and consume more resources in your environment? If so, you can use snapshots to help. You can snapshot production and then clone the databases to your data warehouse instance. Once attached, you can then move the rows from the source databases into your data warehouse. This will relieve pressure off the buffer pool, CPU, I/O subsystem, and the network of your production database so it can do production transactions. And all of this is data reduced, so you’re not consuming additional space in your array.
• Offloading database maintenance: Do you run CHECKDB in production? Hopefully you do. But maybe you can’t because the performance impact of CHECKDB is too high and slows down user workload. This is common in 24×7 environments like hospitals and manufacturing facilities. Using snapshots, you can clone your production database to another instance and run CHECKDB there, relieving the pressure from production. If you find a corruption or integrity issue, you’ll need to run CHECKDB on production to see if you have the same issue there. And, if you do, you can test out how to fix your production database on the clone so you can come up with a fix in a non-production environment and have confidence when you take that fix to production later.
• Combining snapshots with replication: Snapshots aren’t backups, but they can provide a very effective way to recover a database or collection of databases very quickly. You can combine snapshots with FlashArray asynchronous replication of snapshots to replicate data-reduced snapshot data to another FlashArray device or even to the cloud using Pure Cloud Block Store™, AWS S3, or Azure Blob Storage. This can be used as a foundation in a recovery strategy in addition to all of the interesting data access scenarios described.

Snapshot Management Tools

When it comes to managing snapshots, FlashArray supports several tools to create and manage snapshots and clones. You can use PowerShell, Python, the Purity Web GUI, and other tools to coordinate crash-consistent snapshots. If you need point-in-time recovery for your database, you can use the SSMS Extension.

To schedule and coordinate snapshots in your environment, you can build integrations with many different orchestrators. ServiceNow and Jenkins are commonly used. In SQL Server environments, you can schedule PowerShell scripts to execute using the SQL Server Agent.

In this post, I introduced you to snapshots and discussed some of the most common use cases for them, as well as some of the tools used to create and manage snapshots and clones.

For more information on how FlashArray volume snapshots can be used with database management systems, read the technical reference guide, “Using FlashArray Volume Snapshots with Databases.” If you want to try FlashArray for yourself, take a free test drive.

Pure Storage Virtual Lab

Sign up to experience the future of data storage with Pure Storage FlashArray™.

Take the Test Drive