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.
ServerProperty('MachineName') returns neither
'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?
Windows Server Failover Clustering requires a minimum of two computers (Virtual Machines or Physical Machines). Lets refer to these as
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
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
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
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
Server Manager always returns the name of the computer you connect to. In our case, this will be either
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