SQL Server Performance

Load balancing with SQl server 2005 cluster in act

Discussion in 'SQL Server 2005 Clustering' started by geetugupta_in@yahoo.com, Mar 11, 2007.

  1. 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.

  2. MohammedU New Member

  3. sedatduztas New Member

    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 ?
  4. moh_hassan20 New Member

    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 [;)]
  5. sedatduztas New Member

    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
  6. moh_hassan20 New Member

    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 : (different sub net) heart beat
    - virtual cluster server : (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 : (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
  7. sedatduztas New Member

    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.
  8. moh_hassan20 New Member

    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?
  9. sedatduztas New Member

    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.
  10. sedatduztas New Member

    172622EExpress IBM System Storage DS3200 Dual Controller1
    43W7506Express IBM 300GB 3.5in 15K HS SAS HDD(43X0802)3
  11. moh_hassan20 New Member

    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

  12. sedatduztas New Member

    Yes. External storage and will be connected to both servers.
  13. sedatduztas New Member

    IBM BladeCenter S Chassis with 2x950/1450W PSU, Rackable
    Part No.Description
    172622EExpress IBM System Storage DS3200 Dual Controller
    43W7506Express IBM 300GB 3.5in 15K HS SAS HDD(43X0802)
  14. moh_hassan20 New Member

    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

  15. moh_hassan20 New Member

    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.

Share This Page