How deeply does 100 SQL Server Mistakes and How to Avoid Them cover crash consistent snapshot?

Defines block-level consistency, explains buffer-cache risk, and contrasts VSS-based application consistency.

Crash consistent snapshot

As explained in 100 SQL Server Mistakes and How to Avoid Them

A crash-consistent snapshot captures storage as it exists during or immediately after a crash without ensuring that application I/O has been flushed coherently.

A crash-consistent snapshot is a block-level, point-in-time copy that preserves disk write order but does not flush database I/O coherently. It may leave SQL Server inconsistent when transactions are in flight. section 12.3 section 12.12

Mistake: treating a VM snapshot as a reliable database recovery strategy. The snapshot can appear valid until restoration, when buffered data and already-written data no longer describe one coherent database state. section 12.3

Bad-change rollback

A change plan restores a database-hosting VM after a failed deployment. If its storage protection is crash-consistent, the rollback may restore a corrupted database rather than a usable recovery point. section 12.3

Use these checks before relying on storage snapshots for SQL Server:

Confirm that the provider supports application-consistent snapshots, not only crash-consistent snapshots. section 9.7
Verify that Volume Shadow Copy Service (VSS) flushes pending I/O before the snapshot. section 9.7 section 12.3
Keep validated database backups in the recovery strategy; use snapshots as a rapid-recovery complement rather than a replacement. section 12.12

How 100 SQL Server Mistakes and How to Avoid Them covers it

Cover of 100 SQL Server Mistakes and How to Avoid Them

liveBook manning.com

Read 100 SQL Server Mistakes and How to Avoid Them on liveBook Get the book at manning.com

Related concepts

snapshot isolationkind of · Snapshot isolation is a transaction-isolation method in which each transaction reads a consistent versioned snapshot of committed data while concurrent writes are isolated from that view.
application consistent snapshotcontrasts with · An application-consistent snapshot is a storage snapshot coordinated with an application so its persisted data is in a recoverable state.
data corruptionproduces · Data corruption is the unintended alteration, loss, or damage of data that makes it inaccurate, invalid, inconsistent, or unusable.

This explainer was generated by Manning's knowledge graph from the text of 100 SQL Server Mistakes and How to Avoid Them. The section links point to the parts of the book it draws on.