MSA500 G2 and SQL2005 Best Practices

[joe doherty_1]

Hi,

I am configuring a windows cluster with 2 x dl585s and a msa500 g2. My SQL2005 data files will be located on the msa.

My question is how best to configure the storage...

I know MS recommend we have a seperate disk for data files, transaction log, tempdb, etc.

So how do I configure my msa? Do I create one big volume using all the disks and then create logical segments for each drive required - so that each logical partition is shared across all drives? Or should I create a number of volumes and create a logical drive on each?

I want maximum performance (shared across all physical disks) but also want to minimise contention between the database files.

Any help appreciated.

Joe

[JeremyM]

Joe,

I'm not an expert on MSA's, but I am a SQL Server DBA, so I'll speak to that side of things.

Note that these recommendations don't change between SQL Server 2000 & 2005.

You do not want to create a single big volume with logical segments. That will negate the performance advantages of following Microsoft's best practices. You'll want to physically separate the data files, tlog, and tempdb. Different read/write heads for each. Therefore, different physical arrays for each.

When a write is made to the database, the transaction is written to the transaction log (tlog) *before* it is committed to the database (this is an oversimplification). That means that, until the tlog can be written to, any locks that transaction has will not be released. That creates contention. So, you want that tlog write to be finished as quickly as possible. The tlog is a purely sequential write (per database), so putting it on a RAID 1 by itself works well. Don't put anything else on that array (including another tlog).

With the db files, the more spindles you have, the better. It's like having more hands to accomplish more things at the same time. While the human brain might not be able to efficiently utilize many hands, you can bet a good RAID controller can! Btw, this is one reason why RAID 5 is not considered best practice for databases. That parity drive is taking up a perfectly good spindle! (The main reason is that RAID 5 setups have been shown to be much slower on writes than RAID 10s.)

Tempdb is kinda like that scratchpad you have sitting on your desk. The SQL Server engine uses it a lot. Putting it on it's own RAID can improve performance. However, keep in mind that if the tempdb goes down (bad drives, whatever), SQL Server goes down with it.

Here's how I configured a recently acquired, low-range server (DL360G5+MSA50):

- DB files (mdf) incl. tempdb: 10 x RAID 10 on drive E:
- Tlog file (ldf): 2 x RAID 1 on drive D:

I plan to move the tempdb to it's own RAID 1 as soon as I can get two more drives.

Here's some more info on physical storage configuration for SQL Servers:

http://sql-server-performance.com/tips/hardware_tuning_p5.aspx
http://www.sqljunkies.com/Tutorial/99EBD29D-8B59-4DF0-A2D2-EBB2DDF8A808.scuk
http://www.sql-server-performance.com/articles/per/hardware_planning_p1.aspx
http://msdn2.microsoft.com/en-us/library/ms998577.aspx#scalenetchapt14%20_topic17
http://www.microsoft.com/technet/prodtechnol/sql/bestpractice/pdpliobp.mspx
http://www.bolthole.com/uuala/RAID.html
http://www.devx.com/getHelpOn/10MinuteSolution/16532/1954?pf=true

I hope that helps..

..jeremy.