Trying to achieve load balancing and instance/database availability - various approaches RRS feed

  • Question

  • Requirement/problem stmt –
    1. Load balancing within db servers
    2. Optimal use of hardware. (DR server -(8cpu/8gb ram) currently are idle when Primary is up and running)
    3. Assure Instance availability and db availability
    4. Db release activity can be transparent to end customers

    scenario - I have 6 server with same h/w config. and sql 2005 and db are partially oltp

    assumption – 1. all 6 svr have identical hw config

    Option 1 -

    svr1+svr2+svr3 - active/active cluster1 -
    svr4+svr5+svr6 - active/active cluster2 -

    big-ip/3dns between cluster 1 & 2 for load balancing and t.replication between clusters to assure data consistency between db
    remarks - MSDN article for clustering best practice suggest min 3 svr for active/active clustering

    option 2- -

    svr1+svr2 - active/active cluster1 -
    svr3+svr4 - active/active cluster2 -
    svr5+svr6 - active/active cluster3 -

    big-ip/3dns between cluster 1 & 2 & 3 for load balancing and t.replication between clusters to assure data consistency between db

    option 3- -

    all 6 standalone servers (svr need not hv h/w identical) and all db to exist on all 6 svr. -
    big-ip/3dns between 6 svr for load balancing and t.replication between 6 svr to assure data consistency between db
    remarks - instance availability not assured (as compared to clustering) but load balancing fully assured.

    Qn -

    Can bigip/3dns combi be used along with db servers or ONLY with web servers.
    Pl suggest the best of all 3 options

    Thursday, May 14, 2009 10:32 AM

All replies

  • I don't believe you're going to find a load balancing solution for SQL Server in this manner. The way the Big-IP maintains a connection to the same server when stateful connections are used is it adds to the cookie the information it needs to keep you pointed at the right server as you come through the appliance. There's nothing like that on the SQL Server side as that method of a sticky connection is strictly HTTP based.

    Also, another thing to consider when talking about two SQL Server instances on a cluster is do you really need that 3rd node? With Microsoft Exchange it's mandatory for you to have at least one passive node if you cluster it. However, with SQL Server, because the instances will listen on different ports, they can run on the same physical node. The catch is to make sure each physical node has the capacity memory and processor wise to handle both nodes at the same time. If this isn't optimal, then by all means add the passive node.

    But as far as getting better performance on the SQL Server side, you're looking to either scale up the hardware or scale out by effectively hardcoding database connections to individual SQL Servers and then using replication.

    K. Brian Kelley, http://www.truthsolutions.com/
    Friday, May 15, 2009 2:22 AM