SQL Server Performance Forum – Threads Archive

Three Node cluster – Active /Active /Passive

Hello everybody, We are trying to consolidate our servers, (already posted few questions in this forum) and as a requirement from the management we have been asked to move databases to instances (3 nodes) running under Active /Active /Passive mode. My doubts here are (1) Can we ever do this? I said "Yes", it may be possible but never tried. I thought I will get back with you guys, in here! Can anybody give your thoughts on this? (2) As I was doing my research on this, my teammate came up with this article http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/failclus.mspx?pf=true. Now, as I went thro’ this and was specifically highlighted by my team member on a topic: Scenario One: Four-Node Multiple-Instance SQL Server 2000 Failover Cluster, Three Active Nodes, One Standby (N+1) Here is the small brief, With four-node support, Windows 2000 Datacenter Server provides more flexibility in terms of a cluster configuration. The recommended way of using a four-node Windows 2000 Datacenter Server cluster in a SQL Server environment is to have three of the nodes each owning an instance of SQL Server 2000 and have the fourth be the warm standby. This is not unlike a log shipping scenario, or a single-instance failover cluster in which at least one node is waiting for work. This scenario is known as N+1. Instead of configuring your failover cluster to allow the instances to fail first to a node with another instance of SQL Server 2000 running, the fourth node should be configured as the primary failover. This would reduce the issue of having too many instances starving the resources of one node. AWE memory should be enabled in this scenario to allow each instance of SQL Server to address more memory than the 1 GB currently available. This allows your applications to scale out rather than limiting them if they exceed the memory allocation for SQL Server. Can somebody please help me to understand the highlighted portion? And my teammate argument here is that, why do you want to run a 3rd instance on the 3rd node for configuration – Active /Active /Passive, when it is going to be a warm standby server and being a primary failover? I am unable to convince with any explanation but my gut feeling says, the configuration is possible and we would require a 3rd instance running too. But why is it, "according the document", I don’t get it. I really appreciate all your time and help on this.
RoyalSher.
*********
The world is the great gymnasium where we come to make ourselves strong.

Hi ya, the example talks about a 4 node cluster, which for N+1 would mean 3 active plus one passive. You want a three node cluster running N+1 which is 2 active and one passive. Cheers
Twan

Twan Thanks for the reply!. Yes you Got it! I need a 3 node cluster with 2 active and 1 passive. Now, my question out here is that don’t we require a SQL Instance running on the passive node, which is waiting for a failover? RoyalSher.
*********
The world is the great gymnasium where we come to make ourselves strong.

If you want two active instances you install two sql instances that are able to failover to all three nodes. For more than two nodes I think you need data center edition and it has to be setup according to specific guidelines.

No you would not need to install an instance on the 3rd node (passive) You’d set up the cluster first and ensure that failover of the cluster works in all directions and in all circumstances (heartbeat down, network down, server down, etc.) Then install an instance on node1, followed by a second instance on node1. Where node1 is the one which owns the cluster resource Then set the preferred node for each of the two instances and define which other node(s) it can failover to using cluster administration Cheers
Twan

Does anyone have a guide which shows Step-by-Step how an Active/Active config is done? I am also assuming that for the passive node you would follow the Active/Passive guide for doing this. I fully understand that two instances of SQL would be running on a node if a failure ocurred. Looking mainly for maximum processing power for a single database. Currently I am running an Active/Passive configuration. Can I convert this to Active/Active? Would I only need to remove one of the passive nodes or do I need to rebuild the entire configuration? Platform: Windows 2000 Advanced Server
SQL 2000 Enterprise Server
Disk array is a SCSI SAN.
Goal is four machines as Active/Active/Active/Passive or Active/Passive/Active/Passive. Any assistance is appreciated.
Jim
[email protected]

quote:Originally posted by hurdit
Looking mainly for maximum processing power for a single database.

If you support a single database there is no point installing more instances in the cluster. The instances can not interact and share load. If you on the other hand mean that you have at least one different database per instance then you could run it as active / active in the cluster just to utilize more of your hardware. When it comes to an install guide it’s the same as with active/passive just that you run the SQL install part once more for each additional instance you want to install in the cluster. If you then put the instances on one node each (and make them "active") or on the same one is up to you.

How can I have an active/active configuration to share the load on a single database without running two separate databases? The idea is to shed the load between boxes against a single database. Microsoft support alluded to it and after the product was purchased constantly talked around the issue. What is the best way to go about doing this? Another option I looked at was using transactional-replication as a stand-alone model between boxes and then using session based load balancing to route traffic to the appropriate boxes. At that point I’d almost go the route of using Oracle RAC or grid-based computing to accomplish this. Thanks for your help so far.
Jim
[email protected]

They can not share load on a single database. Microsoft clustering is a failover cluster. It’s for hardware redundancy and easy of management when performing hardware or software upgrades. The only load balancing that can occur is that you manually select which instances should be running on which node. What you could do is to create an identical copy of the database and use merge replication between them but that in itself can be a performance hit and depending on how the database is designed with indexes and all it might not be possible to set up such replication. It’s also means a lot more administration and surveillance is required. Another solution would be to separate read only and read/write parts of the database in separate databases. A copy of the read only database could be put on all instances and then you could load balance them in custom manner (say some round robin algorithm when selecting connection string in the application or hardware load balancers). They could then get their data from the read/write database through replication or a detach/attach procedure. But this is not that easy to setup either. In other words it requires careful planning if you want to have a successful load balancing scenario when it comes to sql server databases. You have to plan for it already when designing and building the application you intend to use. It’s not something you can easily add afterwards. Grid computing alone will not help in this scenario since the instances can not share the same storage. To add to that there might be 3rd party tools that could help you out.

Hi RoyalSher, That statement is saying that the 4th node needs to be idle, but set as the preferred node for failover of the 3 other SQL virtual instances. This directly applies to your 2+1 methodology. So install the cluster and present the disks accordingly to all 3 nodes. Then install SQL in the normal way on your preferred cluster nodes. Then open up cluster administrator and in addition to making SQL dependent on the disk drive resources, set the preferred failover sequence in the general properties. Here’s another MS link that may be useful:
http://www.microsoft.com/resources/documentation/msa/edc/all/solution/en-us/pak/build/edcbld10.mspx HTH
Cheers

]]>