November 9, 2025 at 9:54 am
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
November 9, 2025 at 3:57 pm
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 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
Viewing 2 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply