Tuesday, November 30, 2010

Configuring Database Files with RAID systems

RAID stands for Redundant Array of Independent Disks. Even though RAID systems does not included with SQL Server configuration, it greatly affects the SQL Server's performance. RAID can be configured using
hardware or software. But our scope is to identify the best configuration for our needs.

The following are different type of RAID configurations.

RAID 0: (Stripping)


  • In Raid 0 system, data gets split up in blocks that get written across all the drives in the array.
  • RAID 0 has Superior I/O performance.
  • RAID 0 is not fault tolerant.
  • If one disk fails, all the data in RAID 0 arrays are lost.
  • RAID 0 should not be used in mission critical systems.
RAID 1: (Disk Mirroring)


  • RAID 1 provides a redundant copy of the selected disk.
  • RAID 1 improves read performance but degrade write performance.
RAID 5: (Stripping and Parity)


  • RAID 5 stripes the data across the discs and also adds parity information to provide fault tolerance.
  • Parity information is distributed among all disks.
  • If any one of the disk fails, read performance decreases.
  • Requires at least 3 disks to implement.
RAID 10 or RAID 1+0:


  • RAID 10 includes both stripping without parity and mirroring.
  • RAID 10 offers better availability and performance than RAID 5, especially for write-intensive applications.
  • It is best practice to have data files and log files placed in separate disks so that you can improve performance and reduce disk contention.




Questions:

1. You administer a SQL Server 2008 instance. You plan to deploy a new database that has the following capacity requirements:
275 GB for the database data file
50 GB for the transaction log file
The storage array has six 100-GB disk drives available for the database. The disks are attached to a redundant array of independent disks (RAID) controller that supports RAID levels 0, 1, 5, and 10. The
write performance of the transaction log needs to be maximized. The database and transaction log files must be protected in the event of a drive failure. You need to design the storage system. Which storage
configuration should you use?

A. A single RAID 5 volume
B. A single RAID 10 volume
C. A RAID 0 volume and a RAID 5 volume
D. A RAID 1 volume and a RAID 5 volume


2. Your database named DB01 has seven 120 GB hard disk drives. Two of the hard disks are configured as a mirrored volume and contains the operating system and SQL Server 2005 is installation. Another two disks
are configured as a RAID-1 volume and the rest are configured as a RAID-5 volume.

You to need to plan the placement of the database and the transaction logs. You want to implement fault tolerance and best performance for the database.  What should you do?

A. Place the database on the RAID-5 array and the transaction log on the RAID-1 array.
B. Place the database and the transaction log on the RAID-5 array.
C. Place the database on the mirrored volume and the transaction log on the RAID-5 array.
D. Place the database on the RAID-1 array and the transaction log on the RAID-5 array.


3. Which of the following types of RAID should you NOT use as the basis for a volume to host database files if you want to ensure that the volume survives the failure of a hard disk drive?

A. RAID 0
B. RAID 1
C. RAID 5
D. RAID 10


Show Answers:

3 comments:

  1. Very informative article.
    I was able to find the answers for few questions in Microsoft Certification MB3-527 (GP 10.0 Installation & Configuration)

    ReplyDelete
  2. Now I can correlate the Microsoft Dynamics GP Server requirements given below:

    1. Have between 0-20 concurrent users and Process fewer then 1000 transactions per day

    RAID 1 for operating system and applications RAID 5 for SQL database log and data files


    2. Have between 20 and 60 concurrent users and Process between 1000 and 4000 transactions per day

    RAID 1 for operating system and applications (2 disks)
    RAID 1 for SQL database log files (2 disks) RAID 5 (4 disk minimum) or RAID 10 (8 disk minimum) for SQL data files
    RAID 1 for TempDB (2 disks) - optional, but recommended
    RAID 0 for SQL backups (full and log) (2 disks)

    3. Have between 60 and 100 concurrent users and Process more than 4000 transactions per day

    RAID 1 for operating system and applications (2 disks)
    RAID 1 for SQL database log files (4 disks)
    RAID 1 for TempDB (4 disks)
    RAID 0 for SQL backups (full and log) (2 disks)
    RAID 10 for data files (8 disks or more)

    Great post..thanks

    ReplyDelete