Where is SQL Server running in a cluster?

I have a PowerShell function that very quickly grabs me the data. Added to my local PS profile I can quickly call the function and grab the node by querying WMI on the SQL virtual name and returning the servername associated with the disk.

Full details along with the script can be grabbed at http://sirsql.net/blog/2010/10/26/checking-sql-nodes-with-powershell.html

Hi Linchi

Just copy-pasted on my machine and pressed Enter, nothing happened

I cycle my error logs every night and put he current node into the sql errorlog ( I also have dashboards which display this info too )

I use this sql in a job.

-- sql engine log

EXEC master.sys.sp_cycle_errorlog;

-- what node are we on?

declare @message varchar(100) = 'The Current Node/Machine is :- '+convert(varchar(100),SERVERPROPERTY('ComputerNamePhysicalNetBIOS'));

exec sys.xp_logevent 66666,@message;

-- sql agent log

exec msdb.dbo.sp_cycle_agent_errorlog;

Sure, what output should I get?

You can also just get that info from the sessions DMV.

Try:

select host_name from sys.dm_exec_sessions where program_name  = 'Microsoft® Windows® Operating System'

Uri;

If sqlservr.exe is running on the server, it'll be in the tasklist result, and findstr would return that line. Otherwise, you don't get anything and that mans that sqlservr.exe is not running on that server.

Agree with grumpdba, serverproperty element "ComputernamePhysicalNetbios" is by far the easiest option for a microsoft windows cluster.

I usually use

Select SERVERPROPERTY('ComputerNamePhysicalNetBios')