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

MrFlinstone 481 Reputation points
2021-09-23T11:24:54.053+00:00

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
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
12,714 questions
0 comments

2 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Olaf Helper 40,816 Reputation points
    2021-09-23T11:39:57.87+00:00

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

  2. YufeiShao-msft 7,056 Reputation points
    2021-09-24T01:59:47.557+00:00

    Hi @MrFlinstone ，

    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

    0 comments