Hi all,

We are afraid to start to hit the limit of the capacity on our production
server and we are starting to think about scalability. We would like to
scale out SQL Server and I wonder if it is possible to load balance SQL
server. Is there any third paty software able to do that?

It seems that SQL Server clustering is not helping as it seems to be just a
fail-over solution. Like if one node goes down, the other node will take the
work. Am i right or does SQL Server cluster can also do load balancing by
the mean of sharing the workload?

Well as you see i am a little bit new into all of this, then some guidance
would be greatly appreciated.

Also in the same time i would like to know how can i measure the maximum
load my sql server can take? How can i know when the performance will start
to deteriorate? Should i buy some specific software to do that? Should it be
a feature of a load test software for applications or is it better to have a
specific tool, if there is any?

Sorry for the overwheling number of questions, it is just reflecting the
fact i don't know much on the subject.

Thanks in advance.

Best regards,

Francois

You are correct in that SQL clustering is a fail-over solution, not a
scalability solution.

You can measure throughput on your SQL server a number of ways, one of the
best being to take a "black box" profile trace for an hour and play it back
at maximum speed. The ratio of recording time vs. playback time wil tell
you how much capability you have left. The downside is that this requires
taking your server offline for an extended period of time. This is best
done on a new server before going live.

There are publicly available tools that can individually stress components
so you can find your maximum IO rate (http://www.iometer.org/).

In general, SQL servers scale up, not out. That means purchasing a larger
box. Some parts of your system may be offloaded onto a read-only system,
using replication or log shipping to synchronize them, but what can be moved
varies greatly by application.

Finally, you should purchase and install a good monitoring solution to track
performance metrics and alert when they reach certain thresholds. Idera
makes a good basic one (www.idera.com) and Quest makes a very good complex
one (www.quest.com). The SQLH2 toolkit from Microsoft (FREE!!) has
performance logging and basic reporting capability. Measuring where you are
is the first step towards understanding where you need to be.

Geoff N. Hiten
Microsoft SQL Server MVP


[quoted text, click to view]