Storage Systems - SQL Server 2012 Internals and Troubleshooting - page 91

A hardware storage snapshot, shown in Figure 4-10, is a point-in-time data copy. The snapshot dif-
fers from a clone data copy in that it keeps only the original copy of changed data.

NOTE A hardware snapshot differs from a SQL Server database snapshot. The
SQL Server snapshot provides a read only static view of the database based on
a point in time. For more information on SQL Server database snapshots visit:
http://msdn.microsoft.com/en-us/library/ms175158.aspx .

When a change is written to the storage volume, the
array stores the original data and then writes the new
data to disk. All the changes are tracked until the user
requests a point-in-time data copy. The array correlates
all the changed data blocks that now represent the state
of the volume at that moment in time.

Storage Array

The user can continue to create as many snap volumes
as the array supports. Snapshots utilize capacity based
on the rate of data change. It is possible to churn so
much data that the snapshots consume more data than
the actual data volume. Expiring snap volumes can
reduce the amount of space consumed.

4pm Snapshot

Source
Disk

8pm Snapshot

12pm Snapshot

Snapshots do not isolate performance. Any I/O executed
against the snap volume is accessing both the source
volume and any saved changes. If the primary database
server is so busy that you have decided to utilize a
second server that performs reporting functions, a clone
volume may be a better choice than a snapshot.

3am Snapshot

Original
Data

For business continuance and disaster recovery (BCDR)
you could also consider a layered approach. The clone
volume will provide a unique data copy. Keep in mind
that the clone is a mirror of the original data and will
consume the same capacity as the original volume.
Keeping several clone copies can be cost prohibitive.
Snapshots can offer many inexpensive point-in-time copies, but they won't work if the primary data
volume is compromised. You can enable a data clone to protect against a catastrophic data failure.
The snapshots can be taken much more frequently and enable the DBA to roll back to a specii c
point in time (which is extremely useful in case of user error or computer virus).

Snapshot
Change Log

FIGURE 4-10

WARNING A successful recovery requires all user database log and data i les to
be recoverable to the exact point in time.