balancing connections - any idea ?

Discussion in 'microsoft.public.sqlserver.clustering' started by mastiff, May 22, 2009.

  1. mastiff

    mastiff Guest

    I have 2 web server in load balancing configuration (NLB) and one database.
    We are going to add another web server.
    The problem is simple: too many connections on the database. It is a
    bottleneck.

    I know that microsoft did not create Real Application Cluster (like oracle
    ;-)) ), and I was testing 2 instance (one for each web server) with the peer
    to peer transactional replication splitting connections and replicating data
    to align databases.

    There are many problems with peer to peer replications (conflicts, identity
    iussues, ecc.), and I have so many tables.

    Do you have any other idea/solution to split/balance connections ?
    Thanx

    Massimo

    (Mastino)
     
    mastiff, May 22, 2009
    #1
    1. Advertisements

  2. mastiff

    frankm Guest

    What precisely do you mean, "too many connections"?

    I ask because you may have more going on with database design and
    performance.
     
    frankm, May 22, 2009
    #2
    1. Advertisements

  3. How many connections? What is the SQL hardware? What version of SQL? Do you
    have direct connectivity from the Web servers to the SQL using GigE or
    better? What devices are in between? What are the perfmon counters telling
    you are the bottle necks on the SQL?
    This sounds like a matter of scaling up, not scaling out.

    --
    Russ Kaufmann,
    MVP, MCSE: Messaging and Security, MCT, MCITP, MCTS and other stuff

    ClusterHelp.com, a Microsoft Certified Gold Partner
    Web http://www.clusterhelp.com
    Blog http://msmvps.com/clusterhelp
     
    Russ Kaufmann, May 23, 2009
    #3
  4. mastiff

    mastiff Guest

    I did not want to bore the newsgroup with technical details, the hardware
    and the configuration is great, or we can improve that.
    I just wanted to discuss with people on the other side of the world
    (newsgroup's mission ;-))) ), if there are some solution to scale
    connections between instances sharing same data.

    Aaron Bertrand on ...sqlserver.server gave us some very good approach to the
    problem.
    Thanx to you all.
    Regards
    Mastino
     
    mastiff, May 23, 2009
    #4
  5. There are many ways to scale SQl server instance. it’s up to you to choose
    one of them or continues with ongoing configuration

    Some of them are

    1).u can use database mirroring and then redirect all read requests to
    mirror server (by creating snapshot ). This configuration will scale your
    system as it reduce the shared locks on main SQL server table . But this
    workaround needs some application level changes

    2). You can use two node active/active cluster and divide the database into
    two database and then install them on each cluster node . e.g install the
    first half of database to default instance on first node and other half on
    named server on second node , now if one of the node goes done it will fail
    over to other . In this way you will get scalability and high availability.
    But this approach need database level changes.

    3).u can implement transactional replication of important table and then
    redirect all read statement to that server.

    4).use distributed partitioned views




    Manpreet Singh
    http://crazysql.co.nr
    http://crazysql.wordpress.com
    MCITP – DB Admin, DB Dev SQL Server 2005
    MCTS – MOSS 2007, SQL server 2005
     
    Manpreet Singh, May 26, 2009
    #5
  6. mastiff

    mastiff Guest

    Thanx for the 4) approach, I did not consider it.
    Thanx
    Massimo
     
    mastiff, May 27, 2009
    #6
    1. Advertisements

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments (here). After that, you can post your question and our members will help you out.