Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

Monitoring cluster SQL Server

Posted on 2010-11-19
3
337 Views
Last Modified: 2012-05-10
Hi,

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)
if
@var1<>'SQL01'(when it is not SQL01,I know that it has been failed over to SQL02)


thanks
la
0
Comment
Question by:la-tempestad
  • 2
3 Comments
 
LVL 1

Author Comment

by:la-tempestad
ID: 34179213
We wanted to use our monitoring.aspx program.
It is not a problem for us to get the alerts using our own system.
But in this case the first time I have to use serverproperty function to get some results.
I don't know how to create conditional VIEW's for system functions like this "SERVERPROPERTY"

I have created another VIEW only for finding the cluster server name using,

CREATE VIEW dbo.ClusterName
(ServerName) as select serverproperty('ComputerNamePhysicalNetBIOS')

and when I pass "ServerName" as the field input-I get the active cluster name.

But my problem is how I will write conditions in a view especially for "SERVERPROPERTY" function to find out

New View1 SQL01- condition if Servername <> SQL01 -----(so whenever we fail over, this monitor should goes down as it is not SQL01)
output as SQL02.

New View2 SQL02- condition if ServerName <>SQL02---(same here.)
output as SQL01.

thanks
la
0
 
LVL 1

Author Comment

by:la-tempestad
ID: 34187033
I just found out the solution myself.


CREATE VIEW [dbo].[Server1]
 (Server1)as SELECT cast(serverproperty('ComputerNamePhysicalNetBIOS') as varchar(50))
                   where cast(serverproperty('ComputerNamePhysicalNetBIOS') as varchar(50)) !='SQL01'


Now I can call this view to trigger the monitor page to stay up or down.
if the value is not 'SQL01' in the Server1(field) the monitor goes down.

thanks
la
0
 

Accepted Solution

by:
EE_AutoDeleter earned 0 total points
ID: 37141436
Because you have presented a solution to your own problem which may be helpful to future searches, this question is now PAQed and your points have been refunded.

EE_AutoDeleter
0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

INTRODUCTION: While tying your database objects into builds and your enterprise source control system takes a third-party product (like Visual Studio Database Edition or Red-Gate's SQL Source Control), you can achieve some protection using a sing…
The System Center Operations Manager 2012, known as SCOM, is a part of the Microsoft system center product that provides the user with infrastructure monitoring and application performance monitoring. SCOM monitors:   Windows or UNIX/LinuxNetwo…
This video shows how to quickly and easily add an email signature for all users on Exchange 2016. The resulting signature is applied on a server level by Exchange Online. The email signature template has been downloaded from: www.mail-signatures…

839 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question