SQL Server – Using Perfmon with Clustering

You want to choose "Add node to a SQL Server failover cluster". When creating a Failover Cluster Instance (FCI), you will always do one initial cluster installation for the instance, then add nodes to that installation.

Keep in mind that Active/Active and Active/Passive as descriptors is very misleading. By it's very nature, each SQL Instance you install can only be active on a single node at any one time. To leverage multiple physical nodes at the same time, you will need to install multiple named FCIs to your cluster.

For example, let's assume a two node cluster. If we install a single named FCI to this, our layout would look like this:

NODE1:  CLSTSQL01\SQLServer01 (Active)
NODE2: 

This would be the result of you performing one "New SQL Server Failover Cluster installation" with the additional node additions for each node in your cluster. Now let's add a second instance to it so we can utilize each node. For this, we would install a second FCI to the cluster and end up with something like this:

NODE1:  CLSTSQL01\SQLServer01 (Active)
NODE2:  CLSTSQL02\SQLServer02 (Active)

Now, keep in mind that in a failover situation, on either node, the SQL instances could end up on the same node. For example, if NODE1 failed:

NODE1: <<DEAD>>
NODE2: CLSTSQL01\SQLServer01, CLSTSQL02\SQLServer02 (Active)

This is the basics of how you would arrange your instances. You will need to monitor your cluster if you want to make sure your instances are properly balanced across your nodes.

Windows Server Failover Clustering requires a minimum of two computers (Virtual Machines or Physical Machines). Lets refer to these as Node01 and Node02.

Part of the install process for the Windows Server Failover Clustering role defines the name of the cluster. For our purposes let's call that MyCluster.

Once you have created MyCluster, you can add SQL Server instances to the cluster as independent "cluster resource groups". We'll refer to our SQL Server clustered instance as MyClusterSQL\INSTANCE.

So, to clarify, we have:

╔════════════════════════╦═══════════════════════╗
║          Item          ║         Names         ║
╠════════════════════════╬═══════════════════════╣
║ Computers              ║ Node01 and Node02     ║
║ Cluster Virtual Server ║ MyCluster             ║
║ SQL Server Instance    ║ MyClusterSQL\INSTANCE ║
╚════════════════════════╩═══════════════════════╝

Now, if you run SELECT SERVERPROPERTY('ComputerNamePhysicalNetbios') it will return either Node01 or Node02 depending on which computer is hosting the SQL Server Instance.

SELECT SERVERPROPERTY('MachineName'); will return MyClusterSQL. The Microsoft Docs for SERVERPROPERTY says this about "MachineName":

Windows computer name on which the server instance is running.

For a clustered instance, an instance of SQL Server running on a virtual server on Microsoft Cluster Service, it returns the name of the virtual server.

SELECT @@SERVERNAME; will return MyClusterSQL\INSTANCE.

Server Manager always returns the name of the computer you connect to. In our case, this will be either Node01 or Node02. If the SQL Server Clustered Instance is running on Node02, and you connect to it using Remote Desktop to the MyClusterSQL server, Server Manager will show Node02, even though you connected to MyClusterSQL.