3 server active cluster

Does anyone have any thoughts on how to setup and configure a SQL cluster of
three ACTIVE servers. I'm familiar with a 2 node, active/passive and
active/active cluster but have not seen much published on a 3 or 4 node
active(n) cluster. Thanks! - Mike
Hi
No different to a 2 node. The big descision you have to make is onto which
machine an instance can fail over. If you were to allow enough resouces for
one machine to handle all 3 server's instances, you have a lot of unused
resources.
Regards
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Mike" wrote:

> Does anyone have any thoughts on how to setup and configure a SQL cluster of
> three ACTIVE servers. I'm familiar with a 2 node, active/passive and
> active/active cluster but have not seen much published on a 3 or 4 node
> active(n) cluster. Thanks! - Mike
|||I have another 3 server cluster. I want an active/passive and then a dev as a last resort for failover. The dev box is going to have either VMWare or Virtual Server because I am using this as a last resort for failover for Exchange, too.
You can call me crazy but will this work?
Kami

Quote:

Originally posted by Mike Epprecht (SQL MVP)
Hi
No different to a 2 node. The big descision you have to make is onto which
machine an instance can fail over. If you were to allow enough resouces for
one machine to handle all 3 server's instances, you have a lot of unused
resources.
Regards
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Mike" wrote:

> Does anyone have any thoughts on how to setup and configure a SQL cluster of
> three ACTIVE servers. I'm familiar with a 2 node, active/passive and
> active/active cluster but have not seen much published on a 3 or 4 node
> active(n) cluster. Thanks! - Mike

3 queries about active/active and multi-instance

Hi,
1. What is the use of Multiple-Instances of Sql server?
2. If I have only 1 Database, which Mode can I use (Active/Active or
Active/Passive) and why?
3. What is the difference between Active/Active and Active/Passive modes
other than the fact that only Active mode holds the shared device at any
given point of time.
Thanks in advance
Pankaj A. Chitriv
Inline.
joe.
"Pankaj" <pankajchitriv@.rediffmail.com(donotspam)> wrote in message
news:E1CE6DD0-D8A1-4B45-BF58-B309F9BAF770@.microsoft.com...
> Hi,
> 1. What is the use of Multiple-Instances of Sql server?
To run multiple instances of SQL Server in the same machine. Look up
multi-instance in BOL for more info. There are trade-offs to running
multiple instances vs. multiple databases in a single instance. Issues like
administration, security, isolation, memory management, real memory size,
etc... are just some that you need to be aware of.

> 2. If I have only 1 Database, which Mode can I use (Active/Active or
> Active/Passive) and why?
Active/passive. A SQL Server database can be driven by 1 and only 1
instance/engine at any time. It is a shared nothing architecture. Suggest
you review MS failover clustering concepts to get a better feel of how this
works. The term active/active really is kind of a marketing term. It really
means both machines are actively doing work on different databases, not both
machines are servicing the same database.
Btw, active/active and active/passive are actually obsolete terms since SQL
Server does support up to 8 nodes now and up to16 instances (not hard limit
but good luck with PSS if you go beyond). Multiple instance cluster is more
appropriate especially when you get into large clusters.

> 3. What is the difference between Active/Active and Active/Passive modes
> other than the fact that only Active mode holds the shared device at any
> given point of time.
Once you understand what active/active and active/passive mean in the SQL
Server world, this will become very clear. That aside, there are significant
considerations for configuration when you have multiple active nodes in a
cluster. You have to be smart about resource allocation and management and
failover strategies.

> Thanks in advance
> Pankaj A. Chitriv
|||Thanks Joe.
Regards
Pankaj
"joe." wrote:

