Hardware/Software Configuration Advice

Hardware/Software Configuration Advice

Post by Richard Garret » Wed, 30 Jan 2002 12:34:04



I am looking for the best speed/reliability solution for SQL Server 2000.

I currently have the following hardware standing by waiting to be used:

2-IBM x200 Servers - Dual 1GHz PIII, 1GB RAM, ServRAID-4lx cards, 9GB
ultra160 drives, Windows 2000 Advanced Server, SQL Server 2000 (4 processor
licenses)

1-IBM EXP-300 with 6-IBM 18.2GB Ultra3 drives.

What would give me the best mix of performance/reliability using both
servers accessing the SQL databases stored on the IBM EXP-300?  Clustering?
Load Balancing?  Both?

Thanks,

Richard Garrett

 
 
 

Hardware/Software Configuration Advice

Post by Keith Kratochvi » Thu, 31 Jan 2002 00:37:29


This is a difficult question.  Please read my comments inline.

--
Keith, SQL Server MVP


Quote:> I am looking for the best speed/reliability solution for SQL Server 2000.

> I currently have the following hardware standing by waiting to be used:

By this, I will assume that this hardware is just laying around collecting
dust...

Quote:

> 2-IBM x200 Servers - Dual 1GHz PIII, 1GB RAM, ServRAID-4lx cards, 9GB
> ultra160 drives, Windows 2000 Advanced Server, SQL Server 2000 (4
processor
> licenses)

I am not familiar with IBM hardware....how many drives do you have for this
machine?

Quote:

> 1-IBM EXP-300 with 6-IBM 18.2GB Ultra3 drives.

What type of processor(s) and how much RAM does this machine have?

Your machine specs are a little less than complete.

Quote:

> What would give me the best mix of performance/reliability using both
> servers accessing the SQL databases stored on the IBM EXP-300?
Clustering?
> Load Balancing?  Both?

Clustering is available if you have SQL Server 2000 ENTERPRISE Edition.  Do
you have this version, or do you have the standard edition?  Anyway,
clustering is only a "high availability" option.....not a "performance"
option.  Also, as far as I know, you cannot do load balancing with SQL
Server.

It sounds like you want the 300 to be your database server....

Let me give you a quick rundown of "performance" items.
*RAM* - SQL Server likes lots of RAM.  If you browse any of the questions on
this newsgroup, you will see lots of "how come SQL Server is using up my
RAM?" type of questions.  This behavior is by design.  Because of this, it
is best to have the SQL Server box be a dedicated machine (no other apps on
it).
*Processors* - these serve as the data cruncher and the traffic cop within
the machine.  The CPU does lots of work....therefore 2 CPUs would be a good
starting point.  If you have a machine that can scale beyond two, that might
be a good thing....then again, you can always purchase a bigger and better
box later, right?
*disk* - you probaly want to have your data stored on some type of RAID
array to guarantee that a drive failure does not bring you down.  The RAID
you choose also plays a part in the performance.  I am not a hardware
expert, but I know that there is lots of good information within Kalen
Delaney's book "Inside SQL Server 2000."  Also, it is best to store your SQL
Data separate from your SQL Logs which should also be separate from your
operating system partition which could also be separate from tempdb and the
pagefile....  The more disks you have, the faster your machine will be able
to retrieve data off the disks into RAM and on to your application
*application/database* - application design and database design are two
things that are often overlooked.  You could have fantastic hardware, but a
poor database or application design and get mediocre performance....or you
could have average hardware with a great database and application design,
and your applicaion might really perform well.

Quote:

> Thanks,

> Richard Garrett

I hope that you have found my comments helpful.