White Papers

Storage setup and configuration
14 Dell EMC SC Series: Microsoft SQL Server Best Practices | CML1057
requirements can be spread across two or more data files on separate volumes to leverage resources on both
controllers.
4.2.2 Flexibility and manageability
For ultimate flexibility, create a volume for each user database file. This provides the ability to independently
optimize the storage and snapshot configuration for each individual database. With thin provisioning, there is
no space penalty for creating a lot of volumes. However, a large number of volumes can be difficult to
manage, especially in virtualized environments. It is up to the DBA or storage administrator to find the right
balance between flexibility and maintainability when determining the number of volumes to create. Virtualized
SQL Server environments are a good example of where it may make sense to place multiple file types on a
single volume. Understanding the database I/O patterns is critical to making the best decisions.
4.3 Storage profiles
Storage profiles define the RAID level used to protect data on a volume and the tiers where that data is
stored. The information in the profile is used by Data Progression when moving existing pages, as well as by
new SC Series pages. In most environments, using the default storage profile Recommended (All Tiers)
provides good I/O performance for all types of database volumes. It is strongly recommended to use this
storage profile first and evaluate its suitability before attempting to change the storage profiles.
4.3.1 User database data file considerations
On volumes storing the data files, the Recommended (All Tiers) storage profile keeps the highly active parts
of the database on tier 1 and the less active parts on lower tiers. In most cases, this provides the best
performance. However, there are some environments where the entire dataset is highly active and needs the
performance of tier 1. Before changing the storage profile to force the data volume to live only on tier 1,
consider the impact that the frequency of both index maintenance and snapshots has on Data Progression.
Using the Recommended (All Tiers) storage profile allows inaccessible frozen pages to be moved to tier 3,
freeing up tier 1 resources for active pages.
4.3.2 User database transaction log file considerations
The transaction log requires good performance and should reside on tier 1. This storage component has the
greatest impact on transaction latency. Writes to the transaction log start at the beginning of the file, go to the
end of the file and then start again at the beginning of the file. The Recommended (All Tiers) storage profile
allows inaccessible frozen pages to be moved to tier 3, freeing up tier 1 resources for active pages.
4.3.3 Tempdb database file considerations
Tempdb files can require high performance and generally should reside on tier 1. Some applications use
tempdb heavily and others hardly use it at all. If your applications require high performance for tempdb and
you want it to remain on tier 1 this is accomplished by selecting the High Priority Storage Profile for Standard
storage or the Write Intensive Storage Profile for Flash Optimized storage.
4.3.4 System database file considerations
The system databases (master, model and msdb) generally do not have special performance requirements.
Use the Recommended (All Tiers) storage profile for the system database volumes.