Load balancing with SQl server 2005 cluster in act | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

Load balancing with SQl server 2005 cluster in act

is in regards to the MS SQL Server 2005 Cluster testing. I have the set-up in Active/Passive configuration. We decided upon moving to the Active/Active configuration, did some research on the internet for the additional features which we get with this configuration and what could be the benefits of this over the Active/Passive configuration. We have a query regarding the Active/Active configuration. Please find below the details on the same: The Current Setup in Active/Passive configuration has the following details: 1) There are two nodes “node 1” and “node 2” setup on Virtual Server 2005. 2) A cluster has been set up using “node 1” and “node 2”. 3) A SQL server default instance is installed on “node 1”. While SQL server 2005 was installed, it created a resource SQL server IP. To connect to this SQL server instance, our application uses SQL server IP created in step 3. (Please note that this is the only IP through which we are able to connect to SQL cluster). The fail-over features are working fine in this configuration. If “node 1” goes down then the SQL server instance runs in “node 2”. This is taken care of by cluster and is transparent to our application. However, there is no provision for load balancing as only one node is active at a time. After some research, we came across Active/Active configuration which is supposed to support load balancing. We understand that in this configuration, Step 1and 2 are similar to the Active/Passive configuration. The only difference is in step 3 where an instance of SQL server is installed on each node, thus providing two active nodes at a time. The failover works just like in Active/Passive configuration. As per the above information, the Active/Active configuration seems to be similar to two SQL server instances running independently.There will be two seperate databases and on failure of one instance other instance wont be able to cater to the requests designed for first instance, Thus providing no extra benefits from cluster. We require the information on how to take benefits of the load balancing features in this configuration.
SQL Cluster is not for load balancing, it is for High Availability… Load-balancing enhancements in SQL Server 2005
http://searchsqlserver.techtarget.com/tip/0,289483,sid87_gci1133488,00.html Managing SQL Server 2005 Peer-to-Peer Replication
http://www.microsoft.com/technet/technetmag/issues/2006/07/InsideMSFT/default.aspx MohammedU.
Moderator
SQL-Server-Performance.com
Hi,
Addition to the main question, regarding active/active or multiple instances ,we have two different application. One is running on node A and the other running on node B. if node B fails, application running on this node is going to run on the other active node without needing any manual changes like connection string database server name ? What should we do in order to run an application running on the other node in case failover occurs ?
thanks
Sedat
Hi sedatduztas,
As your say: " What should we do in order to run an application running on the other node in case failover occurs ?"
Do the same procedures you did for application A ,but starting in the passive node of application B.
So, at any time application A active in node1 , application B is active at node 2
Cluster formula: Activ / Active = 2 * Active/Passive; in condition that any node is capable of the 2 full loads [;)]
Hi moh_hassan20,
thanks for the answer. I understood from your explanation that infact there is no difference between active/active and active/passive except active/active cluster includes another active/passive cluster too. So if we know active/passive cluster features, all of them are valid for active/active too. So we do not need to make any connection string changes during failover for the active/active clusters since we use virtual cluster name in this configuration too. the only difference is that we have to take into account hardware capacity since we have two different cluster on a node during failover phase. the question here is that the cluster names of these two different cluster should be different ? If so we need two different virtual cluster IP address. Right ?
Regarding IP addresses and virtual cluster names, could you please make a list for active/active configuration ? what is the different between active/active and active/passive from the point of IP addresses and virtual cluster names ?
thanks in advance,
Sedat Duztas
HI sedatduztas
check: http://www.sql-server-performance.com/articles/clustering/clustering_best_practices_p1.aspx
A/P example:
IP addresses

– public ip for node1 : 192.168.1. 2
– public ip for node 2: 192.168.1. 3
– private ip for node1 : 10.10.1. 1 (different sub net) heart beat
– private ip for node2 : 10.10.1.2 (different sub net) heart beat
– virtual cluster server : 192.168.1.4 (that used by application 1)
storage needed:
Quorum drive: Q
dedicated shared array space (SAN) : for data e: , log f:, tempdb g:
These storage can not be accessed by passive node
Resourcse cluster group: include sql service , storages, ip
————————————————————————————————————————————
A/A example:
Same as A/P
plus you need extra virtual cluster server2 : 192.168.1.5 (that used by application 2)
storage needed:
Storage as said in A/P
plus (Complete new extra isolated disks )
Quorum drive: P
dedicated new shared array space (SAN – preferred other unit) : for data H: , log I:, tempdb J:
These storage can not be accessed by passive node
Resourcse cluster group: include named service sql , storages, ip both node 1, node2 are members of DC
Hi ,
I see it. Regarding " Storage as said in A/P plus (Complete new extra isolated disks ) ", Can’t we use the same disks may be partitioning the disks ? We have only one storage.
thanks a lot.
Sedat
Sedat
Can’t we use the same disks may be partitioning the disks ?
No , you can not use the same disk for both clusters
—————-
We have only one storage.
you can make your volumes in storage as:
p, q : 500 MB per one as quirum
E , F for cluster1
G, H for cluster 2
can i know the model and configuration of your shared disks?
Thanks for the answer. Saying that you can not use the same disks for the clusters, did you mean drive or volume like E,F,etc. ?
If so ,we can partition the disks to get enough volume.
We have 3*320 GB Raid 5 disks. So we have 640 GB to use for the clusters.
thanks
Sedat
172622EExpress IBM System Storage DS3200 Dual Controller1
43W7506Express IBM 300GB 3.5in 15K HS SAS HDD(43X0802)3
IS your storage is external storage? and connected to both servers ?
i found in your answer the model of HD and controller , not model of Storage. Can i know the model name
Yes. External storage and will be connected to both servers.
IBM BladeCenter S Chassis with 2×950/1450W PSU, Rackable
Part No.Description
172622EExpress IBM System Storage DS3200 Dual Controller
43W7506Express IBM 300GB 3.5in 15K HS SAS HDD(43X0802)
Storage is the more important player in clustering for IO performance
as many disks you use , as many spindles , as best IO 3*320 GB, RAID5 (RAID hardware) , is not enough for A/A (from performance point of view) try to use complete physical disk (not logical disk) for best IO performance
don’t partition one disk to two or more logical drive.
for best performance:
it is recommended data files :RAID5 /or 10
logfiles: RAID 1
I agree with MohammedU.
SQL Server 2005 doesn’t support load balancing.
You can implement some techniques that may distribute database activities across multiple sql servers, like:
  • Database partitioning
  • Distributed partitioned views
  • replication
  • Log shipping
Be sure that all nodes in active/active cluster is capable for the full load, in case of failover.
]]>