Active Cluster Node for SQL Server

Question

Post reply

Active Cluster Node for SQL Server

Mohan Kumar

SSCertifiable

Points: 5365
More actions
August 25, 2005 at 2:14 pm

#87335

Hi,
I have active/passive cluster configured for one of my sql server production box. is there any way to find out which cluster node was active for SQL Server before server reboot?
Apprciated ur response...
Thanks,
-Mohan

[font="Verdana"]--www.sqlvillage.com[/size][/font]

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

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

Clovis Celestin SSC Enthusiast Points: 141 More actions · Answer 1

Have you looked into the Cluster Log in C:\WINNT\Cluster\cluster.log

You will find a log in each server.

crasc

Jeff Gray SSChampion Points: 10667 More actions · Answer 2

You can also look in the Application Event Log for information about this. There will at least be startup events for each instance, tagged with the physical server name.

JG

Glauco Costa Mr or Mrs. 500 Points: 547 More actions · Answer 3

See in BOL SERVERPROPERTY

SELECT SERVERPROPERTY ( 'MachineName' ) - this query returns hostname in sql is running

Dylan Peters SSC Eights! Points: 916 More actions · Answer 4

SELECT SERVERPROPERTY ( 'MachineName' )

When I run this it returns the name of the virtual server, not helpful in this case.

Dylan Peters
SQL Server DBA

Jeff Gray SSChampion Points: 10667 More actions · Answer 5

OP wanted what the host name WAS before a problem happened.

If you want the hostname NOW, from T-SQL:

create table #foo (

id tinyint identity(1,1),

txt varchar(255))

Insert #foo (txt)

exec master.dbo.xp_cmdshell 'ping localhost -n 1'

select substring(txt,9,charindex('[',txt)-10) from #foo where id=2

drop table #foo

This works on Windows 2003 Enterprise Edition. Might need to be modified for other OSs.

JG

Linda Johanning SSCertifiable Points: 6727 More actions · Answer 6

I use this. Just put in the node names.

select hostname from sysprocesses

where hostname in ('node1','node2')

Ken Davis SSCarpal Tunnel Points: 4329 More actions · Answer 7

I also like this query to determine which cluster node an instance is running on. This shows the logins that are connected to master db. Look at the 'host name' beside the service account that is running SQL and you will see the cluster node name.

SELECT SUBSTRING(p.loginame,1,20) "User", p.hostname "Host"

FROM sysprocesses as p, sysdatabases as d WITH (NOLOCK)

WHERE d.name = 'master' AND

p.dbid = d.dbid;

Mohan Kumar SSCertifiable Points: 5365 More actions · Answer 8

this will give the current node name. but I am looking for node name which was before last restart.

[font="Verdana"]--www.sqlvillage.com[/size][/font]

Mohan Kumar SSCertifiable Points: 5365 More actions · Answer 9

This gets overwritten on every restart. Therefore, this will not have Active Node name which was before restart. So not useful. Although there is solution available on MS site to avoid this overwriting process but not fully recommended to apply.

[font="Verdana"]--www.sqlvillage.com[/size][/font]

Mohan Kumar SSCertifiable Points: 5365 More actions · Answer 10

I had already checked the event viewer but application log did not provide me any clue. This writes log but does not writes against node name. Tx

[font="Verdana"]--www.sqlvillage.com[/size][/font]

Mohan Kumar SSCertifiable Points: 5365 More actions · Answer 11

Hi All,

Thanks for your response. But I am not looking for node which is currently active. I want to find out node name which was active before last restart.

[font="Verdana"]--www.sqlvillage.com[/size][/font]

Clovis Celestin SSC Enthusiast Points: 141 More actions · Answer 12

You need a tool that keeps track of your availability and registers it somewhere. Where I am we use FOGLIGHT from Quest. The SQL Agent has a table called Availability. This will defintely tell you point in time node status.

You could also write a script that monitors the service status on both your nodes and write it either to a db or a flat file, based on a certain time interval

crasc

Steve Ledridge Grasshopper Points: 22 More actions · Answer 13

I created this sproc and run it from the agent every 5 min.

CREATE PROCEDURE CheckCurrentNode

AS

BEGIN

SET NOCOUNT ON

DECLARE @ServiceName VarChar(255)

DECLARE @HostName VarChar(255)

DECLARE @OldHostName VarChar(255)

DECLARE @Date DateTime

DECLARE @Subject VarChar(2000)

DECLARE @Msg VarChar(8000)

SET @Date = GetDate()

select Distinct @ServiceName = @@SERVICENAME, @HostName = HostName from sysprocesses

where hostname in ('NODE1','NODE2')

-- CREATE TABLE IF IT DOESNT EXIST

IF NOT EXISTS ( SELECT * FROM Master.dbo.sysobjects where name = 'CurrentNode' and Type = 'U')

SELECT @ServiceName AS ServiceName

, @HostName AS HostName

, @Date AS ChangeDate

INTO Master.dbo.CurrentNode

-- CHECK IF SERVICE IS REGISTERED

IF EXISTS(SELECT * FROM Master.dbo.CurrentNode WHERE ServiceName = @ServiceName)

BEGIN

SELECT @OldHostName = HostName FROM Master.dbo.CurrentNode WHERE ServiceName = @ServiceName

-- CHECK IF HOSTNAME HAS CHANGED

IF @OldHostName != @HostName

BEGIN

SET @Subject = @ServiceName + ' has been moved.'

SET @MSG = @ServiceName + ' has been moved from ' + @OldHostName + ' to ' + @HostName + ' at ' + Cast(@Date as VarChar)

exec master.dbo.xp_smtp_sendmail

@FROM = ''zzzzz@yyyyy.com ',

@FROM_NAME = 'WildTangent DBA',

@TO = 'xxxxxx@yyyyy.com',

@replyto = 'zzzzz@yyyyy.com',

@priority = 'HIGH',

@subject = @Subject,

@message = @MSG,

@type = 'text/plain',

@server = 'mail.yyyyy.com'

-- UPDATE ENTRY IF IT HAS CHANGED

UPDATE Master.dbo.CurrentNode SET HostName = @HostName, ChangeDate = @Date WHERE ServiceName = @ServiceName

END

ELSE

BEGIN

-- ADD ENTRY IF ONE DOESNT EXIST

INSERT INTO Master.dbo.CurrentNode SELECT @ServiceName AS ServiceName, @HostName AS HostName, @Date AS ChangeDate

END

MohammedU SSCoach Points: 19644 More actions · Answer 14

You can shedule this run when sql agent starts instead of every five minutes...because without sql start node is not going to change...

Or you make the procedure to run at after sql restart which will remove dependency on the sql agent...using sp_procoption procedure...

MohammedU
Microsoft SQL Server MVP