What is the best RAID configuration for Navision SQL?

DeSp

We need to handle about 47 concurrent users with this server configuration:

2xXeon 2.66 Ghz, RAM 2 Gb, LAN 1Gbit
4õ80 GB SATA 150 Western Digital Raptor (740GD) 10000 rpm 8 mb RAID 0+1
Intel RAID Controller SRCS16 PCI64, SATA 150, Cache 64 mb
Windows 2000 Server SP4
SQL Server Standard Edition SP4 running Navision SQL database of 21 Gb

What is the best RAID configuration for running Navision SQL database in our case? Is it necessary to split our RAID 0+1 volume to different logical drives as it described in "Tuning Navision for better performance" document?

Any suggestions will be very much appreciated.

Thanks in advance.
Best regards.

DenSter

Better is RAID 1+0, where you stripe mirrored pairs. If you search this forum and mbsonline, there was a discussion about RAID recently, and someone posted really good links to a site that explains it all.

DeSp

Thanks for your answer.

Should we split our single volume into several logical drives within RAID 1+0?
"Tuning Navision for better performance" says that tempdb, transaction log file, database files, system files - all should be on separate logical drives. Is it correct within RAID 1+0 configuration?

bbrown

Place the transaction log on a seperate physical drive. RAID 1 is best. You will not gain anything from partitioning a RAID set into logical drives. The files would still be on the same physical drives.

DenSter

Usually you would have multiple physical volumes in one RAID array, and each raid array would be one logical drive, unless the number of disks goes up dramatically, in which case I don't know what is best . It makes no sense splitting files up because the RAID controller will take care of putting files on multiple spindles. I have heard someone make a case though to split up the files so there are multiple handles. I really don't know if that makes a difference though. If it does it is likely only so on large transaction volumes. I have no numbers to back that up though.

* OS + SQL Server program files + tempdb on one RAID 1 (if you can afford it 1+0, if you can afford even more put tempdb on separate array)

* data files (the MDF holds system objects, the NDF holds the actual data) on its own logical RAID 1+0 array

* TL on its own array. This one MUST be dedicated to transaction log due to the way the files is appended to. If it is dedicated to the TL, then the HDD head doesn't have to move back and forth and you have optimal speed.

bbrown

Creating multiple physical files within a filegroup can improve performance. SQL will strip the data across the physical files much the same way the native server does. Each of the files should be placed on its own disk set.

DenSter

That is correct, but I don't know if there is an advantage to have multiple data files on SQL Server when you're already in a striped array. I was talking to a hardware guy that works with serious machinery, and from what he told me I understand the RAID controller is much more effective at striping than SQL Server. If you alreayd have say 10 sets of drives to write to apparently it doesn't make much of a difference.

This is a 'he said, she said' kind of thing. I would like to see some sort of white paper with benchmark results to back up either scenario, and from what type of loads (or maybe it's the other way around and you should do this only on smaller hardware configs) it would make sense to split up the files on SQL Server.

I'm not holding my breath though, the official hardware recommendations still say that 20MB of RAM enough to run Navision .

Freestyler

Hi,

if you use multiple date files on SQL server you have one advantage. You can make different filegroups. Now put the data files from one filegroup on the fastest part of your raid (the raid's beginning) and the data files from the other filegroup on the slow part (the raid's end). On this diffrent filegroups with diffrent average access times you can share your tables ordered by priority. This solution is just an example. The most increase of performance you will get with diffrent raids. But this would also the most expensive solution. For example our installation is based on 4 raids. One raid1 for system one raid1 for tempdb one raid10 for transactionlog and one raid10 for databasefiles. The raid for the databasefiles is divided in 3 partitions. Verry critical tables with much stress on it are stored in filegroup 1 which database files locatet in the first partition of the raid 10. This configuration garantee best performance for the tables in filegroup 1. I hope this could help a little bit understanding the advantage of multiple databasefiles.

Best regards.

PS: Sorry for my english

DeSp

Thanks everyone, your advices are very helpful.

DenSter

You have to understand though that this type of configuration is typically done in very specific situations. If you have a 'regular simple' installation with a spread out load and you don't have performance issues, you don't have to set up your system in filegroups. The only reason to go on that route is when all other routes don't give you improvements. If you are not feeling any performance issues, you should just leave it alone.

bbrown

These configurations are generally only used with very large databases (VLDB). Hardware RAID will be faster until the I/O demand exceeds the max. I/O of the RAID array. At this point the array becomes your performance bottleneck. Splitting the database across multiple arrays can improve performance at this point.

Arrays should be built using the same drives. Don't mix and match drives. Partioning an array will not improve performance. You have no control over which physical drive your data is stored on. This is managed by the array controller's operating system. The array will spread the data over the available drives.

DenSter

right what he said

Savatage

I bet if there was a forum for Server Specs & Configurations there would be a 1000 post in there

BGI

If you have the server:

1 array on raid 1+0 for data files
1 array on raid 1+0 but FAT formating for log files
1 array on raid 1+0 fat for temp database

All arrays on different chanels of raid adapter..
!! Cluster size of your disk on 8 (sql reads in chinks of 8 bytes)..

and a lot of internal memory....