I have 2 SQL Servers in a cluster.
I need to monitor both the servers in a cluter to find which is up and which is down.
so whenever I do a failover it has to show the first server SQL01 is down-View1 SQL query
I have to do the same thing when the second server down, has to show SQL02 is down-view2 SQL query.
we have got our own inhouse monitor application running on asp.net which can pass the SQL view as a parameter with relevant columns
for monitoring purposes.
I need to crate 2 views like this(at least this is the logic)
CREATE VIEW dbo.SQL01
declare @var1 as varchar(30)
set @var1 = cast(serverproperty('ComputerNamePhysicalNetBIOS') as varchar)
@var1<>'SQL01'(when it is not SQL01,I know that it has been failed over to SQL02)