Clustering is just a part of an entire strategy needed to help reduce SQL Server downtime. You will also need to have a shared disk array that offers redundancy and make tape backups. So don’t think that clustering is all you need to create a highly available SQL Server system. It is just one part of it.
Chapter 2 – Types of SQL Server Clustering Once you decide to go for clustered SQL Server, you have to choose the cluster layout. This choice is extremely important for architecting the clustering environment and it can be made upon your application and business needs. Let’s look at the configuration types.
Active / Passive
An Active/Passive, or Single Instance cluster, refers to a scenario where only one instance of SQL Server is running on one of the physical node in the cluster, and the other physical node does nothing, other then waiting to takeover should the primary node fail, or a manual failover for maintenance. From a performance perspective, this is the better solution. On the other hand, this option makes less productive use of your physical hardware, which means this solution is more expensive. If an active node fails and there is a passive node available, applications and services running on the failed node can be transferred to the passive node. Since the passive node has no current workload, the server should be able to assume the workload of the failed server without any problems (assuming the hardware of the nodes is the same).
2-Node Clustering Active / Passive Scenario
In this case, let’s look at a two node example, Node X and Node Y. Node X will be configured as Active Node — Primary Owner of SQL Server instance and having that instance running on it. As you can see in the case below, Node Y is in passive or standby mode, doing nothing. The active cluster will be communicating and working along with the shared disks.
2-Node Clustering Active / Passive Failover Scenario When a failover occurs on Node X, SQL Server instance A will get transferred, with all its running processes, connections, and responsibilities to Passive Node Y, and now Node Y will be the Active Node. As you can see, even after the failover, the active cluster is communicating and working with Shared Disks as usual, there is no change.
4-Node Clustering Active / Passive Scenario In this case, let’s look at an example of four nodes, Node X and Node Y, Node XX and Node YY. Node X will be configured as an Active Node — Primary Owner of SQL Server Instance A and Node XX is also an Active Node – Primary Owner of SQL Server Instance AA. As you can see in below case Node Y and YY are in Passive, or Standby mode, doing nothing.