SQL server failover clustering

1. How many failover clustered nodes can you have? and 2. if you add more and more nodes, is it going to slow down?

From serverfault K001

• If I remember correctly, with Standard edition you can have only 2 nodes, and with Enterprise you can have 3 or more. I don't offhand know the upper limit.

  Whether or not the performance is negatively affected depends a bit on your configuration, but the short answer is No. In any cluster you are sharing a common drive between multiple hosts. Since only one server is accessing the live data at a time, it doesn't matter how many hosts you have - additional hosts only add redundancy for fail-over.

  However, if you have a multiple active node configuration (for example, A-A-A-P), then you have 3 nodes each dedicated to specific SQL instances. Since these servers are now load-balancing across multiple hosts, you actually get a significant increase in performance.

  Also, if I remember licensing correctly, you only need to license SQL on the active nodes.

  DISCLAIMER: Always talk to your software rep for proper licensing!

  Peter Schofield : I'd suggest the reference to load-balancing is carefully defined - there's a lot of confusion out there that Windows clustering offers load balancing for SQL Server like RAC does for Oracle.

  Chris S : Licensing: If your licensing per computer, then it's 1 for each Active server only, passive do not count (normal CAL licensing). If it's per processor, then figure out the most that could be used given the number of Active servers only (ex, if you have an Active Uniprocessor, but a Passive dual proc, you need 2 CPU licenses). In all cases a Passive node may not be active for more than 30 days without it's own license.

  Chris S : @Peter: Correct that Clustering does not offer Load Balancing. There are ways however to load balance a cluster without 3rd party tools; it's just much more complex than just setting up clustering.

  Jes : Yes indeed I agree - looking at my answer, load-balancing is not clearly defined. In fact, SQL isn't really load-balanced at all. However, the load can be *distributed* across multiple active nodes on a *per-instance* basis. Each instance, however, runs on only a single node.

  Jes : Oh - also I should probably note that Clustering is an inherent property of the Operating System. It's a function that Windows Server offers, and that SQL Server supports. You can also cluster other things like file shares and whatnot. So if you purchase Windows Server Standard, you might as well only purchase SQL Server Standard, as each has an inherent limit of 2 nodes. Only when you want more than 2 nodes do you need to consider the Enterprise versions of either.
  From Jes

• This tables about Features Supported by the Editions of SQL Server 2008 R2 states the number of failover clustering nodes supported by Sandard Edition is 2, but for Enterprise its Operating system maximum (and Windows Server 2003 supports a maximum of 8 failover cluster nodes. Windows Server 2008 supports a maximum of 16 failover cluster nodes.)

  K001 : I thought nodes were spread across multiple servers? so they are on 1 server?
  From DaniSQL

• Actually --> You don't get load balancing and could potentially have a performance degradation with more and more nodes. The hit wouldn't come straight away at day 1 necessarily (though that depends on how you are sharing out the shared storage and any potential performance concerns there) but you could potentially see a hit in a multi-instance clustering situation (active/active) where you have multiple nodes fail to the same node. That one machine would then be handling the work of multiple instances of SQL Server.

  The link provided by DaniSQL should answer the "how many" question.

  From Mike_Walsh