Building a New Server

Question

Post reply

Building a New Server

John Kandrovy

Right there with Babe

Points: 751
More actions
January 29, 2007 at 11:12 am

#67977

Hi,
I was wondering if anyone can point me into the right direction of building a new server to house a sql 2000 or 2005 server with multiple databases. We are looking to go down the road to create/convert our data to a data warehouse.
I'm looking for the latest and greatest configurations of what types of processors Intel vs AMD, what is the recommended RAM needed, what types,how many, how big the drives should be, and what RAID configuration is optimal.
I'm looking at something like dual or quad intel chips, 4-10 gig ram, 3-5 250 to 500 gig drives, and running a Raid-5. Tell me if I'm on or off track
Thanks

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Carl Federl One Orange Chip Points: 25384 More actions · Answer 1

Regarding the disk drives:

1. Use the smallest and fastest drive that you can purchase (36Gb at 15K RPM ? )

2. Use RAID-1 not RAID-5

3. Use a seperate disk for each database's transaction log.

4. You may need to put tempdb data and tempdb log on seperated RAID-1 disks depending on the usage.

Benchmarks that I have run show that 6 36Gb drives in a RAID 5 will be about 12 times slower than 6 36Gb drives in a RAID-1 where the data and log files are on seperate disks.

SQL = Scarcely Qualifies as a Language

John Kandrovy Right there with Babe Points: 751 More actions · Answer 2

Thanks for the tips... I will have to confer with the rest of the dept about the RAID configurations. Also I've noticed that if I'm not working with a X64 process structure and operating system windows 2k3 Server 64 bit, that ram is irrelevant after 4 gigs...

MohammedU SSCoach Points: 19644 More actions · Answer 3

System and Storage Configuration for SQL Server

http://www.sql-server-performance.com/jc_system_storage_configuration.asp

MohammedU
Microsoft SQL Server MVP

John Kandrovy Right there with Babe Points: 751 More actions · Answer 4

Mohammed thanks for the article... A lot of information to digest but it really does get the accurate point across.

Thanks again

humbleDBA Hall of Fame Points: 3540 More actions · Answer 5

Hi John,

I would second a lot of what Carl has said, especially with regards to the number of spindles. Don't be fooled by the size of the disks, you'll more than likely find that io requirements will mean that this is irrelevant. There's a lot you need to consider, as you mention in your initial question, and to answer them you really need to understand what metrics you will see in production, i.e. throughput, reads, writes, the balance of reads to writes, if you will have bulk uploads, indexing, whether you will have staging tables for data import and clensing, the types of reports to be run, and whether you need or want to de-normalise some of your schema structure.

One book I have found very useful in the past in helping to spec'ing servers for SQL Server is MS SQL Server 2000 Administrators Companion. It has some good info to get you going. Basically, go for the best of what you can get budget for. Also, bear in mind future needs, as in my experience, where I have worked with implementations of data warehouses, I have found that they are utilised much more than you think once business realise the usefulness and reporting posibilities.

Good luck with it.

Rgds

iwg

Scott Coleman One Orange Chip Points: 27452 More actions · Answer 6

You want SQL 2005 x64 on a 64-bit server, with as much RAM as you can possibly get. Right now you can only get quad-core processors from Intel and that is a huge advantage (you pay higher software license fees for each socket, but not for more cores). When AMD quad-core processors are available I wouldn't be surprised if they are faster, and some time after that Intel will have something even faster, and so on, but if you have to choose right this minute go with quad-cores from Intel.

You will probably never have enough drives. You'd like them all to be as fast as possible. You want to use RAID 1 or RAID 10 instead of RAID 5. You want each database's log file to be on a separate drive or RAID group. You want as many data files for tempdb as you have real processor cores (don't count hyperthreading), and ideally these are on separate drives from the other data files. This adds up to a lot of drives, usually you have to make compromises. An alternative is to get a limited number of 15K drives in RAID 10 for the log files and use RAID 5 for the data files. If you can afford a few more 15K drives you can use them for tempdb.

Data changes have to be written to the log files so a transaction can be committed before the server can go on to do something else, then the modified pages are then written to the data files in the background. Fast log drives and a lot of RAM can make up for slower data drives.