Active Active Passive Cluster setup

I am looking for setup information for a active/active/passive cluster. We currently have a active/passive cluster running 64 bit SQL. I would like to convert the existing passive node to an active node and setup a new SQL instance. In addition, I would like to add a third server to the cluster environment(passive). In the case of a failover on either of the 2 active nodes, I would like this 3rd node to be the primary to failover to.

can someone give me some details on:

A) How do I begin to convert the current passive node2 to active (install sql instance, SAN volumes to be made available to each server etc). Do ALL SAN volumes (node1 and 2) need to to be mapped to each server?

Assuming that we do not yet have the 3rd node (the planned passive node), would be be better to change the existing active/passive to active/active and then add the passive node later, or would it be more advisable to wait until the 3rd server (passive) is available.

From SQL Server 2000 Active/Activie or Active/Passive is just a term used to describe the cluster fashion and not a setup issue. By adding necessary disk resources and groups you can turn A-P to A-A.

For instance if you currently have a instance with 3 drives (E, F, and G), to create another instance, you can not use those drives, so you need to get more volumes on your SAN for H, I, J etc.

Except for the actual installation process, all cluster nodes in SQL 2000 are equivalent. During the installation, you run the install app from the node that currently hosts the disk resource you intend to base the SQL instance on.

Keep in mind that Windows 2003 Server and SQL Server 2000 clustering is considered share-nothing technology. What this means is that each instance of SQL Server must have its own exclusive shared array. So, if you have an Active/Active configuration, each instance of SQL Server, one on each node, must have it own exclusive access to a shared array. So instance 1 must have exclusive access to shared array 1, for example, and instance 2 must have exclusive access to shared array 2. Instance 1 cannot talk to shared array 2, and vice versa.

In regard to your question about waiting for the 3rd node. There is no technical requirement to do this in any order. But to make your life easier, here is what I would do.

Currently you have one active node, and one passive node. What I would do is to wait for the third node, and make it an active node, and leave the current passive node passive. This way, you won't have to reinstalled SQL Server. If you change the current passive node to an active node, and later add the new node, which will be passive, then you will have to reinstall SQL Server on both of the active nodes, which is a lot of extra work and down time.

Thanks bradmcgehee and Satya. I did a lot of reading white papers on the MS site and everything you are saying seems to fit in perfectly. I last question - When I add the third node and assign the SAN disks to that node, those those same disks need to be mapped on the SAN to the other nodes on the cluster, or can I simply configure the node with the disks and add it to the existing cluster? Does clustering the nodes make the disks available to all other nodes in the cluster?

for example, when I log into a node in a cluster, I can see all disks available to the cluster, but cannot access them (unless the node i log into is the active node). After I add this new node, will those new disks be viewable (obviouslt may not be available) on ALL nodes?

Clustering is an important factor in utilising the high availability in SQL Server and apart from using pre-supplied tools I'm not inclined to use third party tools for clustering in this case and never used so.

Though the SQL Server 2000 clustering does not provide load balancing; it provides failover clustering. To achieve load balancing between instances of SQL Server, you will need additional software.

You don't need a third-party solution to use NLB and server clusters. For example, you can use NLB for front-end Exchange servers and you can cluster back-end Exchange servers using the
Windows Cluster Service found in Windows 2000 Advanced Server amd above.

hi satya;
can you help me decipher your response?
you seem to contradict yourself.

>> "Clustering is an important factor in utilising the high availability in SQL Server >>and apart from using pre-supplied tools I'm not inclined to use third party tools for >>clustering in this case and never used so."

yes; i understand the high availability speal; thats why i clustered to begin with. now i am looking to gain more performance.

Ok. in the first paragraph; you state you don't like 3rd party; now you tell me i need additional software. I know this; thats why I posted here to begin with.

>>You don't need a third-party solution to use NLB and server clusters. For example, you >>can use NLB for front-end Exchange servers and you can cluster back-end Exchange >>servers using the Windows Cluster Service found in Windows 2000 Advanced Server amd >>above.

Explain to me how MS NLB leverages my idle cluster node? And increases overall database throughput and response time?
Everything that I have read paints NLB as a costly solution that basically "buffers".
Why should I spend money on more OS licenses and Servers to just buffer?

NLB does not scale my performance.

Please; correct me if I am wrong; I would rather be wrong and missed some critical point versus realizing that the performance limit is a 4 cpu AMD 850 with 32GB of memory with a Texas Memory Systems RAMSAN underneath it.

I don't think so, the additional software doesn't refers to be a third party tool, as you have such applications available in Microsoft Windows.

Load balancing is more than just distribution of Web traffic. A software-based load-balancing solution available in Win2K Advanced Server and Win2K Datacenter Server, NLB streamlines administration by letting you manage a group of independent servers as one system. Administrators typically use NLB to evenly distribute Web client requests among Web servers, such as in a Microsoft Internet information Services (IIS) 5.0 server farm.

So if I have a current Active/Passive SQL cluster and want to add another server as a failover and a new instance, I need to (and this is the order I'm planning):

1) Add the new node (which I want as the failover or passive node) to the Windows 2003 cluster
2) Install the new SQL instance on current passive node and select my new node as the failover
3) Reinstall my original instance and name the new node as the failover

Is step #3 correct? Should that be all (besides of course adding the additional disk resources for the new instance)