disk performance

[Maran_1]

I am trying to reconfigure a disk array to provide the best possible performance for a W2K3/SQL2005 db server without sacraficing redundancy/fault tolerance between two storageworks chassis and two dual port 6400 series controllers.

The server is a DL585 875 2.20GHz dual CPU with 8GB ram. It is SCSI attached via two dual channel SmartArray 6400 series controllers to two StorageWorks chassis with (14) 72GB 15k disks in each chassis. Total of 28 disks.

I was considering configuring each chassis with RAID0 across all 14 disks, then using OS level RAID1 to mirror the two arrays.

Or is it possible to do hardware RAID10 across two controllers and chassis? Also, would connecting the storageworks arrays to an MSA1000 or MSA1500 provide any additional options/performance other than provide 14 more spindles to work with?

Thanks in advance!!
Maran

[Rob Leadbeater · Accepted solution]

Hi,

Can you detail how the arrays are configure at the moment, so its possible to determine any likely performance increase ?

Personally, I wouldn't use a mixture of hardware and software RAID, particularly if you don't want to lose any redundancy. Consider what would happen if you had a disk fail in one of the RAID0 stripes. That whole shelf would then go offline, and you're then relying on Windows to switch over to the other shelf. It's unlikely, but disks can fail in quick succession. If you happen to lose a disk in the other shelf, bang goes your database.

You'll be much better off sticking with hardware based RAID10, where each disk is mirrored, and then striped. As to whether you can do this across two controllers I'm not sure...

Hope this helps,

Regards,

Rob

[Ian Posner]

You are better off creating a RAID 10 on each of the chassis, then creating a diskgroup with 2 files, one on each of the drives. That way you'll get hardware mirroring plus the ability to either a) balance IO over two channels or b) choose which of the two channels specific data is resident on.

For option b), this would allow you to separate a table from its indices, placing them on different channels.

Disk groups are the way to go!

[Ian Posner]

Whoops! I meant "SQL Server filegroups" in the above post rather than "disk groups". (It's just that I've been reading a lot about SANs recently)

[Maran_1]

The current configuration is:

I have two dual channel 6402 HBAs cross connected to two 4354 shelves.

HBA1 PortA is connected to Shelf1 portA.
HBA1 PortB is connected to Shelf2 portA.
---
HBA2 PortA is connected to Shelf1 PortB.
HBA2 PortB is connected to Shelf2 PortB.

So each controller is connected to both shelves. There are 7 drives per shelf configured in hardware RAID10. (total 14 drives in RAID10 across two shelves)
---
This weekend, I connected HBA1 port A/B to Shelf1 A/B and connected HBA2 to Shelf2 A/B.

So now each card is dual attached to only one shelf. All 14 drives in the shelf were configured as RAID10.
---

When running each shelf off of a single dualport card, the performance was noticably slower.

It seems to me that both connection scenarios should provide the exact same performance. In both cases the same RAID level was used, the same number of drives in the RAID, the only difference was splitting the RAID10 onto seperate shelves.

Does simply splitting the RAID10 onto seperate shelves really make a performance improvement even though it's still through a single HBA? I could understand a performance gain if the RAID10 was split across HBAs somehow.


Also, my DBA claims that the 6402 controller card supports RAID on RAID. Meaning, he wants to create two hardware RAID0 arrays of 7 disks each, then create a hardware mirror RAID1 of those two RAID0 arrays. I am 99.9% positive this is not possible the way he is describing it. Sounds like what RAID10 does to me, but I defer to the experts :)..any input is greatly appreciated.. Thanks again!

Maran

[Maran_1]

Ian,
I have searched extensively on this forum, google, microsoft and sql sites trying to figure out how to convert a database from a single .mdf file to a database that contains multiple files, .mdf and .ndf files.

I like the idea of filegroups, but I think the first step is to get the database broken down into smaller files spanning multiple RAID arrays. I just can't figure out how to do it in SQL2000.

I have a test box setup, I have tried creating a multi-file database shell, and restoring from backup into the shell, but it just wants to restore back to a single file, the way it was backed up.

So, I did that, I restored it to a single file on a different locally attached array, and the database was up and running, but it was still just a single file. So I tried to export it to a multi-file database shell, but that did not work either.

Either I am missing a step or two, or the SQL2000 GUI is mis-leading.

Any pointers on how to move from a single db file to multiples would be greatly appreciated. Simply adding .ndf files to the existing database does not re-distribute the data across the files. I have to create the files first, then put the data in so the data is spread out evenly across all db files.


thanks!
Maran