3.6 Storage Tiering
Non-volatile storage is generally
manufactured to offer either high performance or high capacity.
High-performance disk and flash drives are much more costly than
high-density storage. In an effort to make the most efficient use of
both capacity and performance, SAN arrays allow several types of
storage to be mixed within a given array.
Our database administrator, Bob, is now stinging
after submitting his budget to his manager, Stan. Bob needs to come up
with a plan for reducing his storage requests for the next fiscal year.
He bumps into Eddy, who works for Widget Company’s storage department,
at the water cooler.
Eddy has heard of Bob’s plight and suggests that
Bob place his backup files on less costly high-capacity SATA drives.
This will reduce Bob’s budget request and make Stan happy. Bob will
keep his log and tempdb files on higher-performance SAS RAID 10 volumes
and shift his data files onto less expensive RAID 5 SAS drives.
Because the server is tied to a storage network,
the different tiers of disks don’t even need to exist within the same
array. Bob could place his data, log, and tempdb files on one array and
move his backup data to a completely different storage system. This
approach offers the added benefit of shrinking the possible failure
domain. If the primary array suffers a catastrophic failure, the backup
data still exists on a separate array.
This mixing and matching of storage within a SAN array is called storage tiering. Some storage arrays provide automated storage tiering,
monitoring volumes or pieces of volumes for high performance. When
predetermined performance characteristics are detected, the array will
migrate data onto a higher tier of storage. Using this model, Bob only
needs to put all his data on a storage volume; the array determines
where to place the data and when to move it.
Different storage vendors have implemented
storage tiering in unique ways. One of the most unique features is the
granularity of data that is migrated. Some arrays use a transfer size
of a few kilobytes. Other arrays migrate gigabytes of data. Each system
offers different performance. Be sure to evaluate any potential system
to ensure it meets your needs.
WARNING
Storage
tiering has the effect of changing performance over time. Many
financial systems always require consistent performance. If your
specific system requires repeatable performance then storage tiering
should not be used.
3.7 Data Replication
SAN arrays offer both internal and
external storage data replication. Internal replication consists of
data snapshots and clones. Some storage arrays offer inner array data
migration features. Both a snapshot (also called a snap) and a clone
offer a point-in-time data copy. This data copy can be used for backup
or reporting.
Both snapshots and clones need to be created in
sync with the database log and data files. In order to maintain SQL
data integrity, both the log file and data files need to be copied at
exactly the same time. If the log and data files are not in sync, the
database can be rendered unrecoverabl
Prior to creating the point-in-time data copy,
you need to decide on the type of SQL Server recovery that is needed.
You can create application-consistent copies using the SQL Server
Virtual Backup Device Interface (VDI). VDI is an application
programming interface specification that coordinates the freezing of
new write operations, the flushing of dirty memory buffer pages to disk
(thus ensuring that the log and data base files are consistent), and
the fracturing of the clone or snap volume. Fracturing is the process
of stopping the cloning operation and making the snapshot or clone
volume ready for use. Once the fracture is complete, the database
resumes normal write operations. Reads are not affected.
WARNING
VDI imposes
a ten-second timer for the completion of the freeze and fracture
operation. When implementing VDI on busy SQL Servers, especially those
with large memory, it may be necessary to stop running SQL Server jobs
prior to executing the freeze and fracture operation. If the SQL Server
can’t write all the dirty data pages to disk in ten seconds, the
operation will fail.
Crash-consistent data copies depend on the fact
that SQL Server uses a write-ahead logging model, as described earlier.
New data is first written to the disk or storage volume. As soon as the
write operation is complete, it is acknowledged. Only after the data
has been successfully written and acknowledged will SQL Server write
data to its buffer pool.
If the database is shut down before it has a
chance to flush dirty buffer-page data to disk, the write-ahead logging
feature enables SQL Server to recover data that was written to the log
but not to the database files. This recovery model enables the use of
advanced replication technologies.
Clone data volumes create an exact replica of the source volume as of the point in time when the clone was created (Figure 9).
Because the clone is an exact copy it enables the isolation of I/O
performance. The source volume can continue to operate normally while
the clone can be mounted to a different host. This enables a workload
to be distributed among many machines without affecting the source
volume. Such a scenario is extremely useful to enable high-performance
reporting systems. Keep in mind that the clone volume requires the same
amount of space within the storage array as the original volume
requires.
A hardware storage snapshot, shown in Figure 10,
is a point-in-time data copy. The snapshot differs 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.
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.
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.
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.
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 specific point in time (which is extremely useful in case of
user error or computer virus).
WARNING
A successful recovery requires all user database log and data files to be recoverable to the exact point in time.
