sql server clustering: Hello Everyone,

Here is my situation:
I have stand alone sql server 2000 and with 2 databases. Unfortunately
that server, which was supposed to be able to handle the workload of both
database and database apps, is not able to do so. So I am looking into
setting up something to distribute the workload between more servers. I have
two other servers coming along my way, as well as a SAN system for the
storage of the SQL Data.
So I am wondering if SQL clustering would be useful in this situation and,
if so, what would I be looking at in configuring these servers? Right now, I
am just in the designing and planning stage, so I can change hardware and
storage design if necessary.

Thanks for the info. So I am in the wrong forum... Should I just head over

Here's what we have currently:
Dual Hyperthread Xeons @ 2.8 GHZ
2 GB or RAM
147 GB 15k SCSI U320 HDD

This single server can easily take care of the load and size requiments for
one of the databases. However, when we run the second database and custom
app for it, the performance levels on both apps drop severely. Plus, as we
quickly realized, our disk space would need to expand exponentially. We will
need around 1.5 terabytes to store our data for the second database and its
app.

I was considering purchasing a dual-processor 3.4 GHZ Xeon with 4 GB of RAM.

I was also going to purchase a SAN storage solution:
http://www.qlogic.com/simplify/sckwinserv.asp - Fiber Channel Kit
http://www-1.ibm.com/servers/storage/disk/ds/ds400/index.html - IBM
TotalStorage DS400
as well as 13x 147GB 10k U320 SCSI drives to put into it; this should solve
my storage problems.

That is all the information I can give at this moment. If you need anything

Hi

A Cluster is for high availability and not load balancing a single instance.
If you can put one DB on each node, you have a solution. If they have to run
on the same node, it won't help performance.

Make sure your hardware is on the Windows HCL for clustering, otherwise you
will just have a lot of trouble.

http://www.microsoft.com/windowsserver2003/technologies/clustering/default.mspx

http://www.microsoft.com/sql/evaluation/features/reliable.asp

Regards
--------------------------------
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland

IM: mike@epprecht.net

MVP Program: http://www.microsoft.com/mvp

Blog: http://www.msmvps.com/epprecht/

[quoted text, click to view]

Hi

SQL Server does not support network load balancing. Once one instance can
work against a database at any one time.

What are your exact requirements?
What is your server load, how big are your databases?

Regards
--------------------------------
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland

IM: mike@epprecht.net

MVP Program: http://www.microsoft.com/mvp

Blog: http://www.msmvps.com/epprecht/

[quoted text, click to view]

Perhaps you should first look at what the bottleneck is. Is it memory?
CPU? Disk? It is possible that by adding the components that you're
lacking, you don't need two servers. As Mike said, clusters are for high
availability, not performance. I'd certainly start with adding memory. SQL
Server loves memory like I like a rack of BBQ ribs. ;-)

--
Tom

----------------------------------------------------
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com
..
[quoted text, click to view]
Here's what we have currently:
Dual Hyperthread Xeons @ 2.8 GHZ
2 GB or RAM
147 GB 15k SCSI U320 HDD

This single server can easily take care of the load and size requiments for
one of the databases. However, when we run the second database and custom
app for it, the performance levels on both apps drop severely. Plus, as we
quickly realized, our disk space would need to expand exponentially. We
will
need around 1.5 terabytes to store our data for the second database and its
app.

I was considering purchasing a dual-processor 3.4 GHZ Xeon with 4 GB of RAM.

I was also going to purchase a SAN storage solution:
http://www.qlogic.com/simplify/sckwinserv.asp - Fiber Channel Kit
http://www-1.ibm.com/servers/storage/disk/ds/ds400/index.html - IBM
TotalStorage DS400
as well as 13x 147GB 10k U320 SCSI drives to put into it; this should solve
my storage problems.

That is all the information I can give at this moment. If you need anything
else, please let me know. Thanks again!

We do need more space. A LOT more space, which was the reason for possibly
purchasing the SAN kit. And I was thinking that a seperate server will help
minimize any conflicts between the two databases.

Our current server has a huge pagefile (plus a severe performance drop) and
very little physical memory (only a few megabytes) available when both
programs are run simultaneously. When only the first database and app is
run, we have plenty of physical memory (about 1 GB free) and the pagefile use
drops dramatically. So then I concluded that the second database/app is the
culprit for drop in performance and we need a heftier server for that
database/app.


Thanks for the help!

Dark_15, I think you missing what the guys are trying to tell you. Let me
try and restate things as I see them:

Facts:
1) You need more disk space for the new app, lots of space.
2) A SAN would indeed give you the needed disk space.
3) The current machine does not have the needed disk space for the new app.
4) You need more RAM to run the new app, lots of RAM, though the exact
amount is known, due to excessive paging in the current config.
5) Adding more RAM or a moving to a new machine with lots of RAM would solve
the need for more RAM.
6) The current machine does not have the needed RAM for the new app.
7) Clustering is for High Availability and as such will not help with facts
1-6.
8) Clustering requires running Enterprise Edition of SQL & Windows, and as
such is more expensive.
9) You don't ever need to cluster to gain better performance.
10) A second machine, attached to a new large SAN, with lots of RAM, and two
fast CPU's will work nicely. The new app will love the RAM, CPU's and disk
space.

I hope this clears things up. You really have not stated anything that makes
think you need to cluster SQL.

Cheers,

Rod

MVP - Windows Server - Clustering
http://www.nw-america.com - Clustering
http://msmvps.com/clustering - Blog

[quoted text, click to view]

Sorry, I've been out for a bit...

I hate to go off on a tangent in the wrong group, but let me see if this is
what you are saying:

Use the existing server (Server A):
Dual Hyperthread Xeons @ 2.8 GHZ
2 GB of RAM
147 GB 15k SCSI U320 HDD

Plus make 2 new servers:
Server B:
Dual Hyperthread 3.4 GHZ Xeon
2 GB of RAM
36 GB 15k RPM SCSI

Server C:
Dual Hyperthread 3.4 GHZ Xeon
4 GB of RAM
36 GB 15k RPM SCSI

On Server A, install the OS (Windows Server 2003 presumably) and the least
stressful application.
On Server B, install the OS and the most stressful application.
On Server C, install the OS and SQL 2000.

Purchase the SAN Kit to store all the actual data and have each machine
connected to it for quick response. Here are the specs on the SAN idea I am
thinking about implementing:
http://www.qlogic.com/simplify/sck3000.asp - Fiber Channel Kit
http://www-1.ibm.com/servers/storage/disk/ds/ds400/index.html - IBM
TotalStorage DS400
13x 147GB 10k U320 SCSI

Does that sound good? And then if I want something to ensure that we will
not have downtime on the SQL server, head back here for help with Clustering?

Oh and almost forgot: slap my software vendor... :-)

I couldn't agree more. If your middle tier is slow, then throwing more
servers at it will help. However, there is only one database server and it
should be capable of handling just the database loads you put on it. As
Rodney said, give it the disk and RAM resources it needs to do the job.

--
Tom

----------------------------------------------------
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com
..
[quoted text, click to view]
Hi

What about separating the application from the DB? Running an application
server and DB on the same machine does not help performance as both
processes contend for the same resources.

Turning your infrastructure into true multi-tier might help. If your
application has to run on the DB server, you have a bad application and slap
your vendor.

Regards
--------------------------------
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland

IM: mike@epprecht.net

MVP Program: http://www.microsoft.com/mvp

Blog: http://www.msmvps.com/epprecht/

[quoted text, click to view]

Why does your app need so much disk? Shouldn't that go for tour database
server?

--
Tom

----------------------------------------------------
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com
..
[quoted text, click to view]
Sorry, I've been out for a bit...

I hate to go off on a tangent in the wrong group, but let me see if this is
what you are saying:

Use the existing server (Server A):
Dual Hyperthread Xeons @ 2.8 GHZ
2 GB of RAM
147 GB 15k SCSI U320 HDD

Plus make 2 new servers:
Server B:
Dual Hyperthread 3.4 GHZ Xeon
2 GB of RAM
36 GB 15k RPM SCSI

Server C:
Dual Hyperthread 3.4 GHZ Xeon
4 GB of RAM
36 GB 15k RPM SCSI

On Server A, install the OS (Windows Server 2003 presumably) and the least
stressful application.
On Server B, install the OS and the most stressful application.
On Server C, install the OS and SQL 2000.

Purchase the SAN Kit to store all the actual data and have each machine
connected to it for quick response. Here are the specs on the SAN idea I am
thinking about implementing:
http://www.qlogic.com/simplify/sck3000.asp - Fiber Channel Kit
http://www-1.ibm.com/servers/storage/disk/ds/ds400/index.html - IBM
TotalStorage DS400
13x 147GB 10k U320 SCSI

Does that sound good? And then if I want something to ensure that we will
not have downtime on the SQL server, head back here for help with
Clustering?

Oh and almost forgot: slap my software vendor... :-)

Thank you very much everyone!

Hi

What about separating the application from the DB? Running an application
server and DB on the same machine does not help performance as both
processes contend for the same resources.

Turning your infrastructure into true multi-tier might help. If your
application has to run on the DB server, you have a bad application and slap
your vendor.

Regards
--------------------------------
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland

IM: mike@epprecht.net

MVP Program: http://www.microsoft.com/mvp

Blog: http://www.msmvps.com/epprecht/

[quoted text, click to view]

No, it doesn't. But since the drive is in there (and working), I am not
going to mess with it. It is more of a case of "If it is not broke, don't
fix it". Also, it is a bit different drive than what I have in my SAN and
for the sake of keeping everything the same, I am not going to add that to

Apparantley, yes. That wasn't my call (it was our vendor who recommended the
first server to us), but after we had so many problems I decided to look into
things myself. And that is why I am here now. All I can say is from what
little I knew about SQL and server performance before, I know just a little
more now... just enough to make sure never make a misake like that again.

[quoted text, click to view]

BTW, one of the things I'd look at is the disk queue length. You want that
as low as possible.

If I read your original description correctly, all this is running on a
SINGLE SCSI drive?

That's going to really kill performance, probably long before CPU.

Given all the other threads, the SAN is definitely a good idea. The more
spindles you can allocate to SQL Server the better.
Put your logs on one set, your data on another, keep OS local. Plan for
redundancy at this point also.

And I'd get 4 gig of RAM in all your machines, not 2. In fact I'd probably
be tempted to go with a slightly slower CPU if the budget is tight and you
can only afford one or the other.


[quoted text, click to view]