Twitter
Tuesday
Oct262010

« Checking SQL Nodes With Powershell »

Have you ever wanted a quick way to check and see what physical node your SQL Server Instance was running on? Yeah, me too.

Within SQL itself this is pretty simple, you can run a quick query


Code Snippet
  1. SELECT serverproperty('ComputerNamePhysicalNetBIOS')

 

This doesn’t work in SQL 2000 though (and sadly I have a few SQL 2000 Instances hanging around). It also requires you to crack open SSMS, connect to the server and run the query.

While working on something else in Powershell I accidentally discovered a dirty rotten hack that will actually give me the information that I am looking for no matter what the SQL version.

Here’s how the hack works out. We use WMI and the Win32_LogicalDisk information to capture the physical server name. We actually pass in the virtual SQL Server name, however it returns the information for the physical. This is based around the fact that a server has a C: drive, so if you systems don’t then you might want to change the filter condition.

 

   1: Function PhysicalNode ($SQLInstance)
   2: {
   3: gwmi Win32_LogicalDisk -ComputerName $SQLInstance -Filter "DriveType=3" | 
   4:     where-object {$_.DeviceID -match "C:"} |  
   5:     Select @{Label="SQL Instance";Expression={"$SQLInstance"}}, 
   6:     @{Label="Physical Node";Expression={$_.SystemName}}
   7: }

Once added to your profile (for more about profiles check out http://msdn.microsoft.com/en-us/library/bb613488(VS.85).aspx ) it’s a simple case of opening powershell and calling the function along with the virtual machine name.


PhysicalNode SQLVirtual


In this example we are looking for the node running SQLVirtual\Instance1.

 

Give it a try and let me know if it works for you.

Reader Comments (3)

PowerShell is the more versitale approach. Depending on the SQL Server Instance you are looking for the host of this is another PowerShell solution:

(Get-WmiObject -query "SELECT GroupComponent FROM MSCluster_NodeToActiveResource WHERE PartComponent = 'MSCluster_Resource.Name=""SQL Server""'" -namespace "root\MSCluster").GroupComponent.Replace('MSCluster_Node.Name=', '').Replace('"', '')

10-27-2010 | Unregistered Commentersqlmashup

When I try running this (or indeed just the select) I get an error stating that it's an invalid class. Is this something available in only Windows 2008+?

10-27-2010 | Unregistered CommenterNic

The MSCluster_NodeToActiveResource class only exists on Clusters but should exist for Windows Server 2003+

10-27-2010 | Unregistered Commentersqlmashup

PostPost a New Comment

Enter your information below to add a new comment.

My response is on my own website »
Author Email (optional):
Author URL (optional):
Post:
 
Some HTML allowed: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <code> <em> <i> <strike> <strong>