RAID Stripping or Filegroup Stripping?
-
We have a production database (SQL Server SP1) of 15GB size.
I was wondering what is the best: To use RAID Stripping or Filegroup Stripping?Or both?
First Choice:
If I use RAID1+0, I can have 4 physical disks ( 2 for stripping e.g. C: D: and 2 for mirroring lets say C-Mirror: D-Mirror: ) but I could see only one logical disk lets say L:. The RAID mechanism will break my datafiles ( and also every file of my server -operating system files also, I suppose-) to C: and D:.
Second Choice
If I dont use RAID1+0 but RAID-0 then I can have two seperate RAID-0 devices e.g.
1. RAID-0 C: and C-Mirror and I could see one logical device , lets say L1
2. RAID-0 D: and D-Mirror and I could see one logical device , lets say L2
Now I can save my operating system to L1 and I can create a filegroup FG1 with two files, lets say L1:\myDb1.ndf and L2:\myDB2.ndf. In this case, sql server will choose how to make the stripping of objects belonging to FG1 since the files L1:\myDb1.ndf and L2:\myDB2.ndf are saved in different physical disks
What is the best?
What if I could combine the two solutions ? If I had RAID0+1 (only one logical disk - see First Choice) then, could the creation of a filegroup help me or it will cause many I/Os because of file fragmentation?
Thanks
-
Filegroups are not my thing because I don't run massive databases. 15GB is far from massive, and personally I would not worry about managing data placement for a database of that size, just get the best resilience out of the RAIDed disks.
You don't say what RAID facilities are available - a full RAID controller with lots of options or a software-managed collection of internal disks. And you don't say how big the disks are and whether they are identical. But in any case I think you should distinguish between what RAID will do for you invisibly and what you might want to manage by manual filegroup allocation.
By 'stripping' I think you mean data-striping, a RAID feature that distributes data across a number of disks to allow concurrent access and speed up I/O. You set up a bunch of disks as a single logical drive with data striped across them, and they perform better than a single disk of the same (total) capacity. You don't gain much unless there are at least 3 disks involved - you need enough spindles for data to be retrieved from several at once. You don't try to second-guess and cannot manage what goes where - it's just a big fat logical drive. Your 'striping' two single disks called C and D does not make sense - they would be eg disks 0 and 1, presented (as you say) as one logical drive.
Mirroring, at disk or controller level, just maintains identical copies, which is great for resilience but adds nothing to performance and requires double the storage capacity you will actually get to use. Common SQL Server practice with larger databases is to mirror the log drive, as it is mainly concerned with serial writes, and to stripe the data drive to improve I/O seeks. For you, mirroring would eat up all the remaining disk space.
I run many databases in the order of 1 to 15Gb on a single server with an array configured as RAID5. If you have the option, this is what I would use if I had only 4 disks - presumably of identical capacity - to work with. RAID 5 takes one disk to record parity data that allows you to keep running if a disk fails. So with 4 disks your total logical drive capacity would be 3 disks-worth. RAID 5 also stripes the data to improve I/O. And it presents a single logical drive.
If you later get some more disks or another array, you could create a second logical drive as a mirrored pair and put the logs there. In any case, it would be very good to have at least one more surplus disk as a Hot Spare (again if your RAID setup can handle it), to kick in should you lose any one live disk.
If you are limited to striping or mirroring I still think you need at least one more disk: you can usefully stripe 3 disks into a single logical data drive, and mirror two more into a single logical log drive. The data drive would be vulnerable, but as long as you have offline backups, you can restore the latest and your logs are kept safe to roll forward. If you can have only those 4 disks, mirror a pair for the logs and stripe the other two, but don't expect a performance gain until you add more to the striped drive. I still wouldn't bother about filegroup management for 15GB. Those who understand it and use it may advise otherwise, so it will be interesting to see if it could help you.
-
Ewans given a lot of good info there, but if I may add my two cents...
Stripping? Well I've never seen anyone stripping in a datacentre, but the youth of today - what can you do?
What is best? You've absolutely got to define business requirements *before* you can decide what is best. How important is availability? How important is performance?
Next, what kind of disk subsystem best suits *your* database? Is it highly transactional? Is it a reporting only database? And so on.
Next - what are your options? How many slots for disk o you have? How much money will the business stump up to meet their availability and performance requirements?
For a generic run of the mill, nobody cares that much about it SQL server, I'd choose a mirrored partition for the OS and binaries, and a three disk RAID 5 array for the data and logs. This gives the least hassle to the server team if things get hairy.
From the limited parameters you've given, I'll assume a single RAID controller, and recommend a single RAID 5 array with all four disks. Create three logical partitions (OS, data and logs) for organisation's sake, disk management, to allow for easier migration in the event.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply