When failover occurs on Node Y, SQL Server Instance B will be transferred with all its running processes, connections, and responsibilities to active Node X, and now Node X will have two instances A and B, sharing all the resources. When failover occurs on Node YY, SQL Server Instance BB will get transferred with all its running processes, connections, and responsibilities to Active Node XX, and now Node XX will have two instances, AA and BB, sharing all the resources.
In a multi-node configuration where there are more active nodes than passive nodes, the servers can be configured so that under average workload they use a proportional percentage of CPU and memory resources.
Active/Active configuration can have multiple-instance cluster set up, which can support up to 16 SQL Server instances. Windows NT Server 4.0 Enterprise Edition, Windows 2000 Advanced Server, and Window 2003 Advanced Server all support two-node clustering, Windows 2000 Datacenter Server supports up to four-node clustering, and Windows 2003 supports up to eight node clustering, however you are limited to four nodes if SQL Server 2000 clustering is to be used.
SQL Server, in a clustered environment, also behaves differently from a stand-alone named instance in relation to IP ports. During the installation process, a dynamic port that may be something other than 1433 is configured, and that port number is reserved for the instance. In a failover cluster, multiple instances can be configured to share the same port, such as 1433, because the failover cluster listens only to the IP address assigned to the SQL Server virtual server, and is not limited to a 1:1 ratio. However, for security and potentially increased availability, you may want to assign each virtual server to its own unique port of your choice, or leave it as it was configured during installation.
Chapter 3 – Scaling Clustering Resources
You can have up to 16 named instances and 1 default instances per Cluster.
Chapter 4 – How Clustering Works
In a two-cluster node Active / Active setup, if any one of the nodes fail, then the another active node will take over the active resources of the failed instance. It is always preferred while creating two-node cluster using Windows 2000 Advanced Server and Microsoft Clustering Service, that each node be connected to a shared disk array using either fiber channel or SCSI cables.
The shared data in the cluster must be stored on shared disks, otherwise, when a failover occurs; the node which is taking over in the cluster pack cannot access it. As we are already aware, clustering does not help protect data or the shared disk array that it is stored on. So it is very important that you select a shared disk array that is very reliable and includes fault tolerance.
Both nodes of the cluster are also connected to each other via a private network. This private network is used for each node to keep track of the status of the other node. For example, if one of the node experiences a hardware failure, the other node will detect this and will automatically initiate a failover.
When clients initiate a connection, how will they know what to do when a failover occurs? This is the most intelligent part of Microsoft Cluster Services. When a user establishes a connection with SQL Server, it is through SQL Server’s own virtual name and virtual TCP/IP address. This name and address are shared by both of the servers in the cluster. In other words, both nodes can be defined as preferred owners of this virtual name and TCP/IP address.
Usually, a client will connect to the SQL Server cluster using the virtual name used by the cluster. And as far as a client is concerned, there is only one physical SQL Server, not two. Assuming that the X node of the SQL Server cluster is the node running SQL Server ‘A’ in an Active/Active cluster design, then the X node will respond to the client’s requests. But if the X node fails, and failover to the next node Y occurs, the cluster will still retain the same SQL Server virtual name and TCP/IP address ‘A’, although now a new physical server will be responding to client’s requests.
During the failover period, which can last up to several minutes, clients will be unable to access SQL Server, so there is a small amount of downtime when failover occurs. The exact amount of time depends on the number and sizes of the databases on SQL Server, and how active they are.
Chapter 5 – Clustering Terms
Cluster Nodes
A cluster node is a server within a cluster group. A cluster node can be Active or it can be Passive as per SQL Server Instance installation.