Post reply

SQL Server Scalability

  • September 15, 2008 at 6:17 am

    #190772

    Hi,

    I remember reading that before launching SQL 2005, MS did some scalability testing with HP flagship servers (can't remember the server name). But the sell was that SQL 2005 can scale to a huge level - something like 60 servers were in the farm.

    I understand that Oracle has some kind of grid computing solution to scalability.

    So my question is how do you scale SQL Server? What if you want to run it on 10 servers, what options do you have?

    I was under the impression that clustering was the way to go, which makes sense: you have one 'virtual sql server' that the app points to and WCS manages the load-balancing.

    But so many comments I see say that clustering is just a DR solution...which I don't really believe. It is a DR solution, certainly, but surely it is more than that and allows for scaling out SQL Server?

  • September 15, 2008 at 7:58 am

    #871742

    Richard McSharry (9/15/2008)

    I understand that Oracle has some kind of grid computing solution to scalability.

    Oracle RAC pursues reliability, not scalability... you also do not put 60 nodes in a RAC, over 4 noedes you are going to start seeing huge interconnect overhead which would defeat your "scalability" purpose.

    _____________________________________
    Pablo (Paul) Berzukov

    Author of Understanding Database Administration available at Amazon and other bookstores.

    Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

  • September 15, 2008 at 8:02 am

    #871749

    SQL Clustering was designed mostly as a DR solution. There is something like active/active cluster but this is rather multi-instance DR cluster which allows you to have all cluster nodes active but each of instance has to be started only on one node. There is nothing like load balancing in this case but only let’s say this : "server resources" balancing spread across all nodes in this cluster. You can have 8 nodes with 8 independent instances and each of the nodes will have at least one active instance on it. If you are hitting SQL virtual name you are working only on one node. MS Network Load Balancing is not supported for SQL servers but theoretically it can work :). I've also seen some open source extended stored procedures which were responsible for translating SQL request (but only very simple requests) and sharing them between all hosts in this "NLB" solution. I wouldn't used that anyway. MS SQL doesn't have any real load balancing clustering solution now.

    Radek

  • September 15, 2008 at 8:13 am

    #871763

    Distributed Partitioned views, SOA techniques, all are available to build a scale out solution.

    The reality is that most DBs do not scale out well. The solutions that have worked are usually custom (like Google's)

    Right now the main solution is to buy a bigger box.

  • September 15, 2008 at 8:14 am

    #871766

    Richard McSharry (9/15/2008)

    Hi,

    I remember reading that before launching SQL 2005, MS did some scalability testing with HP flagship servers (can't remember the server name). But the sell was that SQL 2005 can scale to a huge level - something like 60 servers were in the farm.

    I understand that Oracle has some kind of grid computing solution to scalability.

    So my question is how do you scale SQL Server? What if you want to run it on 10 servers, what options do you have?

    I was under the impression that clustering was the way to go, which makes sense: you have one 'virtual sql server' that the app points to and WCS manages the load-balancing.

    But so many comments I see say that clustering is just a DR solution...which I don't really believe. It is a DR solution, certainly, but surely it is more than that and allows for scaling out SQL Server?

    Hi,

    Well, you have two options in terms of scaling SQL Server 2005, which is "scale-upo" or "scale-out".

    Scale-up is focused on more CPU's, increased memory, faster controllers or faster disk subsystem to allow your server to "do more".

    Scale-out is where you look at using features such as data paritioning to distribute your data access layer across more servers, peer-to-peer replication and linked servers as an example. Clustering is another way of scaling-out your data, but also allows you you build in some redudancy into your overall design with fail-over technology.

    Thanks,

    Phillip Cox

  • September 15, 2008 at 8:40 am

    #871798

    I disagree. Clustering doesn't scale out at all. It provides redundancy, HA solutions, not scale out.

    Polyserve (now HP) has an interesting solution.

  • September 15, 2008 at 8:49 am

    #871808

    Richard McSharry (9/15/2008)

    But so many comments I see say that clustering is just a DR solution...which I don't really believe. It is a DR solution, certainly, but surely it is more than that and allows for scaling out SQL Server?

    Clustering (as in windows clustering) is a high-availability/DR solution. Or the two (or more) nodes in the cluster, only one can actively be serving a database at a time. Only one SQL Server instance can own and use a database file at any one time.

    There's something called Scalable Shared Databases that allows multiple servers to use the same database files, but the files must be read-only (iirc, they must be on a read-only volume).

    You can use peer-to-peer replication for scale out. It works fairly well if the app is read heavy, not so well if it's write-heavy as too much time and bandwidth would be taken up by the servers synchronising with each other. Also there's latency and it's not guaranteed that all the servers will be up to date with all changes

    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

  • September 15, 2008 at 9:24 am

    #871834

    Thanks for all the replies. I was aware that peer-to-peer replication is a kind-of scale-out solution, with the limitations that Gail mentions:

    "You can use peer-to-peer replication for scale out. It works fairly well if the app is read heavy, not so well if it's write-heavy as too much time and bandwidth would be taken up by the servers synchronising with each other. Also there's latency and it's not guaranteed that all the servers will be up to date with all changes."

    There appears to be some disagreement about clustering, but I think the consensus is that it's only a DR solution and not a scale-out solution.

  • September 15, 2008 at 9:34 am

    #871840

    Richard McSharry (9/15/2008)

    There appears to be some disagreement about clustering, but I think the consensus is that it's only a DR solution and not a scale-out solution.

    Absolutely, because the separate nodes of a cluster cannot be serving the same database up.

    Oh, the other option on SQL 2005 and higher - database mirroring. The mirror database is inaccessible, but you can put a database snapshot on it and have a read-only, point-in-time 'copy' of your database. Very useful if you have reports that can run off data an hour or two behind the live system.

    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

  • September 15, 2008 at 10:11 am

    #871877

    Steve Jones - Editor (9/15/2008)

    I disagree. Clustering doesn't scale out at all. It provides redundancy, HA solutions, not scale out.

    I'm in agreement with your disagreement 😀

    _____________________________________
    Pablo (Paul) Berzukov

    Author of Understanding Database Administration available at Amazon and other bookstores.

    Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

  • September 15, 2008 at 12:28 pm

    #871983

    PaulB (9/15/2008)

    Steve Jones - Editor (9/15/2008)

    I disagree. Clustering doesn't scale out at all. It provides redundancy, HA solutions, not scale out.

    I'm in agreement with your disagreement 😀

    NICELY put!


    * Noel

Viewing 11 posts - 1 through 10 (of 10 total)

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