> Inline.
>
> joe.
> "Pankaj" <pankajchitriv@.rediffmail.com(donotspam)> wrote in message
> news:E1CE6DD0-D8A1-4B45-BF58-B309F9BAF770@.microsoft.com...
> To run multiple instances of SQL Server in the same machine. Look up
> multi-instance in BOL for more info. There are trade-offs to running
> multiple instances vs. multiple databases in a single instance. Issues like
> administration, security, isolation, memory management, real memory size,
> etc... are just some that you need to be aware of.
> Active/passive. A SQL Server database can be driven by 1 and only 1
> instance/engine at any time. It is a shared nothing architecture. Suggest
> you review MS failover clustering concepts to get a better feel of how this
> works. The term active/active really is kind of a marketing term. It really
> means both machines are actively doing work on different databases, not both
> machines are servicing the same database.
> Btw, active/active and active/passive are actually obsolete terms since SQL
> Server does support up to 8 nodes now and up to16 instances (not hard limit
> but good luck with PSS if you go beyond). Multiple instance cluster is more
> appropriate especially when you get into large clusters.
> Once you understand what active/active and active/passive mean in the SQL
> Server world, this will become very clear. That aside, there are significant
> considerations for configuration when you have multiple active nodes in a
> cluster. You have to be smart about resource allocation and management and
> failover strategies.
>
>

3 nodes active/active/passive cannot failover

Hi,
I created 2 nodes active/passive and tested the failover.
It worked.
I added a 3rd nodes as active.
Now nothing can failover to the passive.
Any suggestions where I should start troubleshooting.
Thanks in advance.
I'd start by checking the NT eventlogs for any related error, and if
necessary checking the cluster log.
It would also help to observe more closely the behavior of the cluster when
you try to move the group to the 'passive node' Do you see certain resources
online there at least briefly? Does this happen to all the groups? If it's
just some group(s), it could be because you have not done the necessary setup
on the added node as required by the app in the group.
Linchi
"sqlapprentice" wrote:

> Hi,
> I created 2 nodes active/passive and tested the failover.
> It worked.
> I added a 3rd nodes as active.
> Now nothing can failover to the passive.
> Any suggestions where I should start troubleshooting.
> Thanks in advance.
|||Thank you Linchi,
I talked to MS premier support and I ran the SQLDiag.
They could not find anything.
SQLCluster services keeps failing.
Do I have a problem with my registry or ODBC or name resolution?
"Linchi Shea" wrote:
[vbcol=seagreen]
> I'd start by checking the NT eventlogs for any related error, and if
> necessary checking the cluster log.
> It would also help to observe more closely the behavior of the cluster when
> you try to move the group to the 'passive node' Do you see certain resources
> online there at least briefly? Does this happen to all the groups? If it's
> just some group(s), it could be because you have not done the necessary setup
> on the added node as required by the app in the group.
> Linchi
> "sqlapprentice" wrote:
|||Have you checked the Possible owners for the SQL resources?
SQLDiag is almost useless in troubleshooting a cluster issue. SQL
MPSReports gathers the information PSS needs.
Can the cluster resources move to all nodes?
32 bit or 64 bit?
Is this your setup?:
Instance one can live on node 1 or 2
Instance two can live on node 3 or 2
Kevin Hill
3NF Consulting
www.3nf-inc.com/NewsGroups.htm
www.expertsrt.com - not your average tech Q&A site
"sqlapprentice" <sqlapprentice@.discussions.microsoft.com> wrote in message
news:BF44A9E3-A82D-4D1B-A4A3-0D45917C3E1E@.microsoft.com...[vbcol=seagreen]
> Thank you Linchi,
> I talked to MS premier support and I ran the SQLDiag.
> They could not find anything.
> SQLCluster services keeps failing.
> Do I have a problem with my registry or ODBC or name resolution?
> "Linchi Shea" wrote:
|||Can you do the following:
1. Stop the SQL Server
2. With the entire group shut down, move the group to another node
3. If it moves over, go to the Service Control applet, and start the SQL
Server service
4. Shut it down and repeat on the other node
5. If this works, then you cluster config is definitely the problem.
6. If something doesn't work, you are going to get an error of some kind in
the event logs that points to the issue
Mike
http://www.solidqualitylearning.com
Disclaimer: This communication is an original work and represents my sole
views on the subject. It does not represent the views of any other person
or entity either by inference or direct reference.
"Kevin3NF" <Kevin@.DontNeedNoSpam3NF-inc.com> wrote in message
news:Ou2BG4AQGHA.5400@.TK2MSFTNGP09.phx.gbl...
> Have you checked the Possible owners for the SQL resources?
> SQLDiag is almost useless in troubleshooting a cluster issue. SQL
> MPSReports gathers the information PSS needs.
> Can the cluster resources move to all nodes?
> 32 bit or 64 bit?
> Is this your setup?:
> Instance one can live on node 1 or 2
> Instance two can live on node 3 or 2
> --
> Kevin Hill
> 3NF Consulting
> www.3nf-inc.com/NewsGroups.htm
> www.expertsrt.com - not your average tech Q&A site
>
> "sqlapprentice" <sqlapprentice@.discussions.microsoft.com> wrote in message
> news:BF44A9E3-A82D-4D1B-A4A3-0D45917C3E1E@.microsoft.com...
>
|||Hi Michael,
I found out that the MDAC and the registry were missing the named instance
entries.
Not sure what happen.
I fixed the registry and MDAC and the clusters are working now.
I cannot understand why adding a third cluster node as active created so
much problems.
Thanks again for the great help.
"Michael Hotek" <mike@.solidqualitylearning.com> wrote in message
news:Oi73qmNQGHA.4900@.TK2MSFTNGP09.phx.gbl...
> Can you do the following:
> 1. Stop the SQL Server
> 2. With the entire group shut down, move the group to another node
> 3. If it moves over, go to the Service Control applet, and start the SQL
> Server service
> 4. Shut it down and repeat on the other node
> 5. If this works, then you cluster config is definitely the problem.
> 6. If something doesn't work, you are going to get an error of some kind
in[vbcol=seagreen]
> the event logs that points to the issue
> --
> Mike
> http://www.solidqualitylearning.com
> Disclaimer: This communication is an original work and represents my sole
> views on the subject. It does not represent the views of any other person
> or entity either by inference or direct reference.
>
> "Kevin3NF" <Kevin@.DontNeedNoSpam3NF-inc.com> wrote in message
> news:Ou2BG4AQGHA.5400@.TK2MSFTNGP09.phx.gbl...
message[vbcol=seagreen]
necessary
>

