General discussion

  • Creator
  • #2174504

    Disk Partitioning for Database Servers on W2003


    by damon.lane ·

    Hi all

    Just wanted to get some opinions on disk partitioning for a DB server Im implementing

    I have a HP Server with 6 drives and an array card. Im going to setup one array as a mirror for two disks for the OS/Transaction log and another array as a RAID5 set of the other four disks

    Ive read a lot of stuff about splitting your OS and you data accross two arrays etc improves performance rather than having Windows see all the disks as one big array but what Im wondering is are there any best practices with regard to partitioning the RAID5 array for a DB (SQl Server) implemention?

    Our previos standard would have been to create 2 windows partitions within the RAID5 array, 1 for data, 1 for Apps. However this server will only house SQL Server so is there any advantage to multiple partitions for the SQL Server apart from putting transaction.log on a completely different partition

    Many thanks

All Comments

  • Author
    • #3065252

      SQL partitioning

      by bob_stiles ·

      In reply to Disk Partitioning for Database Servers on W2003

      If you can do the following, it would increase perfomance, reliability, and easy disaster recovery.
      1)OS- Win 2003 in your case across 2 drives mirrored
      2)SQL databases stripped with parity RAID 5 across 3 or more depending on space currently needed and future planning.
      3)Transaction logs- mirrored across 2 drives

      This is my current configuration and what a difference it has made with response times. I have given myself room for growth with this type of confiuration, easy recovery, and the performance outstanding. I hope this was helpful.

      • #3056696

        Good strategy

        by doc.pisapati ·

        In reply to SQL partitioning

        This is a good strategy. However, if you want to extend the data to another volume. it is a painful process unless you take advantage of mount points. SQL Server 2005 supports mount points.

        • #3056665

          RAID 10

          by robertlt ·

          In reply to Good strategy

          For better performance go with a raid 1 for the OS and a raid 10 for the SQL Data and logs.

    • #3056605

      RAID5 Not the Best for Databases (OLTP Anyway)

      by jim.orcutt ·

      In reply to Disk Partitioning for Database Servers on W2003

      Most folks know about RAID5 because it is the lowest-cost, fault-tolerent array. With n disks, you get n-1 disks of storage.

      For databases, however, RAID5 is not the best performer. The reason is the read-after-write penalty.

      Virtually all database I/O is update activity at the OS level. Data files are preallocated, so even an insert on a table is really an update on the data file at the OS level. Once a block is updated, the whole stripe that contains the block has to be read so that a new parity for the stripe can be calculated and written. That is extra I/O.

      For a read-only database or a data warehouse where you are not too fussy about the nightly load, this may not a problem. For a high-volume OLTP database, this can be a killer.

      I have found 30%+ improvement by migrating SQL Server databases from RAID5 disk to RAID1+0 (RAID10) disks as was recommended.

      • #3056587

        Partitioning Transaction Logs

        by jim.orcutt ·

        In reply to RAID5 Not the Best for Databases (OLTP Anyway)

        I guess I never directly answered the question about partitioning off the transaction logs.

        I used to just say that was great. After all, a busy database will have lots of log writes, lots of data file writes, and lots of checkpoints. Why not separate the I/Os?

        However, if this is a database you care about, you are probably committed to something close to point-of-failure recovery. As such, you are probably dumping the transaction log throughout the day. The I/Os needed to read the transaction log for dump will compete with the other database I/Os.

        With a mirrored pair for transaction logs, you have 2 spindles to handle regular activity and the dump. If you leave the transaction logs with the data files, you have all n spindles available. Basically, you dilute the pain.

    • #3056561

      RAID1 for Performance

      by magpiper ·

      In reply to Disk Partitioning for Database Servers on W2003

      Use RAID1 for performance as it can read from either disk and dosen’t have to calculate parity. Add another mirrored RAID1 set into the equation on another SCSI HBA or channel and you have created a mirrored, duplexed array.

      RAID5 is not recommended for SQL servers due to the volume of transactions expected. There is simply too much overhead to get good performance for a database with high transactions.

      I never partition an entire array as one partition; always subdivide. There will be many that dispute RAID 1 as a better performer over RAID 5. Without getting too technical do your research on how the OS handles mirrored and duplexed drives in a RAID 1 configuration. I am familiar with Novell Netware and how they optimize RAID 1. I can not vouch for Windows or Linux implementations and technology. In Netware it can read from either mirrored set (duplexed or not) thus increasing performance.

    • #3059510

      RAID 5 for a DB server (don’t)

      by gwscheppink ·

      In reply to Disk Partitioning for Database Servers on W2003


      We use a bunch of Progress and MS-SQL database servers in our companies and by experience I can tell you that RAID 5 is the worst you can do for a database server. The options you have are using RAID 0 (striping) very fast but no reduncy and even worse a higher risk of failure.

      RAID 1 Mirroring Writes are a bit slower reads are spread over multiple disks and if the administrator/engineer uses a smart seperation of data and logs over different mirrored sets performance is good.

      RAID 10 (Mirrored Striped Set of disks). Only works for systems with 4 disks or more. Writing in four disk set is about as fast as a on striped dual drive storage. Reading is even faster.

      Current RAID controllers support RAID 10 very well and use quite a lot of Cache Memory (battery backed up)

      RAID 10 is by far the best solution for a DB server and your 6 drive system is “ideal” for that. You should consider to use a RAID 1 set for the OS and possibly use the remaining space for transaction logs. The remainig four drives in a RAID 10 config used for data will give you very good perfomance.

      Hope this will help you to start of on the right foot.

      Gert-Wim Scheppink

      • #2995832

        It is not complex

        by power zhu ·

        In reply to RAID 5 for a DB server (don’t)

        I think everybody worry too much about raid, in fact it is detected as physical hard disk, the difference it that raid card transter data from physical disks to the OS, or OS to disk. So it is not hard to operate on raid, but generally raid is used for important data, there mustn’t be any error.

        I recommend using partition master, it support raid perfectly and you can copy the drive or partition to backup the data. The most important, it can partition raid easily and safely. this article may help

Viewing 3 reply threads