Hardware Configuration
-
I have, for the first time in my career, the opportunity to spec out a server to host SQL Server 2005 and I am looking for recommendations on the hardware specs, specifically drive configuration. We are planning on going 64-bit. I work for a Non-profit and don't have an unlimited budget. I would guess that I have a max of about $10K to spend, although it could be less. To begin with the server will mainly host a to be developed personnel system. The organization has about 3200 people worldwide and will need to keep an unlimited history of movement, education, skills, etc.. It will also be used to host other systems down the road. I would anticipate using all the available services except for Analysis Services and Notification Services (although possible as I learn more about it).
Thanks in advance,
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question -
for 64 bit harware, i will recommend 64 bit SQL server 2005 with atleast RAID 5 & depends on budget you can have RAM. do some web search on Dell, HP, IBM & Hitachi. that might help.
-
$10k for a Server Class 64Bit system, with Raid Controllers? How much memory are we talking about. If less than 4G, perhaps you don't need 64bit. Does that 10K include SQL Server, OS, Virus Software, CAL's, drives?
Do you have a backup system/server?
In order to determine best configuration, you are going to have to determine what requirements are NEEDED. Then find out what you have to spend to fill those needs.
Depending on your requirements, 10K could be about 4x more than you need to 1/10th of what you need.
-
why not choose anything else than 64-bit, even if you have less than 4 GB of RAM, there is no extra cost for running 64-bit all the way from hardware, OS to SQL Server?
Because SQL Server 2005 has a native 64-bit support, you should go with that if you dont have any spefific reason to choose something else. If you are about to run a dedicated SQL Server box, there shouldn't be any issues, at least I haven't ever heard of a software which would care whether a database is ran on 32-bit or 64-bit version.
When you choose a cpu, you should consider if you have a lot of small queries running at the same time or just a few big ones and whether they are against one or many databases.
If you have multiple databases and lots of queries simultaneously, you should probably go with cpu with lower frequency and more cores, for exampe 1-2 Quad Cores with low frequency.
If you have long running queries which need a lot of cpu at once, got with higher clock frequency and with less cores.
4 GB of ram is probably the minimum you should get for a dedicated database server these days, running on 64-bit OS and SQL Server, you will get more out of the memory as you will not run into 32-bit memory restrictions and do not have to use extensions to make use of more than 2 GB of memory, which is actually the limit when running SQL Server on 32-bit OS without any extra parameters.
Disk system should be something like raid 1 for system with 2 small disks. raid 0+1/10 with 4+ disk as a data storage, if you don't have any SAN solution to hook up with the server. Be sure that you have the possibility to extend more disks later to your raid 0+1/10 (slots for hd's), if you run out of disk space or you have performance issues.
Never use raid 5 on a database server, you don't have so low a budget to go with that solution or at least you never should have. Raid 5 is performance killer if you have any write operations at all.
Don't buy the cheapest raid controller offered, go up a step or two in the price group, buying a cheap, low-performing controller will kill all performance benefits you try to gain with a decent disk system.
Of course if you want to save something from your 10k, you can do that easily, for example with a lot cheaper disk system, for example just a simle raid 1 for system and raid 1 for data will do, if you don't see that the disk will get a lot of pressure from your application(s).
Also choosing a very low frequency dual or quad core cpu might not cost a lot either.
Keep in mind, that from SQL Server licensing point of view, you are better off with high frequency cpu with as much cores as possible, because you pay SQL Server cpu license just for the physical cpu's.
I'm thinking more of spending all your 10k and not saving a bit, of course I don't know what you have counted in to that sum, SQL Server licenses for example will bite quite big part of your budget, so I wouldn't imagine that those are in the sum you mentioned.
Because you especially wanted to know more about the disk usage possibilities, here are a few scenarios.
1.
raid 1 (2 disks) for operating system, page file and sql server binaries.
raid 1 (2 disks) for data and log files and backups
Here you can always get another raid 1 set on top of the two old ones and transfer the log files there for example, if you run into performance issues.
2.
raid 1 (2 disks) for operating system, page file and sql server binaries.
raid 0+1/10 (4 disks) for data and log files and backups
here you can always extend your data+log set with more disks, if you buy disk with high capacity, you can also partition the set in 2 parts. In the first partition (which should be the fast part), you place your data and log files, in the other partition, you place your backup files.
Do not place data and log files to different partitions, use partitioning only for backup purposes, because it doesn't need ultimate performance. By keeping the backups in a different partitions, you will save the data+log partition from fragmentation, because file create and delele wont happen constantly.
3.
raid 1 (2 disks) for operating system, page file and sql server binaries.
raid 1 (2 disks) for log files
raid 5 (3 or more disk) for data and backup files
This is the only decent disk structure with raid 5 in it, logs, which need write performance, are not in a slow-writing raid 5 set. Although I would never use it as you can drop one disk and do a 1+0/10 of the 4 disks and keep one as a spare.
remember, be careful with partitions and raid 5. The worst sets I have ever seen are raid 5 with 3 disk, which is partitioned to 2-3 parts and one partitions includes OS, the other the db logs and the last one the db data files = poor hd's and users
Of course, if your company has invested in big time SAN -system, you should use that for data and log files and get only 2 disk raid 1 set for the OS+page file as a local drives.
-
Thanks for the replies. Here is what I am looking at as far as disk configuration goes (based on Inside MS Sql Server 2000):
Raid 1 drive for OS, Page File and binaries
Raid 10 array for data
Raid 1 for logs
Are there any differences between Sql2K and Sql2K5 that would make another configuration better?
We would go with better hardware raid controllers with each array on a physical controller.
There is another thread discussing stripe size, recommending 64k for the data drives. Would you want that stripe size for the log drive(s) as well?
Where I used to work everything was RAID-5, what the network/server admin was comfortable with, but at least it wasn't partitioned as you mention
.Thanks,
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question -
Not that I disagree with any of your points, there is a "Cost" with going to 64 bit. That cost is that nearly every application that runs in 64bit mode will require more physical memory than does the same 32bit version.
However, with 3200 locations/connections/users, I expect that we are talking about a fairly large database with fairly high availability requirements. Perhaps as much as 99.99% (or less than 1 hour per year). I hope not, because there is no way to implement that type of requirement in that budget.
I guess I was more thinking of where to cut costs without going out and pricing everything for the OP.
Having done this type of spec work before, I know how much time and effort is involved in finding out the real requirements. (FYI, first cut never fits in budget)... Something will move: Budget or Requirements, but usually both will move somewhat.
I used to working with requirements where the budget is more in the $500k-$5M range. And pulling off something like that in $10K just threw me.
-
To my knowledge there aren't any major differences between 2000 and 2005 as far as it comes to things you're looking into.
Personally I would go with broader raid 10 set and put everything there, that way you have 6 or more disks working for both, log and data files.
There is not probably not much use for the data to be put on raid 10 set, if your logs are on a raid 1 set as it will be the bottleneck with for the write operations.
Of course you have to consider this depending on your application(s), what kinda operations are typical for them.
Naturally there is some cost always, when using another technique against another one. You should also consider that there are some resctrictions in Integration services when run on 64-bit platform, I didn't remember that before.
Usually thou, the case is that you have a server with 4 GB or more ram, but run 32-bit SQL Server and for that reason don't get all the benefits of the total amount of your ram.
Naturally if you ran other software alongside SQL Server, the "bit-issue" comes more complex.
About the stripe size: I did some tests and according to those, at least in sequential write very small stripe sizes caused performance issues, the higher the better in that case. I wouldn't go lower than 64.
Remember that there are another settings as well, write back and read-ahead, at least that is what they are called in some controllers. According to my tests, turning on those settings will have very big performance benefits, but naturally they have their own (data safety) downside as well.
Well, you were thinking of putting your logs to a raid 1 set, thats only mirroring, there is no striping, so stripe size shouldn't matter?
Otherwise, if you use raid 10 for your logs aswell, I think I would go with 64 with the logs too.
-
Thanks Jim and Bob.
I spec'd out a Dell 64 bit server onpline and the server without Sql Server was $17K so I am pretty sure that won't fly, but I figure shoot for the sky and then you actually can get what you need and still look like you are giving something back.
Yeah, brain cramp on stripe size for a raid 1 array.
I just got done talking with a co-worker who used to work for Adaptec developing the controllers and he had a lot of good information, including about the read-ahead and write-ahead configurations on the controllers.
I basucally chose the drive configuration from a chart in Inside Sql Server 2000 and the configuration I chose has a very hig hrelative read performance and a high relative writer perfomance.
The main system will be a Personnel package that I would anticipate being more read-intensive than write-intensive. Most of the queries will be who is working where and on what type of things, or who has a birthday in the next 2 months. Because of the type of organization, where specific training is required to continue on, there will be regular queries about attrition, etc...
Thanks again. I love the SSC forums!
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question -
HP just started selling the Proliant DL 580 G5. 4 CPU, 256 GB RAM max, 16 HD's max
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply