question

MrFlinstone-1451 avatar image
0 Votes"
MrFlinstone-1451 asked Yufeishao-msft answered

Get sql connection details from dmv on always on availability group and clustered sql instance

When connecting to either a clustered SQL instance or an instance participating an always on availability group, I would like to get the connection details of the nodes. For always on i looked at sys.dm_hadr_availability_replica_cluster_states and where member_type = 0. I could not get the SQL port number, however. Secondly, is there a DMV I can look at for a standard SQL cluster (active/passive or active/active) ?

Please note that I want to get this information from within a SQL instance only and not from the OS.

sql-server-general
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

OlafHelper-2800 avatar image
0 Votes"
OlafHelper-2800 answered MrFlinstone-1451 commented

You can get the informations with the SERVERPROPERTY (Transact-SQL) function


· 3
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Thanks for this, I read through and I could not see where serverproperty tells me the nodes in the cluster and thier connection details for example, server and port number. Please clarify.

0 Votes 0 ·
 select serverproperty('ComputerNamePhysicalNetBIOS')

Returns the name of the machine = node.

0 Votes 0 ·

That tells me the node name of where SQL is running, I am looking for all nodes in the cluster not just the active/where i am connected to.

0 Votes 0 ·
Yufeishao-msft avatar image
0 Votes"
Yufeishao-msft answered

Hi @MrFlinstone-1451

I think you can try to use sys.dm_os_cluster_nodes, it will return one row for each node in the failover cluster instance configuration




5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.