Home > Storage > Unity XT > Virtualization, Cloud & Applications > Dell Unity XT: Microsoft SQL Server Best Practices > Block Storage Configuration
Dell Unity XT storage is virtualized to take advantage of all the drives in the storage pool. Characteristics such as RAID level and storage tier can have a big impact on performance and are configured at the volume level. Dell Unity XT snapshots are also configured at the volume level. There are many types of files that are part of a SQL Server instance. Those types of data often have different performance and snapshot requirements. For performance-sensitive applications, Dell Technologies recommends creating at least five volumes for an instance of SQL Server as shown in the following table.
File type | Number of volumes | Typical performance requirements | Typical snapshot requirements |
User database data | At least 1 per instance | Lower performance may be acceptable | Frequent snapshots, same consistency group as log volume |
User database transaction log | At least 1 per instance | High performance required | Frequent snapshots, same consistency group as data volumes |
Data root directory (includes system DBs) | 1 per instance | Lower performance may be acceptable | Infrequent snapshots, independent schedule |
Tempdb data and transaction log | 1 per instance | High performance may be required | No snapshots |
Native SQL Server backup | 1 per instance | Lower performance may be acceptable | Snapshots optional, independent schedule |
Memory-optimized filegroup (if used) | At least 1 per instance | High performance required | Frequent snapshots, same schedule as log volume |
Place databases that span multiple LUNs within a consistency group. This helps ensure that other features such as snapshots, replication, and thin clones will be configured properly and maintain data consistency.
When one group of databases requires high performance and another group does not, consider creating a set of volumes for each group of databases, placing each set in its own consistency group. Even if there is only one tier of storage, this strategy will make it easier to adjust the storage configuration in the future.
Having multiple volumes has an additional benefit in dual-controller systems. All I/O requests for a given volume are processed by the controller that owns the volume. While a volume can be owned by either controller, a volume is owned by one controller at a time. Having many volumes makes it easier to distribute the I/O load evenly across both controllers. Databases that have high-performance requirements can be spread across two or more datafiles on separate volumes to leverage resources on both controllers. In cases where maximum performance is required from the array, as many as 16 volumes may be required.
For ultimate flexibility, create a volume for each user database file. This allows storage and snapshot configuration for each individual database. With thin provisioning, there is no space penalty for creating a lot of volumes. However, many 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.
Use a 64 KB allocation unit size when formatting volumes that will contain database files (transaction log and data) or database backups.
Set the MPIO policy to round robin for all database volumes. Round robin is the default for Windows 2008 and newer. It allows all paths to be used, enabling higher throughput between the server and the array. This setting works best for most environments as it is easy to manage and performs well.
For database servers with many I/O ports, the overhead of the round robin MPIO policy can reduce the maximum throughput to the storage array. To maximize throughput, create a volume for each port on the server and use the failover only MPIO policy to define a single, unique, active path for each volume. Define all other paths as standby. By using a datafile of the same size on each volume, the data will be evenly distributed across the volumes. This strategy is more complex to set up and maintain. Use it only in environments that require maximum throughput.