Collecting active node hostname at centralized location

Question

Collecting active node hostname at centralized location

anoop.mig29

SSCrazy

Points: 2238
More actions
November 9, 2025 at 9:54 am

#4682771

So folks we have around 30/40 sql server failover cluster insatnce with each having only 2 nodes in server.Eevry we have to server access we need to riase request for both nodes as we donot which oen is active or passive.So i was think of craeting sp which would execute on each FCI node and dump which node is active of passive in table .Then other sp would update similar table on centrailized server throuh link server and then would ran 3 sp on centrailized server which would send emal to our gorup letting us know which is active or pasive node.I iniatllay wanted to include ip but extracting IP is not easy in sql server i guess without using xp_cmdshell which is exepcted to be turned oFF i our eniveornment.We cannot use 3 party apid tools of monitoing which might have reduce our task...
So folkd let me know if my apporcahed is good or it can be refined too

Viewing 8 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

Johan Bijnens SSC Guru Points: 135603 More actions · Answer 1

We export this data to a central file location and then have our central inventory server pickup and process those files.

SELECT SERVERPROPERTY('ServerName') AS [ServerName]
         , SERVERPROPERTY('IsClustered') AS [IsClustered]
--         , 'using a case statement to determine the actual cluster name' as ClusterName
         , SERVERPROPERTY('ComputerNamePhysicalNetBIOS') AS [ActiveHost]
         , NodeName as PassiveHost
         , case convert(int,SERVERPROPERTY('ProductMajorVersion')) 
    when 9 then 2005
    when 10 then 2008
    when 11 then 2012
    when 12 then 2014
    when 13 then 2016
    when 14 then 2017
    when 15 then 2019
                when 16 then 2022
    else 9999
    end as SQLServerVersion
         , SERVERPROPERTY('Edition') AS [Edition]
         , SERVERPROPERTY('ProductLevel') AS [ProductLevel] -- What servicing branch (RTM/SP/CU)
         , SERVERPROPERTY('ProductUpdateLevel') AS [ProductUpdateLevel] -- Within a servicing branch, what CU# is applied
         , SERVERPROPERTY('ProductVersion') AS [ProductVersionNumber]
         , status_description
         , is_current_owner
    FROM sys.dm_os_cluster_nodes WITH(NOLOCK) 
    Where is_current_owner = 0
    order by NodeName
    OPTION(RECOMPILE);

Johan

Learn to play, play to learn !

Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:

- How to post Performance Problems
- How to post data and code to get the best help

- How to prevent a sore throat after hours of presenting ppt

press F1 for solution, press shift+F1 for urgent solution 😀

Who am I ? Sometimes this is me but most of the time this is me

anoop.mig29 SSCrazy Points: 2238 More actions · Answer 2

Johan Bijnens wrote:

We export this data to a central file location and then have our central inventory server pickup and process those files.

SELECT SERVERPROPERTY('ServerName') AS [ServerName]
         , SERVERPROPERTY('IsClustered') AS [IsClustered]
--         , 'using a case statement to determine the actual cluster name' as ClusterName
         , SERVERPROPERTY('ComputerNamePhysicalNetBIOS') AS [ActiveHost]
         , NodeName as PassiveHost
         , case convert(int,SERVERPROPERTY('ProductMajorVersion')) 
    when 9 then 2005
    when 10 then 2008
    when 11 then 2012
    when 12 then 2014
    when 13 then 2016
    when 14 then 2017
    when 15 then 2019
                when 16 then 2022
    else 9999
    end as SQLServerVersion
         , SERVERPROPERTY('Edition') AS [Edition]
         , SERVERPROPERTY('ProductLevel') AS [ProductLevel] -- What servicing branch (RTM/SP/CU)
         , SERVERPROPERTY('ProductUpdateLevel') AS [ProductUpdateLevel] -- Within a servicing branch, what CU# is applied
         , SERVERPROPERTY('ProductVersion') AS [ProductVersionNumber]
         , status_description
         , is_current_owner
    FROM sys.dm_os_cluster_nodes WITH(NOLOCK) 
    Where is_current_owner = 0
    order by NodeName
    OPTION(RECOMPILE);

So how do you do It , i mean you output above data locally in some file like csv and then export/copt at soem centrailized server lcoation and then same file is read by oyr centralized server ????

Have you tried to use /find IP for servers

Johan Bijnens SSC Guru Points: 135603 More actions · Answer 3

Export csv and move to central share to be picked up by central inventory server.

We only monitor specific instances ( 100+ instances )

General /find on our network would cause management boundary issues