SQL Server Scalability

Question

Post reply

SQL Server Scalability

Richard McSharry

Ten Centuries

Points: 1249
More actions
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?

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

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

PaulB-TheOneAndOnly SSC Guru Points: 66773 More actions · Answer 1

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.

kempes-710026 Valued Member Points: 59 More actions · Answer 2

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

Steve Jones - SSC Editor SSC Guru Points: 728159 More actions · Answer 3

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.

Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com

Big Data Man SSCrazy Eights Points: 8536 More actions · Answer 4

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

Steve Jones - SSC Editor SSC Guru Points: 728159 More actions · Answer 5

Steve Jones - SSC Editor

SSC Guru

Points: 728159

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.

Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com

Gail Shaw SSC Guru Points: 1004494 More actions · Answer 6

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

Richard McSharry Ten Centuries Points: 1249 More actions · Answer 7

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.

Gail Shaw SSC Guru Points: 1004494 More actions · Answer 8

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

PaulB-TheOneAndOnly SSC Guru Points: 66773 More actions · Answer 9

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.

noeld SSC Guru Points: 96590 More actions · Answer 10

noeld

SSC Guru

Points: 96590

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