What is the difference between SQL Server clustering methods: - Active/Active - Active/Passive
in simple worlds and an example usage scenario per method...
Active/Active means that both nodes are active and accessing the shared disk resources, but are running independent instances. When a node fails, you need to be sure that the remaining node has the resources available to handle the additional databases that fail over. You can think of it like this. Node A has 1 database on it, and Node B has 1 database on it. Node A goes down, the resources fail over to Node B, and now Node B has 2 databases running on it.
In an Active/Passive cluster, you would only have 1 database running on a single node at any given time. Node A is active with 1 DB, Node B is passive with no DBs. Node A goes down, the resources fail over to Node B. Node B is now active with 1 database running on it.
I'm sure more experienced cluster admins or SQL admins will savage me for my terminology, but that's it in a nutshell.
Holocryptic is spot on with the description, but it might be open to a bit of mis-interpretation.
A SQL Server clustered instance can run on any node in the cluster, but it will only be running on one node. So, if you have two nodes in the cluster, SQLServerA might be running on node 1 - and it can be failed over to run on node 2. A virtual name given to the client/application means that it doesn't matter which node it is running on. This might be called active/passive, because only one node is used - and licensed.
As long as it is licensed correctly, another instance of SQL Server can be installed onto the cluster - it's called instance stacking. It might be running on node 1 like SQLServerA, but it might also be on node 2 - it's independent. If it's on node 2, the cluster might be called active/active.
And so it goes on - you can install more instances of SQL Server - and you can add more nodes, if you edition of SQL Server and Windows allows. Highly available, but becoming increasingly complex to administer.