MS SQL Server 2000 transaction log placement

Yes, it is a safe bet to use RAID 1/0 for SQL logs and tempdb. But what type of drives: Fibre Channel or Enterprise Flash Drive? FC makes 180 IOPS and EFD makes 2500 IOPS, see the difference?

There is a strong limitation on heat dissipation and power consumtion of the entire system. So, the drive count can't be very high - not more than 30.

What is EFD?

Microsoft recommends RAID 1 or 1/0 for logs and tempdb due to the potential high transactional i/o that can occur with those device files. However, it all really depends on the database in question.

I have deployed SQL servers with the data file LUN and log file LUN sharing the same RAID5 RAID Group and SQL servers with log files on dedicated RAID 1/0 (2+2) RAID Groups. It just depends on the i/o requirements of the database.

If you are unsure of the application behavior it is always a safe bet to use RAID 1/0 for SQL logs and tempdb.

CX4-120

Because of your data center?

What array do you have?

Ahhh, EFD = Enterprise Flash Drives. Silly me. I always think of them as SSDs (Solid State Drives).

I would only use flash drives if you application and budget warrant it. Is there a reason you are considering flash drives?

A FC drive is capable of about 180iops sustained (with higher bursts) but in a RAID 1/0 set you will be using more than one drive which will create a bigger pool of performance for that RAID Group.

I think you need to evaluate the needs of the application before you decide on an architecture. I have deployed some very high i/o SQL servers without using flash drives (we are talking thousands of iops). Keep in mind too that the log files can be broken into multiple device files which can all be on separate LUNs in dedicated RAID 1/0 groups. This will help Windows as well since presenting Windows with multiple drive queues will increase performance.

Roman,
I am just summarizing here so I understand your situation. Correct me if I am wrong on any point.

So you have a Cx4-120 but can only deploy two enclosures on the array due to heat and power limitations in your server closet (I am assuming it is a closet since just two enclosures in a data center that only draw about 2 amps on 208v should hardly impact a heat/power footprint).

You also need to deploy 14 SQL servers on the 30 disks available with only two enclosures and performance is a concern.

Well, that is a tough situation. With only 30 disks for storage you will not have great performance hosting 14 SQL servers on those disks and your storage capacity for each server is limited because of your disk limit. Also keep in mind that the first five drives of enclosure 0 host the array operating code and you should not place high i/o LUNs on those drives. So realistically you only have 25 disks available for high i/o SQL servers.

So for those 14 SQL servers what are the storage requirements? You may have to sacrifice performance for capacity since the SSDs only come in 73GB and 146GB. Do you know what the data, log, and tempdb requirements are?

You are quite right. Disk array capacity is not a primary concern because the size of the each db is not more than 10G, each tempdb is less than 1G and transaction logs are truncated regularly.
In contrary the less the capacity of DA is the better because of EMC CC licenses.

AranH,

Just a side note: currently CLARiiON Enterprise Flash Drives are offered only in 73 GB capacity. The 146 GB version that you mentioned is not offered on CLARiiON arrays.
But this should not be an issue because very modest capacities (10 GB per SQL server) are required in this case.

Thanks.