SQL Server planning: One BIG Server, or several Medium-size Servers?

Hi,
I have recently taken over the Database Administration for a fairly mid-to-large size set of database servers (1-2 TB in size).
When I came in, there were about 15 production Servers - each server containing 5-7 databases.
It has been suggested to get one HUGE SQL cluster - each machine having 4 processors, dual-core, hyperthreaded (net result=16 processors), with 40GB RAM and unlimited SAN storage, and to put EVERY database onto this one huge machine.
All of these applications are internal, used by no more than 50 people total. So concurrent user scalability is not a concern. However, the size of data will definitely grow - by the end of the year, we may well have 3-4 TB of data.
What do you guys think - is it best to use one BIG server, or keep 10-15 medium-strength servers?
Thanks for any input.
Jason

I'm a little bit confused here - you start with "...HUGE SQL cluster..." and you finish with "...this one huge machine...". SQL Server clustering by definion is using a number of physical servers. Your best choice (if money/time is not a problem) is to use SQL Server Clustering. Build your cluster with your HUGE SQL server and a number of medium-strength servers. Don't forget that you're limited to 8 nodes (Enterprise).
SQL Server Clustering = Load Balancing + Automatic Fail-Over.
http://www.sql-server-performance.com/articles/clustering/clustering_intro_p1.aspx
Good Luck.

SQL Server Clustering only help to aviod downtime if any working node fails or if you need to do some maintanence work on any node. This can't be used for Load Balancing, also this is highly dependent on domain controller machine.
Expert please correct me if am wrong, for load balancing you have to think some other solution even if you select to go with clustering. (if am not wrong)
Thanks
John

[quote user="suniljk7"]
SQL Server Clustering only help to aviod downtime if any working node fails or if you need to do some maintanence work on any node. This can't be used for Load Balancing. . .
[/quote]
We're not putting in any load-balancing. This is a very low-concurrency environment, where 0-4 users will be online at any given time. So scalability is not a priority.
They're just going to be querying 100's of GB at a time, with some of their historical archive requests and such.
Also, Sql2000DBA the problem is that archive data is USED by active applications, and that's why things are so large - we'll go back 2 years to create ad-hoc trend analysis.
Thank you both for your input.

[quote user="walkair"]
I'm a little bit confused here - you start with "...HUGE SQL cluster..." and you finish with "...this one huge machine...".
[/quote]

Sorry, yes the "HUGE SQL cluster" consists of 2 machines, each having40 GB RAM, 4 CPU's/dualcore/hyperthreaded, clustered together to looklike one machine. Active/Passive configuration.

Can I Cluster together one super-powerful server, with a medium-strength server as the failover?

Before conslidating all the servers and dumping them onto a cluster I'd highly recommend testing based approach. Measure performance metrics for each of the applications - memory utilization, CPU utilization, IO, their individual SLA's.. etc. does any individual application have custom requirements? if you dump all the databases onto one server..all the databases will need to be offline when you need to do maintenance for one of the databases.. you could have them querying the databases but the dabatase undergoing maintenance will chew up the resources and might starve other applications..
I would recommend build the cluster, throw one database, get the performance numbers, then add one application at a time each time noting the performance numbers and measure how the resource utilization increases - in terms of IO, memory, CPU etc.. perhaps you have one resource hungry application that can kill other databases.. it might need tuning..etc..
do a quick search for database consolidation and you will find a white paper. I'd recommend you read it first.. it has some wonderful information on what to watch out for, what kind of information you need to collect from each server/database before migration etc..

[quote user="ndinakar"]
Beforeconslidating all the servers and dumping them onto a cluster...
[/quote]
Excellent, excellent information - thank you!
It seems the biggest pros to consolidation are reducing licensing fees, and reducing DBA-interventio, maintenance. And, my biggest concern is that one little crash could cause one big problem. My second biggest concern is that one greedy app could bring down the whole server...the whole everything.
Jason

>>>It seems the biggest pros to consolidation are reducing licensing fees, and reducing DBA-interventio, maintenance. And, my biggest concern is that one little crash could cause one big problem. My second biggest concern is that one greedy app could bring down the whole server...the whole everything.
Absolutely..I am not suggesting you against consolidation.. I am suggesting you do a proper analysis/testing and do the consolidation one at a time.. so you fix the issues found before you put the next application in..

Can someone post a link to this white paper? I was unable to find it searching for database consolidation on this site. Thanks

[quote user="zekmoe"]
Can someone post a link to this white paper? I was unable to find it searching for database consolidation on this site. Thanks
[/quote]
http://www.microsoft.com/technet/prodtechnol/sql/2000/plan/sql2kcon.mspx#EAAA
This is a Microsoft SQL Server 2000 whitepaper. I haven't done the full gambit of research yet (obviously).
However, the concepts and principles are the same. Look to Appendix C, where there's an actual Worksheet. It will get you thinking along the right lines of thought.
HTH

I'm curious, You say that you have 4 dual-core processors in this server with hyperthreading. What processor is this? The only dual-core Intel that I can think of that used hyperthreading is the Pentium D Extreme Edition.

[quote user="melvinlusk"]
I'm curious, You say that you have 4 dual-core processors in this server with hyperthreading. What processor is this? The only dual-core Intel that I can think of that used hyperthreading is the Pentium D Extreme Edition.
[/quote]
My bad. it's a 4-processor machine, QUAD-core, no hyperthreading.

The Intel processors and chipsets that support HT technology can be found in:
http://www.intel.com/products/ht/hyperthreading_more.htm

[quote user="zekmoe"]
Can someone post a link to this white paper? I was unable to find it searching for database consolidation on this site. Thanks
[/quote]

The 64-bit platform offers some advantages over the 32-bit platformwith regard to SQL Server consolidation.
The decision to use multipleinstances of SQL Server is more a data management decision than atechnical limitation imposed by the platform
for more information check : SQL Server Consolidation on the 64-Bit Platform
https://www.microsoft.com/technet/prodtechnol/sql/2000/deploy/64bitconsolidation.mspx

Dinakar has some good advice. You need to determine the impact of combining your applications. Create a list of pros and cons. I didn't see any mention of Disaster Recovery. Determine how DR would be addressed. Would having one big server make it easier or more difficult to recover your applications within the agreed upon timeframes? Would your NIC(s) on the cluster be able to handle the load? What type of network traffic is generated by your applications today? Do you have periods of heavy batch processing with some or all of your applications and how would that impact the timing of maintenance jobs? Would reindexing present a problem on a combined server? What security issues would you face through consolidation? Could a possibility exist in the future where one application would require a SQL Server service pack or hot fix that your other applications have not certified? Just a few things to consider. There is no way any of us could tell you YES, combine the servers or NO, don't combine the servers, without a good understanding of each application and the type of loads placed against your current database servers. Start with a list of pros and cons along with some benchmarks and your decision will begin to become clear.
This probably was not the answer you wanted, but it's worked for me.
Good luck.
Dave

All above points mentioned by SQL gurus are perfect solution but I will advice you to look from archiving and purging angle also which will help you to reduce the size of the databases and give you better performance. I have seen many environments where hardware is upgraded without considering archiving and purging. It is impossible that all application users are always using 1-2TB. In my experience only 30 to 40% data may be in use and rest is history. Please think on archiving and purging point.