February 9, 2006 at 5:11 am
Hi,
I've got a query, and I'm a bit confused with regards to it. I know a SQL server cluster can be used for failover, but how does it work for load balancing?
I'm looking at setting up an active/active cluster, but I don't if this will provide some sort of load balancing... All the articles I've found on the net are a bit vague in this regard, and mainly go on about failover, so I was wondering if anyone could clear it up for me... I just need to know if a SQL Server cluster will provide Load Balancing...
thanks
February 9, 2006 at 5:18 am
The only reference that I could find was SQL Server 2000 High Availability Series Implementing Network Load Balancing
I always thought that clustering SQL servers was simply to provide resilience. The link I have attached seems to suggest the same but also suggests that NLB can also be implemented.
February 9, 2006 at 5:28 am
Thanks for that, but its not so much network load balancing that I'm looking for...
I'm more interested if a particular long running query will be distributed over all the processors in the cluster...
network load balancing purely works on network traffic and not processor utlization...
February 10, 2006 at 12:59 pm
I am 99.44% sure that 'load balancing' of a query across servers in a cluster is not possible. The query optimizer will decide if 'parallelism' (splitting up the query and executing it on multiple CPUs within the same server) is needed and more importantly if it is more beneficial to the overall cost of execution of the query.
RegardsRudy KomacsarSenior Database Administrator"Ave Caesar! - Morituri te salutamus."
February 11, 2006 at 9:11 am
Rudy, I'd bump that up to 100%
Actually you can load balance across servers by using Distributed Partitioned Views, but that has proven to be much more difficult in most situations than it sounds and it has absolutely nothing to do with clustering per se.
/*****************
If most people are not willing to see the difficulty, this is mainly because, consciously or unconsciously, they assume that it will be they who will settle these questions for the others, and because they are convinced of their own capacity to do this. -Friedrich August von Hayek
*****************/
February 11, 2006 at 11:44 pm
Thanks for the input guys... still trying to figure out the best way to implement a scalable solution for a client that doesn't want to spend money now...
The problem for me is to figure out a solution for a customer that wants an immediate solution to their performance issues right now without them spending the earth, but it has to be scalable because their database size is expected to grow from 150Gb to 450Gb in the next year and anything that is only good enough to solve the current issues won't be good enough in a year's time so I was thinking clustering was the way to go...
if more power is needed, just slap another node into the cluster... no need to buy a box big enough for 3 years time if you don't need to... scalability its the name of the game guys...
March 2, 2006 at 3:25 am
Do you have your solution found yet? I am interested in this part. (actually I had a job intervieuw yesterday, where a question was asked about load balancing SQL Server) In my opinion this was not possible, but there are some solutions for the problems you can have why you would think of load balancing.
March 2, 2006 at 3:47 am
From what I've found, it looks like a cluster will provide a level of load balancing, but it won't do this for a single query.
Basically if you submit a query to the cluster, that query will be submitted to one of the individual SQL Server's in the cluster and that server will handle the query. If another query is submitted at the same time, that second query will be submitted to another server in the cluster.
The issue I get is that if you want that query to run over all the servers in the cluster that won't happen, which is a problem for me in my scenario where there are nightly ETLs being loaded into the SQL database and I would want the query that runs the load to be processed by all the servers in the cluster, not just a single server... saying that I would be able to run other jobs at the same time as the ETLs, because only one server in the cluster would be busy with load...
Hope this helps..
March 2, 2006 at 3:51 am
On a two node cluster with one SQL Server instance there will be absolutely no load balancing over the two nodes. The SQL Server instance will be running on eiter one of the nodes, but never on both nodes at the same time. How the second query will be run on the second node as you state here, is not clear to me.
Can you provide a source for this information? Off course I can be wrong...
(http://support.microsoft.com/kb/260758/en-us)
Question: Does SQL Server 2000 clustering support load balancing?
Answer: SQL Server 2000 clustering does not provide load balancing; it provides failover support. To achieve load balancing, you need software that balances the load between clusters, not between servers within a cluster.
March 2, 2006 at 9:13 am
Yes, and No... Microsoft Clustering provides ZERO load balancing capabilities for SQL Server, however, you could create an "active/active" cluster with some sort of partitioning or replication between them and then use a load balancer to direct queries to either one. In the case of a partitioning scheme the load balancer would have to know the appropriate instance to query for that particular data, and in the case of replication, it would be most common to direct read-only queries to the copy for reporting, and read/write to the publisher for transaction processing.
There are a bunch of variations that could be used to "split the difference" but those are the most common and workable.
/*****************
If most people are not willing to see the difficulty, this is mainly because, consciously or unconsciously, they assume that it will be they who will settle these questions for the others, and because they are convinced of their own capacity to do this. -Friedrich August von Hayek
*****************/
March 2, 2006 at 9:21 am
Yes, I have read some stuf now, but it is not really load balancing as you would like it. As long you can only update to 1 server and (all) the other(s) are only read, I am not really into calling it load balancing.
But it looks like some sort of step in between until it will be available.
If you want to update on both nodes, you must replicate the transactions to both nodes, and your performance gain is lost in updating all the queries on both nodes...
What you say is correct, you distribute the read load to one node, and the updat load to the other.
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply