Using SMO Part I : Enumerating Servers

I should be posting a different but smaller network monitoring application that I have had to code in the last week or so, to help me out at work. I have created one previously but I wanted to tackle a new version since both would demonstrate the amazingly helpful architecture of Server Management Objects(SMO) to help automate some otherwise daunting tasks. I thought it would be helpful if I put together a couple of quick posts concerning SMO in case anyone was wanting to get acquainted with it. This goes along with the direction that I think I am going to take my blog in after getting a lot of reader feedback…Thanks guys! More smaller snippets…the kind that you can take a five minute break to read and then give them a go …longer articles will still be coming but at a slower pace.
Well then, on we go!

SMO is basically the .NET’ified version of DMO. Much more user friendly in the end implementation and from my standpoint a lot more flexible. So in beginning to deal with SMO we would like to do a couple of things.

1. Enumerate Servers…What the heck is out there?
2. Find out things about the server object
3. Find out about the databases
4. Find out about tables
5. Learn how to access extended procedure….this is related to one of the apps so I had to throw it in.

I will tackle all of these in individual posts so the first is enumerating the servers. So what is normally helpful is to get a list of the instances on the network. First things first! You need to add a couple of references to your code. Specifically….

Imports Microsoft.SqlServer.Management.Smo’
Imports Microsoft.SqlServer

Now to get the list of instances. The EnumAvailableSqlServers function returns a datatable so we have to account for that in our actions. Additionally, it accepts a boolean parameter that indicates whether you would like for it to only search for local instances. We want to search the network so we set it as False.

Dim lServers as New List(Of String)

Dim tmpTable as DataTable = SmoApplication.EnumAvailableSqlServers(False)

For Each drow As DataRow In tmpTable.Rows
Dim sServerString as String
sServerString = drow("Server").ToString
If Not drow("Instance") Is Nothing AndAlso drow("Instance").ToString.Length>0 Then
sServerString &= "\" & drow("Instance").ToString
End If

lServers.Add(sServerString)

Next

Easy as that. You now have a list of the server instances on your network.

Cheers,
AJ