THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

Linchi Shea

Checking out SQL Server via empirical data points

Where is SQL Server running in a cluster?

There are numerous ways to find where a SQL Server instance is running in a cluster. The most convenient tool was cluster.exe. Unfortunately, I have to say it was the most convenient tool, and no longer is because no single cluster.exe works with all versions of Windows. You could also use the PowerShell cluster cmdlets such as Get-ClusterGroup. But it’s not ubiquitous.

These days, if I have to quickly find out which node a SQL Server instance may be running, I generally run the following command on my workstation:

cmd>tasklist /S ServerNodeName | findstr sqlser

You should run this commend once for each node in the cluster. For me, this is by far the quickest way, primarily because it’s ubiquitous, requires no prior setup, and can be executed anywhere as long as there is connectivity and permission.

It works well with any single SQL Server instance cluster. If you have two or more SQL Server instances in a cluster, you can see where all these instances are running, but you can’t tell one from another. That is, if you use the above command to check all the nodes and find more than one sqlservr.exe, you may not be able to tell which sqlservr.exe is what you are looking for without further checking.

Published Tuesday, December 27, 2011 11:55 PM by Linchi Shea
Filed under:

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

 

Nic Cain said:

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

December 28, 2011 12:09 AM
 

Uri Dimant said:

Hi Linchi

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

December 28, 2011 2:37 AM
 

GrumpyOldDBA said:

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;

December 28, 2011 5:14 AM
 

Linchi Shea said:

Uri;

Did you change ServerNodeName to a node name in your own environment?

December 28, 2011 12:16 PM
 

Uri Dimant said:

Sure, what output should I get?

December 29, 2011 1:01 AM
 

Dan said:

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'

December 29, 2011 11:17 AM
 

Linchi Shea said:

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.

December 30, 2011 12:37 AM
 

Perry Whittle said:

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

December 31, 2011 5:43 AM
 

bunny said:

I usually use

Select SERVERPROPERTY('ComputerNamePhysicalNetBios')

January 3, 2012 9:02 AM
 

maxC said:

When you call the Select SERVERPROPERTY('ComputerNamePhysicalNetBios') on a linked Server, you get the Name of the calling Server ;((

October 4, 2013 8:01 AM

Leave a Comment

(required) 
(required) 
Submit

About Linchi Shea

Checking out SQL Server via empirical data points

This Blog

Syndication

Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement