I have a very basic question regarding nodes in MSCS running
sqlserver2k. I'm trying to understand what it means to load-balance.

I thought load-balancing meant that if I have 3 nodes in the MSCS &
all running sqlserver2k, the nodes point to single database on a
shared storage. Basically, load-balance the query requests that come
in from the clients.

But after reading MSKB articles I wonder if tht is wrong? All
articles seems to say that the 2nd & 3rd nodes will also have the
replicated database of the first node. If so then if my database is
500GB, I will need at least 1.5TB of space on the shared storage to
have a load-balanced sqlserver2k of 3 nodes?

Thank you for shedding some light to my understanding of what it is to
operate sqlserver2k in a cluster!

Robert

Simple answer. MSCS is not a load-balance cluster implementation. Only one
instance can control a database at a time.

--
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com

I support the Professional Association for SQL Server
www.sqlpass.org

<Robert> wrote

On Tue, 26 Oct 2004 20:59:27 -0400, "Geoff N. Hiten"
<SRDBA (AT) Careerbuilder (DOT) com> wrote:

Thank you Geoff. So if I understood this right only one sqlserver2k
node can attach to a database at any given time. Hence no
load-balalncing can occur just by increasing the number of nodes in a
cluster.

In that sense is there an easy answer to how to scale sqlserver2k to
balance the load? For example you start with a quad processor box.
You find out that the CPU util is pegged & but can't add any more
CPUs. If disk subsystem is already running efficiently & you need to
relieve CPU util what options does one have?

Thank you for the continueddialog on a rather open-ended topic. I
hope I don't sound to dumb

Robert

<Robert> wrote

Get better programmers. :-)
(seriously, did have a case where code changes in one area basically has
given us 4x the performance since the start of the year. We started with
this one thing running on two servers, both CPU's pretty much pegged, then
one mostly pegged, and now one at about 50%).

Ok, assuming that's not the issue....

What we did was split our applications (web based mostly) so that all the
writes went to a main "publishing" server and this replicated out to
multiple servers from which data could be read only.

This doesn't work in all cases, but it's one way of approaching the problem.
If you need more writing, perhaps a version of merge replication may work
also.

In sum, part of it depends on your applications.

Don't worry about it, it's a large topic and I hardly know all there is to
know.

As Greg said, it is a large topic so here goes my $.02.

SQL just doesn't scale out. You do have distributed partitioned views but
there are way to many restrictions that make it impractical to
implement. Merge and two-way replication sound good until you realize all
the transactions still have to get back to all the nodes somehow. At best
these techniques can spread out peak impacts. At worst, they don't work as
you expect and you end up with corrupt data.

All is not lost, however. I find scaling UP a cluster to be a lot easier
than it used to be. I have successfully gone from a 2-node cluster to a
4-node cluster while replacing the 4-way boxes with 8-way hosts. It does
involve adding and removing and replacing nodes with new hardware but it is
possible. All this does is ease the transition to a new host system. You
still have to purchase the new boxes. IMHO, if your growth takes you out of
the 4-way capability range before the box is obsolete, you have a lot of
other issues besides expanding your SQL host systems.


--
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com

I support the Professional Association for SQL Server
www.sqlpass.org

<Robert> wrote

"Geoff N. Hiten" <SRDBA (AT) Careerbuilder (DOT) com> wrote

I'm going to add to this.

Besides code improvements, hardware improvements have helped us a lot.

We've gone from 6 SQL servers w/o much room for growth down to 4 with plenty
of room for growth. Part of this is simply 3 gig Xeons are a lot faster
than the 550Mhz Xeons our older boxes have.