Link to home
Start Free TrialLog in
Avatar of Swift
Swift

asked on

MS SQL 2005 - HA with Load balancing

Hi

I have a query regarding using a cluster of MS SQL 2005 servers in both HA and load balancing mode using native technologies of microsoft. I had come to know that using Microsoft Cluster services (MSCS) and NLB on physical servers, the above can be achieved.

I currently have a a cluster using MSCS on Windows 2003 Ent. servers configured to provide Active/Passive configuration of SQL. I need to covert this into n+1 failover/load balancing mechanism where both my nodes as of now, can be used in Active mode and when budget permits I will bring in the third server to the cluster to act as a standby.

How do I achieve this without losing the data that currently resides on my A/P SQL cluster?
Avatar of MikeWalsh
MikeWalsh
Flag of United States of America image

I am not sure I understand the question. Are you asking if you can use SQL Server in an "Active-Active" cluster scenario where both nodes are serving the same need and thereby providing load balancing?

The simple answer is, this is not really possible as requested.

The longer answer is:

Yes, you can have, what Microsoft calls, multi-instance clusters where you have more than one active node in a cluster at a time and more than one instance of SQL Server up at the same time in said cluster.

This is not load balancing. You have two separate instances up on different nodes. For instance you could have your financials back end alive on one node and your reporting on the other. In the event of a failure of one node they either both live on the same node or transfer to a common 3rd passive node.

If you wanted to do some sort of load balancing you would really have to look into complex architectural or design questions. Perhaps one function lives on one instance and another function on the other instance and the application middle-tier pieces know this, etc.

The quick answer is SQL clusters are for availability, not load balancing/performance needs. If I misunderstood the question, please let me know how and I am sure myself or someone else can give a better answer.
Avatar of Swift
Swift

ASKER

No Mike, You made your point quite correctly. The concern arose on the scalability aspect and the need to equate MS SQL offering with that of Oracle's RAC.

I currently have multiple SQL databases spread around low performance servers and I wanted to consolidate them all on one cluster while taking into consideration the future scalability needs. If I deploy a SQL cluster on high performance server grade machines today, tomorrow with growth of business, I might need to keep adding more and more clusters. Someone told me and that's why I posted this question, that there is a way to have 7+1 or N+1 nodes cluster in SQL where, if and when we want to scale the performance of SQL servers, we just keep adding nodes and that's all it takes.

I guess it was a case of misplaced and incorrect information then.

But the question still remains, with more and more applications coming into my datacenter that involves SQL server services, what's the best way to architect the design?
ASKER CERTIFIED SOLUTION
Avatar of MikeWalsh
MikeWalsh
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial