Active/Active/Active/Passive and Database Mirroring

We have 4 systems that was initially planned to be joined into an
Active/Active/Active/Passive cluster with SQL 2005.
Can anyone offer a suggestion on the number of instances to install per node?
Does each instance need seperate Disks for the Database, Logs and TempDB?
If we stay with this configuration will Database mirroring still be an
option with the configuration once it is supported by MS.
Instances are installed to the entire cluster, but can be set to prefer
certain nodes. I personally like N-1 clusters (N nodes, N-1 Instances),
just to simplfy management, but other that is not an absolute, inflexible
standard.
Each instance has completely separate resources; IP address, Data disks, Log
Disks, TempDB, and Network Name. Each instance works and appears as a
separate and distinct SQL Server.
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"Chad T" <ChadT@.discussions.microsoft.com> wrote in message
news:B13AE62F-89A3-405F-9F14-FE979336CC7B@.microsoft.com...
> We have 4 systems that was initially planned to be joined into an
> Active/Active/Active/Passive cluster with SQL 2005.
> Can anyone offer a suggestion on the number of instances to install per
> node?
> Does each instance need seperate Disks for the Database, Logs and TempDB?
> If we stay with this configuration will Database mirroring still be an
> option with the configuration once it is supported by MS.
>
|||Thank you for such a fast response.
In the active/active/active/passive configuration is it possible to load
balance the 3 instances over 3 nodes?
Ideally we would like to create 1 instance split over 3 nodes... is this
possible (see EX1) or do we have to do something like EX2?
Ex1: (1 instances - share over 3 nodes)
Node A = Instance1 (load balanced)
Node B = Instance1 (load balanced)
Node C = Instance1 (load balanced)
Node D = Passive
Ex2: (4 nodes - 1 passive = 3 instances split over 3 nodes)
Node A = Instance1 (load balanced)
Node B = Instance2 (load balanced)
Node C = Instance3 (load balanced)
Node D = Passive
"Geoff N. Hiten" wrote:

> Instances are installed to the entire cluster, but can be set to prefer
> certain nodes. I personally like N-1 clusters (N nodes, N-1 Instances),
> just to simplfy management, but other that is not an absolute, inflexible
> standard.
> Each instance has completely separate resources; IP address, Data disks, Log
> Disks, TempDB, and Network Name. Each instance works and appears as a
> separate and distinct SQL Server.
> --
> Geoff N. Hiten
> Senior Database Administrator
> Microsoft SQL Server MVP
>
> "Chad T" <ChadT@.discussions.microsoft.com> wrote in message
> news:B13AE62F-89A3-405F-9F14-FE979336CC7B@.microsoft.com...
>
>
|||SQL Clustering is a failover technology only. It does not load-balance.
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"Chad T" <ChadT@.discussions.microsoft.com> wrote in message
news:DC9DA36B-0D19-4E7E-9CD8-03F3A71F389B@.microsoft.com...[vbcol=seagreen]
> Thank you for such a fast response.
> In the active/active/active/passive configuration is it possible to load
> balance the 3 instances over 3 nodes?
> Ideally we would like to create 1 instance split over 3 nodes... is this
> possible (see EX1) or do we have to do something like EX2?
> Ex1: (1 instances - share over 3 nodes)
> Node A = Instance1 (load balanced)
> Node B = Instance1 (load balanced)
> Node C = Instance1 (load balanced)
> Node D = Passive
> Ex2: (4 nodes - 1 passive = 3 instances split over 3 nodes)
> Node A = Instance1 (load balanced)
> Node B = Instance2 (load balanced)
> Node C = Instance3 (load balanced)
> Node D = Passive
>
> "Geoff N. Hiten" wrote:
|||Just so I understand this correctly, if we go with an
Active/Active/Active/Passive cluster we would have a setup of at least this,
correct?
Is the minimum number of instances in this configuration 3?
Ex2: (4 nodes - 1 passive = 3 instances split over 3 nodes )
Node A = Instance1
Node B = Instance2
Node C = Instance3
Node D = Passive
"Geoff N. Hiten" wrote:

