Active Cluster Node for SQL Server
-
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]
-
Have you looked into the Cluster Log in C:\WINNT\Cluster\cluster.log
You will find a log in each server.
crasc
-
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
-
See in BOL SERVERPROPERTY
SELECT SERVERPROPERTY ( 'MachineName' ) - this query returns hostname in sql is running
-
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 -
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
-
I use this. Just put in the node names.
select hostname from sysprocesses
where hostname in ('node1','node2')
-
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;
-
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]
-
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]
-
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]
-
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]
-
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
-
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
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
END
-
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
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply