Quite often I ask the question: “Which node is this instance running on right now?” – in a clustered environment. Or ‘What is the name of the physical server where this SQL Server instance is installed?’
So easy, found this great tip, this helps with clustered or virtual sql servers.
-- Physical server name on which the instance is running
SELECT SERVERPROPERTY('ComputerNamePhysicalNetBIOS') AS ServerName
Source:
http://sqlconcept.com/2011/06/16/how-to-find-the-name-of-the-physical-server/