Sql-server – How do get correct instance name on Multi instance cluster

dmvsql serversql server 2014sql-server-2012

Just a quick question and somehow unable to figure it out.

We have a Active Active cluster or should i say Multi instance cluster as below. node A and B are part of same window cluster

Node A –> Serv1\SQLINSt1

Node B –> Serv2\SQLINSt2

How can i show the info that node A at a time host instance Serv1\SQLINSt1 and node B other instance Serv2\SQLINSt2 because when i use dmv sys.dm_os_cluster nodes and add SERVERPROPERTY ('instancename') it gives same SQL instance for both the nodes

Also will it be possible to show when both the instances come on says node A while node B host no instance?

Much appreciated thanks

Best Answer

On each instance, run:

SELECT
  ServerAndInstanceName = @@SERVERNAME, 
  UnderlyingServerName  = SERVERPROPERTY(N'ComputerNamePhysicalNetBIOS');

If they both show the same UnderlyingServerName then they're both on the same node.

Related Question