SQL Cluster

Question

Post reply

SQL Cluster

Vishal Singh

SSCertifiable

Points: 5830
More actions
August 3, 2009 at 12:41 am

#99817

Dear fellow DBAs,
any quick trick to check if the SQL server has failed over on another node?
-Forum Etiquette: How to post Performance Problems[/url]
-Forum Etiquette: How to post data/code to get the best help [/url]

Viewing 14 posts - 1 through 13 (of 13 total)

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

Chand00 SSCertifiable Points: 6466 More actions · Answer 1

you can check out clusteradministrator

[font="Verdana"]Thanks
Chandra Mohan[/font]

Vishal Singh SSCertifiable Points: 5830 More actions · Answer 2

I know I can check it from CA but I want to check it from SQL Server.

-Forum Etiquette: How to post Performance Problems[/url]

-Forum Etiquette: How to post data/code to get the best help [/url]

Chand00 SSCertifiable Points: 6466 More actions · Answer 3

I think you can use any of these

SELECT HOST_NAME()

[font="Verdana"]Thanks
Chandra Mohan[/font]

Jeffrey Williams SSC Guru Points: 90252 More actions · Answer 4

Two tricks are available, but limited since you are still on 2000. If you upgrade to 2005 - these 'tricks' are very easy to implement.

First trick is to create a startup stored procedure. When SQL Server is started - it will run this stored procedure. In the stored procedure, you send a notification using email to the appropriate group stating that SQL Server has been started and identify the node.

Second trick is to use an agent job that is defined to run when SQL Server starts. I don't know if this is available in 2000, or if it was added for 2005. Again, same concept as above.

For SQL Server 2000 - you can download and install the extended procedure for SMTP mail at http://www.sqldev.net/xp/xpsmtp.htm or you can build a CDOSYS solution.

Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

― Charles R. Swindoll

How to post questions to get better answers faster
Managing Transaction Logs

MANU-J. SSC-Dedicated Points: 31126 More actions · Answer 5

Why don't make use of script specified at http://www.codecomments.com/WSH/message462337.html to read event viewer logs and check for the cluster node failover event and shooting out a mail to dba group.

HTH!

MJ

Vishal Singh SSCertifiable Points: 5830 More actions · Answer 6

I believe SELECT HOST_NAME() can show me different results. for example if in a cluster node1 and node2

If I login to node1 and register SQL server here for both node1 and node2 and then run SELECT HOST_NAME() it will show me as NODE1 for both

If I login to node2 and register SQL server here for both node1 and node2 and then run SELECT HOST_NAME() it will show me as NODE2 for both

what I am looking out here is anything which will help me to find out which node is currently running and which one is down.

also, without using CA 9cluster administrator) will it be possible to check what type of cluster it is and how many node are added to this cluster group?

-Forum Etiquette: How to post Performance Problems[/url]

-Forum Etiquette: How to post data/code to get the best help [/url]

Linda Johanning SSCertifiable Points: 6727 More actions · Answer 7

You can execute sp_who2 and look for the cluster user account under Login. The active node will be the Hostname column. You can create your own procedure to return this value.

As for your second question, I don't know of any other way to get this information other than through cluster administrator.

"also, without using CA 9cluster administrator) will it be possible to check what type of cluster it is and how many node are added to this cluster group? "

2 Tim 3:16 SSCarpal Tunnel Points: 4541 More actions · Answer 8

I put this in a job and schedule it to run at SQL Server startup. I then get notified any time the node fails and what node it is currently running on.

Declare @ComputerNamePhysicalNetBIOS varchar(256)

Set @ComputerNamePhysicalNetBIOS = 'Server [' + @@servername + '] Running on - ' + Convert(varchar(128),SERVERPROPERTY('ComputerNamePhysicalNetBIOS') )

Declare @Subject varchar(256)

Select @Subject = 'Server Fail-Over Alert [' + + @@servername + ']'

EXEC msdb.dbo.sp_send_dbmail

@profile_name = 'DBMail',

@recipients = 'person@mail.com',

@Body = @ComputerNamePhysicalNetBIOS ,

@subject = @Subject ;

Tim White

Jeffrey Williams SSC Guru Points: 90252 More actions · Answer 9

There is nothing in SQL Server that will tell you anything about the cluster specifically. Why do you need to be able to get this information from SQL Server and not the OS?

Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

― Charles R. Swindoll

How to post questions to get better answers faster
Managing Transaction Logs

Jeffrey Williams SSC Guru Points: 90252 More actions · Answer 10

2 Tim 3:16 (8/4/2009)
I put this in a job and schedule it to run at SQL Server startup. I then get notified any time the node fails and what node it is currently running on.
Declare @ComputerNamePhysicalNetBIOS varchar(256)
Set @ComputerNamePhysicalNetBIOS = 'Server [' + @@servername + '] Running on - ' + Convert(varchar(128),SERVERPROPERTY('ComputerNamePhysicalNetBIOS') )
Declare @Subject varchar(256)
Select @Subject = 'Server Fail-Over Alert [' + + @@servername + ']'
EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'DBMail',
@recipients = 'person@mail.com',
@Body = @ComputerNamePhysicalNetBIOS ,
@subject = @Subject ;

Tim - this won't work on SQL Server 2000. It is the same idea I presented earlier and is what I would recommend on 2005/2008 - and it could be basically the same thing if you install the extended procedure to send SMTP mail that I referenced also.

Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

― Charles R. Swindoll

How to post questions to get better answers faster
Managing Transaction Logs