Implementing SQL server on 4 drives.
-
We're trying to implement SQL server on a new server that only has 4 drives. My boss said if RAID 5 is done there will be a performance problem because the data and logs will still be going across all 3 drives. We need to Optimize performance. There is about 50% Input and 50% output on a daily basis. About 70 users at one time all across the world. NOthing else is on the machine but SQL server.
What about mirroring?
What's the best way to set up SQL server on 4 drives. Where should the OS go? SQL, data and logs? There is no way we can get more hard drives (I already asked). We have to work with only 4 drives. What is the best way to optimize performance with only 4 drives and why?
-
My wish list for the optimum SQL Server HD configuration is 4 SCSI RAID controllers, the first 3 are separate RAID 1 arrays for OS, Swap and OS log files, and SQL Server database Log files. The 4th is the SQL server Database files array and is RAID 10. Which requires a minimum of 10 drives, which will not fit your situation (nor my budget), so lets talk how best to compromise.
The reason to divide these up to seperate channels or controllers is to minimize the backbone I/O conflicts, so instead of 4 controllers you could use 2 for 4 channels. Each of these 4 divisions of work use the HD in different ways so benefit mainly by isolation. RAID 1 is chosen mainly for maximum space, with redundancy which is the reason we use hardware RAID, perferably with battery backed up cache. RAID 10 will provide SQL Server databases the best I/O and redundancy. As always there are those that will argue for a completely different configuration with valid arguments.
I don't know about you but I do not have a SAN budget, hence the trade off, RAID 5. Most people would choose two RAID 1 arrays for your 4 available HDs to maximize available space, I would choose one RAID 5 with 1 standby HD to maximize redundancy. Now if you had 5 drives, then I would choose RAID 1 and RAID 5 with no standby.
So my recomendation is to decide: Do I maximize the available HD space with redundancy or Do I maximize redundancy and have less available space?
Either way I would suggest 3 partitions (OS/Swap, Log, and DB) and have settled for 2 (OS/Swap/Log and DB). In any production situation, the my 1st rule is to seperate the database Log and Database files by partitions at minimum.
Hows that for compromise, I started with a $100K system and ended up with last year's workstation...
Andy
-
Andy,
At least you got a workstation. At my current client I'm bringing in my own personal 64 bit system for them to test on.
As for the best configuration on 4 drives I would probably agree with Andy. It isn't ideal but if you've only been given 4 drives then you don't have much to work on. Besides, how can your boss be that concerned about performance when he's only giving you 4 drives?
Now, if only you could get 5 drives, things could be a little different and here I would go for a slightly different setup to what Andy suggested.
I would go for 1 lone drive (i.e. no RAID) and two seperate RAID 1 arrays. Put the OS on the lone drive (who care's about redundancy on the OS drive - I'm a DBA
). Put the data files on the first RAID 1 array and put the log files on the second RAID 1 array. With this setup you at least get the chance to split the log files and the data files (physically).Come to think of it, I'm wondering whether it would be better to have two RAID 1 arrays with the 4 disk option. Put the OS and Data files on the first array and put the log files on the second array. Problem with this is that you're putting the the data files on the OS drive and I'm never keen on that option.
General rules:
As Andy said, 1st rule is to split up the log file and the data files. Doing it by partitions doesn't really give much benefit though so you ideally want to physically split them up physically.
RAID 1 is faster than RAID 5 for write performance and your log files should always be on RAID 1 because you almost always are just writing to the log files.
-
Thanks for all your input. This really helps. I have another question though. I recently read somewhere that you could implement RAID 10 using a minimum of 4 drives. But then Andy, you stated that for RAID 10 you need a minimum of 10 drives.
I beleive our biggest concern is to maximize performance. With 50% Input and 50% out, I need the fastest machine I can get. But if I use mirroring which takes up so much space, won't it eventually slow down when the database grows bigger. The size of the hard drive is 148G each. I'm still confused as to whether I should use:
- 2x RAID 1 (mirroring)
- RAID 10 (stripping and mirroring)
I don't think I want to use RAID 5 because it's not as fast. I need something really fast.
-
I was wrong about having a 4 drive server. The C:/K: is actual a 2 drives Raid 1. The other drive E is made up of 4 drives raid 3. Then there is 1 spare drive shared between the two arrays. It adds up to 6 drives.
David Bird
-
RAID 10 only needs a minimum of 4 disks, not 10. RAID 10 is a mirrored set of a stripe, or a striped set of a mirror.
The biggest single contributing factor to SQL Server database performance is the performance of the log file. Remember, SQL Server uses a write-ahead log, which effectively means that everything is written to the log file first before it goes to the data file. Take a transaction, the resources that are associated with that transaction will be locked for the duration of the transaction so your aim is to get transactions in and out (committed) of the log as quickly as possible.
So the you want fast drives (RAID 1 or 10) for your log files and you want to seperate them from data files because almost all action on the log files consist of contiguous writes. On data drives, you've got lots of writes (which aren't contiguous) and reads. All this means that the disk heads are moving around a lot more, which impacts performance. Which is another reason why it's good to seperate the log and data.
So if nothing else, put your log files on their own seperate RAID 1 array. That will maximise the performance for you as much as you can with your current drive configuration. Sure, as the size of the data on the log drive increases it get's worse but if you manage your logs correctly this shouldn't be too much of an issue (especially with 148G drives).
Given the two options of 2x RAID 1 or 1x RAID 10 I would go for the first option purely because it means you seperate the log and the data files.
And not only is this a good idea for performance reasons but it's also a good idea for reliability reasons. If you lose the log drive you'll still have the data drive. If it's all on one array and you lose that array then you've lost your data and your logs.
Don't forget you'll need a backup drive
If you knew the configuration we used to have at my last client you'd kill me - money was no object, literally. -
Another point to note is database space requirements, obviously your choice of RAID will determine the available space you have for databases and transaction logs. I would agree completely with grambowk above though about chosing 2 x RAID 1 over 1 x RAID 10, if your manager argues tell him they should let you specify the server next time, it's strange in this day and age to be limited to so few drives. Another option for storage in the future is one of the out of the box affordable "all in one" SAN solutions offered by the likes of DELL and HP.
ll
-
Thank you to everyone who replied to this post. You really helped a great deal. I have a meeting today to talk to my boss about using two RAID1's.
Now, I'm assuming we are going to partition the first mirror and put the OS and SQL server on one set of mirror and the then the logs on the other set.
-
Yep, placing the data files and OS on the same array and then place the logs on their own array. It really isn't ideal but it's the best option in this scenario.
Good luck with the meeting. Disks are cheap - insist on more
-
This is what we did:
C drive is for the OS
D drive is for application (i.e. SQL Server, etc)
E drive is for database files
F drive is for backup files
drive C & D are only backed up when fixes are applied
drive E is never backed up
drive F is backed up on a nightly basis
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply