Win2003 Clustering on more than 2 nodes for SQL Server

#1

I'm attempting to validate whether it makes any sense to
have more than two nodes in a Win2003 / SQL 2000 cluster.
If so, how would it be configured and how would failover
work?

I've checked most of the Microsoft online resources and
can't find specific examples, etc. related to this.

Thanks.
Ron
ron.wildt (AT) regiscorp (DOT) com

#2

I have a working 4-node, 3-instance SQL cluster running on Server 2003. The
configuration and failover sequence issues are much like a 2-node cluster,
only more complex. The same tools work pretty much as you expect, you just
have more choices in each list. For me, it is a great solution since I get
to use the entire power of a host node with each instance and only had to
spend 25% more on hardware to do it.

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

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

"Ron" <anonymous (AT) discussions (DOT) microsoft.com> wrote

Quote:

I'm attempting to validate whether it makes any sense to
have more than two nodes in a Win2003 / SQL 2000 cluster.
If so, how would it be configured and how would failover
work?

I've checked most of the Microsoft online resources and
can't find specific examples, etc. related to this.

Thanks.
Ron
ron.wildt (AT) regiscorp (DOT) com

#3

So what happens in a 4 node cluster for SQL Server, where
3 nodes are 'active' and 1 is a standby node and 2 of the
active nodes fail?

Quote:

-----Original Message-----
With the arrival of larger cluster sizes (four and eight
nodes), the "A+Hs" topologies become important when the

cluster has a set of "A" nodes that are currently active
and a set of "Hs" nodes that are currently

Quote:

passive or in hot Standby mode. The larger clusters
enhance the support (active and/or passive) configurations

such as the preceding configuration because the
configurations can reduce the cost of one or more

Quote:

standby nodes across a larger set of active nodes.

For example, with a two-node cluster, the cost of an
active and/or passive configuration requires twice the

hardware for the same capacity, with eight nodes running
as seven active and one passive. The additional

Quote:

hardware increases the cost by only fifteen percent.

NOTE: With SQL Server 2000, you can go only upto 4 node
cluster.

With Windows 2003 clustering, you can have a true hot
spare. The Windows Clustering group has a new public

property: AntiAffinityClassNames. This property can
contain an arbitrary string of characters. In the

Quote:

event of a failover, if a group that is being failed over
has a string that is not empty in the

AntiAffinityClassNames property, the failover manager can
check all other nodes. If there are any nodes (that are in
the

Quote:

possible owners list for the resource) that are not
hosting a group with the same value in

AntiAffinityClassNames, those nodes are considered a
preferred target for failover. This value can take higher
priority over the

Quote:

Preferred Owners list.

For more information, please refer
KB 299631 Failover Behavior on Clusters of Three or More
Nodes
http://support.microsoft.com/default.aspx?scid=KB;EN-
US;299631

KB 296799 How to configure Windows clustering groups for
hot spare support
http://support.microsoft.com/default.aspx?scid=KB;EN-
US;296799

HTH,
Best Regards,

Uttam Parui
Microsoft Corporation

This posting is provided "AS IS" with no warranties, and
confers no rights.

Are you secure? For information about the Strategic
Technology Protection Program and to order your FREE

Security Tool Kit, please visit
http://www.microsoft.com/security.

Quote:

Microsoft highly recommends that users with Internet
access update their Microsoft software to better protect

against viruses and security vulnerabilities. The easiest
way to do this is to visit the following websites:

Quote:

http://www.microsoft.com/protect
http://www.microsoft.com/security/guidance/default.mspx

.

#4

You end up with more than one instance on a node. For example, MY nodes
have 32GB of RAM with each SQL instance allocating 28GB. If I lose a node,
I reset the MAX SERVER MEMORY to 9GB so I can manually stack instances on
nodes. If you lose a node, you should be able to count on having a live
person paying attention in a very short time frame. At least that is how
things work around here.

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

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

"Ron" <anonymous (AT) discussions (DOT) microsoft.com> wrote

Quote:

So what happens in a 4 node cluster for SQL Server, where
3 nodes are 'active' and 1 is a standby node and 2 of the
active nodes fail?

-----Original Message-----
With the arrival of larger cluster sizes (four and eight
nodes), the "A+Hs" topologies become important when the
cluster has a set of "A" nodes that are currently active
and a set of "Hs" nodes that are currently
passive or in hot Standby mode. The larger clusters
enhance the support (active and/or passive) configurations
such as the preceding configuration because the
configurations can reduce the cost of one or more
standby nodes across a larger set of active nodes.

For example, with a two-node cluster, the cost of an
active and/or passive configuration requires twice the
hardware for the same capacity, with eight nodes running
as seven active and one passive. The additional
hardware increases the cost by only fifteen percent.

NOTE: With SQL Server 2000, you can go only upto 4 node
cluster.

With Windows 2003 clustering, you can have a true hot
spare. The Windows Clustering group has a new public
property: AntiAffinityClassNames. This property can
contain an arbitrary string of characters. In the
event of a failover, if a group that is being failed over
has a string that is not empty in the
AntiAffinityClassNames property, the failover manager can
check all other nodes. If there are any nodes (that are in
the
possible owners list for the resource) that are not
hosting a group with the same value in
AntiAffinityClassNames, those nodes are considered a
preferred target for failover. This value can take higher
priority over the
Preferred Owners list.

For more information, please refer
KB 299631 Failover Behavior on Clusters of Three or More
Nodes
http://support.microsoft.com/default.aspx?scid=KB;EN-
US;299631

KB 296799 How to configure Windows clustering groups for
hot spare support
http://support.microsoft.com/default.aspx?scid=KB;EN-
US;296799

HTH,
Best Regards,

Uttam Parui
Microsoft Corporation

This posting is provided "AS IS" with no warranties, and
confers no rights.

Are you secure? For information about the Strategic
Technology Protection Program and to order your FREE
Security Tool Kit, please visit
http://www.microsoft.com/security.

Microsoft highly recommends that users with Internet
access update their Microsoft software to better protect
against viruses and security vulnerabilities. The easiest
way to do this is to visit the following websites:
http://www.microsoft.com/protect
http://www.microsoft.com/security/guidance/default.mspx

.

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Win2003 Clustering on more than 2 nodes for SQL Server

microsoft.public.sqlserver.clustering microsoft.public.sqlserver.clustering

Win2003 Clustering on more than 2 nodes for SQL Server - 08-18-2004 , 03:04 PM

Re: Win2003 Clustering on more than 2 nodes for SQL Server - 08-18-2004 , 03:27 PM

Re: Win2003 Clustering on more than 2 nodes for SQL Server - 08-19-2004 , 08:21 AM

Re: Win2003 Clustering on more than 2 nodes for SQL Server - 08-19-2004 , 08:35 AM