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