August 3, 2009 at 12:41 am
August 3, 2009 at 1:17 am
you can check out clusteradministrator
[font="Verdana"]Thanks
Chandra Mohan[/font]
August 3, 2009 at 1:30 am
August 3, 2009 at 1:49 am
I think you can use any of these
SELECT HOST_NAME()
[font="Verdana"]Thanks
Chandra Mohan[/font]
August 3, 2009 at 9:52 am
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
August 3, 2009 at 6:20 pm
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
August 4, 2009 at 2:14 am
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?
August 4, 2009 at 8:33 am
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? "
August 4, 2009 at 8:38 am
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
August 4, 2009 at 8:40 am
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
August 4, 2009 at 8:55 am
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
August 4, 2009 at 7:03 pm
You can query registry to get that info
August 4, 2009 at 7:06 pm
And you can also created a job with notification which will run every time SQL Server Agent starts...
September 16, 2009 at 4:20 am
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply