Get SQL Servers and Databases Using SMO - July 2009

I recently created an installation program that needed a list of SQL Servers. Google found four different methods. Two use SQL Server Management Objects (SMO), one reads the registry and one uses executable SQLCmd. I wrote a VB.Net Windows application to test the methods. The methods are encapsulated in module cGetSQLServers.vb. I added a method which reports the names of the databases attached to a selected server. This becomes the July 2009 tip-of-the-month.

2011/02/09 - Today I converted the application to VS/2008 using SMO version 10. Both versions are still available. See below.

The Demo Solution - Upon initial execution the demo uses the four methods to find SQL Servers available on the host. It takes a few seconds.

Get SQL Servers
Three servers were found. The default server is HP-IQ804.
An 'X' indicates which method found the server.
The remote Dell1705 and Dell8500 were found using methods 10 and 40.
The local HP-IQ804, with instances, was found using all methods.

The Methods

Click the 'Go To' link to view the referenced source.
Method Method Uses Author Link
10 SmoApplication.EnumAvailableSqlServers Jasper Smith Go To
20 Registry.LocalMachine Jasper Smith Go To
30 SmoApplication.SqlServerRegistrations.EnumRegisteredServers [v9]
SmoApplication.EnumRegisteredServers [v10]
Jasper Smith Go To
40 Using SQLCmd Superna Parajuli Go To

Using the Demo

bLocalOnly - When checked only the local host's SQL Servers are reported.

bAllowDuplicates - When checked all SQL Servers found with the selected methods are reported. If not checked then only the first occurrence of the SQL Server is reported.

The Methods - All or none of the methods can be checked. Methods 10 and 40 are very slow because they search the network for servers. Methods 20 and 30 are very fast because they only search the local host but they will not report unregistered servers.

Get Servers - Click this button to report the SQL Servers found using the selected methods.

Test Connection - Click this button to attempt to connect to the selected SQL Server.

Properties - Click this button to display the SQL Server Properties.

Get Databases - Click this button to display a list of databases attached to the selected SQL Server.

Dialogs

Connected to Server
The 'Test Connection' button was clicked and reported that a
connection to the selected server was successful.


The 'Properties' button was clicked.
Information about the selected SQL Server's settings is reported.

SQL Server Databases
The 'Get Databases' button was clicked.
A list of databases attached to the selected server is displayed.

Failed to Connect
A connection to the selected server failed.

Operative Code - Code similar to this is used to call GetServers in class cGetSQLServers and use properties of class cSQLServer:

Dim oGSS As New cGetSQLServers
With oGSS
    If .GetServers() Then
        For Each o As cSQLServer In .oCollServers
            '...Do Something...
        Next
    End If
End With

Module cGetServers.vb

Module cGetServers.vb contains two classes: cGetServers and cSQLServer. cGetServers contains the methods described in this tip. cSQLServer describes the properties of each entry in collection oCollServers.

Class cGetServers Public Methods

Method Operands Sample Comment
GetServers   If .GetServers Returns True if successful.
Returns a collection of servers in oCollServers.
Format is defined in class cSQLServer
GetDatabases [sServer] If .GetDatabases Returns True if successful.
Returns a collection of database names in oCollDatabaseNames.
Format is string.
Use the default server if sServer is not supplied.
DoConnectSMO [sServer] If .DoConnectSMO Returns True if successful.
Use the default server if sServer is not supplied.
ExistsInCollectionDatabase sDBName, sServerAlreadyAttachedTo   Use this method in conjunction with GetServers to determine if a database exists in any of the servers found in oCollServers.
Returns True if found and  returns the name of the server in string sServerAlreadyAttachedTo.
This method is not demonstrated here.

Class cGetServers Public Properties

Property - All Boolean Comment
bLocalOnly Return only local SQL Servers. Default is False
bAllowDuplicates Use all selected methods. Don't stop with the first one found. Default is False.
bGetUsing10EnumAvailableSqlServers Use this method if True, default is True
bGetUsing20FromRegistry Use this method if True, default is True
bGetUsing30FromEnumRegisteredServers Use this method if True, default is True
bGetUsing40FromSQLCmd Use this method if True, default is True

Class cSQLServer Public Properties

Property - Use as ReadOnly Sample Comment
sName() As String HP-IQ804\SQLEXPRESS The server and instance name
sServer() As String HP-IQ804 The server name.
sInstance() As String SQLEXPRESS The instance if any. Can be blank.
I have never been able to connect to instance MSSQLSERVER.
Does anyone know why?
sVersion() As String 9.00.1399.06 Only method 10 returns a version number.
bLocal() As Boolean True If the local host then True.
b10FromEnumAvailableSqlServers() As Boolean True Is True if this method was used to find the server.
b20FromRegistry() As Boolean False Is True if this method was used to find the server.
b30FromEnumRegisteredServers() As Boolean False Is True if this method was used to find the server.
b40FromSQLCmd() As Boolean False Is True if this method was used to find the server.

Other Modules Used in the Demo

Module - Click link for More Info Comment
modLocationLocationLocation.vb Used to save and get the location of the forms.
Centers dialogs.
modRegRead.vb Read and Write to the registry.
Save selected values between sessions.

Download VB.Net Code

Download - VB.Net Zipped File  Size 
Get SQL Servers VS/2005 SMO v9 GetSqlServers.zip 61K
Get SQL Servers VS/2008 SMO v10 GetSqlServers_SMO10.zip 62K

Previous Tip   Tip Index   Next Tip

Home

Updated 2011/04/16