Maintenance and Monitoring - Microsoft SQL Server 2008 R2 Administration - page 351

How to do it...
The following process is essential in configuring the storage architecture for the quick recovery
of data. To begin with:
1. Test the disks I/O and capacity growth for a specified configuration by using SQLIO.
exe tool that is available from the Microsoft download center: http://www.
microsoft.com/downloads/details.aspx?familyid=9a8b005b-84e4-
4f24-8d65-cb53442d9e19&displaylang=en.
2. The disks that are presented to the SQL Server, referred to as LUNs, must be
separated for each user database data and logfiles in addition to the tempdb system
database files.
3. All the presented LUNs must be provisioned to the Provisioning tools as per the
Vendor's software. The LUNs are to be divided as four redundant paths for the SQL
operating system and Server 2008 binaries, user database data file, transaction
logfile, and tempdb files.
4. As per the initial configuration, the two dual port host bus adapters (HBAs) must be
installed for HA purpose.
5. All the LUNs are masked and zone to all four HBAs on each server as dedicated fiber
channel ports on the storage platform.
6. For storage configuration, ensure that volume alignment (called sector alignment)
is performed on the file system (NTFS) whenever a volume is created on a
RAID device.
7. When a disk partition is formatted that will be used for SQL Server data files, it is
mandatory to use a 64 KB allocation unit size for data, logs, and tempdb files.
8. When configuring HBAs on the host, ensure that the Queue Depth is set to an optimal
value (depending upon the individual hardware vendor). For better scalability and
performance, set the value to 64 with a discussion with the storage administrator.
9. The LUNs are catered to place database data and logfiles as separate provisioning
pools in addition to tempdb files.
10. To achieve the best availability feature, it is ideal to choose RAID 1+0 for RAID groups
to be presented for database and tempdb files.
11. The required disk capacity growth is calculated as 30 percent of the existing
size to avoid any overhead of increase in file size during the database
transactions operation.
12. Always have provisions for additional capacity to keep the backups for system and
user databases, in line with the Service Level Agreement (SLA) as online availability
for quicker data restores.
13. All the hardware must be hosted as 'hot-swappable' without causing any downtime
to the SQL Server, where Windows Server 2008 (higher) and SQL Server Enterprise
Editions support such a feature.