0

We are planning to deploy a Microsoft SQL Server and to store the databases on a RAID10 (RAID1+0) block-level-storage.

Source: Wikipedia

Does it matter how many drives altogether I put into the array for optimum operation? Be it four, as in the picture above, or six or eight or ten?

I was told that I need the correct amount of drives to have an optimised block size for MSSQL.

The example given was that having a RAID10 with six drives (D1–D6) …

RAID 0
├ RAID1 (D1;D2)
├ RAID1 (D3;D4)
└ RAID1 (D5;D6)

… would cause a problem because a block of 64 kB would have be divided into three and as a result cause a block of the size of 0.33% will have to be written on each RAID1 sub-array.

This is just an example to further clarify my question. So, does it matter how many drives altogether I put into the array for optimum operation?

Please back up your answer with references and explanation.

Daniel
  • 6,940
  • 6
  • 33
  • 64

1 Answers1

2

The block size should be 64kb for MSSql Server, for optimal performance, as described by an easy Google search.

In regards to drives, RAID 10 requires an absolute minimum of 4 drives (look at the image you linked yourself) and then you can expand to your hearts content in pairs of 2 (again look at the image you linked). But remember that once the system is in use, upgrading it with more disks is a pain in the butt, so do make sure and put as many disks in as you need storage!

Addition:

Your question still makes it the same answer, having a block size of 64kB on your raid, will mean it will write chunks of 64kB. MSSql running with 64kB size files, mean it will store the blocks recursively. So say you write two blocks, 64kB will be stored to D1 and then mirrored to D2, then it will write another block to D3 and mirror it to D4. At no point will it attempt to split up the blocks, what would make this happen is if you made a block size of 8kB. Then it would split the block into 8 pieces, so piece 1, 4, 7 would go on D1, piece 2, 5, 8 on D3 and 3, 6 would go to D5. This would then of course be mirrored accordingly.

  • Thanks for the reply, Kris. Alas it is not the answer to my question. – Daniel Apr 27 '15 at 09:36
  • I put in an addition. –  Apr 27 '15 at 09:44
  • Kris, thank you. It may have the same answer, but your addition was important for understanding the technical details. – Daniel Apr 27 '15 at 12:11
  • So, just to make sure I understand what you are saying: The important part is to have the same block size on the storage-level (I read it is called *chunk size* on storage-level) as on the MSSQL-level? As I understand, it does not matter how many disks are in an array, as long as it is a valid RAID-array? – Daniel Apr 27 '15 at 12:12
  • 1
    That is correct, if the *chunk size* is spread over several volumes, it could potentially limit performance, as you'd have to read from almost every single drive, even to access one entry. –  Apr 27 '15 at 12:26