3 Active and 1 Passive Node Cluster in Windows 2003 Server with SQL 2000 SP4

We are looking at setting up the following cluster:
3 Active nodes:
Each server needs to be completely distinct with its own unique
database and its own virtual server and ip. These nodes do not share
the same storage with the other active nodes. Active nodes must only
process for their own db's on the server itself
1 Passive node:
Common backup node for all the active nodes. Cluster service must be
able to transfer the disk ownership from the failed active node to this
one.
Is this possible with Windows 2003 and SQL Server SP4? I'm a little
concerned due to the way you need to apply sp4 to a SQL cluster.
Any advice would be much appreciated
Bill
You are close but are missing some key concepts with SQL clustering. There
are two distinct components involved, nodes and instances. Nodes are the
physical machines that comprise the cluster. Instances are the virtual SQL
servers. A node can host zero or more instances. If you set up a four node
cluster, you can park an instance on each of the three nodes with the fourth
node designated as the first failover node for each instance. As far as the
cluster is concerned, all host nodes are equal so you can allocate the
instances amongst the nodes as you see fit. From the client perspective,
all interaction is with the virtual server (instance) so you don't care
where the instance is actually hosted.
As for storage, you need a storage mechanism that is accessable to all
nodes, typicaly a SAN. The cluster service arbitrates ownership between the
specific nodes so all the resources necessary for any single instance to
function are always together on a single host node. The instance IP address
is one of the unique resources within the virtual server resource group.
As with all service packs, SP4 is cluster-aware, so that if you run the
installer from the node currently hosting an instance, it upgrades the local
binaries on all nodes for that particular instance. You will have to run
the Service Pack separately for each instance, however it is perfectly fine
to run instances at different SP and hotfix levels within a cluster.
FYI, I have built and managed a four-node, three instance cluster like you
have described, up through SP3a + hotfix 9?. I haven't tried SP4 yet but I
don't see any particular problems except for the already resolved AWE issue.
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
<bcrenshaw99@.yahoo.com> wrote in message
news:1124814061.497589.15020@.g49g2000cwa.googlegro ups.com...
> We are looking at setting up the following cluster:
> 3 Active nodes:
> Each server needs to be completely distinct with its own unique
> database and its own virtual server and ip. These nodes do not share
> the same storage with the other active nodes. Active nodes must only
> process for their own db's on the server itself
> 1 Passive node:
> Common backup node for all the active nodes. Cluster service must be
> able to transfer the disk ownership from the failed active node to this
> one.
>
> Is this possible with Windows 2003 and SQL Server SP4? I'm a little
> concerned due to the way you need to apply sp4 to a SQL cluster.
> Any advice would be much appreciated
> Bill
>
|||Sorry, I meant to ask if 3 separate clusters could share a common node?
Node: SQLSRV1
Cluster: SQLCLUST1
Instance: sqlapp1
Node: SQLSRV2
Cluster: SQLCLUST2
Instance: sqlapp2
Node: SQLSRV3
Cluster: SQLCLUST3
Instance: sqlapp3
Node: SQLSRV4
Cluster: SQLCLUST1, SQLCLUST2, SQLCLUST3
Instance: sqlapp1
Instance: sqlapp2
Instance: sqlapp3
|||No. A server can participate in only one cluster. A cluster can support up
to 16 SQL instances and 4 nodes under SQL 2000.
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
<bcrenshaw99@.yahoo.com> wrote in message
news:1124831740.683769.184000@.g47g2000cwa.googlegr oups.com...
> Sorry, I meant to ask if 3 separate clusters could share a common node?
> Node: SQLSRV1
> Cluster: SQLCLUST1
> Instance: sqlapp1
>
> Node: SQLSRV2
> Cluster: SQLCLUST2
> Instance: sqlapp2
>
> Node: SQLSRV3
> Cluster: SQLCLUST3
> Instance: sqlapp3
>
> Node: SQLSRV4
> Cluster: SQLCLUST1, SQLCLUST2, SQLCLUST3
> Instance: sqlapp1
> Instance: sqlapp2
> Instance: sqlapp3
>

