Post reply

SQL Clustering vs. Application clustering

  • December 18, 2008 at 6:42 am

    #197329

    Hello,

    I would like to develop a high availability strategy for a company (both automatic failover and load balancing)

    If I well understood clustering, it allows you to have n nodes on the same IP. Microsoft cluster server is geared for high availability clustering and not for load balancing.

    Application clustering seems to be super easy to implement and you can set up on connection string to your data a failover partner so that automatic failover is possible without SQL clustering.

    I am in a transactional environment where I can write my transactions on any production server. Let's say I have three production servers.

    My application cluster does round-robin with sticky sessions on three application server instances.

    I assign to each of them a data source (with principal and failover partner)

    I use database mirroring on each of my three database servers, with a corresponding secondary server each (the failover partner). I use high safety automatic failover by seting up a witness server. ok. easy.

    What is wrong with that? Will I have a worse performance than setting up Sql clustering?

    I would like to know your thoughts about it. I still cannot figure out what is so great about SQL clustering.

    Thanks for your replies 🙂

    Clement

  • December 18, 2008 at 6:50 am

    #914492

    clementhuge (12/18/2008)

    Hello,

    I would like to develop a high availability strategy for a company (both automatic failover and load balancing)

    If I well understood clustering, it allows you to have n nodes on the same IP. Microsoft cluster server is geared for high availability clustering and not for load balancing.

    Indeed. Clustering is not a load balancing/scale-out scenario. Only one instance can use a database at a time

    Application clustering seems to be super easy to implement and you can set up on connection string to your data a failover partner so that automatic failover is possible without SQL clustering.

    Automatic failover (as in the failover partner in the connection string) is Database Mirroring. It's also a high availability, not a scale-out scenario.

    I use database mirroring on each of my three database servers, with a corresponding secondary server each (the failover partner). I use high safety automatic failover by seting up a witness server. ok. easy.

    Ok. Why?

    The mirrored databases are not readable and not writeable. They'll be in Recovery

    What is wrong with that? Will I have a worse performance than setting up Sql clustering?

    Neither clustering nor mirroring have anything to do with performance.

    I would like to know your thoughts about it. I still cannot figure out what is so great about SQL clustering.

    Full server redundancy with all services able to automatically fail over in the case of a failure of any kind.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • December 18, 2008 at 7:01 am

    #914504

    Thanks for the quick reply. I apreciate your answers.

    I saw how effective application clustering is (by watching a load testing), so I am 100% under the application clustering solution.

    Database mirroring is what I like to implement and indeed, I like to have my secondary servers not available (licence wise, this is what I like). So, yes, I am looking for active/passive automatic failover strategies with database mirroring.

    I tested it and works as a charm. I still need to test load testing.

    The scalability, I like to gain it by duplicating servers. I can because I can federate the transactions on different databases.

    I did that with a telecommunication companies and it really improves performance.

    When I want to report, I just extract data from production servers.

    Your answer confirms what I thought about clustering (for me, pretty much pointless).

    I do not understand your answer when you talk about no scalability. Database Mirroring, I agree, does not improve performance (there is actually a small hint with keeping the data in sync).

    However, by load balancing with several servers and let the round-robin clustering done by the application, I should achive load balancing, no?

    any thoughts would be very much appreciated

    Clement

  • December 18, 2008 at 7:09 am

    #914510

    clementhuge (12/18/2008)

    However, by load balancing with several servers and let the round-robin clustering done by the application, I should achive load balancing, no?

    How are you replicating the changes from one server to another?

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • December 18, 2008 at 7:15 am

    #914513

    I do not need to replicate any changes for one principal server to another principal one as it only contains different sessions and "transactions"*. I would use database mirroring only for automatic failover reasons.

    *"Transactions" do not refer to sql transactions. It refers to isolated operations like:

    - a payment

    - an email sent

    - an order

    etc...

    The only trick is to keep sessions onto the same server. That is it and application clustering will do that by applying sticky sessions.

    Clement

  • December 18, 2008 at 7:24 am

    #914526

    Ok, so you're implementing application load balancing, completely outside of SQL and DB mirroring for high availability.

    What's the question then? I think I've missed something.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • December 18, 2008 at 7:29 am

    #914530

    The questions are:

    1. Is my solution ok or stupid? Before setting this up, I would like some thoughts from experienced gurus 🙂

    2. what does SQL clustering will add to this solution? or Does it add anything? I am completely inexperienced in SQL clustering and wonder if this can add something?

    (I just do not understand what is the fuzz about SQL clustering. I think this is very complex to install, expensive and does not add much. But I have never implemented SQL clustering, so that is why I ask)

    Clement

  • December 18, 2008 at 9:01 am

    #914641

    I know clustering will add $$$$$ compared to Database Mirroring for sure

    but Clustering seems the de facto way for large Enterprise who needs 24/7 uptime, period

    I personally like DB Mirroring as a cheap startup High Availability

    Problem is it only supports 2 nodes (unlike Cluster where you can add more)

    And also DB Mirroring is less proven (vs Cluster), and the Application needs to be mirror-aware for auto failover (.Net is) where Cluster goes by Virtual IP/Name

    For reporting, if you are using SQL Enterprise edition, you can create Database Snapshots on the mirrored server for read-only query

    SQLServerNewbie MCITP: Database Administrator SQL Server 2005

  • December 18, 2008 at 11:01 am

    #914767

    Thank you very much for this answer.

    When ou talk about snapshot, are you considering snapshots on mirrored databases ? because the mirrored databases are in recovery state, so I thought they cannot be read?

    Are you considering snapshots with the cluster solution?

    Please explain your point of view on snapshots, I like it very much. Thanks.

    Clement

  • December 18, 2008 at 11:03 am

    #914771

    oh yes I forgot. My application server is also mirror-aware.

    Clement

  • December 18, 2008 at 11:16 am

    #914780

    clementhuge (12/18/2008)

    Thank you very much for this answer.

    When ou talk about snapshot, are you considering snapshots on mirrored databases ? because the mirrored databases are in recovery state, so I thought they cannot be read?

    They can't, but a database snapshot on one can

    Are you considering snapshots with the cluster solution?

    There's only one copy of the database in a cluster. What would you put the snapshot on?

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • December 18, 2008 at 11:23 am

    #914787

    grea answer! This is a good news. I never thoughtI can do a snapshot on a database "in-recovery".

    I am going to check this. cool.

    Thanks

    Clement

  • December 18, 2008 at 12:10 pm

    #914812

    clementhuge (12/18/2008)

    grea answer! This is a good news. I never thoughtI can do a snapshot on a database "in-recovery".

    In general you can't. It only works for a mirrored database that's synchronised.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • December 18, 2008 at 1:00 pm

    #914837

    cool. Tanks.

    Clement

Viewing 14 posts - 1 through 13 (of 13 total)

You must be logged in to reply to this topic. Login to reply