Using FlashArray Snapshots with Microsoft SQL Server Technology Overview

Microsoft Platform Guide
Audience
Public
Source Type
Documentation

The solution outlined in this white paper encompasses the following technology components:

  • SQL Server instances running on Windows Server or a supported Linux operating system.

  • Everpure® FlashArray as block storage for SQL Server user databases

The following section provides details of each technology used in this solution.

Microsoft SQL Server

SQL Server is a relational database management system that stores and retrieves data as requested by other software applications. Developed by Microsoft, SQL Server stores, retrieves, and manages large volumes of data for enterprise applications. SQL Server supports a wide range of workloads, from online transactional processing to complex online analytical processing. Data resiliency features, such as transparent data encryption and role-based security, help organizations keep their data safe both at rest and in transit. As a Microsoft product, SQL Server offers deep integration with other Microsoft enterprise-grade technologies, such as Active Directory and Azure

Everpure FlashArray

Everpure FlashArray is a unified block- and file-storage solution designed to deliver a seamless and reliable user experience in SQL Server environments. Driven by software-defined technology, FlashArray incorporates high data reduction that does not compromise performance. FlashArray is ideal for organizations seeking to improve storage system sustainability.

  • FlashArray//E™: Economical-at-scale storage for workloads that aren't latency-sensitive

  • FlashArray//C™: Low-latency storage for capacity-oriented workloads

  • FlashArray//X™: High-performance, high-capacity storage that is ideal for performance-oriented workloads

  • FlashArray//XL™: High-performance storage at scale that helps reduce the number of arrays needed to run large applications

FlashArray Volumes, Snapshots, and Protection Groups

Snapshots are point-in-time images of a volume. A volume itself is a logical storage unit within a FlashArray. Both snapshots and volumes use metadata pointers to reference data blocks on the FlashArray. A protection group is designed to manage and automate the protection of multiple related storage objects, such as volumes. Snapshots can use protection groups to act on multiple volumes concurrently.

Figure 1. FlashArray volumes and snapshots use metadata pointers to reference data blocks on the FlashArray.

Volumes

A FlashArray volume is a block storage object that can be presented to a host, where it appears as a block device. Volumes store user data on a file system managed by the host's operating system. When a FlashArray volume is exported to a host,it allows applications like SQL Server to read and write data to this storage.

In FlashArray, the internal mapping of data for volumes differs from the way it is presented to the user. Rather than directly containing the user data, FlashArray volumes maintain metadata pointers that map to data blocks stored in the array's media pool. When a host writes data to a FlashArray volume, the data itself is stored in the media pool, and the volume's metadata is updated to reflect the location of each new data block. This separation means that the user sees a traditional block storage volume, but the actual storage is managed through metadata pointers, which optimizes storage efficiency and enables features like instant snapshots.

This unique architecture allows FlashArray to create snapshots that reference existing data blocks without duplicating them, making snapshots highly space-efficient.

Snapshots

Volume snapshots provide a fast, secure, and less-disruptive means to capture data at a particular point in time. When a volume snapshot is taken, the FlashArray makes a copy of a volume's metadata such that both the volume and snapshot are now pointing to the same data blocks, without copying the individual data blocks. The data blocks that are referenced by the volume and snapshot are read-only and cannot be changed, which makes the snapshot immutable.

If a SQL Server host requests a volume write that would change any data block that is referenced by a snapshot, that request is redirected and written as a new data block. The FlashArray updates the volume metadata to point to the new data block, while the previous data block is preserved and referenced by the snapshot.

FlashArray Volume Snapshots Advantages and Benefits

The ability to preserve data blocks referenced by a snapshot ensures that snapshots:

  • Maintain their data integrity and immutability because referenced data blocks cannot be overwritten

  • Are space-efficient because the FlashArray is maintaining metadata pointers to the data, not actual copies of the data

  • Can be instantly provisioned by the FlashArray into new cloned volumes

  • Are easily replicable by matching target blocks already present with those to be transferred and only transmitting the differences, which helps optimize bandwidth usage and helps significantly reduce replication time

These advantages are not just for data protection and recovery, but can aid in application development, testing, and replication. As a point-in-time reference to the volume data as it was when the snapshot occurred, snapshots have several practical uses in a SQL Server environment, such as:

  • Fast recovery of data in case of a ransomware attack: If a volume's data becomes encrypted due to a ransomware attack, the volume can be instantly rolled back to a previous state using a snapshot. This helps prevent data loss and extended downtime.

  • Development or testing of applications where frequent rollback occurs: Snapshots taken from a production volume can be attached to dev and test SQL Server hosts instead of using full backups to restore databases. These snapshots can be quickly refreshed in seconds, as opposed to hours, days, or longer with traditional backups.

  • Backup of the state of a database before a major upgrade: Database and storage administrators can take a snapshot of the state of a volume where a user database data is stored and can instantly roll back a volume to the snapshot if problems occur during an update.

  • Offload CHECKDB operations without impacting performance: Database maintenance tasks, such as CHECKDB, can be offloaded from a production SQL Server host by mounting a snapshot that contains the database data to another SQL Server host. The CHECKDB process can then be run on the snapshot on the other host, ensuring the integrity of the database without impacting the performance of the production host.

  • Efficient intra-instance extract, transform, and load and data movement: Snapshots allow data to be duplicated within the instance for extract, transform, and load processes. By using snapshots for extract, transform, and load tasks, data transformations can occur independently of production databases, which reduces the risk of interference with production workloads.

Protection Groups

While a volume is a logical storage unit within a FlashArray, a protection group is designed to manage and automate the protection of multiple related storage objects, such as volumes and hosts.

Protection groups offer several advantages, including snapshot management and consistency. For example, protection groups provide for the scheduling of snapshots. When a snapshot is taken, all volumes within the protection group are snapped together to create a point-in-time consistency group across all included volumes.
Figure 2. High-level overview of FlashArray protection groups, volumes, and snapshots.
Database and storage administrators can use protection groups and snapshots to create consolidated recovery points for SQL Server databases. If a SQL Server host uses multiple volumes to store user database data, a storage administrator can create a protection group that includes all of the user database volumes. Regular snapshots can be scheduled for the protection group, which creates instant recovery points for the user database data. If problems with a database occur, the storage administrator and database administrator can roll a volume back to a previous snapshot instantly, which reduces downtime when compared with restoring a full database backup.
Figure 3. SQL Server database recovery via volume snapshots.