THE SQL Server Blog Spot on the Web

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

Allen White

Accessing WMI Information Using SMO

While doing some research on SMO I found a couple of very handy features of the WMI accessibility features of SMO. I'm playing with PowerShell because it's easy to browse the SMO objects in that environment, but here are a couple of tips I found useful.

We need to load the SMO assembly into PowerShell - here's the command:

[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SMO")  | out-null

First, when building a script to automate processes I don't want to have to hard code the names of the instances on a given server. How do I find out what instances are installed? We need to instantiate a ManagedComputer object. Once that's done we simply query the ServerInstances collection and have the information we need.

$m = new-object ('Microsoft.SqlServer.Management.Smo.WMI.ManagedComputer') 'MyServer'
$m.ServerInstances | SELECT Name

You can then pipe that into a for-each loop to perform whatever maintenance you wish on each instance.

Another useful piece of information is the IP port number used by a given instance of SQL Server. To get this we need to follow a couple of steps, first getting the specific instance, then drilling down into the ServerProtocols collection.

$i=$m.ServerInstances['MyInstance']
$p=$i.ServerProtocols['Tcp']

Once we've grabbed the TCP protocol object we need to reference 'IPAll' from the IP Address collection, because the port number is set independent of the specific IP address for the server. Here's how we get that.

$ip=$p.IPAddresses['IPAll']

Now we just need to query the value of the 'TcpDynamicPorts' property and we have the port number for that instance.

$ip.IPAddressProperties['TcpDynamicPorts'].Value

When applications run into difficulties connecting to a given server it's important to know the port number so you can set up the client connectivity properly. Having a script to return that information easily will save you time.

Allen

Published Friday, December 28, 2007 1:31 PM by AllenMWhite
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

 

Christopher Rhodes said:

Very helpful.  NB.  I found the WMI stuff in this assembly not the one you listed

Microsoft.SqlServer.SqlWmiManagement

May 15, 2012 9:08 PM
 

AllenMWhite said:

Hi Christopher. The assembly you identified was introduced in SQL Server 2008, and my post was written almost a year before that version was released.  It's still valid for SQL Server 2005.

May 15, 2012 10:12 PM
 

Russell Young said:

when I run this script it works for some of my SQL Server but not all. I am a local admin on all the servers and am running the script with my admin account.,Is there something else I need to initialize or authorize on the servers.  

December 6, 2012 12:24 PM

Leave a Comment

(required) 
(required) 
Submit

About AllenMWhite

Allen White is a consultant and mentor for Upsearch Technology Services in Northeast Ohio. He has worked as a Database Administrator, Architect and Developer for over 30 years, supporting both the Sybase and Microsoft SQL Server platforms over that period.

This Blog

Syndication

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