June 24, 2012 at 3:16 am
I'm currently running SQL Server 2008 R2 Express, but an installation to the full version is in the works. I have been reading all sorts of articles about configurations, lots of good info out there and I'm slowly starting to make sense of it.
One thing that still leaves me puzzled - how many disks are appropriate for various configurations? I have a single server with six disks in two RAID arrays. I didn't configure the machine, and it wasn't originally setup by anyone with any SQL Server experience, nor with the intention of being a database server, so I'm a little confused on how it would be best organized. Maybe someone here can provide me with some advice.
The C drive is two disks in RAID 1+0, the D drive four disks in RAID 5.
The D drive, the RAID 5 configuration makes sense, but I was thinking of splitting it into two RAID 1 arrays, for two reasons - speed and redundancy. The way it is now, I have much more capacity than I need and I'm always happier with better performance and safety. RAID 1 is supposed to be faster, and two I/O channels seems like it might aid throughput, as well as giving me more choices on where to place tempdb, actual data, the SQL Server program files, log files and so on. There are six 'things' that I need to locate:
1. OS
2. Swap file
3. SQL Server Installation
4. SQL Server Data files
5. SQL Server TempDB
6. SQL Server Log files
If all disks (or at least these six) were perfect, no problem – one thing on each disk and I'm done. Since they're not, doubling into RAID is prudent. Which of these things should absolutely be on separate drives, and which can be combined? The machine has 6GB or RAM. I was thinking 4GB for SQL Server, 2GB left for the OS and whatever else, which should not be much of anything. A FileZilla server with very little traffic and a few nightly copy tasks is about it.
June 24, 2012 at 3:28 pm
whichever way you look at it your disk config is limiting, as is the RAM capacity.
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
June 25, 2012 at 7:33 am
Perry Whittle (6/24/2012)
whichever way you look at it your disk config is limiting, as is the RAM capacity.
Agreed Perry.
Personally with that config I would go with 2-disk RAID1 for C drive and 4-disk RAID10 for D drive. You will need to use the C drive for something to get best throughput too. tempdb log at a minimum, perhaps all logs. With 4 disks together you have a teeny bit of spindle aggregation available.
I sure hope you don't expect much performance out of this box.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
June 25, 2012 at 8:31 am
TheSQLGuru (6/25/2012)
Personally with that config I would go with 2-disk RAID1 for C drive and 4-disk RAID10 for D drive. You will need to use the C drive for something to get best throughput too. tempdb log at a minimum, perhaps all logs. With 4 disks together you have a teeny bit of spindle aggregation available.
What advantage would there be to converting from RAID 5 to RAID 10 on the D drive? Is it even worth doing? Seems like keeping all four disks aggregated will not yield any great improvements, no matter what scheme I select.
I sure hope you don't expect much performance out of this box.
Performance isn't at all bad, actually, even with the Express version, but I don't have that many users. Often none at all, rarely more than two or three at a time. My main focus is making individual queries return data as quickly as possible - resource contention, deadlocks and such are just not an issue. There is one primary database on the machine now, just over 100MB with ten total users, a copy for my test and development purposes, and a handful of other, very small databases, with very occasional users.
This may change, eventually, depending on the success of some projects underway now, but if there is significant growth, more hardware will be added. Right now I'd just like to tune this machine as well as possible, given the hardware that I have.
So OS and all log files on C, everything else on D, leave D in a set of four - is that what you would recommend? What about breaking one out of the D set for a swap drive? I also have attached a couple of 12TB external Iomega boxes, linked via a Gigabit network cable. Would there be any point in adding this to the mix, maybe the log files, or would the network traffic be so slow that it would negate the benefit from taking it off the internal drives?
And the memory configuration? 4GB SQL Server, 2GB OS? Does that sound reasonable, or something else maybe better?
June 25, 2012 at 9:47 am
pdanes (6/25/2012)
TheSQLGuru (6/25/2012)
Personally with that config I would go with 2-disk RAID1 for C drive and 4-disk RAID10 for D drive. You will need to use the C drive for something to get best throughput too. tempdb log at a minimum, perhaps all logs. With 4 disks together you have a teeny bit of spindle aggregation available.
What advantage would there be to converting from RAID 5 to RAID 10 on the D drive? Is it even worth doing? Seems like keeping all four disks aggregated will not yield any great improvements, no matter what scheme I select.
I sure hope you don't expect much performance out of this box.
Performance isn't at all bad, actually, even with the Express version, but I don't have that many users. Often none at all, rarely more than two or three at a time. My main focus is making individual queries return data as quickly as possible - resource contention, deadlocks and such are just not an issue. There is one primary database on the machine now, just over 100MB with ten total users, a copy for my test and development purposes, and a handful of other, very small databases, with very occasional users.
This may change, eventually, depending on the success of some projects underway now, but if there is significant growth, more hardware will be added. Right now I'd just like to tune this machine as well as possible, given the hardware that I have.
So OS and all log files on C, everything else on D, leave D in a set of four - is that what you would recommend? What about breaking one out of the D set for a swap drive? I also have attached a couple of 12TB external Iomega boxes, linked via a Gigabit network cable. Would there be any point in adding this to the mix, maybe the log files, or would the network traffic be so slow that it would negate the benefit from taking it off the internal drives?
And the memory configuration? 4GB SQL Server, 2GB OS? Does that sound reasonable, or something else maybe better?
RAID5 suffers a fairly substantial write penalty, which is why most avoid it.
With a 100MB database and a few users - who cares? I can run that from my phone. 🙂 Until you hit 3+GB of active data everything will be sitting in memory anyway on this box, so all that will slow you down really is writes (especially tlog) - and for that you don't want RAID5. But if the system isn't busy, again I say nothing matters or will make much difference either way.
I wouldn't bother setting up an iSCSI drive on the Iomega stuff. I don't think that will help you at this point.
4 for SQL and 2 for everything else sounds reasonable, although it may not be enough for the everything else bucket. Just watch for consistent paging and drop SQL Server back down if necessary.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
June 25, 2012 at 10:24 am
TheSQLGuru (6/25/2012)
With a 100MB database and a few users - who cares?
Well, I do, and such a low workload also makes it a good time to experiment, and learn how different setups work, which is part of why I'm doing this. I'd rather learn how to do a good job up front than do a crappy one and try to fix it later, after the crappy setup starts becoming a problem.
But if the system isn't busy, again I say nothing matters or will make much difference either way.
It will be getting busier, but probably not too much, at least not in the immediate future.
I wouldn't bother setting up an iSCSI drive on the Iomega stuff. I don't think that will help you at this point.
Okay, I'll just leave that for semi-offline storage. The primary database scans some folders looking for 'attachments' to records, and I'm moving that to the Iomega, mostly just to get stuff that's not pure database off the server. Right now, everyone uses it as the rumpus room closet, into which they throw everything that they might want someday, including trash like wedding pictures. I'm slowly tightening screws to make it a real server and not everyone's junkyard.
4 for SQL and 2 for everything else sounds reasonable, although it may not be enough for the everything else bucket. Just watch for consistent paging and drop SQL Server back down if necessary.
All right, I'll set it up that way initially and see what develops.
Thanks for the advice - I'm one of those developers who is slowly becoming an Accidental DBA, and I'd like to get as much of this straight as possible, before it really becomes important. Tough learning curve, but it's interesting.
June 25, 2012 at 11:04 am
I meant "who cares" as in you can't actually test anything about your configuration to determine if A is better than B or C because you don't have enough data and/or enough concurrent load. 🙂
Consider yourself lucky that you are becoming an "Accidental DBA" on such a low-end system!! Some get thrown off the deep end (and under the proverbial bus) when they are "required" to make that transition!! You are correct though - it is a tough learning curve but it is EXTREMELY interesting!! I absolutely LOVE what I do, and have done for the past 15 years or so!!
Enjoy the journey! Oh, and see if you can hit some SQL Saturday events to get your feet wet on the DBA side of things. AWESOME events!!
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
June 25, 2012 at 2:27 pm
Being a DBA, I'm as concerned with recoverability/"livability" as with speed.
Having all your data and log files on one RAID5 provides no inherent recovery capability. Are the SQL trans logs on the C: drive currently?
Do you take backups every night?
If a drive fails, how quickly can you get it replaced? How much down time can you afford?
The advantage of RAID1 here is not likely speed -- atlhough you could see some speed up -- but in recoverability. With RAID1, a single drive failure does not cost you any data, and typically no (or very limited) down time. Get a good quality controller, and you should be able to recover seamlessly.
For that reason, based on the info so far, I'd recommend the RAID1 configuration. RAID5 is great for reads, but can be used only with proper preparation and available maintenance.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
June 25, 2012 at 3:27 pm
ScottPletcher (6/25/2012)
The advantage of RAID1 here is not likely speed -- atlhough you could see some speed up -- but in recoverability. With RAID1, a single drive failure does not cost you any data, and typically no (or very limited) down time. Get a good quality controller, and you should be able to recover seamlessly.
agree on the whole but you would almost certainly notice some performance degradation whilst the RAID1 or 5 array is being rebuilt.
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
June 25, 2012 at 3:40 pm
agree on the whole but you would almost certainly notice some performance degradation whilst the RAID1 or 5 array is being rebuilt.
True, but RAID1 with a good controller should not really be noticeable in a system used this lightly. RAID5 recovery woud indeed by very tough on performance.
If you build a the "hot spare" into the RAID5 array, my objections go away, and you could then base your choice purely on performance.
If you use database filegroups with two files, one on each RAID1 array, I don't think you'll have any performance issues.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
June 25, 2012 at 4:13 pm
ScottPletcher (6/25/2012)
agree on the whole but you would almost certainly notice some performance degradation whilst the RAID1 or 5 array is being rebuilt.
True, but RAID1 with a good controller should not really be noticeable in a system used this lightly. RAID5 recovery woud indeed by very tough on performance.
If you build a the "hot spare" into the RAID5 array, my objections go away, and you could then base your choice purely on performance.
If you use database filegroups with two files, one on each RAID1 array, I don't think you'll have any performance issues.
This is a 6 disk server. There is no hot spare.
With RAID1 you lose spindle aggregation - such that it is with just 4 drives in the RAID10.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
June 25, 2012 at 4:17 pm
With RAID1 you lose spindle aggregation - such that it is with just 4 drives in the RAID10.
Even with my specified condition of:
database filegroups with two files, one on each RAID1 array?
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
June 25, 2012 at 5:09 pm
I did lose a disk about two months ago, in the RAID 5 set. The server wouldn't let me connect remotely, but otherwise kept running. I and several others from the department were headed out of town for a conference just as it happened, so all I did was make a last-minute copy of the most critical database .MDF files and shut the server down while we were gone. When we got back, I dug up a disk, plugged it in and the RAID rebuilt itself, no fuss, and we've been running on that ever since. I heard that it also lost one several years ago, before I started working on it. Same deal - the IT staff plugged in a new one and that was that.
I don't do backups as often as I should - Express doesn't have the ability to schedule jobs, so I do them by hand whenever I see some activity on the machine. It's one of the things I intend to automate as soon as I get the standard version installed. The two Iomega disk arrays will be additional backup stores - one next to the server and the other in another building about ten miles from here. I'm also going to see about periodic storage completely offsite, but I don't have any details worked out on that yet.
The server is not in time-critical service. The data is important, but very rarely does anyone need anything off it 'right now'. I try for maximum uptime, doing tests and backups evenings, weekends and holidays, but if occasionally something does happen where something on the machine is unavailable for a while, it's not a big deal. Normal use is pretty much 100% available, and odd jobs are usually such that 'by the end of the week' is a common deadline. Otherwise, I work right in the department that owns the machine, so if I do screw something up, it's ten feet down the hall to talk to me about it, and if I accidentally mess up someone's access to something, I can either fix it immediately or get whatever they need for them manually, which holds them over until I get it fixed correctly.
#500 - Both .MDF and .LDF files are in one folder on the D drive. That's one of the things I was wondering about, whether it would make sense to split them. Write performance is not a big deal - the great majority of the activity is reads. Write activity is hugely limited by human factors - one new record every couple of minutes is damn fast for this application.
#500 I'm still kind of fuzzy on the 'files and filegroups' concept. I've been reading up on it, and the main record table seems to me could benefit from some segregation - there are groups of records that are considered in some set operations, that have zero overlap with other sets. I've been looking at indexed and filtered views to speed up access to these sets, but I don't see how I would split them into files or filegroups. Do you know of any tutorials on the net that do a good job of explaining this?
#Scott - Yes, this is a 6-disk machine and all the bays are full. There isn't even any place in the box for more disks. It's also old enough that I doubt if I could find much of anything to beef it up. I looked at getting a second processor for it a while back - the bozo who ordered it only bought it with one, although it has two sockets. Brick wall - everyone claims that such a processor is no longer available. Seems a little odd to me - it's not THAT old. I'd think a processor shouldn't be hard to find, but the IT staff said they couldn't find one anywhere.
June 25, 2012 at 5:29 pm
ScottPletcher (6/25/2012)
With RAID1 you lose spindle aggregation - such that it is with just 4 drives in the RAID10.
Even with my specified condition of:
database filegroups with two files, one on each RAID1 array?
That's just one database, and you are introducing something (filegroups) that didn't already exist to cover something you can get "out of the box" with RAID10. What about the other databases? And the tlogs?
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
June 26, 2012 at 12:28 am
pdanes
I don't do backups as often as I should - Express doesn't have the ability to schedule jobs, so I do them by hand whenever I see some activity on the machine. It's one of the things I intend to automate as soon as I get the standard version installed
use a windows scheduled task to execute scripts via SQLCMD 😉
ScottPletcher (6/25/2012)
With RAID1 you lose spindle aggregation - such that it is with just 4 drives in the RAID10.
Even with my specified condition of:
database filegroups with two files, one on each RAID1 array?
I'm with Kevin here, for one database?
Be better off with 1 RAID10 array IMO. Also you're introducing a concept (file groups) the OP is not comfortable with at present.
Sure splitting file groups across arrays we know is good for SQL server performance but as I said in my original quote "you're limited here whichever way you look at it".
With RAID controllers there aren't to many around only a handful of mainstream options that come with every new server, in any case it's the controller software\firmware that controls the RAID activities such as reads, writes and rebuilds, more important to keep this updated.
Just my 2 cents worth
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
Viewing 15 posts - 1 through 15 (of 38 total)
You must be logged in to reply to this topic. Login to reply