2-node cluster (Active/Passive) but with a catch!

Hi,
We have 2 blade servers and we need a clustering solution which will run
win2k3 and SQL 2005 in an Active/Passive mode. The catch is that our SAN will
not be in for another 5 weeks. The business needs to go live so I am trying
to find out what is the best way to handle this. I am thinking the following:
Install sql server 2005 on one server and run it as a stand alone until we
get the SAN. Then configure the second with the SAN and MSCS, migrate the
databases, rebuild the first server, install MSCS and join it to the cluster.
What do you guys think?
--
Senior DBA
MSc in CS, MCSE4, ITIL, IBM Certified MQ 5.3 Administrator
"I saw it work in a cartoon once so I am pretty sure I can do it."
It seems like you have a grip on the problem/solution.
Without some form of common storage resources, I don't see that you have
much of a choice.
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
"Sas" <Sas@.discussions.microsoft.com> wrote in message
news:7206318F-DC16-4C51-9D7A-DD59C441D863@.microsoft.com...
> Hi,
> We have 2 blade servers and we need a clustering solution which will run
> win2k3 and SQL 2005 in an Active/Passive mode. The catch is that our SAN
> will
> not be in for another 5 weeks. The business needs to go live so I am
> trying
> to find out what is the best way to handle this. I am thinking the
> following:
> Install sql server 2005 on one server and run it as a stand alone until we
> get the SAN. Then configure the second with the SAN and MSCS, migrate the
> databases, rebuild the first server, install MSCS and join it to the
> cluster.
> What do you guys think?
> --
> --
> Senior DBA
> MSc in CS, MCSE4, ITIL, IBM Certified MQ 5.3 Administrator
> "I saw it work in a cartoon once so I am pretty sure I can do it."