Database Copies for Development, Testing, or Analysis Using FlashArray Snapshots

Overview

FlashArray snapshots can be used for copying databases to other devices. These cloned databases are useful for conducting dev/test outside of the production environment. For example, a dev/test database can be used to test whether SQL Server backups meet data-restore requirements, such as length of downtime and percentage of data loss, without impacting the performance and integrity of production systems.

Solution Benefits

Databases copied using FlashArray snapshots offer multiple advantages for SQL Server development, testing, and analysis. They can be used in dev/test environments to speed up dev/test refresh cycles, streamline in-place upgrades, and offload dev/test operations from production systems.

Developers shorten dev/test refresh cycles by enabling access to multiple refresh variations on copied volumes rather than accessing full databases in production. Database copies can also deliver nearly instantaneous reversion to a pre-upgrade state, which can significantly reduce recovery time and risk in case of upgrade failures. CHECKDB database maintenance tasks can also be offloaded to avoid impacting production performance. Extract, transform, and load processes can be offloaded to database copies in dev/test environments, reducing resource-intensive production loads such as buffer pool, CPU, input/output subsystem, and networking operations.

Technical Solution

FlashArray can generate two kinds of snapshots for database copies: crash-consistent and application-consistent. Crash-consistent snapshots preserve SQL Server data and log files but not uncommitted transactional data. When a crash-consistent snapshot is taken, the FlashArray works with SQL Server's write-ahead logging protocol to ensure the database is recoverable. Application-consistent snapshots capture transactional data, enabling point-in-time recovery, provided the database is paused (input/output quiescing) before taking the snapshots. Crash-consistent volume snapshots are appropriate for use when point-in-time recovery is not required.

FlashArray volume snapshots can be created, configured, and deleted by using:

Code examples for snapshots can be found on the Everpure SQL Server scripts GitHub page, which can be implemented using the Everpure FlashArray PowerShell SDK2 module.

The figure below illustrates a SQL Server database copy used for development or testing.