Skip to content

Detect sql instances

detect sql instances by using :

OSQL

execute osql -L from the command-line (which sends a boradcast on UDP 1434)
If you want more control,  create a socket object in c# and broadcast on udp 1434 with a data value of 0x2 to receieve the announcements from listening instances. 
Note that if an instance is hidden, you’ll not receieve any response…
OSQL also has network  (sub net) issues

SMO

You need to call EnumAvailableSQLServers multiple times.
The reason for this is that some instances may timeout before they’re able to respond to the broadcast on UDP 1434. 

So, if you create a list/array etc and store the results of EnumAvailableSQLServers,
then call EnumAvailableSQLServers again and append any newly-found instances from the second call

cmbServerName.Items.Clear()
Dim dtSQLServers As DataTable = SmoApplication.EnumAvailableSqlServers(False)
Dim I As Integer
For I = 0 To dtSQLServers.Rows.Count - 1
     Dim ServerName As String = dtSQLServers.Rows(I)("Server").ToString()
     If (dtSQLServers.Rows(I)("Instance") IsNot Nothing And dtSQLServers.Rows(I)("Instance").ToString().Length > 0) Then
        ServerName = ServerName + "\" + dtSQLServers.Rows(I)("Instance").ToString()
        cmbServerName.Items.Add(ServerName)
     End If
Next I

If you’re using the .Net Framework 2.0, then you can use the SqlDataSourceEnumerator.GetDataSources() method
(http://msdn2.microsoft.com/en-us/library/system.data.sql.sqldatasourceenumerator.getdatasources.aspx).
keep in mind that since the underlying mechanism relies upon a UDP broadcast, the results are not deterministic.

netstat -o

see what app’as are listening on what ports

select @@servicename  – name of the OS service
select @@servername   – (network) name of the SQL server