2

I have a SQL Server 2008 R2 instance on a two node (say names X and Y) Windows server cluster (Windows Server 2008 R2 Enterprise).

Server Manager on both nodes shows 'X' as the 'Current Host Server'.

However, when I run ServerProperty('ComputerNamePhysicalNetBIOS') it returns 'Y' which should be the current active node.

If I RDP to the SQL's IP, it indeed connects to Y.

  • Which one is the correct one?
  • Why does Server Manager shows X as the active node?

Also, ServerProperty('MachineName') returns neither X nor Y but 'Z'. Z is the name of neither computers. What does it represent?

According to BOL for ComputerNamePhysicalNetBIOS (which returns Y as above):

If the instance of SQL Server is in a failover cluster and you want to obtain the name of the failover clustered instance, use the MachineName property.

Then, why does it return Z?

These machines are not virtualized, they are physical. Does it mean Z is the virtual server name of the cluster? "current host server" seems to refer to the "Active Node in the cluster" (from an online forum). I am not entirely sure what is meant by that with two FCIs and one active on each node.

2

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.

0

Community wiki answer:

Z should be the virtual server name of the cluster. I don't use Server Manager so I have no idea what "current host server" means and whether or not it is supposed to represent the active node for the SQL Server clustered instance (which is different from the Windows cluster).

If you had two separate FCIs and one was currently active on each node, what would you expect "current host server" to say? Does Server Manager represent each SQL Server cluster separately or just the windows cluster?

I understand these are physical machines - this is a completely different concept. "Virtual Server Name" is the external name of the SQL Server failover cluster instance - it's how SSMS and your applications connect to SQL Server regardless of which physical node the clustered instance is currently using.

I suspect "current host server" is something completely different and has to do with the Windows cluster, not the SQL Server failover clustered instance sitting on top of Windows.

ComputerNamePhysicalNetBIOS is going to give you the physical name of the currently active node vs MachineName will give you the virtual name of the cluster.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.