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

 

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

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

w

Connecting to %s