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

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 SQLVirtualInstance1.

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