FlashArray supports crash-consistent and application-consistent snapshots for data protection and other data-management requirements with SQL Server. These types of snapshots differ from traditional SQL Server backups in significant ways, and the use cases will vary depending on how you are using them.
| When to Use Application-consistent Snapshots | When to Use Crash-consistent Snapshots |
|---|---|
|
Recovering to a specific point-in-time that is facilitated by restoring log backups into a SQL Server database in restoring mode |
|
Traditional Backups
Traditional backups are a key component of the disaster recovery and data-protection management process and have several key benefits, as the backup:
-
Contains a full copy of all database data
-
Contains all active transaction log data up to the point of the backup
-
Can be written out to a variety of archival media, such as tape, disk, and cloud storage
While these are important benefits, traditional backups also have certain drawbacks:
-
Backing up or restoring large, multi-terabyte databases can take a considerable amount of time.
-
Because the backup is a full copy of the database and active portion of the transaction log, it can require a large amount of storage space.
-
As databases are constantly growing, so are the backups and the time required to take or restore those backups.
-
Backups can consume CPU, memory, and network bandwidth resources on the host, which takes those resources away from application requests.
In contrast, FlashArray snapshots are storage-efficient, portable, and can be taken as near as instantaneous with no impact to the SQL Server host. Together, snapshots and traditional SQL Server backups can provide organizations with a comprehensive data-protection and data-portability strategy.
Crash-consistent Snapshots
Crash-consistent FlashArray snapshots are a point-in-time view of the database data at the time it was taken; therefore, the recovery point is the point at which the snapshot occurred. Crash-consistent snapshots can be used in any scenario for database recovery, assuming all database files are on volumes in a protection group that uses snapshots. Note that the term "crash-consistent" refers to a restore point being captured at the instant leading up to a server crashing or being powered off. In the case of FlashArray snapshots, this type of snapshot can be taken or scheduled as often as five minutes or taken manually at any time.
Crash-consistent snapshots are reliable because FlashArray avoids caching and does not reorder I/O operations. When a crash-consistent snapshot is taken, FlashArray respects the requirements of the SQL Server write-ahead logging protocol, ensuring the database remains recoverable. There is no impact to the SQL Server host, and the snapshot does not require that the database be quiesced, thereby avoiding delays that can occur when a database is quiesced. For more information about the SQL Server write-ahead logging protocol, see the section on Description of logging and data storage algorithms that extend data reliability in SQL Server.
Crash-consistent snapshots can provide reliable, rapid recovery of databases that only require a recovery point of when the snapshot is taken. For transactional-level recovery, application-consistent snapshots should be used. Other examples of the type of restore or recovery for crash-consistent snapshots include handling data loss due to user error, such as an accidental table deletion.
Application-consistent Snapshots
Application snapshots differ from crash-consistent snapshots in the following ways:
-
The database is quiesced, which causes a brief pause to database input/output operations.
-
Any application-consistent snapshot can be rolled forward to achieve a more granular point-in-time recovery with SQL transaction log backups, if they are available.
When transaction-level, point-in-time recovery is essential, an application-consistent snapshot can be taken of a database. Combined with SQL Server transaction log backups, the database can be restored to an exact point in time. Application-consistent snapshots ensure point-in-time recovery as long as SQL log backups are implemented as part of the restore.
SQL Server 2022 introduces T-SQL Snapshot Backup, a new feature that integrates with FlashArray and lets database administrators produce application-consistent snapshots transparently without the need of the Volume Shadow Copy Service.
T-SQL Snapshot Backup provides:
-
SQL Server-aware snapshots: Because SQL Server controls the snapshot process, it records what's in the snapshots in its backup history.
-
Database quiescing with no external tools: A single database, group of databases, or an entire SQL Server instance can be quiesced directly using T-SQL, which eliminates the need for external tools while maintaining consistency and recoverability.
-
A snapshot-based point for log restores: Restoring to an exact recovery point can be done without having to read from a full backup. A snapshot can be restored, and then the log backups can be restored to an exact recovery point. This capability dramatically reduces recovery times while enabling exact recovery points.
For an example using T-SQL snapshots, see the Everpure script library: Point In Time Recovery - Using SQL Server 2022's T-SQL Snapshot Backup feature.
Alternatively, database administrators can use the Volume Shadow Copy Service provider for SQL Server 2019 and earlier. More information about the Volume Shadow Copy Service is available at Microsoft Learn.