> SQL Clustering is a failover technology only. It does not load-balance.
> --
> Geoff N. Hiten
> Senior Database Administrator
> Microsoft SQL Server MVP
>
> "Chad T" <ChadT@.discussions.microsoft.com> wrote in message
> news:DC9DA36B-0D19-4E7E-9CD8-03F3A71F389B@.microsoft.com...
>
>
|||You can have however many instances you want as long as you have enough
resoruces (disks, IPs, and network names).
It's a reasonable design in a four-node cluster to have three SQL2005
instances, each of which runs on a separate node and the 4th node is left as
a spare.
In addition to configuring where the instances should normally run, you also
need to plan/configure the failover behavior. For instane, one design would
be for the instance on Node A to failover only to Node D, and do the same
for the instances on Node B and Node C. In other words, you designate Node D
to be the only and common spare. This design would not give you any HA if
Node D fails. But you may consider that as by design. And if this is no good
for your requirements, choose a different design. The point is that you need
to be explicit about the failover behavior, and communicate that to the
users so that hopefully they know what to expect.
FWIW, I usually call this a N+1 cluster following the Unix tradition, where
N is the umber of 'active nodes' and 1 is the spare.
'Active/Active/Active/Passive' is just too mouthful.
Linchi
"Chad T" <ChadT@.discussions.microsoft.com> wrote in message
news:EEF10540-FCF0-4411-AFC0-44ECDE2B6BFD@.microsoft.com...[vbcol=seagreen]
> Just so I understand this correctly, if we go with an
> Active/Active/Active/Passive cluster we would have a setup of at least
> this,
> correct?
> Is the minimum number of instances in this configuration 3?
> Ex2: (4 nodes - 1 passive = 3 instances split over 3 nodes )
> Node A = Instance1
> Node B = Instance2
> Node C = Instance3
> Node D = Passive
>
> "Geoff N. Hiten" wrote:
|||I understand that you can have many instances but what is the minimum number
of instances for an active/active/active/passive configuration?
I assume it is 3, but I thought I would get clarification.
"Linchi Shea" wrote:

> You can have however many instances you want as long as you have enough
> resoruces (disks, IPs, and network names).
> It's a reasonable design in a four-node cluster to have three SQL2005
> instances, each of which runs on a separate node and the 4th node is left as
> a spare.
> In addition to configuring where the instances should normally run, you also
> need to plan/configure the failover behavior. For instane, one design would
> be for the instance on Node A to failover only to Node D, and do the same
> for the instances on Node B and Node C. In other words, you designate Node D
> to be the only and common spare. This design would not give you any HA if
> Node D fails. But you may consider that as by design. And if this is no good
> for your requirements, choose a different design. The point is that you need
> to be explicit about the failover behavior, and communicate that to the
> users so that hopefully they know what to expect.
> FWIW, I usually call this a N+1 cluster following the Unix tradition, where
> N is the umber of 'active nodes' and 1 is the spare.
> 'Active/Active/Active/Passive' is just too mouthful.
> Linchi
>
> "Chad T" <ChadT@.discussions.microsoft.com> wrote in message
> news:EEF10540-FCF0-4411-AFC0-44ECDE2B6BFD@.microsoft.com...
>
>
|||Well, 'active/active/active/passive configuration' is not really a technical
term that everybody agrees to have a common meaning. Intuitively, I guess
it's self evident that you are talking about three 'active nodes' plus one
'passive node'. So really there is nothing to clarify.
Linchi
"Chad T" <ChadT@.discussions.microsoft.com> wrote in message
news:21206D26-AF9D-4CCB-BD6E-92E1FFB183D1@.microsoft.com...[vbcol=seagreen]
>I understand that you can have many instances but what is the minimum
>number
> of instances for an active/active/active/passive configuration?
> I assume it is 3, but I thought I would get clarification.
>
>
> "Linchi Shea" wrote:
|||Well, if you want active/active/active/passive, I count 3 distinct SQL
instances there (active/active/active). So, yes, the minimum number, in
that configuration, would be 3 instances. As Geoff pointed out, if you
have 1 instance, it would only run on 1 node and the other 3 nodes would
be idle no matter how hard node A was working (Ex2 in your scenarios).
*mike hodgson*
http://sqlnerd.blogspot.com
Chad T wrote:
[vbcol=seagreen]
>I understand that you can have many instances but what is the minimum number
>of instances for an active/active/active/passive configuration?
>I assume it is 3, but I thought I would get clarification.
>
>
>"Linchi Shea" wrote:
>
|||Prefect! thanks...
"Mike Hodgson" wrote:

> Well, if you want active/active/active/passive, I count 3 distinct SQL
> instances there (active/active/active). So, yes, the minimum number, in
> that configuration, would be 3 instances. As Geoff pointed out, if you
> have 1 instance, it would only run on 1 node and the other 3 nodes would
> be idle no matter how hard node A was working (Ex2 in your scenarios).
> --
> *mike hodgson*
> http://sqlnerd.blogspot.com
>
> Chad T wrote:
>