Database Reference
In-Depth Information
Getting ready
The following are the referred best practices to configure the storage for the database server
to reduce any fragmentation.
F
SQL Server 2008 R2 and Windows Server 2008 R2
F
Using 64-bit operating system and software servers
F
Using a dedicated server for SQL Server and application database
F
The application requirement of processing large volumes of transactions and
cater reporting needs to data warehouse systems requires substantial I/O
system resources
How to do it...
The process defined here is essential in configuring and managing the storage system for
optimized defragmentation processes.
We will go through a two-fold step here, initially looking at the storage configuration practices
and then closing out the recipe by using the available tools.
1.
For the high-end mission-critical application, it is always essential to use a SAN and
RAID10 wherever possible.
In recent times, the hardware vendor's implementation of SAN as
RAID-DP (RAID Double Parity) or using a RAID6 setup platform will be a
better choice based on application-specific and budgetary flexibility.
2. Perform disk volume alignment on NTFS volume by using the
diskpart.exe
program that is included as part of the Windows operating system.
3. In general, the hardware vendor will provide offset, otherwise it can be obtained
through testing. A 64 KB offset (128 sectors) is a common value on many
storage arrays.
4. It is ideal to set the offset value as 1024 KB (2048 sectors) for high-end application
performance on the storage subsystem.
5. Set the NTFS allocation unit size as multiples of 64 KB (512 KB is minimum)
allocation unit for the disks where data, log, and tempdb files will be installed.
6. When configuring Host Bus Adapter (HBA), choose the value as 16; that is ideal as a
start-up value to cater for I/O intensive operations from SQL Server.
7.
When designing the number LUN (Logical Unit Number), ensure to provide a
mirrored drive in the event of disk failure. Keep the LUN volume structure and
number of files as small as possible.
Search WWH ::
Custom Search