SQL Server Performance Forum – Threads Archive

RAID configuration for multiple databases

I have some questions about RAID configurations with SQL Server 2000 on Windows 2000. As context, my team has six related OLTP databases on the same SQL Server, all of which have fairly high levels of write activity. One database experiences higher levels of write activity than others and uses a single large table with a large IMAGE column. Lots of the write activity is associated with inserts and updates to that column. Relatively speaking, that database is about 50 GB in size and the others are all less than 1 GB in size. Transactions sometimes involve two databases simultaneously. Each database has a single data file and transaction log. Today, these are all sitting on the same RAID 5 array, along with their backups. We intend to move the transaction logs to a RAID 1 array and to move the data files to a RAID 10 array. We also plan to move tempdb to its own RAID 1 array. Finally, we intend to leave the backups of data and transaction logs to an isolated RAID 5 array. Here are my questions: 1. We can’t afford to put each of the six transaction logs on its own RAID 1 array. Is there a performance or other benefit in creating disk partitions/logical drives for each of the transaction logs? Should we do this? If not, should we simply place all of the logs on the same array and give each of them a large, fixed size?
2. Similar to the first question, we plan to have all of the data files sitting on their own RAID 10 array. Is there a benefit to putting them in separate partitions? Should we consider something else?
3. We backup the files to local storage (the RAID 5 array) before putting them on tape. Full backups happen once a week. Transaction log backups happen every 4 hours. Is there a performance reason to put these intermediate local backups on something other than RAID 5?
4. Is there a benefit to using an NTFS allocation unit of 64K for the arrays? I read recently that this is optimal for SQL Server.
5. Any other advice for us about our configuration? Thanks,
Ollie Zimmermann
Monitor Software
[email protected]

Ok. This is one of, IÂ´m sure, many oppinions you will find in nexts post. 1) and 2): There is no benefit using disk partitions. Yes if you can put in differents drives and better differents chanels.
If you can do that, then one idea is put data in one drive/chanel and indexs in other.
3) Backup will faster on RAID 1 or 10 than 5, but if time is not problem, leave in RAID 5.
4) I read same thing but I donÂ´t test it.
5) Be carefully with log space, because backup method means set recovery full and this means log grow.
I gess other suggestion became when you decide hardware configuration.
Luis Martin …Thus mathematics may be defined as the subject in which we never know what we are talking about, nor whether what we are saying is true.
Bertrand Russell

Hi ya,
I have never really seen any significant performance benefit between the various hardware RAID levels… This may be partially because we always use hardware arrays that are battery backed up, etc. and therefore allow write caching anyway. Reading is hardly ever a problem unless there are RAM pressures In terms of 64K allocation units. The biggest disadvantage is that you won’t be able to defrag the drive at all. Win2K only allows defragging allocation units up to 4K. Win2K3 seems to have extended this to 64K Cheers
Twan

For information review thishttp://www.sql-server-performance.com/q&a38.asp link. Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided â€œAS ISâ€ with no rights for the sake of knowledge sharing.

The document is rigth.
It depends of many variables.
In my experience with severals customer, I get more performance using RAID 1 or 10 for data and index than RAID 5.
Luis Martin …Thus mathematics may be defined as the subject in which we never know what we are talking about, nor whether what we are saying is true.
Bertrand Russell

]]>