Say If I build a 4 node cluster where 3 nodes are active and 1 is passive ,
is it possible theoretically for the 1 node to take over the 3 active nodes
onto itself should all 3 nodes fail ?

Or is clustering setup only enabled for just one node to failover at a time
?

Where can I read more ?

Sure, the spare node can be configured to take all three instances. But in
this particular sceanrio, there will likely be performance hit and the
performance hit could be severe.

Typically, in a 3+1 cluster (i.e. three nodes are configured to run an
instance each in a normal operationg mode, and the fourth node is left
standby), we write the SLA to support failure on a single node at any given
time. If the business is not okay with the performance of running two or more
instances on the spare node, you should condsider having multiple two-node
clusters, each of which runs a single SQL instance.

Linchi

[quoted text, click to view]